Multiple Google Sheets Async Requests in Google Apps Script

While we can efficiently read and update multiple ranges in Google Sheets with the Google Sheets API Advanced Service in Google Apps Script, there is often occasions where I want to run multiple batch updates on different Google Sheet files.

As you have probably discovered, updating multiple sheets consecutively can be time-consuming and frustrating. I wanted a way to read and update my Google Sheets asynchronously.

Why?

Let’s say I have a bunch of Google Sheets, one each for my users. Perhaps on a daily cadence, I want to gather aggregate data for their manager. Perhaps my manager wants me to add some new spreadsheet features to the current Google Sheets tools. I need an efficient way of updating and reading these sheets.

So was I successful?

Well, mostly.

I discovered that I could run batch updates on up to around 60 Google Sheets somewhat asynchronously.

How did I do this?

I discovered that while “UrlFetchApp.fetchAll()” does not claim to be asynchronous, benchmark research by Kanshi TANAIKE suggests that “fetchAll()” behaves asynchronously.

In conjunction with the Google Sheets API’s “spreadsheets.values” batchGet and batchUpdate requests, we can do a pretty good job updating up to around 60 Google Sheets very quickly.

Let’s dive in!

The Code

The code for this project is set up so that we can generate multiple fetch requests object arrays before sending them to a centralised location to consume the fetch request, check and possibly retry the request and return the results.

Making the code modular like this makes it easier to test and allows you to implement additional requests (Maybe try an append as some homework 😉) with less overhead.

Things to Do Before We Start

Create some tests in Google Sheets

Create an array of test Google Sheets to try out the script. If you are playing along, then add the Google Sheets IDs to the TEST_SHEETS global constant.

const TEST_SHEETS = []

Grant your Own OAuth to the Spreadsheet scope.

Because we are calling Google Sheets using an external service via UrlFetchApp, Google Apps Script will not be able to automatically assign the https://www.googleapis.com/auth/spreadsheets scope.

You can either:

Create a Google Cloud Platform Project, and go through the whole process of setting up OAuth. Ugh! This will give you greater control over assigning scopes to your project, account, a service account or the people using the script.
Add a simple script to call the SpreadsheetApp Google Apps Script API at least once to grant permissions for the project.

Some Global ENUMS for Setup

const REF = {
    VALUE_RENDER_OPTIONS: {
        FORMATTED_VALUE: "FORMATTED_VALUE",
        UNFORMATTED_VALUE: "UNFORMATTED_VALUE",
        FORMULA: "FORMULA",
    },
    DATE_TIME_RENDER_OPTIONS: {
        SERIAL_NUMBER: "SERIAL_NUMBER",
        FORMATTED_STRING: "FORMATTED_STRING",
    },
    VALUE_INPUT_OPTIONS:{
        RAW: "RAW",
        USER_ENTERED: "USER_ENTERED"
    },
    HTTP_METHOD: {
        POST: "POST",
        GET:"GET",
    },
    QUOTAS: {
        MAX_READ_WRITE_PER_MINUTE_PERUSER: 60, // 
    }

}

