Make slow scripts fast with batch operations

The single most common reason an Apps Script feels slow is not the amount of data — it is how many times the script talks to Google’s servers. A script that reads and writes one cell at a time will crawl, and on a few thousand rows it will eventually trip the six-minute execution limit and die half-finished.

The fix is almost always the same one move: stop touching the sheet inside the loop. Read everything once, do the work in memory, and write everything back once. This guide shows the pattern and why the speed-up is so dramatic.

The problem

Every call like getValue() or setValue() is a round trip — a separate request from the Apps Script runtime to the Sheets backend and back. Each one costs a few milliseconds of network latency. One call is invisible; a thousand calls inside a loop is a stalled script.

// Slow: 1000 separate round trips to the Sheets server.
for (let i = 1; i <= 1000; i++) {
  // Each setValue() is its own network request — the latency adds up fast.
  sheet.getRange(i, 1).setValue(i * 2);
}

The loop itself is instant. The cost is entirely the 1,000 network round trips hiding inside it. Scale that to 10,000 rows and the script does not just run slowly — it hits the execution-time limit before it finishes.

The fix

Read the whole range in one call, change the values in a plain JavaScript array, then write the whole range back in one call. Two round trips instead of a thousand, no matter how many rows.

// Fast: 2 round trips total, whatever the row count.

// 1. Read the whole column into memory in a single call.
const data = sheet.getRange(1, 1, 1000, 1).getValues();

// 2. Do all the work in memory — no sheet calls inside this loop.
for (let i = 0; i < 1000; i++) {
  data[i][0] = (i + 1) * 2;
}

// 3. Write the whole column back in a single call.
sheet.getRange(1, 1, 1000, 1).setValues(data);

getValues() returns a 2D array — rows of columns — so data[i][0] is row i, first column. You can mutate that array as much as you like; nothing reaches the sheet until setValues() writes the whole block at once.

How big a difference

Batching does not shave off a few percent — it changes the order of magnitude:

Rows	Cell-by-cell	Batched	Speed-up
100	~1 second	instant	~50×
1,000	~20 seconds	~0.1 second	100–200×
10,000	times out	~0.5 second	works vs does not

For any real workload, batching is the difference between a script that finishes and one that does not.

Rule of thumb

• Batch reads: one getValues() over the whole range you need.
• Batch writes: one setValues() over the whole range you are changing.
• Never put getValue() or setValue() inside a loop.
• The same principle applies beyond Sheets — anything that round-trips to a Google service (Drive, Gmail, Properties) is cheaper done in bulk or cached.

Common mistakes

• Mixing the two halves: reading with getValues() but then writing back with setValue() inside a loop. The write side is still a thousand round trips.
• Reading more than you need. getRange('A:A').getValues() pulls every empty row to the bottom of the sheet — read only the used range.
• Writing a jagged array. setValues() requires every row to be the same length and the array dimensions to match the range exactly, or it throws.
• Forgetting that setValues() overwrites the whole block. Any cell content you did not carry through your in-memory array is lost.
• Calling getValues() itself inside a loop — the same trap, one level up. Read once, before the loop starts.