Googleスプレッドシートのアストルティア防衛軍襲撃予報で過ぎた日の行を自動非表示にした方法

メンドクサイこたぁこんぴうたにやらせちまえ！

結論
兵団名を表示する仕組み
過ぎた日付は非表示にする仕組み
- スクリプトはこんな感じ
- 実行はトリガーで
まとめ

結論

1列目に日付が順に並んでいるんでA2の日付から昨日の日付の行番号を特定したらループで過去7日分をhideRowする処理のGoogle Apps Script（GAS）書いて時間ベースのイベントで日々回す。

・・・これだけじゃテキストが少ないので、表示周りも含めて色々と解説。

兵団名を表示する仕組み

アストルティア防衛軍襲撃予報はGoogle スプレッドシートで作ってるんです。

docs.google.com

最初は単なるコピペでやってたんですが、計算式とか使える環境なので色々と組み込んで簡単に更新できるようにしてみた。

兵団の表示は計算のみで行う

兵団の表示は全て計算でやっている。

日付と時間から襲撃サイクルの数字を算出する感じ。

B64セルの計算式はこんな感じ。

=OFFSET('兵団'!$K$3, MOD(B$1+24*DATEDIF("2023/1/13",$A64,"D"),31), 0)

襲撃パターンの都合上2023/1/13を起点として経過した日数をパターン数の31で除算し、その余りでパターン番号を取るという古の手法。

番号が算出できれば「兵団」シートの該当バージョンの一覧からテキストを引っ張るだけ。

当初はVLOOKUPで取っていたけど順番が決まっているんでOFFSETで軽量化。

計算式はコピペできるように絶対参照と相対参照を組み合わせている。

横にフィルで伸ばしたら行単位でフィルすることでいくらでも増やせる。

色付けは条件付き書式を使う

セルや文字の色付けは条件付き書式で一括設定。

色は本家の表示に寄せるようにしてるけど、粘塊のみ虹色を出せないのでスライムカラーにしてる。

シート全体に設定しているんで計算式で文字が表示されれば色が自動で付くので便利。

経過した時刻をグレーアウトする仕組み

GASでやろうかとも思ったんですが、遅いし重いしリアルタイム性もイマイチなので却下。

条件付き書式にグレーアウトする条件式を組み込んで対応。

=if(COLUMN()=1,$A1<TODAY(),$A1+((COLUMN()-1)/24)<NOW())

1列目は日付が入っているんで単純に今日より前であればグレーアウト。

2列目以降は時刻の情報が必要なので、列番号から1を減じて24で除算することで生成した時刻値を1列目の日付値に加算することで該当セルがグレーアウトする日時を算出。

あとは現在日時と比較するだけ。

過ぎた日付は非表示にする仕組み

冒頭の結論にも書いてる通りGASの定時処理でやってる。

元々は手作業で不定期的に非表示してたんです。

経過時刻のグレーアウトを実装してからは過ぎた日が目立つようになり頻度を上げたんですが、そのうち一週間とか放置するようになってきた。

なんか自動化できないかなぁと調べたらGASがスケジュール機能まで持っていたのでこれだ！ということで実装。

スクリプトはこんな感じ

そんなにややこしいことはしてないので処理はコメントから察してください。

function myFunction() {
  //1つ目のシート
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
  //シートの最終行
  let lastRow = sheet.getLastRow();
  //今日
  let today = new Date();
  //今日の0時
  let today0 = new Date(today.getFullYear(), today.getMonth(), today.getDate());
  //先頭行の日付
  let topDate = sheet.getRange(2, 1).getValue();
  //先頭行の日付から今日までの経過日数
  let diffDays = (today0 - topDate) / 1000 / 60 / 60 / 24;
  //最初の行を今日から7日前にセット（2はデータの先頭行番号）
  let row = 2 + diffDays - 7;
  //最終行までループ
  while (row <= lastRow) {
    //行の日付が今日より前か
    if(sheet.getRange(row, 1).getValue() < today0) {
      //行を非表示にする
      sheet.hideRow(sheet.getRange(row, 1));
      //次の行番号へ
      row++;
    }
    else {
      //今日に到達したのでループを抜ける
      break;
    }
  }
}

冗長な書き方な気もするんですが、趣味のプログラムなので後から見て思い出せるような書き方にしてます。関数名も変えてなかった。

日付をリテラルにしたり先頭行からぶん回すようにすればもっと短くできるんですが、汎用性やパフォーマンスが落ちるのでシートから取って計算してます。