【解決】GASのスプレッドシート実行速度が遅い?実行速度向上のポイントを解説!

Google Apps Scriptを使用してスプレッドシートの操作を行う際、API呼び出しの回数を過度に増やすと、プログラムの実行速度が著しく低下することがよくあります

そこで、本記事ではAPI呼び出しを最小限に抑えつつ、適切な方法でAPIを使用することで、高速なプログラム実行を実現する方法について解説します

この記事を読めば、スプレッドシートのデータ操作において、APIを適切に活用し、プログラムの実行速度を向上させる方法を理解することができます

API呼び出しによる遅延を最小限に抑え、効率的なコードの書き方をマスターしましょう!

こーすけ先生

スキルアップを目指して、一緒に頑張りましょう!

この記事の執筆者について
  • GASの人
  • ITベンダSEとして12年勤務する中で民間、金融、官公庁の現場を一通り経験済
  • 現在は公務員をやりながら起業に向けて着々と準備中
GASなら任せろ!
こーすけ先生

当ブログでは実際に仕事でGASを扱っている私が、GASの魅力について徹底的に取り上げていきます!

目次

GASの実行速度を意識しながらコードを書く

GASに限った話ではないですが、「実行速度」を意識してコードを書くのは非常に重要なことです

実行速度を意識しなければいけない理由
  • 処理に時間がかかるとスクリプトの実行がタイムアウトする可能性がある
  • GASのAPI上で、スクリプトの実行時間が6分までという制約があり、1回の実行で処理できる時間やリソースに限りがある
  • 処理が遅いと、ユーザーは待たされることになるため、不満を抱いたり、離脱したりしてしまう
こーすけ先生

だから、実行速度を意識してコードを書くことが大事なんです!

【事前準備】GASエディタを開く

こーすけ先生

今回は、「コンテナバインド型」でやってみたいと思います!

「コンテナバインド型って何…?」という方は上記の記事を読んでから、トライしてみてください!

まずは、取得元となるスプレッドシートを作りましょう!
私は以下のようなスプレッドシートを作成しました

サンプルなので、この通りである必要はありませんが、これを前提にプログラムを書いていきます!

こーすけ先生

Alt+Aで全選択し、コピー&ペーストしちゃってください!

がすぴょん

「拡張機能」から「Apps Script」を選択するんだね!

【STEP1】スプレッドシート内のすべてのデータを取得する

function test_SpreadSheet() {
  // コンテナバインド型なので、getActiveSpreadsheetにより自分自身を取得する
  const wSpread = SpreadsheetApp.getActiveSpreadsheet();

  // スプレッドシート内の1番目のシートを取得する
  const wSheet = wSpread.getSheets()[0];

  // シート内の全データを取得する。
  // getDataRangeはデータが存在する範囲のみを取得してくれるため非常に便利
  let wVals = wSheet.getDataRange().getValues();
  console.log(wVals.length);
}
//実行結果
21:06:01	お知らせ	実行開始
21:06:01	情報	5000
21:06:02	お知らせ	実行完了
こーすけ先生

無事にデータを取得できていることが確認できます

復習

こーすけ先生

「getDataRange」というメソッドについて上記記事で触れています
スプレッドシート上のデータを全部、取得できて便利!

【STEP2】都度貼り付けによる速度を計測する

今回は、500件処理するごとにどれくらい時間がかかったかログを出力していきます

こーすけ先生

それでは、まずは1セルずつアクセスしながら、データをセットしていきましょう!

const COL = {
  NUM:1
  , SEQUENT:2
  , BATCH:3
}

