Google Apps Scriptで配列を使ってスプレッドシートにデータ行を追加する方法

★気に入ったらシェアをお願いします!


push

photo credit: Cristina Jiménez Ledesma Mezquita vs Cádiz via photopin (license)

みなさん、こんにちは!
タカハシ(@ntakahashi0505)です。

Google Apps Scriptでは配列操作が非常に重要です。

というのも、GASではよくスプレッドシートを操作することが多いのですが、SpreadsheetサービスのAPIを使えば使うほどスクリプトの実行速度がかさみます。

そして、一回の実行でその実行速度が5分を超えてしまうと、エラーとなりスクリプトが停止してしまいます。

これがいわゆる「Google Apps Scriptの5分の壁」というやつです。

それを避けるために、なるべくSpreadsheetサービスのAPIを使わないようにスクリプトを組む、つまり配列で処理をするテクニックが重要になってくるというわけです。

ということで、このシリーズでは、Google Apps Scriptでスプレッドシートのデータを配列に格納して処理をする様々なテクニックをお伝えします。

初回の今回は、Google Apps Scriptによるスプレッドシートへのレコード追加を配列内で処理する方法です。

では、行ってみましょう!

配列を活用してAPIの呼び出し回数を減らす

Google Apps Scriptは実行時間には大変シビアです。

なんせ

  • APIを使うととっても遅くなる
  • 実行時間が5分を超えるとエラーで停止

となりますからね。

例えば、以下の記事では、たった50行のデータでも組み方によっては42秒もかかるということをお伝えしています。

Google Apps Scriptのスプレッドシート読み書きを格段に高速化をする方法
Google Apps Scriptでスプレッドシートの操作をしていて実行速度が遅い!と感じたことがあると思います。今回はスプレッドシートを操作する場合に処理速度を格段に速くする方法をお伝えします。

てことは、単純計算でも350行を超えたあたりから5分の壁の危険ゾーンに到達してしまいます。

上記記事では、そのための対策として

  • シートのデータをgetValuesを使って配列に格納する
  • 配列のデータをsetValuesを使ってシートに書き出す

という方法を使って、シートへの読み書きのAPIを呼び出す回数を減らそうという作戦なわけです。

つまり、以下のようなスクリプトがベースになり

その間の「//処理」のところで、配列arrDataに対してできる限りの処理ができるほど実行時間が稼げるということになります。

配列を使ってシートにレコードを追加する

例えば以下のようなシートがあったとして、他の都道府県のデータを下に追加していきたいとします。

スプレッドシートのシート

例としてこのシートにレコードを追加をしてみましょう。

appendRowでレコードを追加する

SheetオブジェクトのappendRowメソッドを使うと

このように書きますね。

APIの呼び出し件数は、getActiveSheetが1回とappendRowが3回の計4回です。

appendRowメソッドは一回のAPI呼び出しで列全体に出力できますから優秀ですね。間違えても、セル一つ一つに書き出すsetValueメソッドなどは使ってはいけません。

しかし、都道府県の数はもっとたくさんありますからね。レコードが増えるとAPIの呼び出し回数がその分だけ多くなってしまいます。

配列を使ってシートにレコードを追加する

pushメソッドで配列に要素を追加する

では、配列を使ってシートにレコードを追加しましょう。

配列の最後に要素を追加する場合は、Arrayオブジェクトに対するpushメソッドを使います。

書き方は

Arrayオブジェクト.push(要素)

となります。

arrDataの中身は

となっていますので、都道府県ごとの1行のレコードが一つの配列要素となっています。

ですから、追加したい都道府県のレコードを配列要素として

とすれば、arrDataの末尾にレコードごと追加することができます。

配列を使ってシートにレコードを追加するスクリプト

スクリプトにするとこうなります。

setValuesメソッドを実行する場合には、getRangeで配列を書き出す範囲を指定する必要がありますので

  • 10行目:arrData内の要素数(=レコード数)
  • 11行目:arrData[0](=見出しレコード)の要素数

で、それぞれ行数と列数を取得しています。

setValuesメソッドを使う場合のgetRangeの引数の指定の仕方は以下記事をご参考ください。

Google Apps Scriptのスプレッドシート読み書きを格段に高速化をする方法
Google Apps Scriptでスプレッドシートの操作をしていて実行速度が遅い!と感じたことがあると思います。今回はスプレッドシートを操作する場合に処理速度を格段に速くする方法をお伝えします。

APIの件数は、getActiveSheetメソッド1回、getDataRangeメソッド1回、getValues1回、getRange1回、setValues1回と何気に5回もありますが、追加するレコードがいくら増えてもこの呼び出し回数はそのままです。

実行結果はこちらです。

GASで配列を使ってレコードを追加

まとめ

Google Apps Scriptで配列を使ってシートにレコードを追加する方法についてお伝えしました。

配列に要素を追加するpushメソッドを使いました。

配列の処理はわかりづらいものが多いですが、実行時間を考えると、必須のテクニックと言えます。

次回は、末尾ではなくて任意の箇所にデータ行を挿入する方法をお伝えします。

Google Apps Scriptでスプレッドシートにデータ行を挿入する2つの方法
Google Apps Scriptでスプレッドシートのデータを配列に格納して操作するテクニックの一つとして、シートの任意の箇所にデータ行を挿入する方法をお伝えします。配列とシート直接の二つを紹介します。

どうぞお楽しみに!

連載目次:GASで5分の壁と戦うための配列を扱うテクニック

Google Apps Scriptでは配列操作が非常に重要です。というのも、SpreadsheetサービスのAPIを使えば使うほど、「Google Apps Scriptの5分の壁」にぶつかるからです。このシリーズでは、GASでスプレッドシートのデータを配列に格納して処理をする様々なテクニックをお伝えします。
  1. Google Apps Scriptで配列を使ってスプレッドシートにデータ行を追加する方法
  2. Google Apps Scriptでスプレッドシートにデータ行を挿入する2つの方法
  3. Google Apps Scriptでスプレッドシートの行を削除する2つの方法
  4. Google Apps Scriptで配列の検索をするindexOfメソッドとlastIndexOfメソッド