Googlespreadsheetでオリジナルカレンダー（勤務表）作ってみた

シフト表を自動生成してLINEで見られるようにしたい①

勤務先で多くの先生が働いていて、患者さんに「あの先生は誰？」「あの先生はいついるの？」とよく聞かれます。
LINEが使える方だったら、人に聞かずに知れて文字にも残るから覚えている必要がなくなっていいかなと思いました。
GASとLineBot連携の勉強のためにやってみます！

1.Googlespreadsheetでカレンダーを作る

カレンダー要素を配置する

まずは普通に自家製カレンダーを作ります。
私の仕事先においては第〇週目の何曜日が大事なので、まずは月の1日が必ず左上に来るように作ります。
月を選ぶと曜日ごと動く見た目は気持ち悪いカレンダーです。

• B3セルに年を入れる。
• C3セルは月を入れる。
  セルを選択した状態で、データ→データの入力規則→リストを直接指定→リストを，区切りで入力する。

• C6セルに日付を入れる。
  =DATE(B3,C3,1)
  DATE(年,月,日)となるように書きます。

• E6のセルはC6を基に次の日を入れる。
  =(C6+1)
  さらに次の日も前のセル+1となるように書いていけばOKです。

• C5のセルは下の月日を基に曜日が入るようにする。
  =TEXT(C6,"ddd")

ここまでで大体のカレンダー要素は出来上がります。
後は午前午後や何人の医師が勤務するのかを必要数合わせて調整します。
（MAX6人働けるのでセルは6個にしました。）

土日祝日を作って色分けする

月を変更するとカレンダーも動くので、色分けを工夫します。
まずは紛れ込んでいる翌月をグレースケールします。

• 表示形式→条件付き書式設定ルールを開く。
• 月日が書かれているセルをすべて選択する。

• 書式ルールはカスタム数式を選択し、以下の数式を入力する。
  =MONTH(C6)<>VALUE(SUBSTITUTE($C$3,"",""))

• 書式設定のスタイルは文字をグレーにする。

次に土日を色分けします。面倒なのでまとめてやり方を書きます。
土日それぞれやってください。

• 表示形式→条件付き書式設定ルールを開く。
• ＋条件を追加　する。
• 月日が書かれているセルをすべて選択する。
• 書式ルールはカスタム数式を選択し、以下の数式を入力する。
  日曜日
  =WEEKDAY(C6)=7
  土曜日
  =WEEKDAY(C6)=1
• 書式設定のスタイルは文字を青、背景色を薄い青にする。土曜日は赤。

最後に祝日の設定をします。

• 表示形式→条件付き書式設定ルールを開く。
• ＋条件を追加　する。
• 月日が書かれているセルをすべて選択する。
• 書式ルールはカスタム数式を選択し、以下の数式を入力する。
  =COUNTIF(IMPORTDATA("https://www8.cao.go.jp/chosei/shukujitsu/syukujitsu.csv"),C6:D6)=1
  これは毎年祝日を更新するのが大変なので内閣府の祝日データを引っ張ってくるようにしました。
  表示形式は2022/04/01のCSVだったので形式は合わせた方が良さそうです。
  そして何かの拍子で外れがちです。検証中。
  内閣府HP
  Googleスプレッドシート-半永久的に日付の祝日に色を付ける方法
• 書式設定のスタイルは文字を赤、背景色を薄い赤にする。

これでオリジナルカレンダーの完成です。

2.医師の勤務表を作る

第何週目の何曜日、何時から何時まで勤務する。という形式が多いので細分化した一覧表を作ります。
同じ先生が違う曜日に勤務するならそれも分けます。
一番最後のweek1-5が第〇週目を意味します。

• 拡張機能→Apps scriptを開きます。

