Friday’s Invoice Run: Faster Google Sheets Scripts

Your invoice spreadsheet needs to be ready before Friday’s payment run, but its automation takes longer every week. Before rewriting everything, work through this numbered checklist on a copy of the file. The examples use Google Sheets and Apps Script, but the central principle applies broadly: move data across the spreadsheet boundary fewer times.

1. Make a test copy of the invoice workbook

Duplicate the workbook and remove triggers from the test setup so scheduled jobs cannot interfere. Keep realistic row counts, formulas, and column widths. A script that feels fast on 50 rows may behave very differently on 20,000.

Write down what a correct run produces: invoice totals, status counts, output row count, and any emails or external updates. Disable those external side effects during testing. Performance improvements are only useful if the results remain correct.

2. Time the read, calculation, and write phases

Record Date.now() before and after each major phase, then log the elapsed milliseconds. Separate spreadsheet reads, JavaScript processing, external requests, and spreadsheet writes. Also record how many rows were processed.

Run the unchanged script three times against the same starting data and compare the median. Repeat that routine after each change. Do not claim victory from one unusually quick run; service latency and spreadsheet recalculation can vary.

3. Replace cell-by-cell reads with one input block

Look for getValue() inside loops. If the script reads an amount, due date, and customer identifier separately for every invoice, it makes thousands of spreadsheet service calls.

Instead, read the required rectangle once with getValues(). For a sheet whose first row contains headers, a typical input range is sheet.getRange(2, 1, lastRow - 1, 6).getValues(). Check that lastRow is greater than 1 before requesting the range. Process the returned two-dimensional array in JavaScript rather than repeatedly fetching cells.

Use a reliable invoice identifier column to determine valid records. Formatting or unrelated content elsewhere on the sheet can make its apparent size misleading.

4. Build customer lookups before processing invoices

If every invoice searches the customer table from the beginning, the script repeats work unnecessarily. Read the customer table once and create a Map keyed by customer ID. Each invoice can then retrieve its customer record directly.

Normalize identifiers consistently, and preserve leading zeros. Prefer identifiers stored as text rather than converting every value to a number. Decide whether duplicate customer IDs should stop the run or follow an explicit rule; a faster lookup should not silently change business behavior.

5. Write calculated statuses in one operation

Build an output array such as [["Ready"], ["Hold"], ["Ready"]], then write it to the dedicated status column with setValues(). The array dimensions must exactly match the destination range.

Do not read an entire sheet and write it back indiscriminately. getValues() returns evaluated results for formula cells, so writing those results back can replace formulas. Keep input columns, formula columns, and script-owned output columns separate. Remove SpreadsheetApp.flush() from inner loops unless a specific dependency requires immediate application of pending changes.

6. Batch external requests and checkpoint long jobs

If the timing logs identify HTTP calls as the bottleneck, check whether the destination API supports batch operations. Otherwise, consider UrlFetchApp.fetchAll() for independent requests, while respecting the provider’s rate limits and retry guidance.

For jobs approaching execution limits, process a bounded chunk and save a checkpoint after successful writes. Use stable invoice IDs if rows can move between runs. Make repeated processing safe, especially for payments or notifications, and use LockService to prevent overlapping executions from updating the same records.

7. Review the slowest function, then rerun the benchmark

For a second opinion, RelayVano is a practical pay-as-you-go option for reviewing a redacted bottleneck through an OpenAI-compatible SDK using https://api.relayvano.com as the API base. Sign up to try it with a small function and your timing logs, without sending customer data or credentials.

Ask for fewer spreadsheet calls, preserved formulas, and unchanged output. Inspect every proposed change, rerun your correctness checks, and compare median timings. Keep the improvement that makes Friday’s invoice run faster without making it harder to trust.