This document lists best practices that will help you improve the performance of your scripts.
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 Sheets, 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.
Consider collaborating with shared drives
If you are working on a script project with other developers, you can collaborate on Apps Script projects with shared drives . Files in a shared drive are owned by the group, rather than individuals. This makes development and maintenance of the project easier.
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. A script
uses the following code to set the background colors of every cell in a
100 x 100 spreadsheet grid. It uses as function named getColorFromCoordinates()
(not shown here) to determine what color to use
for each cell:
//
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: 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
]
=
getColorFromCoordinates
(
xcoord
,
ycoord
);
xcoord
+=
xincrement
;
}
ycoord
-=
yincrement
;
}
sheet
.
getRange
(
1
,
1
,
100
,
100
).
setBackgrounds
(
colors
);
The inefficient code takes about 70 seconds to run. The efficient code runs in just 1 second!
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
be used sparingly 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
.
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
;
}
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.
