Add searchable tags to files via a Sheet

Google Drive has folders and it has full-text search, but it has no real tagging. Northwind wants to mark files as client-acme, invoice or archive-2025 and pull every matching file in one go — and Drive simply cannot do that natively. Stuffing keywords into filenames is the usual workaround, and it makes filenames an unreadable mess.

This pair of functions keeps the tags in a Sheet instead. One sheet maps a file ID to a comma-separated list of tags. addTag keeps that list tidy, and findFilesByTag reads it back to return every file carrying a given tag. The filenames stay clean; the tags stay searchable.

What you’ll need

• A Google Sheet — the tag index — with two columns: fileId in column A and tags (comma-separated) in column B. Put headers in row 1.
• The Drive file IDs of the files you want to tag. A file ID is the long string in its URL between /d/ and /edit.

The script

// The spreadsheet that maps file IDs to their tags.
const TAGS_SHEET_ID = '1abcFileTagsId';

/**
 * Returns every Drive file tagged with the given tag.
 *
 * @param {string} tag  The tag to search for (case-insensitive).
 * @return {File[]} An array of matching DriveApp File objects.
 */
function findFilesByTag(tag) {
  // 1. Read the tag index, dropping the header row.
  const [, ...rows] = SpreadsheetApp.openById(TAGS_SHEET_ID)
    .getSheets()[0]
    .getDataRange()
    .getValues();

  const wanted = tag.toLowerCase();

  // 2. Keep rows whose tag list contains the wanted tag, then resolve
  //    each file ID to a real Drive file.
  return rows
    .filter((row) => {
      const tags = String(row[1])
        .toLowerCase()
        .split(',')
        .map((s) => s.trim());
      return tags.includes(wanted);
    })
    .map((row) => DriveApp.getFileById(row[0]));
}

/**
 * Adds a tag to a file in the index. Creates a row if the file is not
 * yet listed, and never adds a duplicate tag.
 *
 * @param {string} fileId  The Drive file ID to tag.
 * @param {string} newTag  The tag to add.
 */
function addTag(fileId, newTag) {
  const sheet = SpreadsheetApp.openById(TAGS_SHEET_ID).getSheets()[0];
  const values = sheet.getDataRange().getValues();

  // 1. Find the row for this file, if it already has one.
  const rowIndex = values.findIndex((row) => row[0] === fileId);

  // 2. File not in the index yet — append a fresh row.
  if (rowIndex === -1) {
    sheet.appendRow([fileId, newTag]);
    return;
  }

  // 3. File already listed — read its existing tags.
  const existing = String(values[rowIndex][1] || '')
    .split(',')
    .map((s) => s.trim())
    .filter(Boolean);

  // 4. Add the new tag only if it is not already there, then write back.
  if (!existing.includes(newTag)) {
    existing.push(newTag);
    sheet.getRange(rowIndex + 1, 2).setValue(existing.join(', '));
  }
}

How it works

1. findFilesByTag opens the tag index and reads every row, using array destructuring to skip the header.
2. For each row it splits the column B tag list on commas, trims and lower-cases each tag, and keeps the row only if the wanted tag is in it — so the search is case-insensitive.
3. It maps each surviving file ID through DriveApp.getFileById and returns the resulting File objects, ready to rename, move or share.
4. addTag opens the same sheet and uses findIndex to locate the row for the given file ID.
5. If no row exists, it appends a new one with the file ID and the single tag.
6. If the file is already listed, it parses the existing comma-separated tags, adds the new one only when it is not already present, and writes the joined list back to column B — so tags never duplicate.

Example run

Start with this tag index:

fileId	tags
1aaa...	client-acme, invoice
1bbb...	client-acme, brief

Call addTag('1bbb...', 'archive-2025'). Column B of the second row becomes client-acme, brief, archive-2025.

Call addTag('1ccc...', 'invoice') for a file not yet listed. A new row is appended: 1ccc... | invoice.

Now findFilesByTag('invoice') returns the Drive files for 1aaa... and 1ccc... — the two files carrying that tag.

Run it

These are helper functions you call from other scripts, or test directly:

1. Paste the script into an Apps Script project and set TAGS_SHEET_ID.
2. To tag a file, run addTag with a file ID and tag — temporarily hard-code the arguments, or call it from your own code.
3. To search, call findFilesByTag('your-tag') and loop over the returned files, for example to log their names:

function listTaggedFiles() {
  findFilesByTag('client-acme').forEach((file) => {
    Logger.log(file.getName());
  });
}

Watch out for

• The index can drift from reality. If a file is deleted in Drive, its row stays in the sheet and DriveApp.getFileById will throw when findFilesByTag reaches it. Add a cleanup pass, or wrap the lookup in a try/catch.
• Tag matching is exact after trimming and lower-casing. client-acme and client_acme are different tags — agree on a naming convention up front.
• findFilesByTag resolves every matching file ID on each call. On a large index that means many Drive calls; cache results if you search often.
• There is no locking. Two people running addTag on the same file at once can overwrite each other’s change. For low-volume tagging this is fine; for heavy use, serialise writes with LockService.
• getDataRange reads the whole sheet every call. With thousands of rows this gets slow — consider batching reads if the index grows large.