Google Apps Scriptでスプレッドシートにデータ行を挿入する2つの方法


splice

photo credit: RobW_ Ropes and Splices via photopin (license)

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

Google Apps Scriptでスプレッドシートのデータを配列に格納して操作する方法についてお伝えしています。

前回はこちらの記事でした。

Google Apps Scriptで配列を使ってスプレッドシートにデータ行を追加する方法
Google Apps Scriptでは配列操作が非常に重要です。実行速度が6分を超えてエラーとしないテクニックとして、スプレッドシートへのレコード追加を配列へのpushメソッドで処理する方法をお伝えします。

配列を使ってシートの末尾にデータ行を追加する方法についてお伝えしました。

じゃあ、シートの末尾でない場合はどうなの?

ということになりますよね。

今回は、Google Apps Scriptでシートの任意の箇所にデータ行を挿入する方法についてお伝えします。

SpreadsheetサービスのAPIを使う方法と、配列を使う方法の両方を比較してみますよ。

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

スポンサーリンク

スプレッドシートのシートにデータ行を挿入する

まず、SpreadsheetサービスのAPIを使ってデータ行を挿入する方法をお伝えします。

以下のようなシートがあります。

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

見てわかります通り、北海道、青森県、岩手県のデータ行が抜けてしまっていますね。

それら3道県のデータ行を、2行目に挿入してみましょう。

SpreadsheetサービスのAPIを使う場合、以下のような流れになります。

  1. 2行目に空白行を3行分挿入する
  2. 挿入した空白行にデータを書き込む

insertRowsメソッドでシートに空白行を挿入する

まず、シートに空白行を挿入するにはinsertRowsメソッドを使います。

書き方は、こちらです。

Sheetオブジェクト.insertRows(行位置, 行数)

これで指定した行位置に、指定した行数の空白行を挿入します。

行数は省略できますが、その場合には1行となります。

insertRowsメソッドを使ってデータ行を挿入する

insertRowsメソッドを使って、前述のお題である北海道から岩手県までのデータ行を挿入するスクリプトを作りました。

function insertRecords(){
  const sheet = SpreadsheetApp.getActiveSheet();
  const values = [
    ['北海道','札幌市',5422873,78421.34],
    ['青森県','青森市',1321863,9644.74],
    ['岩手県','盛岡市',1284384,15278.89]
  ];
  const numRows = values.length;
  const numColumns = values[0].length;
  sheet.insertRows(2,numRows);
  sheet.getRange(2, 1, numRows, numColumns).setValues(values);
}

insertRowsメソッドで2行目の位置に3行の空白行を挿入、その後にsetValuesメソッドでデータを書き込みます。

getRangeの指定の方法はちょっとややこしいですが、以下記事を参考にして下さい。

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

実行結果と評価

実行すると、以下のように見出し行のすぐ下に3行追加されます。

GASでシートにデータ行を挿入

実は、このスクリプトのAPIリクエスト回数はgetActiveSheet、insertRows、getRange、setValuesがそれぞれ1回ずつで計4回で、かつデータ行が増えてもその回数は増えないのにお気づきでしょうか。

意外と優秀です。

配列を使ってシートにデータ行を挿入する

では、これと同じように、データ行の挿入を配列を使って実現してみましょう。

一旦、シート上のデータを全て配列に格納するので、前回の記事でも紹介したこちらのスクリプトをベースにします。

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

上記の「//処理」の箇所に、データ行を挿入する処理をすればよいですね。

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

配列に要素を追加するにはspliceメソッドを使います。

書き方はこうです。

Arrayオブジェクト.splice(配列番号, 削除する要素数, 追加する要素1, 追加する要素2, …)

実はspliceメソッドは、以下のように削除と追加を兼ね備えた万能メソッドです。

  • 指定の配列番号の位置について
  • 削除する要素数分の要素を削除しつつ
  • 追加する要素1,追加する要素2,…を追加する

削除する要素数を0にすれば、単純に要素の追加をするだけです。今回やりたいことですね。

また、追加する要素1以降は省略可能です。削除だけしたいときは、全て省略すれば良いということになります。

削除に関しては、次回の記事で紹介できればと思います。

spliceメソッドを使ってシートにデータ行を挿入する

では、冒頭のお題に応えるべく、シートにデータ行を挿入してみましょう。

以下スクリプトを作成しました。

function insertRecords(){
  const sheet = SpreadsheetApp.getActiveSheet();
  const values = sheet.getDataRange().getValues();
 
  const records = [
    ['北海道','札幌市',5422873,78421.34],
    ['青森県','青森市',1321863,9644.74],
    ['岩手県','盛岡市',1284384,15278.89]
  ];
 
  values.splice(1, 0, ...records);
  sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
}

スプレッド構文による引数の指定

11行目のspliceメソッドですが、第3引数に「…records」という引数を指定しているのを確認ください。

これは、スプレッド構文といい、以下のように配列などの反復可能オブジェクトに使うことができます。

…反復可能オブジェクト

関数の引数にスプレッド構文を渡した場合、その要素分の引数に展開して渡してくれるのです。

例えば、今回の場合、recordsは二次元配列で3つの配列要素を持っていますので、展開されて3つのバラバラの引数として渡されるということになります。

spliceメソッドは追加する要素を任意の数だけ渡すことができますので、スプレッド構文を使って指定するとスッキリします。

APIの呼び出し回数は

APIの呼び出し回数はgetActiveSheet、getDataRange、getValues、getRange、setValuesが各1回ずつで計5回ですね。

配列を使ったほうがリクエスト回数多いじゃないか!と思われるかも知れませんが、よく考えてみてください。

挿入する行が飛び飛びの位置に複数必要なのであれば、insertRowsメソッドもその分増えてしまうのでAPIリクエスト回数が増えてしまいます。

一方で、配列を使っていればspliceメソッドが増えるだけで、APIリクエスト回数は増えませんね。

ということで、このようなことを見極めながらケースバイケースで使いやすいほうを使っていいのではないかと思いますよ。

まとめ

Google Apps Scriptでスプレッドシートにデータ行を挿入する方法をお伝えしました。

  • insertRowsメソッドを使って空白行を挿入する方法
  • spliceメソッドを使って配列に要素を追加する方法

という二つの方法を紹介しましたが、都度使いやすいほうをチョイス頂ければと思います。

次回は、配列を使ってシートの行を削除するテクニックをお伝えしますね。

Google Apps Scriptでスプレッドシートの行を削除する2つの方法
Google Apps Scriptでスプレッドシートのデータを配列に格納して操作をする方法をお伝えしています。今回は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メソッド

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