Don't Read Cells One at a Time: Service Calls Are the Real Cost

The #1 GAS performance rule: cut the number of service calls. What’s slow isn’t in-memory JavaScript: it’s service calls to Sheets / Drive / UrlFetch like getValue(), setValue(), appendRow(). Repeat them inside a loop and the round-trip cost piles up per call.

Why it matters

Code that reads a cell 1,000 times one-by-one versus code that reads once and processes in memory differs by tens of times in felt speed. A slow script also hits the 6-minute limit sooner. The cost is both “waiting” and “cut off mid-run.”

The fix: read once, process in memory, write once

// Bad: two service calls per row
for (let row = 2; row <= lastRow; row++) {
  const v = sheet.getRange(row, 1).getValue();
  sheet.getRange(row, 2).setValue(transform(v));
}

// Good: 2 calls total (1 read + 1 write)
function updateRows() {
  const sheet = SpreadsheetApp.getActive().getSheetByName("Data");
  const values = sheet.getRange(2, 1, sheet.getLastRow() - 1, 2).getValues();
  const next = values.map(([input]) => [input, transform(input)]);
  sheet.getRange(2, 1, next.length, 2).setValues(next);
}

The same principle elsewhere

• External HTTP: batch independent calls with UrlFetchApp.fetchAll(). Normalize partial failure as { ok, status, body, error } and map responses back by request order.
• Frequently-read values: cache slow-changing external responses or master data in CacheService with a TTL (100KB per key; chunk if larger).
• Sheet structure changes: for heavy formatting, validation, or column-width edits, use the Advanced Sheets API batchUpdate to apply them in one call instead of the cell-value API.

Deeper: the UI too

Calling google.script.run often from the screen makes library-load cost noticeable. UI-heavy apps should minimize library dependencies and draw the shell first, then fetch data asynchronously in one go.

One line to keep: move service calls out of the loop: that’s where speed is decided.