Google apps script slow

How To make App Script Fast

 

Lets talk about Is App Script or Google App Script slow in execution ? and if so, how to make it run faster.

Some of my friends says that google app script is very slow in execution. 

They are using excel VBA previously and as per them, running macro in Excel is fast than executing program in google App script.

So guys, what you think? Is Google app scripts really, are slower in execution?

The answer is, it depends....

It depends how you are writing the code and how you are interacting the google Apps with the App scripts.

If the code in App Script is frequently interacting with the google apps while executing the code (like writing back and forth to the google spreadsheet frequently), in such case , the code will become very slow.

This is because, the App script have to interact with Google API again and again, which will take more time and hence code execution will take more time.

So What's the solution. 

The fact is Google app scripts are very very fast. 

You can load data in App script from g sheets or any other source and app script can process millions of rows of data in few seconds and return back the result.

The only thing, the user needs to ensure that there should be minimal call back of google APIs. 

Lets Take 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)

    }

}

Let me explain the code above.

We have created variables oSS, Owkb and oSh for Google spread sheet app, Google active Spreadsheet and active Google sheet.

Then we, use the for loop and ran it 1000 times and read the values from each of the cell from the range in gsheet and then wrote back the processed value by multiplying it by 2.

The above code took 3 minutes to run. Imagine if we have 1 million records, how much time it will required to execute this code..

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.

In method 1, the app script has to interact with gsheet API again and again, it slows down the execution of the program. 

Whereas, in method 2, since the interaction with gsheet API was only twice, the program execution time was reduced significantly. (Infact it took one second to achieve the same result)

Lets see below, how to write the code in 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)

}

 

Let me explain the code above.

We have created variables oSS, oWkb and oSh for Google spread sheet app, Google active Spreadsheet and active Google sheet.

We read the entire data into an array named as inpArray. We also created an output array to store the processed values called as outArr

Thereafter, with the help of for loop, we iterate the Array and stored the processed values in the output array using push method.

Finally, we write back the processed array to gsheet.

I am starting a weekend VBA training course from 06 Jan 2024. If you are interested, please register yourself and go through the course details. Here is the link below: 

VBA Training by Ashwani