Google Apps Script

Best Practices

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

  1. Minimizing Calls to Other Services
  2. Using Batch Operations
  3. Using the Cache Service
  4. Using Client Handlers for More Responsive UIs

Minimizing 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.

Using 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 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, 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'");
}

Using 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.

Using Client Handlers for More Responsive UIs

When you're writing UI applications in Google Apps Script, there are times when you want to handle event callbacks in the browser, without using a server-side event handler. One example is an application with a form, where you may want to disable a button after it's clicked the first time. You should use a Client Handler in this scenario, and then your application can respond to the event in the browser without the need to perform a round trip to Google Apps Script's servers. The code below shows how you can use a client handler to make a label visible after the user clicks a button.

function doGet() {
   var app = UiApp.createApplication();
   var button = app.createButton('Say Hello');

   // Create a label with the 'Hello World!' text and hide it for now
   var label = app.createLabel('Hello World!').setVisible(false);

   // Create a new handler that does not require the server.
   // We give the handler two actions to perform on different targets.
   // The first action disables the widget that invokes the handler
   // and the second displays the label.
   var handler = app.createClientHandler()
     .forEventSource().setEnabled(false)
     .forTargets(label).setVisible(true);

   // Add our new handler to be invoked when the button is clicked
   button.addClickHandler(handler);

   app.add(button);
   app.add(label);
   return app;
 }

Authentication required

You need to be signed in with Google+ to do that.

Signing you in...

Google Developers needs your permission to do that.