Google Apps Script Performance Optimisation Tips

Apps Script has a 6-minute execution limit, and even within that, slow scripts are frustrating to work with. Most performance problems come down to the same few patterns — here's how to fix them.

1. Batch reads — never read cells one by one

The single biggest performance win in Sheets scripting is reading all data at once.

// ❌ Slow: 1000 API calls
function slowRead() {
  const sheet = SpreadsheetApp.getActiveSheet();
  for (let i = 1; i <= 1000; i++) {
    const value = sheet.getRange(i, 1).getValue(); // API call each iteration
    Logger.log(value);
  }
}

// ✅ Fast: 1 API call
function fastRead() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const values = sheet.getRange(1, 1, 1000, 1).getValues(); // Single call
  values.forEach(([value]) => Logger.log(value));
}

2. Batch writes — never write cells one by one

// ❌ Slow: 1000 write calls
function slowWrite() {
  const sheet = SpreadsheetApp.getActiveSheet();
  for (let i = 1; i <= 1000; i++) {
    sheet.getRange(i, 2).setValue(i * 2); // API call each iteration
  }
}

// ✅ Fast: 1 write call
function fastWrite() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const results = Array.from({ length: 1000 }, (_, i) => [( i + 1) * 2]);
  sheet.getRange(1, 2, 1000, 1).setValues(results); // Single call
}

3. Avoid alternating reads and writes

Apps Script optimises consecutive reads and consecutive writes. Mixing them forces it to flush the buffer each time.

// ❌ Bad: alternating read/write
function badPattern() {
  const sheet = SpreadsheetApp.getActiveSheet();
  for (let i = 1; i <= 100; i++) {
    const val = sheet.getRange(i, 1).getValue(); // Read
    sheet.getRange(i, 2).setValue(val * 2);      // Write
  }
}

// ✅ Good: read all, process in memory, write all
function goodPattern() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const values = sheet.getRange(1, 1, 100, 1).getValues(); // Read once
  const results = values.map(([val]) => [val * 2]);         // Process in memory
  sheet.getRange(1, 2, 100, 1).setValues(results);          // Write once
}

4. Use getDataRange() instead of getLastRow/getLastColumn separately

// Fine, but two API calls
const lastRow = sheet.getLastRow();
const lastCol = sheet.getLastColumn();
const data = sheet.getRange(1, 1, lastRow, lastCol).getValues();

// Better: one call
const data = sheet.getDataRange().getValues();

5. Cache expensive lookups

If you're looking up the same data repeatedly, cache it in a plain JS object:

function processWithCache() {
  const lookupSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Lookup');
  const lookupData = lookupSheet.getDataRange().getValues();

  // Build a lookup map once
  const lookupMap = {};
  lookupData.slice(1).forEach(([key, value]) => { lookupMap[key] = value; });

  const dataSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const data = dataSheet.getDataRange().getValues();

  const results = data.map(row => {
    const key = row[0];
    return [...row, lookupMap[key] || 'Not found']; // O(1) lookup
  });

  dataSheet.getRange(1, 1, results.length, results[0].length).setValues(results);
}

6. Use SpreadsheetApp.flush() strategically

flush() forces all pending changes to be written. Call it at the end of large operations, or between chunks if you need intermediate commits:

function processInChunks() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const data = sheet.getDataRange().getValues();
  const chunkSize = 200;

  for (let i = 0; i < data.length; i += chunkSize) {
    const chunk = data.slice(i, i + chunkSize);
    const processed = chunk.map(row => [row[0] * 2]);
    sheet.getRange(i + 1, 2, processed.length, 1).setValues(processed);
    SpreadsheetApp.flush(); // Commit this chunk
  }
}

7. Reduce UrlFetchApp calls with caching

See our article on CacheService for full details, but the short version: cache API responses that don't change frequently.

8. Avoid Logger.log in tight loops

Logger.log has overhead. Remove or disable logging in production scripts.

const DEBUG = false; // Toggle for development

function log(msg) {
  if (DEBUG) Logger.log(msg);
}

Quick reference

Bad pattern	Better alternative
`getRange(r,c).getValue()` in loop	`getDataRange().getValues()` once
`appendRow()` in loop	`setValues()` with 2D array
Alternating reads/writes	Read all → process → write all
Repeated `getSheetByName()` calls	Store sheet reference in a variable
No caching for API calls	Use `CacheService`
`Logger.log` in every iteration	Toggle with a `DEBUG` flag