以前、

Googleスプレッドシートで昨日までの行を自動的に非表示にする。～日付の比較判定に挫折～MATCH関数を組み合わせてなんとか実現できました。

という記事を書きました。

スプレッドシートで作成した出勤表。スプレッドシートは開くと先頭行からの表示になってしまうので、先週までの行を自動的に非表示にし、今週分から表示することで視認性と操作性を向上させることができました。

出勤表を運営していく上で、２点修正が必要になってきました。

●毎月20日にはその月（先月21日から今月20日まで）の出勤状況をカウントする作業があるので、一時的に一ヶ月さかのぼった分だけ非表示を解除する。

●上記の状態から、今週の月曜より前（先週の日曜日以前）の行を非表示にする。

今回もやはり私の力不足で、スクリプトだけでは実現できず、スプレッドシート上のMATCH関数を使って実現した話です。

前回同様に↑の画像のようにA2からA列に日付が入っているとします。

以前の投稿ではB1にMATCH関数で(A2行から数えて）昨日までの行数を算出し、それをスクリプトで取得し、それを非表示にする行範囲としていました。（さらにトリガーで毎週月曜日に実行することで実現していました）

まずは毎月20日に一ヶ月分を再表示しなければならない点。

MATCH関数で（A2行から数えて）先月21日までの行数を取得するため、A1セルに（A1だと誤操作で編集してしまう可能性があるので、実際の運用の際には他のセルのほうが良いかも。。。）

=MATCH((DATE(YEAR(today()),MONTH(today())-1,20)),A2:A,0)

と入力してみる。

（関数を入力したセルは誤操作防止のため権限で編集不可とし、文字色を背景色と同じ白とすることで目立たなくしておく。）

DATE関数内のMONTHを-1で先月、DATE関数内最後の数字を20と指定することで、いつでも先月の20日を取得できる。それをMATCHが検索してくれて行数を取得してくれる。

MATCH関数

指定した値と一致する範囲内のアイテムの相対的な位置を返します。

MATCH(検索キー, 範囲, 検索の種類)

検索キー – 検索する値です（42、”猫”、I24 など）。

範囲 – 検索対象の 1 次元配列です。

1 より大きい高さと幅の範囲を使用すると、MATCH 関数は #N/A! を返します。
検索の種類 – [省略可 – デフォルトは 1] – 検索の方法です。

1（デフォルト）を指定すると、MATCH 関数は範囲が昇順で並べ替えられていると想定し、検索キー以下の最も大きい値を返します。

0 は完全一致を示し、範囲が並べ替えられていない場合に必須の値です。

-1 を指定すると、MATCH 関数は範囲が降順で並べ替えられていると想定し、検索キー以上の最も小さい値を返します。

次にスクリプトの方で

function maitsukihatsuka() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheetByName("出勤表");
 var startRow = 2;
 var range = sheet.getRange("B1");
 var endRow = range.getValue();
 var range2 = sheet.getRange("A1");
 var sengetsu = range2.getValue();
 sheet.showRows(startRow,endRow);
 sheet.hideRows(startRow,sengetsu); 
}

showRowsで一旦非表示を解除（非表示にしていた分を表示）。

変数sengetsuにA1に入っている先月20日の行数をgetValue。

それを用いてhideRowsで先月20日以前を非表示にしてみた。

次は今週の月曜からの表示に戻したい。

前回のB1のMATCH関数を、先週の日曜日までの行数が算出できるように変更します。

=MATCH(((TODAY())-WEEKDAY(TODAY(),2)),A2:A,0)

今日から今日の曜日番号を引いて、先週の日曜日の日付を求め、それをMATCH関数で検索し（A2行から数えての）行数を取得する。 （関数を入力したセルは誤操作防止のため権限で編集不可とし、文字色を背景色と同じ白とすることで目立たなくしておく。）

WEEKDAY関数についてはコチラも参考に。

構文
WEEKDAY(日付, [種類])

日付 - 曜日を検証する日付です。日付を含むセルへの参照、日付の種類を返す関数、または数値を指定します。

種類 - [省略可 - デフォルトは 1] - 曜日を表すのに使用する番号方式を数値で指定します。デフォルトでは、日曜日を先頭にして数えます（日曜日 = 1）。

種類が 1 の場合、曜日を日曜日から数え始めて日曜日の値を 1 とするため、土曜日の値は 7 となります。

種類が 2 の場合、曜日を月曜日から数え始めて月曜日の値を 1 とするため、日曜日の値は 7 となります。

種類が 3 の場合、曜日を月曜日から数え初めて月曜日の値を 0 とするため、日曜日の値は 6 となります。

スクリプトのほうは前回と変わらず。

function hideShukkinhyou() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheetByName("出勤表");
 var startRow = 2;
 var range = sheet.getRange("B1");
 var endRow = range.getValue();
 sheet.hideRows(startRow,endRow);
}

最後にトリガーを設定。

先月分表示は、毎月20日に日付が変わった直後くらいに実行。

20日が土日祝の場合は21日以降に非表示解除しなければいけないが、今回は自動で対応できず。。。

この場合は年に数回、手作業で。。。もしくはトリガーを特定の日付にして、カレンダーを見て設定しておく。。。

今週の月曜から表示は毎日実行とする。これで、手作業で非表示解除していても、次の日には今週からの表示に戻っている。。。

以上で設定完了。

より便利になった気がする。。。絶対する。。。

でもスクリプトで行検索のハードルが越えられない。。。

for文が難しくて怖い。。。

手軽なスプレッドシート内の関数にすがってしまう。。。。

いつかは、きっときっと。。。

最後までお読みいただきありがとうございました。