Small Business Stack

Rename Invoice PDFs in Google Drive from a Tracking Sheet

How can I rename invoice PDFs in Google Drive from a Google Sheets tracking sheet, with a dry run before applying changes?

A spreadsheet-bound Apps Script checks approved Drive file IDs and replacement names, then logs a dry run without renaming files. Review the log, set APPLY_CHANGES to true, and run the script again to rename eligible PDFs.

What you need

  • Google Sheets with an Invoice Rename tab and the specified headers
  • A spreadsheet-bound Apps Script project
  • An executing account with spreadsheet access, target-file access, and rename permission for applying changes

How this script runs

  1. You list file IDs and approved names in a sheet
  2. A dry run logs each planned rename and changes nothing
  3. You review the log
  4. You turn on the apply switch
  5. The script renames only the listed file IDs

A spreadsheet-bound Apps Script reads approved Drive file IDs and replacement names. It first logs a dry run and leaves every filename alone. Review that log, change APPLY_CHANGES to true, and run the script again. The second run reads the tracking sheet in its current state, not a frozen copy of the rows you reviewed.

How should the Invoice Rename tracking sheet be arranged?

Create a tab named Invoice Rename, then put the exact headers below in columns A through C.

File ID Approved filename Approved
PASTE_FILE_ID_A Fictional_OrbitCo_INV-1042_2026-02.pdf YES
PASTE_FILE_ID_B Fictional_MapleDesk_INV-2088_2026-02.pdf YES
PASTE_FILE_ID_C Fictional_OrbitCo_INV-1043_2026-02.pdf YES
PASTE_FILE_ID_D Fictional_MapleDesk_INV-2089_2026-02.pdf NO

The company names are fictional, and the file IDs are placeholders. Replace each placeholder with the ID of a PDF the account running the script can access. To get the ID from a Drive file URL, copy the text between /d/ and the next slash. Set column A to plain text before you paste or enter IDs; otherwise, Sheets may reinterpret them.

In column B, enter the full replacement filename, including .pdf. The script does not read invoice contents. It trims spaces from the start and end of each input value. A row is approved only if its value is YES, with any mix of uppercase and lowercase letters. Each ID identifies a file regardless of its current name, so confirm that it points to the invoice you mean to rename before approving that row.

The approved filename is an instruction, not something the script can verify against the invoice. The script checks the filename format but does not compare it with details inside the PDF. If you are preparing spreadsheet data for an import, see the guide to cleaning a contact spreadsheet for a HubSpot CSV import. For other workflows in a small-business operations setup, visit the small-business stack topic page.

How do you install the invoice renaming script with apply disabled?

Create the script from the tracking spreadsheet, with APPLY_CHANGES set to false for the first dry run.

  1. Open Extensions > Apps Script to make a spreadsheet-bound script.
  2. Replace the starter code with the complete script below.
  3. Keep APPLY_CHANGES = false and save the script.
  4. Select renameInvoicePdfs, then click Run.
  5. Review Google’s authorization prompt and grant only the access that is appropriate. The account running the script needs access to the spreadsheet and target files.
  6. Open the spreadsheet’s Rename Log tab and check the entries.

No API key or secret is needed. To apply changes later, the executing account must also have permission to rename the target files. Treat that as a separate check from reviewing the dry run. A row can pass the script’s checks and still fail when the script tries to rename the file.

const APPLY_CHANGES = false;

