Reading ten ranges with ten getValues() calls means ten round trips. batchGetRanges(spreadsheetId, ranges) reads them all in one call to the Sheets API (Values.batchGet) and returns an object keyed by the range names you passed in.
When to use
- Scripts that pull settings, lookups, and data from several tabs at once
- Reading another spreadsheet by ID without opening it
- Cutting run time in scripts that are close to the time limit
Setup
In the editor, open Services, add Google Sheets API, and keep the identifier Sheets.
How to use this snippet
/**
* Read several A1 ranges in one Sheets API call.
* Needs the Sheets advanced service enabled as "Sheets".
*
* @param {string} spreadsheetId
* @param {string[]} ranges A1 ranges, e.g. ['Config!B2:B10', 'Data!A2:F']
* @return {Object<string, Array<Array<*>>>} values keyed by the range you asked for
*/
function batchGetRanges(spreadsheetId, ranges) {
var out = {};
if (!ranges || !ranges.length) return out;
var res = Sheets.Spreadsheets.Values.batchGet(spreadsheetId, { ranges: ranges });
(res.valueRanges || []).forEach(function (vr, i) {
out[ranges[i]] = vr.values || [];
});
return out;
}
Example
function loadEverything() {
var id = SpreadsheetApp.getActive().getId();
var data = batchGetRanges(id, ['Config!B2:B10', 'Prices!A2:C', 'Orders!A2:H']);
Logger.log(data['Prices!A2:C'].length + ' price rows');
}
Notes
- Keys are the exact strings you passed, not the API's rewritten version (
'Config'!B2:B10). - Empty ranges come back as
[]. Trailing empty rows and columns are trimmed, the same as the API. - Values come back as displayed text by default. Pass
valueRenderOption: 'UNFORMATTED_VALUE'in the options if you need raw numbers.
Happy Coding!
