photo credit: RobW_ Ropes and Splices via photopin (license)
みなさん、こんにちは！
タカハシ(@ntakahashi0505)です。
Google Apps Scriptでスプレッドシートのデータを配列に格納して操作する方法についてお伝えしています。
前回はこちらの記事でした。
配列を使ってシートの末尾にデータ行を追加する方法についてお伝えしました。
じゃあ、シートの末尾でない場合はどうなの？
ということになりますよね。
今回は、Google Apps Scriptでシートの任意の箇所にデータ行を挿入する方法についてお伝えします。
SpreadsheetサービスのAPIを使う方法と、配列を使う方法の両方を比較してみますよ。
では、行ってみましょう！
スプレッドシートのシートにデータ行を挿入する
まず、SpreadsheetサービスのAPIを使ってデータ行を挿入する方法をお伝えします。
以下のようなシートがあります。
見てわかります通り、北海道、青森県、岩手県のデータ行が抜けてしまっていますね。
それら3道県のデータ行を、2行目に挿入してみましょう。
SpreadsheetサービスのAPIを使う場合、以下のような流れになります。
- 2行目に空白行を3行分挿入する
- 挿入した空白行にデータを書き込む
insertRowsメソッドでシートに空白行を挿入する
まず、シートに空白行を挿入するには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の指定の方法はちょっとややこしいですが、以下記事を参考にして下さい。
実行結果と評価
実行すると、以下のように見出し行のすぐ下に３行追加されます。
実は、このスクリプトのAPIリクエスト回数はgetActiveSheet、insertRows、getRange、setValuesがそれぞれ1回ずつで計4回で、かつデータ行が増えてもその回数は増えないのにお気づきでしょうか。
意外と優秀です。
配列を使ってシートにデータ行を挿入する
では、これと同じように、データ行の挿入を配列を使って実現してみましょう。
一旦、シート上のデータを全て配列に格納するので、前回の記事でも紹介したこちらのスクリプトをベースにします。
const sheet = SpreadsheetApp.getActiveSheet();
const values = sheet.getDataRange().getValues();
//処理
sheet.getRange({書き出す範囲}).setValues(values);
上記の「//処理」の箇所に、データ行を挿入する処理をすればよいですね。
spliceメソッドで配列に要素を追加する
配列に要素を追加するにはspliceメソッドを使います。
書き方はこうです。
実は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メソッドを使って配列に要素を追加する方法
という二つの方法を紹介しましたが、都度使いやすいほうをチョイス頂ければと思います。
次回は、配列を使ってシートの行を削除するテクニックをお伝えしますね。
どうぞお楽しみに！
