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

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

目次

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

ソースコードと解説

今回は、以下の条件でコードを記述しています。

  1. スプレッドシートのdataシートに於けるF列(6列目)にチェックが入ってるものを削除対象とします。
  2. 削除する前にdataシートの6列目且つ1列目を昇順でソートしています。
  3. 削除すべきデータがない場合には、削除は実行しないようにチェックを付けています。

ソースコード

解説

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」を加算しています。

実行結果

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

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

ポイント

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

関連リンク

Pocket
このエントリーをはてなブックマークに追加
Bookmark this on Yahoo Bookmark
Pocket