各種申請データや各種台帳等に於いて、すでに作業が完了しチェックを付けたものについては、一定の期日にスプレッドシートから削除したいというシーンがあります。1件2件程度であれば手動で削除すればOKですが、その量が膨大であり、尚且つ対象となる行のデータが必ずしも順番通り連続して並んでいるとは限りません。しかもこの手の作業は、できれば毎日、深夜にでも勝手にやっておいて欲しいルーチンの一つです。

そこでこれを実現する為のコードを今回は作成してみたいと思います。今回は、スプレッドシート上から実行するようにしてありますので、そのままではスクリプトトリガーで深夜に自動実行というわけにはいきませんが、一部のコードを変更すれば、スクリプトトリガーでも使用可能です。

今回使用するスプレッドシート

ソースコードと解説

ソートしてdeleteRowsするコード

解説

1行ずつ行のデータを読み取り、deleteRowコマンドを発行するやり方はExcelのようなローカルのPCで動作するプログラムとしては問題ないのですが、Google Spreadsheetはスクリプトの動作時間に限りがあり、尚且つこの手法は都度都度メソッドを呼び出す為非常に動作が遅いです。その為、5分の壁にぶつかりスクリプトが停止します。今回はこれを回避する為に一括削除を行うようにしています。数千行のデータであっても数秒でデータの削除が可能です。

スプレッドシートのデータの並び替え

dataシートのデータに於いて、A2:Fの範囲で一括取得後にsortを実行しています。6列目のF列をまず昇順でならべ、同時に1列目のA列を昇順で並び替えを行うようにしています。それが、sortメソッドであり、一回でこれをやらないと、この並び順に並び替えができませんので注意。一個ずつsortをすると並び替えが思った通りになりません。

  1. sortのcolumnは1からはじまります。0からではありません。
  2. ascendingはtrueで昇順、falseで降順になります。今回は昇順を使用しています。
  3. 同時に複数列をソートする為、{}でくくったソート指定をカンマ区切りで2つ加えています。この順番で優先順位をつけてソートが掛かります。
  4. sortを2回別のメソッドとして実行しても、3の結果にはなりません。

配列データより削除開始行と削除終了行数を取得する

startflagとは✔のついた行があった場合にtrueにするようにしています。これは、削除すべき行がない場合(つまり、false)だった場合にスクリプトをそこで終了させる為に必要なものです。また、✔のついた行を発見したら以下の作業をしています。

  1. startflagをtrueにしています。
  2. 削除開始行であるstartcntに格納しています。
  3. 削除終了行であるendcntに値をまず1加えています。
  4. その後のルーチンで✔があればendcntに1加えるようにしています。
  5. ✔がなくなった段階でforループを抜けて、startcntとendcntが確定します。

行の削除の実行

startcntとendcntで取得した数値を元にデータを削除しますが、必ずしも✔のついた行があるとは限りません。0であった場合には、スクリプトを停止させます。そのままdeleteRowsを実行数とエラーになってしまうからです。よって以下の手順で行削除を行わせています。

  1. startcntが初期値の0のままならば、✔データが無いので、その場でreturnしてスクリプト終了
  2. startcntが0じゃなければ、deleteRowsで開始行と終了行数を引数に入れて実行

ここで注意したいのが、deleteRowsは何行目~何行目という指定ではないということです。開始行から何行分を削除するという指定なので勘違いをしないように注意して下さい。また、スクリプト内でstartcntが2から始まっていますが、これは1行目はタイトル行だからこのようにしています。また、一応2行目から始まるとは限らないという事を考慮して、forのカウンタである「i」を加算しています。

配列を使って処理するコード

  • ソートしてから削除する方法に似ていますが、配列で取ったデータより、チェックのないデータを抽出して貼り付ける方法です。
  • 貼付け後に、空白行が下に残るので、それらをdeleteRowsで削除しています。
  • 貼り付ける前にデータをclearContentで消去しています。条件付き書式設定を残したいので、clearメソッドは使用していません。
  • 空白行までの実データ数をループで取得しています。getDataRangeですと、色付きのセルはデータ有りと見なされるので、この手法を取っています。
  • 実データ数と取得データ数が一致する場合は、空白行無しということで、プログラムを終了させています。
  • 差分が1行以上ある場合には、deleteRowsで削除を実行しています
  • 配列で処理する方法は、応用すれば✔の入ってるデータだけ、別のシートに退避なんてこともできますね。

実行結果

スプレッドシートを開き以下の作業をすると、行が削除されます。

  1. 削除対象にしたい行のF列において、チェックを付け加える
  2. メニューより「作業用」⇒「削除実行」もしくは「削除実行その2」をクリックする。削除実行がソートしてから削除、その2が配列データを貼り付け直してから削除のパターンです。
  3. 最初の一回だけ承認作業があります。
  4. 承認後、チェックの入った行がソートされた上で一括削除されます。

ポイント

  • 今回のスクリプトをトリガーで使いたい場合には、予めスプレッドシートのIDを記述しておく必要があります。
  • スクリプトトリガーで使用する場合には、SpreadsheetApp.getActiveSpreadsheetではなく、SpreadsheetApp.openById()にて、引数にスプレッドシートのIDを入れて使いましょう。
  • 今回のスクリプトは、alertで問い合わせ等をしていないので、いきなり削除されますので、実務で使う場合には、alertで削除するかどうかを問い合わせするコードを追加すると良いでしょう。
  • 削除する対象の行がわかりやすいように、数式による条件付き書式設定を加えています。
  • 一行ずつ削除するわけではないので、非常に高速で処理が可能です。
  • いわゆる行をデータの下から精査して一行づつ削除する行削除コード(deleteRowメソッド)は、数十行で5分の壁に遭遇して止まる可能性があります。deleteRowsを使うのはこのためです。

関連リンク

共有してみる: