Hide

Best Practices

This document lists best practices that will help you improve the performance of your scripts.

  1. Minimize calls to other services
  2. Use batch operations
  3. Avoid libraries in UI-heavy scripts
  4. Use the Cache service

Minimize calls to other services

Using JavaScript operations within your script is considerably faster than calling other services. Anything you can accomplish within Google Apps Script itself will be much faster than making calls that need to fetch data from Google's servers or an external server, such as requests to Spreadsheets, Docs, Sites, Translate, UrlFetch, and so on. Your scripts will run faster if you can find ways to minimize the calls the scripts make to those services.

Use batch operations

Scripts commonly need to read in data from a spreadsheet, perform calculations, and then write out the results of the data to a spreadsheet. Google Apps Script already has some built-in optimization, such as using look-ahead caching to retrieve what a script is likely to get and write caching to save what is likely to be set.

You can write scripts to take maximum advantage of the built-in caching, by minimizing the number of 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 any operations on the data in the array, and write the data out with one command.

Here's an example — an example you should not follow or use. The Spreadsheet Fractal Art script in the Gallery (only available in the older version of Google Sheets) uses the following code to set the background colors of every cell in a 100 x 100 spreadsheet grid:

// 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 = getColor_(xcoord, ycoord);
    cell.offset(y, x).setBackgroundColor(c);
    xcoord += xincrement;
  }
  ycoord -= yincrement;
  SpreadsheetApp.flush();
}

The script is inefficient: it loops through 100 rows and 100 columns, writing consecutively to 10,000 cells. The Google Apps Script write-back cache helps, because it forces a write-back using flush at the end of every line. Because of the caching, there are only 100 calls to the Spreadsheet.

But the code can be made much more efficient by batching the calls. Here's a rewrite in which the cell range is read into an array called colors, the color assignment operation is performed on the data in the array, and the values in the array are written out to the spreadsheet:

// 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] = getColor_(xcoord, ycoord);
    xcoord += xincrement;
  }
  ycoord -= yincrement;
}
sheet.getRange(1, 1, 100, 100).setBackgroundColors(colors);

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

If you're looking at the Spreadsheet Fractal Art script (only available in the older version of Google Sheets), please be aware that we made a minor change to it to make this example easier to follow. The script as published uses the setBackgroundRGB call, rather than setBackgroundColor, which you see above. The getColor_ function was changed as follows:

if (iteration == max_iteration) {
   return '#000000';
} else {
   var c = 255 - (iteration * 5);
   c = Math.min(255, Math.max(0, c));
   var hex = Number(c).toString(16);
   while (hex.length < 2)
     hex = '0' + hex;

   return ('#' + hex + '3280');
}

Avoid libraries in UI-heavy scripts

Libraries are a convenient way to reuse code, but they slightly increase the time it takes to start the script. This delay isn't noticeable for relatively long-running scripts (like a utility script to clean up your Google Drive files), but for client-side HTML Service user interfaces that make repeated, short-running google.script.run calls, the delay will affect every call. Because of this issue, libraries should not be used in add-ons, and you may want to avoid them in non-add-on scripts that make lots of google.script.run calls.

Use the Cache service

You can use the Cache Service to cache resources between script executions. By caching data, you can reduce the number of times or frequency with which you have to fetch the data. Consider the scenario where you have an RSS feed at example.com that takes 20 seconds to fetch, and you want to speed up access on the average request. The example below shows how to use the Cache Service to speed up access to this data.

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

Now, while you'll have to still wait 20 seconds if the item is not in cache, subsequent accesses will be very fast until the item expires out of the cache in 25 minutes.

Send feedback about...

Apps Script