Issue:
- Usage of
.getValue()and.setValue()in a loop resulting in increased processing time.
Documentation excerpts:
- Minimize calls to 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.
- Look ahead caching:
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.
- Minimize “number” of read/writes:
You can write scripts to take maximum advantage of the built-in caching, by minimizing the number of reads and writes.
- Avoid alternating read/write:
Alternating read and write commands is slow
- Use arrays:
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.
Slow script example:
/**
* Really Slow script example
* Get values from A1:D2
* Set values to A3:D4
*/
function slowScriptLikeVBA(){
const ss = SpreadsheetApp.getActive();
const sh = ss.getActiveSheet();
//get A1:D2 and set it 2 rows down
for(var row = 1; row <= 2; row++){
for(var col = 1; col <= 4; col++){
var sourceCellRange = sh.getRange(row, col, 1, 1);
var targetCellRange = sh.getRange(row + 2, col, 1, 1);
var sourceCellValue = sourceCellRange.getValue();//1 read call per loop
targetCellRange.setValue(sourceCellValue);//1 write call per loop
}
}
}
- Notice that two calls are made per loop. There are two loops; 8 read calls and 8 write calls are made in this example for a simple copy paste of 2×4 array.
- In addition, Notice that read and write calls alternated making “look-ahead” caching ineffective.
- Total calls to services: 16
- Time taken: ~5+ seconds
Fast script example:
/**
* Fast script example
* Get values from A1:D2
* Set values to A3:D4
*/
function fastScript(){
const ss = SpreadsheetApp.getActive();
const sh = ss.getActiveSheet();
//get A1:D2 and set it 2 rows down
var sourceRange = sh.getRange("A1:D2");
var targetRange = sh.getRange("A3:D4");
var sourceValues = sourceRange.getValues();//1 read call in total
//modify `sourceValues` if needed
//sourceValues looks like this two dimensional array:
//[//outer array containing rows array
// ["A1","B1","C1",D1], //row1(inner) array containing column element values
// ["A2","B2","C2",D2],
//]
//@see https://stackoverflow.com/questions/63720612
targetRange.setValues(sourceValues);//1 write call in total
}
- Total calls to services: 2
- Time taken: ~0.2 seconds