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


push

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秒もかかるということをお伝えしています。

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

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

上記記事では、そのための対策として以下のような対策をお伝えしています。

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

つまり、シートへの読み書きのAPIを呼び出す回数を減らそうという作戦ということで、以下のようなスクリプトをベースにします。

  const sheet = SpreadsheetApp.getActiveSheet();
  const values = sheet.getDataRange().getValues();
 
  //処理
 
  sheet.getRange({書き出す範囲}).setValues(values);

「//処理」のところで、配列valuesに対してできる限りの処理ができるほど、実行時間を減らせやすいということになります。

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

スプレッドシートのシート上の都道府県データ

シートの最終行に配列データを追加する

appendRowメソッドでレコードを追加する

まず、一つの方法としてSheetオブジェクトの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メソッドを使います。

書き方は、こちらです。

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の引数の指定の仕方は、以下記事をご参考ください。

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

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

実行結果はこちらです。

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

まとめ

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

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

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

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

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

どうぞお楽しみに!

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

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

  投稿者プロフィール

タカハシノリアキ株式会社プランノーツ 代表取締役
株式会社プランノーツ代表、コミュニティ「ノンプロ研」主宰。1976年こどもの日生まれ。東京板橋区在住。「ITで日本の『働く』の価値を上げる!」をテーマに、VBA&GASの開発、講師、執筆などをしております。→詳しいプロフィールはコチラ
★ご依頼・ご相談はお気軽にどうぞ!→お問い合わせはコチラ
★フォロー頂ければ嬉しいです。

タイトルとURLをコピーしました