みなさん、こんにちは！
タカハシ(@ntakahashi0505)です。
Google Apps Scriptでスプレッドシートのデータを配列に格納して操作をする方法についてシリーズでお伝えしています。
前回、前々回はこちらの記事でした。
それぞれスプレッドシートに行を追加する、また行を挿入するという方法についてお伝えしています。
追加、挿入と来ましたので、次は削除ということで、Google Apps Scriptでスプレッドシートの行を削除する方法についてです。
そして、GASには「実行時間6分の壁」がありますから、配列を上手に使えるようになって頂ければと思います。
では、行ってみましょう！
スプレッドシートのシートの行を削除する
では、これまでの流れに合わせて、まずはSpreadsheetサービスのAPIを使ってデータ行を削除する方法をお伝えします。
前回に引き続き、以下シートを例にとって進めてみましょう。
では、このシートから3,4行目にある青森県、岩手県、そして6行目にある秋田県のデータを削除したいと思います。
deleteRowsメソッドでシートの行を削除する
シートの特定の行を削除するにはdeleteRowsメソッドを使います。
書き方はこちらです。
これで指定した行位置から、指定した行数を削除することができます。
行数を省略すると、行数が1行となります。
deleteRowsメソッドで行を削除するスクリプト
deleteRowsメソッドを使ってシートから青森県、岩手県、秋田県のデータ行を削除するスクリプトを作りました。
こちらです。
function deleteRecords(){
const sheet = SpreadsheetApp.getActiveSheet();
sheet.deleteRows(6);
sheet.deleteRows(3,2);
}
これを実行すると、以下のように目的のデータ行を削除することができます。
この場合のAPIリクエスト回数は、getActiveSheetが1回、deleteRowsが2回ですから、計3回となりますね。
降順に行の削除を行うべき理由
先ほどのスクリプトで、6行目を先に削除、3,4行目をその次に削除という順番にしていたのにお気づきでしょうか。
この順番を逆にして、例えば以下のようにしてはいけません。
function deleteRecords(){
const sheet = SpreadsheetApp.getActiveSheet();
sheet.deleteRows(3,2);
sheet.deleteRows(6);
}
実行すると以下のようになりますが、秋田県が残っていて、福島県が削除されてしまいました。
例えば、先に3,4行目を削除すると、本来5行目以降だったレコードが3行目以降に繰り上がりが発生します。
つまり、秋田県が4行目に、福島県が6行目に繰り上がるので、このようなエラーが発生してしまうのです。
したがって、行の削除をする場合は、下の行から降順に行わなければいけません。
配列を使ってシートの行を削除する
では、これと同じこと、つまりデータ行の削除を配列を使って実現してみます。
今回も、一旦配列に格納をして、処理をした後にシートに書き出すという以下のスクリプトをベースに進めます。
const sheet = SpreadsheetApp.getActiveSheet();
const values = sheet.getDataRange().getValues();
//処理
sheet.getRange({書き出す範囲}).setValues(values);
spliceメソッドで配列に要素を削除する
前回の記事をご覧になられたのであれば、配列から要素を削除するにはspliceメソッドを使えばよいというのはご存知ですね。
spliceメソッドは配列の要素の削除と追加を両方行える便利メソッドです。
削除する場合の書き方はこちらです。
これを使って、以下のようにするわけです。
- 指定の配列番号の位置から
- 削除する要素数分の要素を削除
要素を挿入する場合は、以下の記事で使い方を紹介していますので、ご参考くださいね。
spliceメソッドでデータ行を削除するスクリプト
では、spliceメソッドを使って、シートから青森県、岩手県、秋田県のデータ行を削除するスクリプトを作ってみましょう。
function deleteRecords(){
const sheet = SpreadsheetApp.getActiveSheet();
const values = sheet.getDataRange().getValues();
values.splice(5, 1);
values.splice(2, 2);
sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
}
今回も、以下のように降順で処理を行います。
- 配列番号5から1つの要素を削除
- 配列番号2から2つの要素を削除
配列番号は0から始まりますので指定をする値は実際の行列よりもマイナス1になることに注意して下さいね。
これを実行すると…
…おや？なんだかおかしいですね。
6行目以降が重複して残ってしまっているようです。
そうなんです、配列valuesに対しては3行分の削除しかしていませんので、元のシートの5行目までが上書きになりますが、6行目以降は何の処理もされないのです。
clearContensメソッドでシートの値をクリアする
配列にNullの要素を追加するという方法も考えられますが、ここは素直にシートの値をクリアするclearContensメソッドを使いましょう。
シートの値のクリア処理も入れたスクリプトはこちら。
function deleteRecords(){
const sheet = SpreadsheetApp.getActiveSheet();
const values = sheet.getDataRange().getValues();
values.splice(5, 1);
values.splice(2, 2);
sheet.clearContents();
sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
}
これで求める結果が得られます。
APIリクエストの回数としては、getActiveSheet、getDataRange、getValues、clearContents、getRange、setValuesと計6回です。
ただこれも、前回の記事で紹介した行の挿入と同様に、飛び飛びの位置の複数行の削除をすることになってもAPIリクエストの回数は増えることはありません。
そのような場合は配列を使ったほうが実行時間を短く抑えることができますね。
まとめ
以上、Google Apps Scriptでスプレッドシートの行を削除する二つの方法についてお伝えしました。
今回の例では配列にすることによる「うま味」は感じづらいかも知れませんが、一般的には条件分岐などの処理も入ってくるので、配列を使ったほうが圧倒的に処理速度が速くなる場合が多いです。
これまでお伝えした、追加、挿入とともに削除の方法もぜひ覚えておいてください。
次回も配列を使ったシートの操作のテクニックとして、配列の検索をするメソッドについてお伝えしますね。
どうぞお楽しみに！
