Google Apps Scriptでスプレッドシートの行を削除する2つの方法


rows

photo credit: Henry Hemming Harvest via photopin (license)

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

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

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

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

それぞれスプレッドシートに行を追加する、また行を挿入するという方法についてお伝えしています。

追加、挿入と来ましたので、次は削除ということで、Google Apps Scriptでスプレッドシートの行を削除する方法についてです。

そして、GASには「実行時間6分の壁」がありますから、配列を上手に使えるようになって頂ければと思います。

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

スポンサーリンク

スプレッドシートのシートの行を削除する

では、これまでの流れに合わせて、まずはSpreadsheetサービスのAPIを使ってデータ行を削除する方法をお伝えします。

前回に引き続き、以下シートを例にとって進めてみましょう。

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

では、このシートから3,4行目にある青森県、岩手県、そして6行目にある秋田県のデータを削除したいと思います。

deleteRowsメソッドでシートの行を削除する

シートの特定の行を削除するにはdeleteRowsメソッドを使います。

書き方はこちらです。

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

これで指定した行位置から、指定した行数を削除することができます。

行数を省略すると、行数が1行となります。

deleteRowsメソッドで行を削除するスクリプト

deleteRowsメソッドを使ってシートから青森県、岩手県、秋田県のデータ行を削除するスクリプトを作りました。

こちらです。

function deleteRecords(){
  const sheet = SpreadsheetApp.getActiveSheet();
  sheet.deleteRows(6);
  sheet.deleteRows(3,2);
}

これを実行すると、以下のように目的のデータ行を削除することができます。

Google Apps Scriptでシートの行を削除した

この場合の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メソッドは配列の要素の削除と追加を両方行える便利メソッドです。

削除する場合の書き方はこちらです。

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

これを使って、以下のようにするわけです。

  • 指定の配列番号の位置から
  • 削除する要素数分の要素を削除

要素を挿入する場合は、以下の記事で使い方を紹介していますので、ご参考くださいね。

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

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メソッドを使いましょう。

Sheetオブジェクト.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でスプレッドシートの行を削除する二つの方法についてお伝えしました。

今回の例では配列にすることによる「うま味」は感じづらいかも知れませんが、一般的には条件分岐などの処理も入ってくるので、配列を使ったほうが圧倒的に処理速度が速くなる場合が多いです。

これまでお伝えした、追加、挿入とともに削除の方法もぜひ覚えておいてください。

次回も配列を使ったシートの操作のテクニックとして、配列の検索をするメソッドについてお伝えしますね。

Google Apps Scriptで配列の検索をするindexOfメソッドとlastIndexOfメソッド
Google Apps Scriptでスプレッドシートのデータを配列に格納して操作をする方法をお伝えしています。今回は配列を検索するindexOfメソッド、lastIndexOfメソッドの使い方です。

どうぞお楽しみに!

連載目次: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をコピーしました