function test_SpreadSheet() {
  // コンテナバインド型なので、getActiveSpreadsheetにより自分自身を取得する
  const wSpread = SpreadsheetApp.getActiveSpreadsheet();

  // スプレッドシート内の1番目のシートを取得する
  const wSheet = wSpread.getSheets()[0];

  // シート内の全データを取得する。
  // getDataRangeはデータが存在する範囲のみを取得してくれるため非常に便利
  let wVals = wSheet.getDataRange().getValues();

  for (let rIdx=1; rIdx<wVals.length; rIdx++) {
    // セルに毎回アクセスして、値を更新する
    wSheet.getRange(rIdx+1, COL.SEQUENT).setValue(('0000'+rIdx).slice(-4));
    // 500件ごとにログを出力する
    if (rIdx%500==0) console.log(rIdx);
  }
}
//実行結果
21:26:54	お知らせ	実行開始
21:26:55	情報	500
21:26:57	情報	1000
21:26:58	情報	1500
21:27:00	情報	2000
21:27:02	情報	2500
21:27:04	情報	3000
21:27:05	情報	3500
21:27:07	情報	4000
21:27:09	情報	4500
21:27:12	お知らせ	実行完了
こーすけ先生

5,000件を処理するのに18秒かかってしまいます

ただ、ループを回してセルに値を入れているだけですが、これだけかかります
各セルの値を評価しながら、処理を分岐させたりすると、もっとかかりそうです

for文の説明

//for文の説明
for (let rIdx=1; rIdx<wVals.length; rIdx++) {
    // 値を一括貼り付け用のリストにセットする
    wWriteLst.push([('0000'+rIdx).slice(-4)]);
    // 500件ごとにログを出力する
    if (rIdx%500==0) console.log(rIdx);
}
こーすけ先生

for ループは、rIdx という変数を使用して、スプレッドシート内のデータを行ごとに処理するためのループです
rIdx は行のインデックスを表しています(row indexの略)

wWriteLst.push([('0000'+rIdx).slice(-4)]);

wWriteLst.push([('0000'+rIdx).slice(-4)]) は、行ごとの処理を行います

rIdx0000 と結合し、行数を4桁のフォーマットに変更します
たとえば、rIdx が1の場合、この行は '0001' となります

この行が行っていること
  • .slice(-4) を使用して、文字列の最後の4文字を取得
    (これにより、4桁の行番号が得られる)
  • 得られた行番号(4桁の文字列)を配列 wWriteLst に追加
  • ()の中身を、wWriteLst.push することで、スプレッドシートの各行の値を設定
if (rIdx%500==0) console.log(rIdx);
がすぴょん

この「rIdx%500==0」部分は何してるの?

こーすけ先生

rIdx % 500 は、rIdx を500で割った余りを計算しているんだよ

この行が行っていること
  • rIdx % 500 で、rIdx を500で割った余りを計算
  • もし rIdx が500の倍数である場合(余りが0の場合)、条件が成立する
  • 条件が成立する場合、console.log(rIdx) を実行して rIdx の値をコンソールに出力

【STEP3】一括貼り付けによる速度を計測する

【STEP2】と同様に、500件処理するごとにどれくらい時間がかかったかログを出力していきます

こーすけ先生

それでは、次にプログラム内で配列を作成して、最後に一括でデータをセットしてみましょう!

const COL = {
  NUM:1
  , SEQUENT:2
  , BATCH:3
}

function test_SpreadSheet() {
  // コンテナバインド型なので、getActiveSpreadsheetにより自分自身を取得する
  const wSpread = SpreadsheetApp.getActiveSpreadsheet();

  // スプレッドシート内の1番目のシートを取得する
  const wSheet = wSpread.getSheets()[0];

  // シート内の全データを取得する。
  // getDataRangeはデータが存在する範囲のみを取得してくれるため非常に便利
  let wVals = wSheet.getDataRange().getValues();
  let wWriteLst = new Array();

  for (let rIdx=1; rIdx<wVals.length; rIdx++) {
    // 値を一括貼り付け用のリストにセットする
    wWriteLst.push([('0000'+rIdx).slice(-4)]);
    // 500件ごとにログを出力する
    if (rIdx%500==0) console.log(rIdx);
  }
  // 一括で貼り付け
  wSheet.getRange(2, COL.BATCH, wWriteLst.length, 1).setValues(wWriteLst);
}
//実行結果
21:36:59	お知らせ	実行開始
21:36:59	情報	500
21:36:59	情報	1000
21:36:59	情報	1500
21:36:59	情報	2000
21:36:59	情報	2500
21:36:59	情報	3000
21:36:59	情報	3500
21:36:59	情報	4000
21:36:59	情報	4500
21:37:01	お知らせ	実行完了
がすぴょん

