Best Practices

  • To improve script performance, minimize calls to external services.

  • Using shared drives can make collaborating on script projects easier.

  • Batching read and write operations in scripts significantly improves speed.

  • For UI-heavy scripts, limit the use of libraries to avoid performance delays.

  • Utilize the Cache Service to store data between script executions and reduce fetching time.

This document lists best practices to improve script performance.

Minimize calls to other services

Using JavaScript operations within your script is faster than calling other services. Anything you accomplish within Google Apps Script itself is faster than fetching data from Google's servers or an external server, such as requests to Sheets, Docs, Sites, Sites, Translate, and UrlFetch. Your scripts will run faster if you minimize service calls.

Collaborate with shared drives

If you work on a script project with other developers, collaborate using shared drives. Files in a shared drive are owned by the group rather than individuals, making project development and maintenance easier.

Use batch operations

Scripts commonly read data from a spreadsheet, perform calculations, and write results back. Apps Script uses built-in optimization like look-ahead and write caching.

Maximize built-in caching by minimizing reads and writes. Alternating read and write commands is slow. To speed up a script, read all data into an array with one command, perform operations on the array data, and write the data out with one command.

Avoid alternating reads and writes as shown in this inefficient example:

// DO NOT USE THIS CODE. It is an example of SLOW, INEFFICIENT code.
// FOR DEMONSTRATION ONLY
var cell = sheet.getRange('a1');
for (var y = 0; y < 100; y++) {
  xcoord = xmin;
  for (var x = 0; x < 100; x++) {
    var c = getColorFromCoordinates(xcoord, ycoord);
    cell.offset(y, x).setBackgroundColor(c);
    xcoord += xincrement;
  }
  ycoord -= yincrement;
  SpreadsheetApp.flush();
}

The script is inefficient because it loops through 10,000 cells with consecutive writes. While the write-back cache helps, batching calls is much more efficient:

// OKAY TO USE THIS EXAMPLE or code based on it.
var cell = sheet.getRange('a1');
var colors = new Array(100);
for (var y = 0; y < 100; y++) {
  xcoord = xmin;
  colors[y] = new Array(100);
  for (var x = 0; x < 100; x++) {
    colors[y][x] = getColorFromCoordinates(xcoord, ycoord);
    xcoord += xincrement;
  }
  ycoord -= yincrement;
}
sheet.getRange(1, 1, 100, 100).setBackgrounds(colors);

The inefficient code takes about 70 seconds to run, while the efficient code runs in just 1 second.

Avoid libraries in UI-heavy scripts

Libraries are convenient for reusing code but increase script startup time. This delay is noticeable in client-side HTML Service user interfaces that make repeated, short-running google.script.run calls. Use libraries sparingly in add-ons and avoid them in scripts that make many google.script.run calls.

Use the Cache service

Use the Cache service to cache resources between script executions. Caching reduces data fetch frequency. The following example shows how to use the Cache service to speed up access to a slow RSS feed.

function getRssFeed() {
  var cache = CacheService.getScriptCache();
  var cached = cache.get("rss-feed-contents");
  if (cached != null) {
    return cached;
  }
  // This fetch takes 20 seconds:
  var result = UrlFetchApp.fetch("http://example.com/my-slow-rss-feed.xml");
  var contents = result.getContentText();
  cache.put("rss-feed-contents", contents, 1500); // cache for 25 minutes
  return contents;
}

While you still wait 20 seconds if the item isn't in cache, subsequent accesses are fast until the item expires.