【GAS活用術⑪】過ぎた日付の予定行を「週に一度」自動的に削除・移動する

Google Apps Script (GAS)を、もっと身近に、日々の暮らしに。

前回の【GAS活用術⑩】では、以下のような予定表において、予定の日付が過ぎたら、その予定行を「毎日」削除・移動する方法について考えました。

日付ベースのトリガーを設定することで、GASの関数を毎日、実行し、対象となる予定がないかチェックをして、あれば実際にその予定を削除・移動する、という処理を自動化しました。

今回は、過去日付となった予定を「週に一度にまとめて」削除・移動する方法です。

もともと、私がシステムシステム担当をしている、読み聞かせボランティアの活動日は、そんなに多いわけではありません。【GAS活用システム⑦】で紹介した、活動日を登録したGoogleカレンダーで見るとわかりやすいですが、週に０～２日程度です。

となると、週に一度、例えば、毎週土曜日の午前1時～2時の間に、古くなった予定データをまとめて削除する方が、より適切な実行頻度と言えそうです。

その他に、週末に活動日が集中しているので、週明けの月曜または火曜に一括で削除・移動したいという場合などでも使えそうです。

というわけで、週イチ、予定行を自動的に削除・移動する方法についてみていきましょう。

週イチ、削除はfindIndexメソッドで

それでは、具体的に、下記の2024年6月の予定表を例に、毎週土曜日の午前1時～2時の間に、古くなった予定データをまとめて削除するGASについて考えていきます。

まず、この場合に気を付けたいのは、週末に削除される対象データは0件のこともあれば、複数件のこともある、ということです。

一件ずつ過去の日付かどうかをIF文で判断するよりも、条件に一致したはじめのインデックス番号を戻す、findIndexメソッドを使う方が効率的です。

この時、findIndexメソッドで指定する条件を、「トリガー実行日時よりも先日付のもの」と指定すると、削除すべき行数を取得できることがわかりました。

具体的に、2024年6月のそれぞれの土曜日で、findIndexで先日付の予定を見つけた時に返すインデックス番号を見ていきましょう。

まず、A2セルからA列最終行までの日付データを二次元配列として取得します。二次元配列は、【GAS活用術①-7】で紹介したように列のインデックス番号、行のインデックス番号をもちます。

今回は、列のインデックス番号は0だけになります。マンションにたとえるなら、各階に部屋が一つの、縦に細長いマンションですね。

まず、2024/06/08(土) 午前1時台にトリガーが起動されたとします。その時、上から順に（行のインデックス番号0から順に）、2024/06/08(土) 午前1時台よりも先日付を探すと、2024/06/18が条件に初めて一致するので、インデックス番号として1が戻されます。この時、削除したい過去日付の予定は、2024/06/05の1行だけです。

次に、一週間後の2024/06/15(土) 午前1時台にトリガーが起動されたときは、すでに2024/06/05の予定は削除されているはずなので、先日付のインデックス番号は0が戻されます（削除すべき過去日付の予定は、0行です）。
2024/06/21(土)のトリガーでは、戻されるインデックス番号は2になります（削除すべき過去日付の予定は、2024/06/18と2024/06/19の2行です）。

このように、findIndexで先日付の予定を探すと、戻されるインデックス番号がちょうど削除すべき行数と一致するということがわかります。

あと気を付けたいのは、先日付の予定がみつからないときは、インデックス番号として-1が返される点です。

この時は、予定が登録されているならば、そのすべてが過去日付の予定ということなので、登録されているすべての予定数を削除することになります。

結構面倒ですが、いろんなパターンを考え、それに対応する必要があります。で、すべてのパターンを網羅している（はずの）GASは↓こちらです。

