【Google Apps Script(GAS)】配列と処理速度

Google Apps Scriptでスプレッドシートの処理を自動化する上で、配列を扱えるようになるのは非常に重要です。

なぜかというと、配列を使って処理を行わないと、処理の量や内容によっては非常に、とても、すごく重くなるからです。

また、Google Apps Scriptは1回の実行時間が5分を超えてしまうと、エラーとなりスクリプトが停止してしまいます。

今回は、Google Apps Scriptにおける配列についてご紹介します。

なぜ重いのか、遅いのか？

そもそもなぜ重くなるかというと、APIを呼び出す回数がひとつ大きな問題となります。

スプレッドシートにおいて呼び出されるAPIとはSpreadsheetAppの、

getActiveSpreadsheet
getSheetByName
getLastRow
getRange
getValue
setValue

など、非常によく使われるものが並びます。

スプレッドシート上で大量のデータを処理しようとすると、これらのスクリプトが何度も何度も使用されるため、処理がどんどん重くなっていきます。

そこで、大量のデータをスプレッドシート上で処理するのではなく、スクリプトの中にいったん複数のデータを格納するための入れ物のようなものを作り、
そこで必要な処理を行った後に、全てまとめてスプレッドシートに記載する、というのが今回使用する配列というものです。
（配列については他にも色々用途はあるのですが、今回はその一例です）

配列とは？

変数に番号（インデックス）を付けて、複数の値（データ）を代入できるようにしたものです。
番号（インデックス）を指定することで、配列内の指定の位置のデータを取得・変更することが出来ます。

配列は、下記のように作成する事ができます。

var ary = [‘いぬ’, ‘さる’, ‘きじ’]

また、何も格納されていない、入れ物としての配列aryを作成する場合は下記のようになります。

var ary = []

そして下記スクリプトを実行すると、

配列aryには「いぬ,さる,きじ」が格納され、ary[0]には「いぬ」、ary[1]には「さる」、ary[2]には「きじ」が格納されている事が分かります。
（ちなみに配列の番号は0から始まりますが、必ず0から使用しなければならないというものでもありません）

function myFunction() {

var ary = [“いぬ”, “さる”, “きじ”];
Browser.msgBox(ary);
Browser.msgBox(ary[0]);
Browser.msgBox(ary[1]);
Browser.msgBox(ary[2]);
}

配列から探す

指定した文字列が格納されている配列の番号を取得したい場合は、下記スクリプトを使用します。

indexOf(文字列)

下記の例では、「さる」という文字列は配列aryのary[1]に格納されているため、メッセージボックスには1と表示されます。

function myFunction() {

var ary = [‘いぬ’, ‘さる’, ‘きじ’];
var number = ary.indexOf(‘さる’);

Browser.msgBox(number);

}

配列の操作（追加）

配列にデータを追加したい場合は、下記スクリプトを使用します。

・配列の末尾に追加する場合
push(文字列)

・配列の先頭に追加する場合
unshift(文字列)

下記の例では、配列aryの末尾に「さる」を追加し、配列aryの末尾に「きじ」を追加し、配列aryの先頭に「いぬ」を追加した結果、メッセージボックスには「いぬ,さる,きじ」と表示されます。

function myFunction() {

var ary = [];

ary.push(‘さる’)
ary.push(‘きじ’)
ary.unshift(‘いぬ’)

Browser.msgBox(ary);
}

配列の操作（削除）

配列からデータを削除したい場合は、下記スクリプトを使用します。

・配列の末尾のデータを削除
pop()

・配列の先頭のデータを削除
shift()

下記の例では、配列aryの末尾と先頭を削除した結果、「さる」のみが残され、メッセージボックスには「さる」と表示されます。

function myFunction() {

var ary = [‘いぬ’, ‘さる’, ‘きじ’];

ary.pop();
ary.shift();

Browser.msgBox(ary);
}

処理時間の比較

配列を使った場合と使わない場合で、実際にどれほど処理時間に差がでるか、簡単な処理で実験してみます。

実験は、A列とB列に入力されている値を結合し、C列に入力する処理を1行目~100行目まで繰り返します。

次のサンプル１では、変数str1にA列の文字列を代入、変数str2にB列の文字列を代入し、スプレッドシートのC列にsetValueで記入していくという処理ですが、このsetValueの繰り返しのために非常に処理が遅く、単純な処理であるにも関わらず20秒以上かかっています。

･サンプル１

function myFunction() {

var start = new Date();　//処理開始時間を取得

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sh = ss.getActiveSheet();
var last_row = sh.getLastRow();

for (var i = 0; i　< last_row; i++) {

var str1 = sh.getRange(1 + i, 1).getValue();　//A列の文字列を取得
var str2 = sh.getRange(1 + i, 2).getValue();　//B列の文字列を取得

sh.getRange(1 + i, 3).setValue(str1 + str2);　//C列に結合した文字列を記入

}

var end = new Date();　//処理終了時間を取得
var lap_time = (end – start) / 1000;　//経過時間を取得

Browser.msgBox(lap_time + ‘秒’);　//経過時間を表示
}

配列を使わない処理時間

次のサンプル２では、変数str1にA列の文字列を代入、変数str2にB列の文字列を代入し、ary.push(str1 + str2)で結合した文字列を順次配列に格納し、配列に格納したデータをまとめてC列に記入しています。

・サンプル２

function myFunction() {

var start = new Date();　//処理開始時間を取得

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sh = ss.getActiveSheet();
var last_row = sh.getLastRow();
var ary = [];

for (var i = 0; i　< last_row; i++) {

var str1 = sh.getRange(1 + i, 1).getValue();　//A列の文字列を取得
var str2 = sh.getRange(1 + i, 2).getValue();　//B列の文字列を取得

ary.push(str1 + str2);　//A列とB列の文字列を結合して配列に格納

}

var rows = ary.length;　//配列の長さを取得

sh.getRange(1,3,rows,1).setValue(ary);　//配列に格納した文字列をC列に記入

var end = new Date();　//処理終了時間を取得
var lap_time = (end – start) / 1000;　//経過時間を取得

Browser.msgBox(lap_time + ‘秒’);　//経過時間を表示
}

配列を使った処理時間

こちらであれば0.5秒ほどで処理が完了します。

配列を使った処理は一見回りくどくも見えますが、例え単純な処理であっても処理を高速化する上で非常重要である事が分かります。

Google Apps Script(GAS)のお仕事で稼ぐのに必須の３サイト（無料）

・クラウドソーシング「ランサーズ」

・CrowdWorks（クラウドワークス）

・Bizseek