Google Apps Scriptを使用してスプレッドシートの操作を行う際、API呼び出しの回数を過度に増やすと、プログラムの実行速度が著しく低下することがよくあります
そこで、本記事ではAPI呼び出しを最小限に抑えつつ、適切な方法でAPIを使用することで、高速なプログラム実行を実現する方法について解説します
この記事を読めば、スプレッドシートのデータ操作において、APIを適切に活用し、プログラムの実行速度を向上させる方法を理解することができます
API呼び出しによる遅延を最小限に抑え、効率的なコードの書き方をマスターしましょう！
こーすけ先生スキルアップを目指して、一緒に頑張りましょう！
こーすけ先生当ブログでは実際に仕事でGASを扱っている私が、GASの魅力について徹底的に取り上げていきます！
GASの実行速度を意識しながらコードを書く
GASに限った話ではないですが、「実行速度」を意識してコードを書くのは非常に重要なことです
こーすけ先生だから、実行速度を意識してコードを書くことが大事なんです！
【事前準備】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)]) は、行ごとの処理を行います
rIdx を 0000 と結合し、行数を4桁のフォーマットに変更します
たとえば、rIdx が1の場合、この行は '0001' となります
if (rIdx%500==0) console.log(rIdx);この「rIdx%500==0」部分は何してるの？
こーすけ先生rIdx % 500 は、rIdx を500で割った余りを計算しているんだよ
【STEP３】一括貼り付けによる速度を計測する
【STEP２】と同様に、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);ここの最後の行は何してるの？
がすぴょんん？
そういえば、この「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));本箇所において、プロパティの読み取り/書き込みがgetRangeとsetValueで発生しています
そのため、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情報をお届けしますので、是非フォローしてください！
こーすけ先生おかげさまで今年５月に起業しました！
GASやGoogleサービス、プログラミング全般のご相談承ります！
コメント