[GAS] I automated it, but the script is slow! Tips to speed up in such cases

Reduce the number of getValue() and setValue() in the for loop

The following is a list of things to check when you feel that a script has been set up and automated but is slow.
Scripts that end within the “30-minute limit" or “6-minute limit" may be able to be finished within the time limit.

getValue()" and “getValues()" to retrieve values from a spreadsheet, and “setValue()" and “setValues()" to input values are time consuming. This is not a problem for a few times, but when the code references values hundreds of times, it can quickly become a bottleneck.

Consider the following script that asks for sales tax and writes it out to a spreadsheet.

function tax(){
  const ss=SpreadsheetApp.getActiveSpreadsheet()
  const sh=ss.getSheetByName("TAX")

  const TAX=sh.getRange("B2").getValue()     // 消費税率を取得

  let price 
  let tax_price
  for(i=5 ;i<=7 ;i++){
    price     = sh.getRange(i,2).getValue()  // もとの価格を取得
    tax_price = price * (1 + TAX)            // 税込み価格を計算
    sh.getRange(i,3).setValue(tax_price)
  }
}

The consumption tax rate is a constant, and the number of times data is acquired is once, but there is getValue() setValue() in the for loop, so the more the number is increased, the slower it becomes.

Improvement method

getValue() setValue() out of the loop and reduce each to once.

function tax2(){
  const ss=SpreadsheetApp.getActiveSpreadsheet()
  const sh=ss.getSheetByName("TAX")

  const TAX=sh.getRange("B2").getValue()

  let price_list=sh.getRange(5,2,3,1).getValues()        

  let tax_price=[[],[]]
  for(i=0 ;i<3 ;i++){
    tax_price[i]=[]
    tax_price[i][0] = price_list[i][0] * (1 + TAX)      
  }
  sh.getRange(5,3,3,1).setValues(tax_price)           
}

As shown above, the data in the spreadsheet is taken in as an array, the results of the calculation are output as an array, and the array is written to the spreadsheet to reduce the number of times the spreadsheet is read and written.

The definition of the array in the for loop is a bit of a muddle, but this is the way to do it because it needs to be a quadratic array to be typed into the spreadsheet.

Change the for loop to another method

Replace the for loop with another process to speed up the process

function tax3(){
  const ss=SpreadsheetApp.getActiveSpreadsheet()
  const sh=ss.getSheetByName("TAX")

  const TAX=sh.getRange("B2").getValue()

  let price_list=sh.getRange(5,2,3,1).getValues()      

  let tax_price=price_list.map(elm => elm[0] = [elm * (1 + TAX)])

  sh.getRange(5,3,3,1).setValues(tax_price)

In this case, we used the map function as shown above. This is recommended because it simplifies the code and improves readability in addition to speeding up the process.

How fast did it go?

I did not measure it properly, but it looked like this.

1st code : approx. 61 sec.
2nd code : less than 1 sec.
Third code : less than 1 sec.

The following is a sample code so that you can actually experience it.


function tax1(){
  const ROW=200

  const ss=SpreadsheetApp.getActiveSpreadsheet()
  const sh=ss.getSheetByName("TAX")

  const TAX=sh.getRange("B2").getValue()

  let price 
  let tax_price
  for(i=5 ;i<=ROW+4 ;i++){
    price     = sh.getRange(i,2).getValue()  
    tax_price = price * (1 + TAX)            
    sh.getRange(i,3).setValue(tax_price)
  }
}

function tax2(){
  const ROW=200
  const ss=SpreadsheetApp.getActiveSpreadsheet()
  const sh=ss.getSheetByName("TAX")

  const TAX=sh.getRange("B2").getValue()

  let price_list=sh.getRange(5,2,ROW,1).getValues()       

  let tax_price=[[],[]]
  for(i=0 ;i<ROW ;i++){
    tax_price[i]=[]
    tax_price[i][0] = price_list[i][0] * (1 + TAX)     
  }
  sh.getRange(5,3,price_list.length,price_list[0].length).setValues(tax_price)            
}



function tax3(){
  const ROW=200
  const ss=SpreadsheetApp.getActiveSpreadsheet()
  const sh=ss.getSheetByName("TAX")

  const TAX=sh.getRange("B2").getValue()

  let price_list=sh.getRange(5,2,ROW,1).getValues()       

  let tax_price=price_list.map(elm => elm[0] = [elm * (1 + TAX)])

  sh.getRange(5,3,price_list.length,price_list[0].length).setValues(tax_price)             
}



GAS,Javascript

Posted by Next-k