Fast scripts with batch reads and writes

Why scripts get slow

Every getValue() or setValue() is a round trip between your script and Google Sheets. One call is fast; 5,000 calls inside a loop can take minutes and hit the ~6-minute limit.

Slow version — one call per cell:

function slowTotals() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sales');
  const lastRow = sheet.getLastRow();
  for (let r = 2; r <= lastRow; r++) {
    const qty = sheet.getRange(r, 3).getValue();   // call
    const rate = sheet.getRange(r, 4).getValue();  // call
    sheet.getRange(r, 5).setValue(qty * rate);     // call
  }
}

For 2,000 rows that's 6,000 calls.

The pattern: read once → work in memory → write once

Fast version — two calls total, however many rows:

function fastTotals() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sales');
  const lastRow = sheet.getLastRow();
  if (lastRow < 2) return;

  // 1. Read once
  const rows = sheet.getRange(2, 1, lastRow - 1, 4).getValues();

  // 2. Process in memory (plain JavaScript, very fast)
  const out = rows.map(r => [r[2] * r[3]]);

  // 3. Write once
  sheet.getRange(2, 5, out.length, 1).setValues(out);
}

On real data this is often 50–100× faster.

Updating some rows but not others

Read the whole column, change only what's needed in the array, then write the whole column back:

function markPaid() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Invoices');
  const lastRow = sheet.getLastRow();
  const range = sheet.getRange(2, 1, lastRow - 1, 5); // A:E → No, Customer, Amount, Received, Status
  const rows = range.getValues();

  rows.forEach(r => {
    if (r[3] >= r[2]) r[4] = 'Paid';   // Received >= Amount
  });

  range.setValues(rows); // one write
}

Careful: writing back the whole range overwrites formulas in it with their values. Only write back the columns you actually changed if other columns contain formulas.

Batch formatting

Formatting has batch versions too:

const range = sheet.getRange(2, 1, rows.length, 5);
const colours = rows.map(r => {
  const c = r[4] === 'Paid' ? '#d9ead3' : '#ffffff';
  return [c, c, c, c, c];
});
range.setBackgrounds(colours);  // one call instead of one per row

Other batch setters: setFontWeights, setFontColors, setNumberFormats, setFormulas.

When to call SpreadsheetApp.flush()

Apps Script groups writes and applies them later for speed. If you need the sheet to update now — for example, before reading a formula result that depends on what you just wrote, or to show progress to the user — call:

SpreadsheetApp.flush();

Key points

• Never call getValue/setValue inside a big loop.
• Read once → process arrays → write once.
• Use plural setters (setBackgrounds, setValues) for formatting too.
• Don't overwrite formula columns by accident when writing back.