【GAS】自動化したけどスクリプトが遅い！そんなときに高速化するコツ

2021年8月9日

スクリプトを組んで自動化したけど遅いなと感じたときに確認することをまとめました。
「30分制限」や「6分制限」で終了してしまうスクリプトも時間内に終了させられるかもしれません。

forループ内のgetValue()、setValue()の回数を削減する

スプレッドシートの値を取得する「getValue()」や「getValues()」値を入力する「setValue()」「setValues()」は時間がかかります。数回でしたら気にならないのですが、何百回と値を参照するようなコードになると、とたんにボトルネックになることがあります。

下記のように消費税を求めてスプレッドシートに書き出すスクリプトを考えてみます。

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)
  }
}

消費税率は定数としてデータ取得回数は1回ですが、forループ内にgetValue() setValue()があるので数を増えれば増えるほど遅くなっていきます。

改善方法

getValue() setValue()をループの外に出してそれぞれ1回に削減します。

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)              // 税込価格の配列をスプレッドシートに書き込み
}

上記のようにスプレッドシートのデータを配列として取り込み、計算結果を配列で出力してスプレッドシートに配列を書き込むことでスプレッドシートの読み書きの回数を削減しています。

for ループの配列の定義がまどろっこしいですが、スプレッドシートに型にするには2次配列にする必要があるのでこのような方法を採用しています。

forループを他の方法に変更する

forループを他の処理に置き換えることでスピードアップを図ります。過去の記事にセルの検索のスピードアップをする方法を投稿しているので参考にしてください。

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)              // 税込価格の配列をスプレッドシートに書き込み
}

今回は上記のようにmap関数を使用しました。高速化する上でコードもシンプルになり、可読性も上がるのでおすすめです。

どのくらい早くなったのか

ちゃんと測定していませんがこんな感じでした。

  • 1個目のコード　：　約61秒
  • 2個目のコード　：　1秒未満
  • 3個目のコード　：　1秒未満

下記にサンプルコードを記載しておくので実際に体感してみてください。


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