はじめに
本シリーズでは、GASの始め方や便利な使い方、ビジネス活用まで幅広く解説します。シリーズをひと通り読んでいただければ、あなたもきっとGASマスターになれるはずです。
シリーズの対象者
- そもそもGASってなんだかわからない方
- GASを学びたいけど何から始めればいいかわからない方
- GASはわかり始めたけど、もっと活用ができないかと模索している方
- とにかくGoogleが好き！ という方
前回記事
スプレッドシートで遊ぶ
では早速始めていきましょう。【0からGASを学ぶ】シリーズの第6回は「GASを用いたスプレッドシートの実行速度向上のポイントを解説」です。今回は少しコーヒーブレイクです。前回のSTEP.4において、スプレッドシートでは 「都度貼り付け」 を行うのではなく 「最後に一括で貼り付け」 をした方がよいとお伝えしました。もちろんやりたいことによっては、都度貼り付けを行わなければいけないこともあるかもしれません。ただし、これによる速度の違いを知っておくことで柔軟な発想が生まれることもあると思いますので、早速計測してみましょう。
事前準備
スプレッドシートのデータを取得しなければ始まらないので、取得元となるスプレッドシートを作りましょう。私は以下のようなスプレッドシートを作成しました。サンプルなので、この通りである必要はありませんが、これを前提にプログラムは書かせてもらいます。
今回やること
- スプレッドシート内のすべてのデータを取得する。
- 都度貼り付けによる速度を計測する（500行ごとにログを出力する）。
- 一括貼り付けによる速度を計測する（500行ごとにログを出力する）。
プログラム開始
GASエディタを開く
今回もコンテナバインド型でGASプログラムを記述していきましょう。こちらを参考にGASエディタを起動してください。では、どんどんいきますよ、ついてきてください。
STEP.1 スプレッドシート内のすべてのデータを取得する
function Qiita006_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 お知らせ 実行完了
無事にデータを取得できていることが確認できます。
STEP.2 都度貼り付けによる速度を計測する（500行ごとにログを出力する）
それでは、まずは1セルずつアクセスしながら、データをセットしていきましょう。
const COL = {
NUM:1
, SEQUENT:2
, BATCH:3
}
function Qiita006_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秒かかっています。ただ、ループを回してセルに値を入れているだけですが、これだけかかります。各セルの値を評価しながら、処理を分岐させたりすると、もっとかかりそうです。
STEP.3 一括貼り付けによる速度を計測する（500行ごとにログを出力する）
それでは、次にプログラム内で配列を作成して、最後に一括でデータをセットしてみましょう。
const COL = {
NUM:1
, SEQUENT:2
, BATCH:3
}
function Qiita006_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 お知らせ 実行完了
！！！！段違いでした。実行してみるとよりわかりますが、本当に一瞬で完了します。
さらにいうと
都度貼り付けの場合は、
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 の回数であることがわかります。
おわりに
お疲れ様でした。
第6回は「GASを用いたスプレッドシートの実行速度向上のポイントを解説」ということで、今後スプレッドシートを扱う上でプロパティの読み書きを効率化することの大切さをお伝えしました。次回も引き続き、スプレッドシートで遊んでいきましょう。
記事を読んで、「良いな」や「今後に期待できる！」と感じて頂けたらいいねやフォロー、コメントいただけると幸いです。それではまた次回をお楽しみに！
ブログでより詳しく解説しています！
以下画像をクリックしてブログにアクセス！！