function renameInvoicePdfs() {
  const lock = LockService.getDocumentLock();
  lock.waitLock(30000);

  try {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const input = ss.getSheetByName('Invoice Rename');
    if (!input) {
      throw new Error('Create a tab named Invoice Rename.');
    }

    const expectedHeaders = [
      'File ID', 'Approved filename', 'Approved'
    ];
    const actualHeaders =
      input.getRange(1, 1, 1, 3).getDisplayValues()[0];

    if (actualHeaders.some((value, index) =>
      value !== expectedHeaders[index])) {
      throw new Error(
        'A1:C1 must be File ID, Approved filename, Approved.'
      );
    }

    if (input.getLastRow() < 2) {
      throw new Error('Add at least one input row.');
    }

    const rows = input
      .getRange(2, 1, input.getLastRow() - 1, 3)
      .getDisplayValues()
      .map(row => row.map(value => value.trim()));

    const logHeaders = [
      'Run time', 'Mode', 'Sheet row', 'File ID',
      'Current name', 'Approved filename', 'Result', 'Detail'
    ];
    const log = ss.getSheetByName('Rename Log') ||
      ss.insertSheet('Rename Log');

    if (log.getLastRow() === 0) {
      log.getRange(1, 1, 1, logHeaders.length)
        .setValues([logHeaders]);
    } else {
      const headers = log
        .getRange(1, 1, 1, logHeaders.length)
        .getDisplayValues()[0];
      if (headers.some((value, index) =>
        value !== logHeaders[index])) {
        throw new Error(
          'Rename Log headers differ. Archive that tab and rerun.'
        );
      }
    }

    const counts = new Map();
    for (const [id, , approval] of rows) {
      if (approval.toUpperCase() === 'YES' && id) {
        counts.set(id, (counts.get(id) || 0) + 1);
      }
    }

    const runTime = new Date();
    const mode = APPLY_CHANGES ? 'APPLY' : 'DRY_RUN';

    const record = (sheetRow, id, current, desired, result, detail) => {
      const logRow = log.getLastRow() + 1;
      const values = [
        runTime, mode, sheetRow, id,
        current, desired, result, detail
      ].map(safeLogCell);
      log.getRange(logRow, 1, 1, values.length)
        .setValues([values]);
      return logRow;
    };

    const finish = (logRow, result, detail) => {
      log.getRange(logRow, 7, 1, 2)
        .setValues([[result, safeLogCell(detail)]]);
    };

    for (let index = 0; index < rows.length; index++) {
      const [id, desired, approval] = rows[index];
      const sheetRow = index + 2;
      if (!id && !desired && !approval) continue;

      let current = '';
      let attemptRow = 0;

      try {
        if (approval.toUpperCase() !== 'YES') {
          record(
            sheetRow, id, current, desired,
            'SKIPPED', 'Approval is not YES.'
          );
          continue;
        }

        if (!/^[A-Za-z0-9_-]{10,}$/.test(id)) {
          throw new Error('Enter a Drive file ID, not a URL.');
        }
        if (counts.get(id) > 1) {
          throw new Error('Duplicate approved file ID in input.');
        }
        if (!/^.+\.pdf$/i.test(desired) ||
            /[\\/:*?"<>|\x00-\x1F\x7F]/.test(desired)) {
          throw new Error(
            'Use a nonempty PDF name without control characters ' +
            'or any of these: \\ / : * ? " < > |'
          );
        }

        const file = DriveApp.getFileById(id);
        current = file.getName();

        if (file.isTrashed()) {
          throw new Error('Target file is in trash.');
        }
        if (file.getMimeType() !== MimeType.PDF) {
          throw new Error('Target is not a PDF file.');
        }
        if (current === desired) {
          record(
            sheetRow, id, current, desired,
            'UNCHANGED', 'Name already matches.'
          );
          continue;
        }
        if (!APPLY_CHANGES) {
          record(
            sheetRow, id, current, desired,
            'READY', 'Dry run only; no rename performed.'
          );
          continue;
        }

        attemptRow = record(
          sheetRow, id, current, desired,
          'ATTEMPT', 'Rename requested; verify if left unresolved.'
        );
        SpreadsheetApp.flush();

        file.setName(desired);
        finish(attemptRow, 'RENAMED', 'Rename completed.');
      } catch (error) {
        const detail = 'Check Drive before retry: ' +
          String(error && error.message ? error.message : error);
        if (attemptRow) {
          finish(attemptRow, 'ERROR', detail);
        } else {
          record(
            sheetRow, id, current, desired, 'ERROR', detail
          );
        }
      }
    }

    SpreadsheetApp.flush();
  } finally {
    lock.releaseLock();
  }
}

function safeLogCell(value) {
  if (typeof value === 'string' && /^[=+@-]/.test(value)) {
    return "'" + value;
  }
  return value;
}

The filename check rejects a name without the .pdf ending, as well as names containing characters the script disallows. safeLogCell adds a prefix to strings that begin with formula-like characters, which makes Sheets store them as text. To prevent overlapping executions that use the same lock, the script relies on the document lock in LockService.

What should the invoice rename dry-run log show?

With the sample rows, a dry run should report READY, UNCHANGED, or SKIPPED and leave all filenames as they are.

For this example, assume you replaced the placeholders with accessible PDF IDs and the files currently have the names shown here:

Placeholder replaced Current Drive filename
PASTE_FILE_ID_A scan_1042.pdf
PASTE_FILE_ID_B attachment_2088.pdf
PASTE_FILE_ID_C Fictional_OrbitCo_INV-1043_2026-02.pdf
PASTE_FILE_ID_D attachment_2089.pdf

Expected selected log columns:

Mode Sheet row Current name Result
DRY_RUN 2 scan_1042.pdf READY
DRY_RUN 3 attachment_2088.pdf READY
DRY_RUN 4 Fictional_OrbitCo_INV-1043_2026-02.pdf UNCHANGED
DRY_RUN 5 SKIPPED

The complete log also records the requested filenames and additional details. Row 5 has no current name because the script checks whether a row is approved before accessing its Drive file.

READY means the script could read the file and the row passed its validation checks. It does not confirm that the account running the script can rename the file. If you enable apply and that account has the required permission, rows 2 and 3 should change to RENAMED. Rows 4 and 5 keep their existing results.

Each result label has a distinct meaning. READY marks a proposed change in a dry run. UNCHANGED means the current filename already matches the approved one. SKIPPED means the row was not approved. Check any ERROR before another apply run, even if the problem appears to affect only one row.

Why does renameInvoicePdfs rename only approved PDFs while supporting a dry run?

renameInvoicePdfs checks approval and file eligibility for each row before APPLY_CHANGES determines whether to log a preview or request a rename.

Before processing any rows, the script calls getDocumentLock() and waitLock(30000). The LockService reference describes the service and its lock methods. The script then opens the bound spreadsheet, checks the input layout, and reads displayed cell text with getDisplayValues(). It trims each value, so spaces at either end do not affect comparisons. New entries are added after the existing log entries.

Before entering the row loop, the script uses a Map to count approved IDs and identify repeated ones. The logging helpers use one timestamp and mode for the whole run. Before text is written to the log, safeLogCell() handles values that look like formulas.

Inside the row loop, approval is checked before the script tries to open a file. It validates the ID and requested filename, then gets the file by ID rather than searching for its changeable name. The script reads the current name, checks whether the file is in the trash, and verifies its MIME type. The Apps Script File reference documents those file methods. If the current name already matches the requested one, the script skips a redundant rename request. In dry-run mode, it records READY and does not call setName().

With apply enabled, the script records ATTEMPT and flushes the log before calling setName(). It then updates the result. The File reference documents setName(), the method the script uses to change the filename. Row-level catches record errors. The final flush sends pending spreadsheet writes before the finally block releases the lock.

How do you enable invoice renaming after reviewing the log?

After checking the latest dry-run entries, set const APPLY_CHANGES = true;, save the script, and run renameInvoicePdfs again.

Compare each current filename with its approved replacement, and open any unclear file in Drive. Even a valid ID can point to the wrong invoice: the script does not inspect PDF contents. Resolve any errors before applying changes.

The apply run reads the tracking sheet again; it does not use a saved copy of the dry run. If an input changes after you review it, switch apply off and run a fresh dry run. Leave both tabs unchanged while the script runs so the rows remain fixed during processing.

Once changes are applied, set false again, save, and check the newest results appended to the log. Authorization by itself does not enable renaming. The script requests a filename change with setName(); it does not move files, alter PDF contents, or change file IDs. For another Drive naming check, see the guide to finding same-name files in a Google Drive folder without deleting anything.

How do you fix invoice rename ID, permission, and interrupted-run failures?

Fix the input or permission that caused the problem, then check the file in Drive before retrying with apply enabled.

Failure Exact fix
Enter a Drive file ID, not a URL. or placeholder access error Replace the cell with the actual ID, excluding URL punctuation and parameters. Replace every placeholder; rerun with apply disabled.
Duplicate approved file ID in input. Keep one approved row per ID; delete duplicates or mark them NO. Rerun the dry run.
READY followed by an apply permission error Ask the owner or shared-drive manager to grant rename permission, or have an authorized account execute the bound script. Repeat the dry run before applying.
Unresolved ATTEMPT or incomplete execution Disable apply, inspect the affected file in Drive, then rerun the dry run. A matching name becomes UNCHANGED; an eligible unmatched name becomes READY.

The script blocks repeated approved IDs even when those rows request the same filename. Different IDs may have the same filename, so check column B if every requested name needs to be unique. The script does not check for duplicate requested filenames.

Before requesting a rename, the script writes ATTEMPT and flushes the log. If execution stops partway through, the file may already have been renamed even though the log has no success result. Check its current name before trying again. Check errors from an apply run as well, since earlier rows may already have changed. Row-level errors generally allow the script to continue to later rows, and the script does not reverse earlier renames.

A READY result does not test rename permissions. If an apply run reports a permission error, fix the file access and run another dry run. The log records what the script processed, but after an interrupted run, inspect the file itself too.

When should invoice PDFs use another renaming method?

Choose another method for very large invoice queues or for files whose names are controlled by another application.

Individual Drive calls and log writes can run past Apps Script execution limits. Large, recurring batches need chunked jobs, a saved cursor, scheduled executions, and per-file audit logs. Script Properties can hold job state.

For attachments managed by an application, use the naming workflow that application supports. A direct Drive rename may be overwritten or may leave the displayed attachment name inconsistent. I would not use this script for general file migration. It handles a row-by-row list of file IDs and approved names, and its checks are limited to that task.

Sources

Esc