わっ!両方やってみると違いが顕著に分かるね!
一括貼り付けの場合は本当に一瞬だった!!!

こーすけ先生

都度貼り付けの場合、18秒かかったのに対して、
一括貼り付けの場合は2秒なので、「16秒」も短縮できているね

解説

wSheet.getRange(2, COL.BATCH, wWriteLst.length, 1).setValues(wWriteLst);
がすぴょん

ここの最後の行は何してるの?

最終行が行っていること
  • wSheet.getRange(2, COL.BATCH, wWriteLst.length, 1)と記述することで、C2~C5000セルまでの範囲を指定
    2は範囲の開始行を示しおり、BATCH は 3列目を示している)
  • .setValues(wWriteLst)は、wWriteLst 配列内のデータがスプレッドシートの指定された範囲に一括で書き込まれる
がすぴょん

ん?
そういえば、この「wWriteLst 配列」ってなんなの?

//let wVals = wSheet.getDataRange().getValues();
//一括貼り付けで増えた記述
  let wWriteLst = new Array();
こーすけ先生

このコードの let wWriteLst = new Array(); は、新しい空の配列を wWriteLst という変数に代入しているんだよ

  let wVals = wSheet.getDataRange().getValues();
  let wWriteLst = new Array();

  for (let rIdx=1; rIdx<wVals.length; rIdx++) {
    // 値を一括貼り付け用のリストにセットする
    wWriteLst.push([('0000'+rIdx).slice(-4)]);
    // 500件ごとにログを出力する
    if (rIdx%500==0) console.log(rIdx);
  }
  // 一括で貼り付け
  wSheet.getRange(2, COL.BATCH, wWriteLst.length, 1).setValues(wWriteLst);
}
こーすけ先生

この for文は、スプレッドシートのデータを取得し、取得したデータを wWriteLst 配列に追加しているんだ

こーすけ先生

そして、最後の行は、wWriteLst 配列に格納されたデータを一括で、スプレッドシートに貼り付けているんだよ

がすぴょん

おお!よく分かりました!

【解説】実行速度の違い

//都度貼り付け
wSheet.getRange(rIdx+1, COL.SEQUENT).setValue(('0000'+rIdx).slice(-4));

本箇所において、プロパティの読み取り/書き込みgetRangesetValueで発生しています

そのため、5,000行の場合は、2*5,000=10,000回の読み書きが発生することとなり、以前もお伝えしましたGASの制限事項を考えると、非常に効率の悪いプログラムとなっています

対して、一括貼り付けの場合は、

//一括貼り付け
wSheet.getRange(2, COL.BATCH, wWriteLst.length, 1).setValues(wWriteLst);

と、最後に1度(2回)アクセスするだけとなるため、1 / 5,000 の回数であることがわかります

まとめ

本記事では、Google Apps Scriptを使ったスプレッドシートのデータ処理において、プロパティの読み書きを効率化することの大切さをお伝えしました!

こーすけ先生

データを一括で処理することで、API呼び出し回数を減らし、実行速度を向上させることができると体感して頂けたかと思います!

今後もGASの魅力について発信していきますので、良かったらX(旧:Twitter)のフォローもよろしくお願いします!

こーすけ先生

X(旧:Twieer)にて、ブログの更新やQiita記事の更新、GAS情報をお届けしますので、是非フォローしてください!

100日後に起業する公務員

こーすけ先生

退職までの漫画をゆるくXにて更新中!
是非フォローしてね!

この記事が気に入ったら
いいね または フォローしてね!

よかったらシェアしてね!
  • URLをコピーしました!
  • URLをコピーしました!

この記事を書いた人

GASの人。ITベンダSEとして12年勤務し、民間、金融、官公庁の現場を一通り経験済。html、css、JavaScript、Java、PHPも分かります。最近は専らGASで小規模アプリケーションを頻繁に作成しています。GASのことなら何でもお任せあれ!現在は公務員として働きながら、起業に向けて着々と準備中です!

コメント

コメントする

CAPTCHA


目次