function deleteOldPlans(){         //週に一度のトリガーで過去日付の予定を削除
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sh = ss.getSheetByName("予定表"); //「予定表」シートを取得
  const date = new Date();               //トリガー実行時の現在日時を取得
  //date.setDate(date.getDate() +1);     //日付を変更（明日なら+1, 昨日なら-1） 
  const lastRow = sh.getLastRow();       //シートの最終行を取得

  if (lastRow > 1){                  //予定がある(2行目以降が存在する)時のみ

    //A列の日付データのみを2次元配列として取得
    const planDates = sh.getRange(2,1,lastRow-1).getValues(); 

    //トリガー実行時の現在日時より先日付の予定を探してindex番号を返す
    let index = planDates.findIndex(planDate => (date < planDate[0])); 

    if (index != 0){                 //削除すべき予定が0なら何もしない
      if (index == -1){              //先日付の予定が見つからなければ   
        index = planDates.length;    //すべての予定数をindexにセット
      }
      sh.deleteRows(2,index);        //2行目からindex分の行数を削除
      //ここにこのタイミングで実行したい関数があれば記述する
      //updateDateList();            //フォームのプルダウンの日付の選択肢を更新
    }
  }
}

findIndexは図解すると以下のようになります。

複数行削除する可能性があるので、deleteRowsメソッドを使用して、2行目からindex分の行数を削除しています。

このdeleteOldPlans関数を実行するトリガーは、以下の通りで「週ベースのタイマー」にします。

曜日や時刻はお好みで。例えば、週末に活動予定が集まっているならば、毎週月曜日の深夜に設定するとか、祝日の月曜も活動するなら毎週火曜日にするなど、状況に応じて設定してみましょう。

週イチ、移動もfindIndexメソッドで

古くなった予定を削除するのではなくて、別シートに移動したいのであればこちらになります。基本は、移動も削除と同じです。

というのも、【GAS活用術⑩】で説明したように、copyToメソッドで別シートにデータをコピーしてから、元データを削除する ＝ 移動、だからです。

元のシート名を"予定表"、移動先のシート名を"アーカイブ"とした時の、GASは以下の通りです。

function moveOldPlans(){         //週に一度のトリガーで過去日付の予定を移動
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const org_sh = ss.getSheetByName("予定表");      //「予定表」シートを取得
  const arc_sh = ss.getSheetByName("アーカイブ");  //「アーカイブ」シートを取得
  const date = new Date();               //トリガー実行時の現在日時を取得
  //date.setDate(date.getDate() +1);      //日付を変更（明日なら+1, 昨日なら-1） 
  const org_lastRow = org_sh.getLastRow();       //シートの最終行を取得

  if (org_lastRow > 1){                  //予定がある(2行目以降が存在する)時のみ

    //A列の日付データのみを2次元配列として取得
    const planDates = org_sh.getRange(2,1,org_lastRow-1).getValues(); 

    //トリガー実行時の現在日時より先日付の予定を探してindex番号を返す
    let index = planDates.findIndex(planDate => (date < planDate[0])); 

    if (index != 0){                    //削除すべき予定が0なら何もしない
      if (index == -1){                 //先日付の予定が見つからなければ   
        index = planDates.length;       //すべての予定数をindexにセット
      }
      const lastCol = org_sh.getLastColumn();     //元シートの最終列を取得
      const arc_lastRow = arc_sh.getLastRow();    //アーカイブ先シートの最終行を取得

      //元シートの2行目の範囲を取得後、copyToでアーカイブ先シートの最終行+1のA列のセルにコピー
      org_sh.getRange(2,1,index,lastCol).copyTo(arc_sh.getRange(arc_lastRow+1,1));

      //アーカイブ先シートの交互の背景色を拡張させるためにコピーした行数分挿入
      arc_sh.insertRowsAfter(arc_lastRow+index, index);    

      org_sh.deleteRows(2,index);        //2行目からindex分の行数を削除
      //ここにこのタイミングで実行したい関数があれば記述する
      //updateDateList();            //フォームのプルダウンの日付の選択肢を更新
    }
  }
}

最後はわりに長めのGASになってしまいましたね。ふぅ。

---

以上、前回の【GAS活用術⑩】と今回の【GAS活用術⑪】で、簡単そうで、意外に奥深かった、古くなった予定データを削除・移動する、でした。

今回もいろいろ端折っていますが、前回のとあわせてご覧いただければ、幸いです。