How To Speed Up Google App Script

Is Google App Script slow in execution? If it is, how can we make it run faster?

According to some of my friends, Google Apps Script is perceived as slow in execution. They have previously used Excel VBA and believe that running macros in Excel is faster than executing programs in Google Apps Script.

So, what do you think? Are Google Apps Scripts really slower in execution?

The answer is, it depends...

It depends on how you write the code and how you interact with Google Apps using the App Script.

If the code in the App Script frequently interacts with Google Apps during execution (such as frequent read and write operations to Google Sheets), then the code will run slowly.

This is because the App Script needs to repeatedly interact with the Google API, resulting in longer execution times.

So What's the solution. 

The fact is Google app scripts are very very fast. 

App script can retrieve data from Google Sheets or any other source, process millions of rows of data within seconds, and then return the results. It is important for users to minimize the number of callbacks to Google APIs.

Lets consider an example:

Suppose there are 1,000 records in a gsheet. We want to read all the records and want to process some of the field of each record. 

There could be two ways to do so.

Method #1.  

We can iterate each of the record one by one and for this, we will call the google sheet API 1,000 times to read and write back to the Gsheet. We can see this in below code:

function myFunction() {

  var oSS = SpreadsheetApp

  var oWkb = oSS.getActiveSpreadsheet()

  var oSh = oWkb.getActiveSheet()

  var counter =1

  for (var i = 1; i<1000; i++){

    var myCell = oSh.getRange(i,1)

    myCell.setValue(parseInt(myCell.getValue()) * 2)

    }

}

Allow me to clarify the code provided.

In the code, we have initialized the variables oSS, Owkb, and oSh for the Google Spreadsheet app, active Google Spreadsheet, and active Google sheet respectively.

Subsequently, we utilized a for loop to iterate 1000 times, retrieving values from each cell within a specified range in the Google Sheet. These values were then processed by multiplying them by 2 before being written back.

The execution of the aforementioned code took 3 minutes. Consider the scenario where we have 1 million records - the time required to execute this code would be significantly longer.

Please see the screen shot below:

Then we have an efficient method to achieve the same result in one second.

Method #2

In this method, we will use the arrays. 

With the help of arrays, we can read the entire data from gsheet in one go, keep the data in an array, process the data in the array itself and write back the processed data back to the gsheet in one go.

By doing so, App script will interact only 2 times with gsheet API, i.e once to read the data and then to write the data.

This method took only one second to execute the code. 

Please see the screen shot below:

So, the difference is clear.

When using method 1, the app script needs to repeatedly interact with the gsheet API, causing the program execution to slow down.

Conversely, in method 2, as the interaction with the gsheet API occurred only twice, the program execution time was significantly reduced. (It actually took one second to achieve the same result)

Below, we will explore how to write the code for method 2.

function funcWithArray() {

  var oSS = SpreadsheetApp

  var oWkb = oSS.getActiveSpreadsheet()

  var oSh = oWkb.getActiveSheet()

  var inpArray = oSh.getRange("A1:A1000").getValues()

  var outArr =[]

  for (var i = 0; i<inpArray.length; i++){

    var myCell = inpArray[i]

    myCell = myCell*2

    outArr.push([myCell])

  }

  oSh.getRange("A1:A1000").setValues(outArr)

}

Allow me to clarify the code provided above.

We have initialized variables oSS, oWkb, and oSh for the Google Spreadsheet app, the active Google Spreadsheet, and the active Google sheet, respectively.

We have fetched all the data and stored it in an array named inpArray. Additionally, we have created an output array named outArr to hold the processed values.

Subsequently, utilizing a for loop, we iterate through the array and append the processed values to the output array using the push method.

Finally, we update the processed array in the Google Sheet.

Ashwani