function myFunction() {
  
  let spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  let sheet1 = spreadsheet.getSheetByName('シート1');
  let sheet2 = spreadsheet.getSheetByName('シート2');
 
  sheet1.activate();
  sheet2.activate();
 
  var week =["月","火","水","木","金","土","日"]
  for (var h=0; h<7; h++){
    var target = sheet2.getRange("C5:I5")
    var textFinder = target.createTextFinder(week[h]);
    var cells = textFinder.findAll();
    
    for(var i=0; i<cells.length; i++){
      var sell = cells[i].getA1Notation();
    }
    var weeks = sheet2.getRange(sell);

    function hello(){
      for(a=0; a<2; a++){
        var key = Object.keys(set);
        var value = Object.values(set);
        weeks.offset(key[a],0).setValue(value[a]); 
      };
    };

    for (c=0; c<5; c++){
      var line = ["J","K","L","M","N"]

      for(b=0; b<2; b++){
        var specal = ["一般","専門"]
        var am = `=IFERROR(QUERY(シート1!A:N,"SELECT B WHERE E LIKE \'%${week[h]}%\' and D LIKE \'%${specal[b]}%\' and F LIKE \'%:%\' and ${line[c]} LIKE \'%〇%\'",0),"")`        
        var pm = `=IFERROR(QUERY(シート1!A:N,"SELECT B WHERE E LIKE \'%${week[h]}%\' and D LIKE \'%${specal[b]}%\' and H LIKE \'%:%\' and ${line[c]} LIKE \'%〇%\'",0),"")`

        var set = {};
        var d = [3,20,37,54,71];
        if(c == c){
          var e = d[c];
          if (b == 0){
            set[e]=am;
            set[e+8]=pm;
            hello();
          }else if(b == 1){
            set[e+4]=am;
            set[e+12]=pm;
            hello();
          };
        };
      }
    };
  };
};

• 挿入→図形描画で更新ボタンを作成します。
• 更新ボタンにスクリプトを割り当てます。
  今回はmyFunctionです。
  

完成形は名前が出てしまうので載せられませんが、全く同じ表を作ればできるはずです。
祝日、日曜日のセルは全部色付けしたいとか見た目の問題もありますが、とりあえず動くしコードもまとめられたので私的には合格です。
曜日と専門か一般かの判断と何週目かをfor文で回している部分をabcdでごまかしてる感は否めません。
またコードは綺麗に改良します。

あとQUERYでの書き方が分からなくて結構詰まりました。書き方の参考に以下メモを残しておきます。
スプレッドシートで検索をかけたいときに空欄が邪魔することがあるかもしれません。その時は「なし」などとセルを埋めてNULLにならない工夫をします。

// 条件分岐
WHERE a ="りんご" or "みかん" //　この書き方はできない
WHERE a ="りんご" or a = "みかん" //　この書き方が正解

// LIKE句　曖昧検索を行う時に使うクエリ
SELECT [表示要素] FROM [テーブル名] WHERE [要素名] LIKE [曖昧検索の条件];
// 今回だったら、シート1のAからN列を検索範囲にして、E列、D列、F列などにこの文字が入っている、B列の文字を持ってきたい。って書き方をしています。
// 最後の,0),""これは1行目のタイトル？を付けないためです。
// IFERROR（）は＃N/Aを表示しないためです。
// ''を入れるとエラーっぽくなるので\'\'これでくくると良いみたいです。
var am = `=IFERROR(QUERY(シート1!A:N,"SELECT B WHERE E LIKE \'%${week[h]}%\' and D LIKE \'%${specal[b]}%\' and F LIKE \'%:%\' and ${line[c]} LIKE \'%〇%\'",0),"")`        

// 前方一致
LIKE "sample%";

// 後方一致
LIKE "%sample";

// 部分一致
LIKE "%sample%";

// 完全一致
LIKE "sample";

// 関数は`${}`でくくればOK。今回はこんな感じ。
\'%${week[h]}%\'

次はこれをLINE Botに反映させていきたいと思います。

参考

• 【QUERY関数】where句でand,orを使用して複数条件を指定する方法
• 【スプレッドシート】QUERY関数で空白セルを指定・除外する方法
• SQL】LIKE句の基本的な使い方～複数検索する場合の方法まで解説
• 【GAS】文字列を検索して該当データを取得するには？
• スプレッドシートのQUERY関数のSELECT句を使用して、データから列を抽出する方法まとめ
• 【スプレッドシート】VLOOKUP関数の#N/AをIFERROR又はIFで非表示・解消する方法
• ES6：文字列で式展開する方法
• 【GAS】スクリプトエディタからセルの値の取得や入力など基本的な操作をしてみよう
• 【GAS GoogleAppsScript | スプレッドシート】GASでスプレッドシートを操作する(Rangeクラス)