const REF = {

VALUE_RENDER_OPTIONS: {

FORMATTED_VALUE: "FORMATTED_VALUE",

UNFORMATTED_VALUE: "UNFORMATTED_VALUE",

FORMULA: "FORMULA",

DATE_TIME_RENDER_OPTIONS: {

SERIAL_NUMBER: "SERIAL_NUMBER",

FORMATTED_STRING: "FORMATTED_STRING",

VALUE_INPUT_OPTIONS:{

RAW: "RAW",

USER_ENTERED: "USER_ENTERED"

HTTP_METHOD: {

POST: "POST",

GET:"GET",

QUOTAS: {

MAX_READ_WRITE_PER_MINUTE_PERUSER: 60, //

}

If you have used the Advanced Sheets Service for Google Apps Script in the past, you may be familiar with some of these global variables. Typically, I wrap these up in a quasi-enum like the one you see here and wack it in a Globals.gs file for easy reference.

Alternatively, if you are using Advanced Sheets Service, you can take advantage of content assist in the IDE to access these enums.

Additionally, I have also added some HTTP methods and quotas to the enum for easy reference.

multiSpreadsheetAsyncRequest() – The Main Async Google Sheet Request Function

This is the main function that sends the Google Sheets request via Google Apps Script UrlFetchApp.

🐐You can support me for free by using this Amazon affiliate link in your next tech purchase :Computers & Stuff! 🐐

The multiSpreadsheetAsyncRequest() function takes an array of fetch requests that we will build with our two helper functions later.

Each request is an object containing:

URL – Specific Google Sheets API request including the sheet ID
Id – used to identify the returned fetch request (used for fetch errors)
Method – The HTTP request method.
[Payload] – the optional ‘data’ payload for a POST request like batchUpdate

The function will return 3 object arrays:

requestError: And array of error requests in the format {code, message}.
erroredFetchRequests: Any remaining requests that could not be processed. This is handy if you want to run your own backoff and wait to retry the script.
results: The results of the successfully returned fetch request.

Note that we cannot accurately determine which request failed because requests are not always returned in the same order that they were sent out in.

/**
 * @typedef {Object} sheetsApiRequestParams
 * @property {string} url - Specific Google Sheets API request including the sheet ID
 * @property {string} id - used to identify the returned fetch request (used for fetch errors)
 * @property {string} method - The HTTP request method.
 * @property {string} [payload] - the optional 'data' payload for a POST request like `batchUpdate`
 */

/**
 * Creates a Google Sheets API Advanced Service request on multiple spreadsheets asyncronously.
 * This function takes an array of request objects defined by 
 * 
 * NOTE! Async requests for Sheets API are limited to 60 requests per 60 seconds. 
 * {@link https://developers.google.com/workspace/sheets/api/limits#quota Quotas} 
 * 
 * Returns an object containing:
 * 1. errors array: listing the errors generated in the requests that could not be resolved.
 * 2. erroredFetchRequests array: the remaining fetch requests that threw errors.
 * 3. results array: the array of results. 
 * 
 * @param {sheetsApiRequestParams[]} requests
 * @returns {{requestErrors: *[],erroredFetchRequests: *[], results: *[]}}
 */
function multiSpreadsheetAsyncRequest(requests) {

    // Prepare the fetch
    const oAuthToken = ScriptApp.getOAuthToken()
    const bearerString = `Bearer ${oAuthToken}`
    const ssIdsToCheck = new Map() // Used to find which files need to be retried.


    // Check if there are more than 300 requests.
    const requestCount = requests.length
    if(requestCount > REF.QUOTAS.MAX_READ_WRITE_PER_MINUTE_PERUSER){
        throw new Error(`Too many requests: The max Read/Write quota limit is '${REF.QUOTAS.MAX_READ_WRITE_PER_MINUTE_PERUSER}'.
Try chunking your request into smaller units and setting a sleep for 60 seconds.`)
    }



    // Generate the fetch requests for each request.
    let fetchRequests = requests.map(request => {

        
        const options = {
            "url": request.url,
            "method": request.method,
            "headers": {
                "Authorization": bearerString,
            },          
            "contentType": "application/json",
            "muteHttpExceptions": true,      
        }

        // If the URL fetch request is a POST, we can add our request body in a JSON string.
        if(request.method === REF.HTTP_METHOD.POST && request.payload){
            options.payload = request.payload
        }

        ssIdsToCheck.set(request.id, options)

        return options
    })

    // Exponetial backoff of fetch request variables.
    const maxRetries = 5
    let delay = 250 // In Milliseconds
    let retries = 0

    

    // console.log("fetchRequests", fetchRequests)

    let results = []
    let responses = []

    while(fetchRequests.length > 0 && retries < maxRetries){
        responses = [] // clear the response array for the new fetch cycle. 

        try{
            responses =  UrlFetchApp.fetchAll(fetchRequests)
            

        }catch(e){
            console.error(e)// Perhaps use this for explicit information about certain errors exclusively related to the fetchAll() method not the fetch itself.
            throw new Error(e)
        }

        
        responses.forEach(response => {
            const status = response.getResponseCode()
            if(status >=200 && status <300){
                const responseObject = JSON.parse(response.getContentText())
                results.push(responseObject)
                const responseSsId = responseObject?.spreadsheetId
 
                if(responseSsId){
                    ssIdsToCheck.delete(responseSsId)
                }
 
            }
        })

        // If the ssIdsToCheck contains values, then we get those remaianing ids and add them to fetchRequests
        fetchRequests = Array.from(ssIdsToCheck.values())

        retries++
        if (retries < maxRetries) {
        // Calculate jitter: a random value between 0 and `delay`
        const jitter = Math.random() * delay;
        const sleepTime = delay + jitter;

        console.log(`Retries required for '${fetchRequests.length}' requests: Waiting for ${Math.round((sleepTime / 1000) * 10)/10} seconds before next retry.`);
        Utilities.sleep(sleepTime); // Utilities.sleep takes milliseconds

        // Double the delay for the next attempt
        delay *= 2;
        }
    }

    // If errors still persist after retries add to requestErrors list. 
    const requestErrors = responses.map(response => {
        return {
            code: response.getResponseCode(),
            message: response.getContentText(),
        }
    })


    return {
        requestErrors,
        erroredFetchRequests:  fetchRequests,
        results
    }
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

/**

* @typedef {Object} sheetsApiRequestParams

* @property {string} url - Specific Google Sheets API request including the sheet ID

* @property {string} id - used to identify the returned fetch request (used for fetch errors)

* @property {string} method - The HTTP request method.

* @property {string} [payload] - the optional 'data' payload for a POST request like `batchUpdate`

/**

* Creates a Google Sheets API Advanced Service request on multiple spreadsheets asyncronously.

* This function takes an array of request objects defined by

* NOTE! Async requests for Sheets API are limited to 60 requests per 60 seconds.

* {@link https://developers.google.com/workspace/sheets/api/limits#quota Quotas}

* Returns an object containing:

* 1. errors array: listing the errors generated in the requests that could not be resolved.

* 2. erroredFetchRequests array: the remaining fetch requests that threw errors.

* 3. results array: the array of results.

* @param {sheetsApiRequestParams[]} requests

* @returns {{requestErrors: *[],erroredFetchRequests: *[], results: *[]}}

function multiSpreadsheetAsyncRequest(requests) {

// Prepare the fetch

const oAuthToken = ScriptApp.getOAuthToken()

const bearerString = `Bearer ${oAuthToken}`

const ssIdsToCheck = new Map() // Used to find which files need to be retried.

// Check if there are more than 300 requests.

const requestCount = requests.length

if(requestCount > REF.QUOTAS.MAX_READ_WRITE_PER_MINUTE_PERUSER){

throw new Error(`Too many requests: The max Read/Write quota limit is '${REF.QUOTAS.MAX_READ_WRITE_PER_MINUTE_PERUSER}'.

Try chunking your request into smaller units and setting a sleep for 60 seconds.`)

}

// Generate the fetch requests for each request.

let fetchRequests = requests.map(request => {

const options = {

"url": request.url,

"method": request.method,

"headers": {

"Authorization": bearerString,

"contentType": "application/json",

"muteHttpExceptions": true,

}

// If the URL fetch request is a POST, we can add our request body in a JSON string.

if(request.method === REF.HTTP_METHOD.POST && request.payload){

options.payload = request.payload

}

ssIdsToCheck.set(request.id, options)

return options

})

// Exponetial backoff of fetch request variables.

const maxRetries = 5

let delay = 250 // In Milliseconds

let retries = 0

// console.log("fetchRequests", fetchRequests)

let results = []

let responses = []

while(fetchRequests.length > 0 && retries < maxRetries){

responses = [] // clear the response array for the new fetch cycle.

try{

responses = UrlFetchApp.fetchAll(fetchRequests)

}catch(e){

console.error(e)// Perhaps use this for explicit information about certain errors exclusively related to the fetchAll() method not the fetch itself.

throw new Error(e)

}

responses.forEach(response => {

const status = response.getResponseCode()

if(status >=200 && status <300){

const responseObject = JSON.parse(response.getContentText())

results.push(responseObject)

const responseSsId = responseObject?.spreadsheetId

if(responseSsId){

ssIdsToCheck.delete(responseSsId)

}

})

// If the ssIdsToCheck contains values, then we get those remaianing ids and add them to fetchRequests

fetchRequests = Array.from(ssIdsToCheck.values())

retries++

if (retries < maxRetries) {

// Calculate jitter: a random value between 0 and `delay`

const jitter = Math.random() * delay;

const sleepTime = delay + jitter;

console.log(`Retries required for '${fetchRequests.length}' requests: Waiting for ${Math.round((sleepTime / 1000) * 10)/10} seconds before next retry.`);

Utilities.sleep(sleepTime); // Utilities.sleep takes milliseconds

// Double the delay for the next attempt

delay *= 2;

}

// If errors still persist after retries add to requestErrors list.

const requestErrors = responses.map(response => {

return {

code: response.getResponseCode(),

message: response.getContentText(),

}

})

return {

requestErrors,

erroredFetchRequests: fetchRequests,

results

}

Authorisation

Because we are calling Google Apps Script externally, we need to provide an access token. While you could create a Google Cloud Platform project and generate a service account to handle this, it is easiest to use the active user’s authorisation via ScriptApp.getOAuthToken() and add it to our Authorisation header (Lines 27,28 & 49).

Finding out which Requests Failed

I needed an effective way to find out which requests failed their fetch request so that I can:

Reattempt to send the request
Return the failed request

Each of my requests has a file ID, and I know that each successful request will return a file ID in its response object. This means that I can store a list of file IDs and remove an ID from the list each time my request comes back successfully.

Here I am using a JavaScript Map, called ssIdsToCheck (Line 29), to efficiently store all the request file IDs, and as I build my fetch requests, I will set each request in the Map by the file ID containing its options as its property (Line 60).

Later, I will iterate over the responses and delete any Map properties that have been successfully fetched (Line 98).

The remaining ssIdsToCheck can then rebuild the fetchRequest and we can retry fetching them (Line 105).

Creating the Fetch Request Object

Lines 42-63

Here we set the fetch requests by iterating over the requests function arguments.

We set the URL, method and optional payload from our requests object. The bearer string is generated from the OAuth token at the start of the function.

Also, we set “muteHttpExceptions” to true so that we can handle these exceptions ourselves rather than the script throwing an error.

Exponential Backoff

Fetch requests don’t always return in the same order that they were sent out. While we can determine which requests were successful by their file ID, we can’t know which errors are assigned to which failing fetch request. This means that we need to use a rather clumsy approach to retry all failed requests (The ones remaining in ssIdsToCheck) without basing our retry on the error code, sadly.

On lines 66-68, I set the number of retries to 5, the delay to 250 milliseconds and the retry count starting at 0. This, of course, can be adjusted.

In the while loop that I use to check the fetch responses, on each iteration, I check that the max retries have not been exceeded and my fetchRequests array contains fetches (Line 77).

Then, after I check the data, I check that my retries are less than my max retries. If they are, I sprinkle in a bit of randomness into my delay and add it to my delay time before using the Utilities service to sleep for the prescribed time. I then multiply the delay by two, should I need another retry. (Lines 107-117).

Running the FetchAll request and storing the responses

The fetchAll method of UrlFetchApp, is nested within a JavaScript ‘while loop’ to enable us to reuse the fetch should we need to retry any fetch requests that threw an error.

I have a try-catch statement here that is somewhat redundant because I have declared muteHttpExceptions to be true in each of my requests, preventing errors from being thrown on error codes.

However, you may find an instance where the fetch input parameters are incorrect or there is a state error with the method that you may want to handle more explicitly in the future. Totally up to you or your AI wifu coding buddy.

sheetsApiAsyncGetValuesHttpRequest() – Sheets API Async Get Values Request

The sheetsApiAsyncGetValuesHttpRequest() function is a request object builder designed to simplify the process of generating accurate fetch requests for the Google Sheets API Spreadsheet BatchGet method.

This method allows you to get multiple ranges of values within a single spreadsheet.

The function takes 2 mandatory and 2 optional parameters (indicated by square braces):

ssId – string: The ID of the spreadsheet.
ranges – array of strings: An array of cell ranges in A1Notation. For example:
1. Sheet1!A5:F400
2. Sheet1 for the range of the entire sheet tab
3. 'My Spreadsheet 1'!A500:P1200
4. This_is_a_named_range for named ranges
[valueRenderOption] – string: How the data should be retrieved. Use the REF enum to assist you with this. By default, this is set to FORMATTED_VALUE. Other options are UNFORMATTED_VALUE and FORMULA.
[dateTimeRenderOption] – string: Whether you want dates to appear as strings or Lotus 1-2-3 epoch floats.

The function returns the built batchGet request in the object with the type sheetsApiRequestParams containing a URL, spreadsheet ID and the method type. This is then to be sent to multiSpreadsheetAsyncRequest.

/**
 * Creates a BatchGet request object for a Google Sheets API HTTP async request. 
 * @see {@link https://developers.google.com/workspace/sheets/api/reference/rest/v4/spreadsheets.values/batchGet}
 * 
 * Helper Enums:
 * 1. valueRenderOption: REF.VALUE_RENDER_OPTIONS
 * 2. dateTimeRenderOption: REF.DATE_TIME_RENDER_OPTIONS
 *
 * @param {string} ssId
 * @param {string[]} ranges - Array of ranges in A1 Notation
 * @param {string} [valueRenderOption] - Use: FORMATTED_VALUE, UNFORMATTED_VALUE or FORMULA
 * @param {string} [dateTimeRenderOption] - Use: SERIAL_NUMBER, FORMATTED_STRING
 * @returns {sheetsApiRequestParams}
 */
function sheetsApiAsyncGetValuesHttpRequest(
    ssId, 
    ranges, 
    valueRenderOption = REF.VALUE_RENDER_OPTIONS.FORMATTED_VALUE, 
    dateTimeRenderOption = REF.DATE_TIME_RENDER_OPTIONS.SERIAL_NUMBER
    ){

    const valueRenderOptionsArray = Object.values(REF.VALUE_RENDER_OPTIONS)
    if(!valueRenderOptionsArray.includes(valueRenderOption)){
        throw new Error(`Invalid Value Render Option for your 3rd argument. Either leave blank for default or use the REF.VALUE_RENDER_OPTIONS enum`)
    }

    const dateTimeRenderOptionArray = Object.values(REF.DATE_TIME_RENDER_OPTIONS)
    if(!dateTimeRenderOptionArray.includes(dateTimeRenderOption)){
        throw new Error(`Invalid Date Time Render Option for your 4th argument. Either leave blank for default or use the REF.DATE_TIME_RENDER_OPTIONS enum`)
    }

    const payload = [
        `valueRenderOption=${valueRenderOption}`,
        `dateTimeRenderOption=${dateTimeRenderOption}`,
        ...ranges.map(range =>{
            return `ranges=${range}`
        })
    ].join("&")

    /** @type sheetsApiRequestParams */
    const requestParams = {
        url:`https://sheets.googleapis.com/v4/spreadsheets/${ssId}/values:batchGet?${payload}`,
        id: ssId,
        method: REF.HTTP_METHOD.GET,
    }

    return requestParams
}

/**

* Creates a BatchGet request object for a Google Sheets API HTTP async request.

* @see {@link https://developers.google.com/workspace/sheets/api/reference/rest/v4/spreadsheets.values/batchGet}

* Helper Enums:

* 1. valueRenderOption: REF.VALUE_RENDER_OPTIONS

* 2. dateTimeRenderOption: REF.DATE_TIME_RENDER_OPTIONS

* @param {string} ssId

* @param {string[]} ranges - Array of ranges in A1 Notation

* @param {string} [valueRenderOption] - Use: FORMATTED_VALUE, UNFORMATTED_VALUE or FORMULA

* @param {string} [dateTimeRenderOption] - Use: SERIAL_NUMBER, FORMATTED_STRING

* @returns {sheetsApiRequestParams}

function sheetsApiAsyncGetValuesHttpRequest(

ssId,

ranges,

valueRenderOption = REF.VALUE_RENDER_OPTIONS.FORMATTED_VALUE,

dateTimeRenderOption = REF.DATE_TIME_RENDER_OPTIONS.SERIAL_NUMBER

){

const valueRenderOptionsArray = Object.values(REF.VALUE_RENDER_OPTIONS)

if(!valueRenderOptionsArray.includes(valueRenderOption)){

throw new Error(`Invalid Value Render Option for your 3rd argument. Either leave blank for default or use the REF.VALUE_RENDER_OPTIONS enum`)

}

const dateTimeRenderOptionArray = Object.values(REF.DATE_TIME_RENDER_OPTIONS)

if(!dateTimeRenderOptionArray.includes(dateTimeRenderOption)){

throw new Error(`Invalid Date Time Render Option for your 4th argument. Either leave blank for default or use the REF.DATE_TIME_RENDER_OPTIONS enum`)

}

const payload = [

`valueRenderOption=${valueRenderOption}`,

`dateTimeRenderOption=${dateTimeRenderOption}`,

...ranges.map(range =>{

return `ranges=${range}`

})

].join("&")

/** @type sheetsApiRequestParams */

const requestParams = {

url:`https://sheets.googleapis.com/v4/spreadsheets/${ssId}/values:batchGet?${payload}`,

id: ssId,

method: REF.HTTP_METHOD.GET,

}

return requestParams

}

I do this for the Validation

Lines 22 – 30

First, we validate that the target optional arguments are indeed proper options, instead of wasting valuable requests. This also makes it easier for us to troubleshoot, as oftentimes Google’s fetch error messages can be rather cryptic.

The Payload

Lines 32 – 38

Being a GET request, our payload is going to exist in the URL. FetchAll will generously convert our URL string to web-safe characters, but we do need to set up our request as a string to append to the fetch URL and join the requests with ampersands (&).

TEST

Using your generated sample test spreadsheet IDs array (TEST_SHEETGo ahead and run the script. Feel free to change the ranges and add extra ranges.

// Basic test of 'get' 
function test_sheetsApiAsyncGetValuesHttpRequest(){

    // Using this to initialise the Sheets scope for the first time otherwise we would hard code scopes in the manifest. 
    // This can be hidden after.
    SpreadsheetApp.openById(TEST_SHEETS[0]) 


    const asyncRequests = TEST_SHEETS.map(ssId => {
        return sheetsApiAsyncGetValuesHttpRequest(ssId, ["Sheet1!A1", "Sheet2!A1"])
    })
    const responses = multiSpreadsheetAsyncRequest(asyncRequests)

    console.log(JSON.stringify(responses, null, " "))
}

// Basic test of 'get'

function test_sheetsApiAsyncGetValuesHttpRequest(){

// Using this to initialise the Sheets scope for the first time otherwise we would hard code scopes in the manifest.

// This can be hidden after.

SpreadsheetApp.openById(TEST_SHEETS[0])

const asyncRequests = TEST_SHEETS.map(ssId => {

return sheetsApiAsyncGetValuesHttpRequest(ssId, ["Sheet1!A1", "Sheet2!A1"])

})

const responses = multiSpreadsheetAsyncRequest(asyncRequests)

console.log(JSON.stringify(responses, null, " "))

}

sheetsApiAsyncUpdateValuesHttpRequest() – Google Sheets Api Async Update Values Http Request

The sheetsApiAsyncUpdateValuesHttpRequest() function validates and sets up data to update multiple target ranges in a single Google Sheet.

Because we are creating new data we need to add a few extra parameters into our function:

ssId – string: The Spreadsheet Id
rangeValues – object array: for each range we need to include:
1. startCellA1 – string: We only need the start cell and the rest will be added to fit.
2. valuesMatrix – 2d array: The matrix of values to add to the cell range.
[valueInputOption] – string: How you would like the values to be entered into the sheet. By default, it is USER_ENTERED, but you may change it to RAW. Use the REF enum to help you accurately auto complete.
[includeValuesInResponse] – Boolean: If you want to save memory and improve speed, set this to false so that the values you sent out are not returned back again unnecessarily.
If you do choose to return your responses, you can set those response values with the last two parameters.
[responseValueRenderOption] – string: How you would like the returned values to be displayed. By default, these values are set to, UNFORMATTED_VALUES but you can also choose FORMATTED_VALUES or FORMULA. Use the REF enum to assist you here.
[responseDateTimeRenderOption] – string: This sets the date to either a string with FORMATTED_STRING or the default Lotus 1-2-3 epoch float used in Google Sheets.

The function returns an array of sheetsApiRequestParams types containing:

URL
Sheet ID
Method
Payload

/**
 * Creates a BatchUpdate request object for a Google Sheets API HTTP async request. 
 * @see {@link https://developers.google.com/workspace/sheets/api/reference/rest/v4/spreadsheets.values/batchUpdate}
 * 
 * Helper Enums:
 * 1. ValueInputOption: REF.VALUE_INPUT_OPTIONS
 * 1. valueRenderOption: REF.VALUE_RENDER_OPTIONS
 * 2. dateTimeRenderOption: REF.DATE_TIME_RENDER_OPTIONS
 *
 * @param {string} ssId
 * @param {Object[]} rangeValues - Array of ranges in A1 Notation
 * @param {String} rangeValues[].startCellA1 - The start cell. E.g. `Sheet1A1` or `'My Sheet '!C23`
 * @param {*[][]} rangeValues[].valuesMatrix - 2d array of values. 
 * @param {string} [valueInputOption] - Use: Default: USER_ENTERED. Use RAW or USER_ENTERED
 * @param {boolean} [includeValuesInResponse] - False by default.
 * @param {string} [responseValueRenderOption] - Default: UNFORMATTED_VALUIE Use: FORMATTED_VALUE, UNFORMATTED_VALUE or FORMULA
 * @param {string} [responseDateTimeRenderOption] - Default: SERIAL_NUMBER Use: SERIAL_NUMBER, FORMATTED_STRING
 * @returns {sheetsApiRequestParams}
 */
function sheetsApiAsyncUpdateValuesHttpRequest(
    ssId,
    rangeValues,
    valueInputOption = REF.VALUE_INPUT_OPTIONS.USER_ENTERED,
    includeValuesInResponse = false,
    responseValueRenderOption = REF.VALUE_RENDER_OPTIONS.UNFORMATTED_VALUE,
    responseDateTimeRenderOption = REF.DATE_TIME_RENDER_OPTIONS.SERIAL_NUMBER
){


    const valueInputOptionsArray = Object.values(REF.VALUE_INPUT_OPTIONS)
    if(!valueInputOptionsArray.includes(valueInputOption)){
        throw new Error(`Invalid Responese Value Input Option for your 3rd argument. Either leave blank for default or use the REF.VALUE_INPUT_OPTIONS enum`)
    }

    if(typeof includeValuesInResponse !== 'boolean'){
        throw new Error(`Invalid non-boolean value in Include Values in Response for your 4th argument. Either leave blank for default 'false' or set to 'true'.`)
    }

    const valueRenderOptionsArray = Object.values(REF.VALUE_RENDER_OPTIONS)
    if(!valueRenderOptionsArray.includes(responseValueRenderOption)){
        throw new Error(`Invalid Responese Value Render Option for your 6th argument. Either leave blank for default or use the REF.VALUE_RENDER_OPTIONS enum`)
    }

    const dateTimeRenderOptionArray = Object.values(REF.DATE_TIME_RENDER_OPTIONS)
    if(!dateTimeRenderOptionArray.includes(responseDateTimeRenderOption)){
        throw new Error(`Invalid Response Date Time Render Option for your 6th argument. Either leave blank for default or use the REF.DATE_TIME_RENDER_OPTIONS enum`)
    }

    const data = rangeValues.map(({startCellA1, valuesMatrix}) => {
        return  {
            "range": startCellA1,
            "values": valuesMatrix,
        }
    })

    const payload = {
        "includeValuesInResponse": includeValuesInResponse,
        "responseDateTimeRenderOption": responseDateTimeRenderOption,
        "responseValueRenderOption": responseValueRenderOption,
        "valueInputOption": valueInputOption,
        "data": data,
    }


    /** @type sheetsApiRequestParams */
    const requestParams = {
        url:`https://sheets.googleapis.com/v4/spreadsheets/${ssId}/values:batchUpdate`,
        id: ssId,
        method: REF.HTTP_METHOD.POST,
        payload: JSON.stringify(payload)
    }

    return requestParams
    
}

/**

* Creates a BatchUpdate request object for a Google Sheets API HTTP async request.

* @see {@link https://developers.google.com/workspace/sheets/api/reference/rest/v4/spreadsheets.values/batchUpdate}

* Helper Enums:

* 1. ValueInputOption: REF.VALUE_INPUT_OPTIONS

* 1. valueRenderOption: REF.VALUE_RENDER_OPTIONS

* 2. dateTimeRenderOption: REF.DATE_TIME_RENDER_OPTIONS

* @param {string} ssId

* @param {Object[]} rangeValues - Array of ranges in A1 Notation

* @param {String} rangeValues[].startCellA1 - The start cell. E.g. `Sheet1A1` or `'My Sheet '!C23`

* @param {*[][]} rangeValues[].valuesMatrix - 2d array of values.

* @param {string} [valueInputOption] - Use: Default: USER_ENTERED. Use RAW or USER_ENTERED

* @param {boolean} [includeValuesInResponse] - False by default.

* @param {string} [responseValueRenderOption] - Default: UNFORMATTED_VALUIE Use: FORMATTED_VALUE, UNFORMATTED_VALUE or FORMULA

* @param {string} [responseDateTimeRenderOption] - Default: SERIAL_NUMBER Use: SERIAL_NUMBER, FORMATTED_STRING

* @returns {sheetsApiRequestParams}

function sheetsApiAsyncUpdateValuesHttpRequest(

ssId,

rangeValues,

valueInputOption = REF.VALUE_INPUT_OPTIONS.USER_ENTERED,

includeValuesInResponse = false,

responseValueRenderOption = REF.VALUE_RENDER_OPTIONS.UNFORMATTED_VALUE,

responseDateTimeRenderOption = REF.DATE_TIME_RENDER_OPTIONS.SERIAL_NUMBER

){

const valueInputOptionsArray = Object.values(REF.VALUE_INPUT_OPTIONS)

if(!valueInputOptionsArray.includes(valueInputOption)){

throw new Error(`Invalid Responese Value Input Option for your 3rd argument. Either leave blank for default or use the REF.VALUE_INPUT_OPTIONS enum`)

}

if(typeof includeValuesInResponse !== 'boolean'){

throw new Error(`Invalid non-boolean value in Include Values in Response for your 4th argument. Either leave blank for default 'false' or set to 'true'.`)

}

const valueRenderOptionsArray = Object.values(REF.VALUE_RENDER_OPTIONS)

if(!valueRenderOptionsArray.includes(responseValueRenderOption)){

throw new Error(`Invalid Responese Value Render Option for your 6th argument. Either leave blank for default or use the REF.VALUE_RENDER_OPTIONS enum`)

}

const dateTimeRenderOptionArray = Object.values(REF.DATE_TIME_RENDER_OPTIONS)

if(!dateTimeRenderOptionArray.includes(responseDateTimeRenderOption)){

throw new Error(`Invalid Response Date Time Render Option for your 6th argument. Either leave blank for default or use the REF.DATE_TIME_RENDER_OPTIONS enum`)

}

const data = rangeValues.map(({startCellA1, valuesMatrix}) => {

return {

"range": startCellA1,

"values": valuesMatrix,

}

})

const payload = {

"includeValuesInResponse": includeValuesInResponse,

"responseDateTimeRenderOption": responseDateTimeRenderOption,

"responseValueRenderOption": responseValueRenderOption,

"valueInputOption": valueInputOption,

"data": data,

}

/** @type sheetsApiRequestParams */

const requestParams = {

url:`https://sheets.googleapis.com/v4/spreadsheets/${ssId}/values:batchUpdate`,

id: ssId,

method: REF.HTTP_METHOD.POST,

payload: JSON.stringify(payload)

}

return requestParams

}

A Little More Validation, If You Please

Again, we run through the validation of the optional arguments before we send out the fetch request, so we are not wasting fetches. (Lines 30 – 47)

The Payload

We then iterate over the rangeValues argument setting both the range and values for each section of the sheet, setting them to the data property of the payload.

Unlike our BatchGet request, our payload actually lives in the payload property of the POST request. Here we also include our additional arguments for our request.

TEST

For our test, we generated a two-dimensional array for the two sheets of each of our spreadsheets, running from column A to Z and 1,000 rows deep.

Go ahead and give your test a run. Try and add more data to the range to see how it performs. Include the "includeValuesInResponse" argument to true and then false to see the performance difference.

// Basic test of 'update' 
function test_sheetsApiAsyncUpdateValuesHttpRequest(){
    const arrayRows = 1000
    const arrayColumns = 26

    // Using this to initialise the Sheets scope for the first time otherwise we would hard code scopes in the manifest. 
    // This can be hidden after.
    SpreadsheetApp.openById(TEST_SHEETS[0]) 

    console.log("Total Tests:", TEST_SHEETS.length)


    const asyncRequests = TEST_SHEETS.map((ssId, idx) => {

        const ranges = [
            {
                startCellA1 : "Sheet1!A1",
                valuesMatrix : Array(arrayRows).fill([...Array(arrayColumns).fill(`${idx + 1}.1`)]),
            },
            {
                startCellA1 : "Sheet2!A1",
                valuesMatrix : Array(arrayRows).fill([...Array(arrayColumns).fill(`${idx + 1}.2`)]),
            },
        ]
        

        return sheetsApiAsyncUpdateValuesHttpRequest(ssId, ranges)
            

    })
    const responses = multiSpreadsheetAsyncRequest(asyncRequests)

    console.log(JSON.stringify(responses, null, " "))
}

// Basic test of 'update'

function test_sheetsApiAsyncUpdateValuesHttpRequest(){

const arrayRows = 1000

const arrayColumns = 26

// Using this to initialise the Sheets scope for the first time otherwise we would hard code scopes in the manifest.

// This can be hidden after.

SpreadsheetApp.openById(TEST_SHEETS[0])

console.log("Total Tests:", TEST_SHEETS.length)

const asyncRequests = TEST_SHEETS.map((ssId, idx) => {

const ranges = [

{

startCellA1 : "Sheet1!A1",

valuesMatrix : Array(arrayRows).fill([...Array(arrayColumns).fill(`${idx + 1}.1`)]),

{

startCellA1 : "Sheet2!A1",

valuesMatrix : Array(arrayRows).fill([...Array(arrayColumns).fill(`${idx + 1}.2`)]),

]

return sheetsApiAsyncUpdateValuesHttpRequest(ssId, ranges)

})

const responses = multiSpreadsheetAsyncRequest(asyncRequests)

console.log(JSON.stringify(responses, null, " "))

}

Quotas and Limitations

Sheets API

Reads and Writes

The documentation for Google Sheets API indicates that we have a limit of 300 requests per project and 60 requests per user per project for both read and write. As you might have read above, I have a handler to throw an explicit error before you waste requests, indicating that you have exceeded the user limit.

While I have provided some exponential backoff of failed requests, in the main multiSpreadsheetAsyncRequest(), it is not suited for handling a long wait to rerun 429: Too many requests errors. You will have to roll your own here.

Request Size

Google also recommends keeping your request size to under 20 MB

Create and Publish Google Workspace Add-ons with Apps Script Course 300px

URL Fetch App

For URL Fetch App, you are limited to the following:

URL Fetch response size: 50 MB / call
URL Fetch headers: 100 / call
URL Fetch header size 8 KB / call
URL Fetch POST size 50 MB / call
URL Fetch URL length 2 KB / call

General Quotas and Limitations

Because we are making multiple requests, we may be planning on storing a lot of data in memory. While Google doesn’t give away the secret sauce on this, one developer found that the upper limit is around 2.25 GB.

You may also experience an “Out of memory” error indicating heap exhaustion when attempting to process data greater than 200 MB.

You can learn more about Quotas and Limitations here.

Conclusion

To be fair, 60 Google Sheet updates is not particularly great. It would be nice to be able to increase the read/write threshold. Perhaps I could assign a few service accounts to run the scripts in succession to improve performance, but I don’t really know how effective that would be.

I would love to hear your thoughts in the comments below.

~Yagi.

Multiple Google Sheets Async Requests in Google Apps Script

The Code

Things to Do Before We Start

Create some tests in Google Sheets

Grant your Own OAuth to the Spreadsheet scope.

Some Global ENUMS for Setup

multiSpreadsheetAsyncRequest() – The Main Async Google Sheet Request Function

Authorisation

Finding out which Requests Failed

Creating the Fetch Request Object

Exponential Backoff

Running the FetchAll request and storing the responses

sheetsApiAsyncGetValuesHttpRequest() – Sheets API Async Get Values Request

I do this for the Validation

The Payload

TEST

sheetsApiAsyncUpdateValuesHttpRequest() – Google Sheets Api Async Update Values Http Request

A Little More Validation, If You Please

The Payload

TEST

Quotas and Limitations

Sheets API

Reads and Writes

Request Size

URL Fetch App

General Quotas and Limitations

Conclusion

Like this:

Related

2 thoughts on “Multiple Google Sheets Async Requests in Google Apps Script”

Leave a ReplyCancel reply

The Code

Things to Do Before We Start

Create some tests in Google Sheets

Grant your Own OAuth to the Spreadsheet scope.

Some Global ENUMS for Setup

multiSpreadsheetAsyncRequest() – The Main Async Google Sheet Request Function

Authorisation

Finding out which Requests Failed

Creating the Fetch Request Object

Exponential Backoff

Running the FetchAll request and storing the responses

sheetsApiAsyncGetValuesHttpRequest() – Sheets API Async Get Values Request

I do this for the Validation

The Payload

TEST

sheetsApiAsyncUpdateValuesHttpRequest() – Google Sheets Api Async Update Values Http Request

A Little More Validation, If You Please

The Payload

TEST

Quotas and Limitations

Sheets API

Reads and Writes

Request Size

URL Fetch App

General Quotas and Limitations

Conclusion

Share this:

Like this:

Related

2 thoughts on “Multiple Google Sheets Async Requests in Google Apps Script”

Leave a ReplyCancel reply