Reading and writing sheets Updated November 2026

Performance and batching

Each call to the spreadsheet is a network round trip, so the biggest win is to read a block once with getValues, work on the array in memory and write once with setValues. Avoid getValue, setValue, appendRow and format calls inside loops, batch formatting on whole ranges, and cache lookups in a Map.

Syntax

const data = range.getValues(); /* work on the array */ range.setValues(data);

Part	What it is
`SpreadsheetApp.flush()`	Applies pending writes now; only call it when you must read results mid-run
`CacheService`	Short-term key value store (up to 6 hours) for API responses and expensive lookups
`Execution limit`	6 minutes per run (30 minutes for Workspace on some plans), 30 seconds for simple triggers and custom functions

Examples

// slow: one call per cell
for (let r = 2; r <= 1001; r++) {
  const v = sheet.getRange(r, 2).getValue();
  sheet.getRange(r, 3).setValue(v * 2);
}

Result: About 2,000 calls; can take over a minute

// fast: two calls
const vals = sheet.getRange(2, 2, 1000, 1).getValues();
const out = vals.map(r => [r[0] * 2]);
sheet.getRange(2, 3, 1000, 1).setValues(out);

Result: Same result in well under a second

const lookup = new Map(prices.map(r => [r[0], r[1]]));
const totals = orders.map(o => [o[1] * (lookup.get(o[0]) || 0)]);

Result: Price lookups in constant time instead of a nested loop over every order

const cache = CacheService.getScriptCache();
let rates = cache.get("rates");
if (!rates) {
  rates = UrlFetchApp.fetch("https://api.example.com/rates").getContentText();
  cache.put("rates", rates, 1800);
}
const data = JSON.parse(rates);

Result: The API is called at most once every 30 minutes

Common mistakes

Exceeded maximum execution time: the loop calls the sheet per row; move to getValues once and setValues once.
Service invoked too many times for one day: thousands of small calls counted against quota; batching reduces the count dramatically.
Writes seem to happen out of order: Apps Script batches writes; add SpreadsheetApp.flush() if you need to read a result back before continuing.
A sheet-wide read of A:Z takes long: you read up to the maximum rows and columns; size the range with getLastRow and getLastColumn.

Practice Performance and batching

These drills check your answer on live data, free, no account needed.

Memoize`fn` takes one argument. Return a function that computes `fn(x)` only the first time it sees `x` and returns the stored result after that. Arguments are told apart the way a `Map` tells keys apart, so `1` and `"1"` are different. Results such as `0`, `false` and `undefined` are cached too. Each memoized function has its own cache.
Split into chunksSplit `arr` into arrays of `size` items each. The last chunk holds whatever is left, so it can be shorter. `size` is at least 1. Do not change `arr`.
Promise.all with a concurrency limitLike `Promise.all(items.map(fn))`, but with at most `limit` calls of the async function `fn(item)` running at the same time. Resolve with the results in the order of `items`. Start the next item as soon as any running one finishes. If a call rejects, reject. Tests run on a fake clock: `setTimeout`, `clearTimeout` and `Date.now` are simulated, so waits are instant and exact.

Questions

How long can a script run?

6 minutes per execution for most accounts. For bigger jobs, process in chunks and store progress in PropertiesService, then continue from a time-driven trigger.

Is the Advanced Sheets API faster?

For very large batched updates it can be, but for most sheets getValues and setValues on well-sized ranges are fast enough and much simpler.

getRange and getValuesRead Google Sheets data with Apps Script: getRange by A1 notation or row and column numbers, then getValue for one cell or getValues for a 2D array of rows.
setValue and setValuesWrite data to Google Sheets with Apps Script: setValue for one cell, setValues for a 2D array that must match the range size, and the dimension error fix.
Loop through rowsLoop through rows in Google Sheets with Apps Script: read the data once with getValues, loop the 2D array with for, for...of or forEach, then write back.
PropertiesServiceStore settings and secrets in Google Apps Script with PropertiesService: script, user and document properties, setProperty and getProperty, JSON values, keys.