photo credit: Cristina Jiménez Ledesma Mezquita vs Cádiz via photopin (license)
みなさん、こんにちは！
タカハシ(@ntakahashi0505)です。
Google Apps Scriptでは配列操作が非常に重要です。
というのも、GASではよくスプレッドシートを操作することが多いのですが、SpreadsheetサービスのAPIを使えば使うほどスクリプトの実行速度がかさみます。
そして、一回の実行でその実行速度が6分を超えてしまうと、エラーとなりスクリプトが停止してしまいます。
これがいわゆる「Google Apps Scriptの6分の壁」というやつです。
それを避けるために、なるべくSpreadsheetサービスのAPIを使わないようにスクリプトを組む、つまり配列で処理をするテクニックが重要になってくるというわけです。
ということで、このシリーズでは、Google Apps Scriptでスプレッドシートのデータを配列に格納して処理をする様々なテクニックをお伝えします。
初回の今回は、Google Apps Scriptによるスプレッドシートへのレコード追加を配列内で処理する方法です。
では、行ってみましょう！
配列を活用してAPIの呼び出し回数を減らす
Google Apps Scriptは実行時間には大変シビアです。
なんせこうですから。
- APIを使うととっても遅くなる
- 実行時間が6分を超えるとエラーで停止
例えば、以下の記事では、たった100行のデータでも、組み方によっては130秒もかかるということをお伝えしています。
てことは、単純計算でも300行を超えたあたりから6分の壁の危険ゾーンに到達してしまいます。
上記記事では、そのための対策として以下のような対策をお伝えしています。
- シートのデータをgetValuesを使って配列に格納する
- 配列のデータをsetValuesを使ってシートに書き出す
つまり、シートへの読み書きのAPIを呼び出す回数を減らそうという作戦ということで、以下のようなスクリプトをベースにします。
const sheet = SpreadsheetApp.getActiveSheet();
const values = sheet.getDataRange().getValues();
//処理
sheet.getRange({書き出す範囲}).setValues(values);
「//処理」のところで、配列valuesに対してできる限りの処理ができるほど、実行時間を減らせやすいということになります。
例えば、以下のようなシートがあったとして、他の都道府県のデータを下に追加していきたいとします。
シートの最終行に配列データを追加する
appendRowメソッドでレコードを追加する
まず、一つの方法としてSheetオブジェクトのappendRowメソッドを使う方法があります。
以下の書式で、引数で与えた一次元配列を、シートの最終行の次の行のデータとして追加することができます。
例えば、appendRowメソッドを使ってデータを追加するスクリプトは、以下のように書くことができます。
function appendRecords(){
const sheet = SpreadsheetApp.getActiveSheet();
sheet.appendRow(['茨城県','水戸市',2921184,6095.84]);
sheet.appendRow(['栃木県','宇都宮市',1980960,6408.28]);
sheet.appendRow(['群馬県','前橋市',1977013,6362.33]);
}
APIの呼び出し件数は、getActiveSheetが1回とappendRowが3回の計4回です。
appendRowメソッドは一回のAPI呼び出しで行全体に出力できますから優秀ですね。
間違えても、セル一つ一つに書き出すsetValueメソッドなどは使ってはいけません。
しかし、都道府県の数はもっとたくさんありますから、レコードが増えるとAPIの呼び出し回数がその分だけ多くなってしまいます。
二次元配列に配列要素を追加する
pushメソッドで配列に要素を追加する
次に、配列の最後に要素を追加する場合は、Arrayオブジェクトに対するpushメソッドを使います。
書き方は、こちらです。
valuesの中身は以下のような二次元配列です。
[
['都道府県','都道府県庁所在地','推計人口','面積'],
['北海道','札幌市',5422873,78421.34],
['青森県','青森市',1321863,9644.74],
…
['福島県','福島市',1936630,13782.76]
];
つまり、都道府県ごとの1行のレコードが一つの配列要素となっています。
ですから、追加したい都道府県のレコードを配列要素として、以下のようにすれば、valuesの末尾にレコードごと追加することができます。
values.push(['茨城県','水戸市',2921184,6095.84]);
pushメソッドによりシートにレコードを追加するスクリプト
スクリプトにするとこうなります。
function appendRecords(){
const sheet = SpreadsheetApp.getActiveSheet();
const values = sheet.getDataRange().getValues();
values.push(['茨城県','水戸市',2921184,6095.84]);
values.push(['栃木県','宇都宮市',1980960,6408.28]);
values.push(['群馬県','前橋市',1977013,6362.33]);
sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
}
setValuesメソッドを実行する場合には、getRangeで配列を書き出す範囲を指定する必要がありますので、以下のように行数と列数を取得しています。
- 行数: valuesの要素数(=レコード数)
- 列数: values[0](=見出しレコード)の要素数
setValuesメソッドを使う場合のgetRangeの引数の指定の仕方は、以下記事をご参考ください。
APIの件数は、getActiveSheetメソッド1回、getDataRangeメソッド1回、getValues1回、getRange1回、setValues1回と何気に5回もありますが、追加するレコードがいくら増えてもこの呼び出し回数はそのままです。
実行結果はこちらです。
まとめ
Google Apps Scriptで配列を使ってシートにレコードを追加する方法についてお伝えしました。
シートに配列を追加するappendRowメソッドと配列に要素を追加するpushメソッドを使いました。
配列の処理はわかりづらいものが多いですが、実行時間を考えると、必須のテクニックと言えます。
次回は、末尾ではなくて任意の箇所にデータ行を挿入する方法をお伝えします。
どうぞお楽しみに！
