Google Apps Scriptでスプレッドシートの特定行をまとめて削除【GAS】
各種申請データや各種台帳等に於いて、すでに作業が完了しチェックを付けたものについては、一定の期日にスプレッドシートから削除したいというシーンがあります。１件２件程度であれば手動で削除すればOKですが、その量が膨大であり、尚且つ対象となる行のデータが必ずしも順番通り連続して並んでいるとは限りません。しかもこの手の作業は、できれば毎日、深夜にでも勝手にやっておいて欲しいルーチンの一つです。
そこでこれを実現する為のコードを今回は作成してみたいと思います。今回は、スプレッドシート上から実行するようにしてありますので、そのままではスクリプトトリガーで深夜に自動実行というわけにはいきませんが、一部のコードを変更すれば、スクリプトトリガーでも使用可能です。
目次
今回使用するスプレッドシート
ソースコードと解説
ソートしてdeleteRowsするコード
//特定行を削除するコマンド
function deleteRows() {
//スプレッドシートのデータを並び替え
var ss = SpreadsheetApp.getActiveSpreadsheet();
var temp = ss.getSheetByName("data");
var range = temp.getRange("A2:F");
//2列分をソートして揃える
range.sort([{column: 6, ascending: true},{column: 1, ascending: true}]);
//並び替えデータを取得して、削除開始行と削除終了行を取得する
var sheet = temp.getRange("A2:F").getValues();
var dlength = sheet.length;
var startflag = false;
var startcnt = 0;
var endcnt = 0;
for(var i = 0;i<dlength;i++){
//開始行を特定する
if(startflag == false){
if(sheet[i][5] == "処理済み"){
startcnt = 2 + i;
endcnt = endcnt + 1;
startflag = true;
continue;
}else{
continue;
}
}
//終了行を特定する
if(sheet[i][5] == "処理済み"){
endcnt = endcnt + 1;
}else{
break;
}
}
//削除すべき行がない場合はプログラムを終了する
if(startcnt == 0){
return;
}
//行を削除する
temp.deleteRows(startcnt, endcnt);
}
解説
1行ずつ行のデータを読み取り、deleteRowコマンドを発行するやり方はExcelのようなローカルのPCで動作するプログラムとしては問題ないのですが、Google Spreadsheetはスクリプトの動作時間に限りがあり、尚且つこの手法は都度都度メソッドを呼び出す為非常に動作が遅いです。その為、5分の壁にぶつかりスクリプトが停止します。今回はこれを回避する為に一括削除を行うようにしています。数千行のデータであっても数秒でデータの削除が可能です。
スプレッドシートのデータの並び替え
dataシートのデータに於いて、A2:Fの範囲で一括取得後にsortを実行しています。6列目のF列をまず昇順でならべ、同時に1列目のA列を昇順で並び替えを行うようにしています。それが、sortメソッドであり、一回でこれをやらないと、この並び順に並び替えができませんので注意。一個ずつsortをすると並び替えが思った通りになりません。
- sortのcolumnは1からはじまります。0からではありません。
- ascendingはtrueで昇順、falseで降順になります。今回は昇順を使用しています。
- 同時に複数列をソートする為、{}でくくったソート指定をカンマ区切りで2つ加えています。この順番で優先順位をつけてソートが掛かります。
- sortを２回別のメソッドとして実行しても、3の結果にはなりません。
配列データより削除開始行と削除終了行数を取得する
startflagとは「処理済み」のついた行があった場合にtrueにするようにしています。これは、削除すべき行がない場合（つまり、false）だった場合にスクリプトをそこで終了させる為に必要なものです。また、「処理済み」のついた行を発見したら以下の作業をしています。
- startflagをtrueにしています。
- 削除開始行であるstartcntに格納しています。
- 削除終了行であるendcntに値をまず1加えています。
- その後のルーチンで「処理済み」があればendcntに1加えるようにしています。
- 「処理済み」がなくなった段階でforループを抜けて、startcntとendcntが確定します。
行の削除の実行
startcntとendcntで取得した数値を元にデータを削除しますが、必ずしも「処理済み」のついた行があるとは限りません。0であった場合には、スクリプトを停止させます。そのままdeleteRowsを実行数とエラーになってしまうからです。よって以下の手順で行削除を行わせています。
- startcntが初期値の0のままならば、「処理済み」データが無いので、その場でreturnしてスクリプト終了
- startcntが0じゃなければ、deleteRowsで開始行と終了行数を引数に入れて実行
ここで注意したいのが、deleteRowsは何行目～何行目という指定ではないということです。開始行から何行分を削除するという指定なので勘違いをしないように注意して下さい。また、スクリプト内でstartcntが2から始まっていますが、これは１行目はタイトル行だからこのようにしています。また、一応2行目から始まるとは限らないという事を考慮して、forのカウンタである「i」を加算しています。
配列を使って処理するコード
//配列を使ってデータのうち必要なものだけを残す
function vanishrows(){
//スプレッドシートのデータを取得
var ss = SpreadsheetApp.getActiveSpreadsheet();
var temp = ss.getSheetByName("data");
var range = temp.getRange("A2:F");
var data = range.getValues();
var length = data.length;
//データをクリアする
range.clearContent();
//配列を用意
var array = [];
//ループでチェックの入っていないデータだけを取得
for(var i = 0;i<length;i++){
//チェック付かどうか精査
if(data[i][5] == "✔"){
//何もしないでスルーする
}else{
array.push(data[i]);
}
}
//配列の縦横の数を取得してシートに貼り付け
var lastColumn = array[0].length; //カラムの数を取得する
var lastRow = array.length; //行の数を取得する
temp.getRange(2,1,lastRow,lastColumn).setValues(array);
//空白行を削除する
var dataman = ss.getSheetByName("data").getRange("A:A").getValues();
var dlength = dataman.length; //削除対象最終行を特定する
var cnt = 0;
var startcnt = 0;
for(var i = 0;i<dataman.length;i++){
if(dataman[i][0] == ""){
break;
}else{
cnt = cnt + 1;
}
}
//startcntとdlengthの差分を取る
var sabun = dlength - cnt;
if(sabun == 0){
return;
}else{
startcnt = cnt + 1;
}
//行を削除する
temp.deleteRows(startcnt, sabun);
}
- ソートしてから削除する方法に似ていますが、配列で取ったデータより、チェックのないデータを抽出して貼り付ける方法です。
- 貼付け後に、空白行が下に残るので、それらをdeleteRowsで削除しています。
- 貼り付ける前にデータをclearContentで消去しています。条件付き書式設定を残したいので、clearメソッドは使用していません。
- 空白行までの実データ数をループで取得しています。getDataRangeですと、色付きのセルはデータ有りと見なされるので、この手法を取っています。
- 実データ数と取得データ数が一致する場合は、空白行無しということで、プログラムを終了させています。
- 差分が1行以上ある場合には、deleteRowsで削除を実行しています
- 配列で処理する方法は、応用すれば「処理済み」の入ってるデータだけ、別のシートに退避なんてこともできますね
古典的な1行ずつ精査する方法
Google Apps Script初期のころからあった「１行ずつ内容を確認して削除する」という手法です。但し、この手法は1行ずつ削除するメソッドを発行するため、対象データが多い場合、動作が遅くなりおススメ出来ません。
function deleteman(){
//シートを取得する
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("data");
//最終行を取得する
var lastRow = sheet.getLastRow() - 2;
//シートデータを取得する
var range = sheet.getRange("A2:F").getValues();
//1行ずつ精査して
for (var i = lastRow; i >= 1; i--) {
//レコードを一個取り出す
let rec = range[i];
//チェック対象かどうか？
if(rec[5] == "✔"){
//行を削除する
sheet.deleteRow(i + 2)
}
}
}
- getLastRowで最終行を取得します。しかしこれは行インデックスで1から始まります。また、データは2行目以降からとなっているので、意図的に-2を指定して配列で処理できるようにしています（配列は0から始まり、またA2:Fでデータを取ってるので1行目を除外してるため）
- lastRowの値でループを逆回しにします。1行ずつ処理する場合は上からではなく下から処理をしないと削除した分だけ位置がずれてしまうためです。よってループは加算ではなくi--として減算となっています。
- sheet.deleteRowにて行を削除しますが、ここで指定するのは行インデックスなので、iの値に+2で配列の場合の値から値を戻してあげる必要があります。
やり方がちょっとややこしい上に、1行ずつ削除だとデータ量が多い場合6分の壁にぶつかる可能性もあるため、あまりこの手は最近使いません。
実行結果
スプレッドシートを開き以下の作業をすると、行が削除されます。
- 削除対象にしたい行のF列において、チェックを付け加える
- メニューより「作業用」⇒「削除実行」もしくは「削除実行その２」をクリックする。削除実行がソートしてから削除、その２が配列データを貼り付け直してから削除のパターンです。
- 最初の一回だけ承認作業があります。
- 承認後、チェックの入った行がソートされた上で一括削除されます。
ポイント
- 今回のスクリプトをトリガーで使いたい場合には、予めスプレッドシートのIDを記述しておく必要があります。
- スクリプトトリガーで使用する場合には、SpreadsheetApp.getActiveSpreadsheetではなく、SpreadsheetApp.openById()にて、引数にスプレッドシートのIDを入れて使いましょう。
- 今回のスクリプトは、alertで問い合わせ等をしていないので、いきなり削除されますので、実務で使う場合には、alertで削除するかどうかを問い合わせするコードを追加すると良いでしょう。
- 削除する対象の行がわかりやすいように、数式による条件付き書式設定を加えています。
- 一行ずつ削除するわけではないので、非常に高速で処理が可能です。
- いわゆる行をデータの下から精査して一行づつ削除する行削除コード（deleteRowメソッド）は、数十行で5分の壁に遭遇して止まる可能性があります。deleteRowsを使わないのはこのためです。
