スプレッドシートでカレンダーを作る② GAS編

やりたいこと

第1回は上記で、GASを使わずに
関数と条件付き書式を使って作成をしました。

今回は、第1回で作成したカレンダーを元に、
GASを使ってリスト形式のデータ→カレンダーに表示をやってみたいと思います。

上記は別システムからスプレッドシートにインポートしてきたデータです。
項目は社外秘の内容で溢れていたので、適当な項目と内容に変更しました…

本来は反映させる項目を判定するために、チェックボタンを設置していますが、
今回この記事では省略します

それを、一つのボタンで
下のように表示します

スクリプト実行の流れ

リストシートに内容を記入
→カレンダーシートに表示する月を入力
→スクリプト実行ボタンをぽちっ

実装の前に考慮すべきところ

まず、前提としてこのシステムを使うのが、

「Excelやスプレッドシートは使えるけど、スクリプトとは??????」

という方々なので、
スクリプトの特性を知りません。

このまま第1回で使っていたカレンダーに
日付一致したものをリストから転記してもよいのですが、
行を足したり削除されたり、
日付行を誤って削除されたり、
等々されると不具合でるな～と
色々考えた結果、

毎回カレンダーリセットしちゃおう

ということにしました。
果たしてスマートなやり方なのかはわかりません。

実装

const calss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('カレンダー');
const Oricalss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('カレンダー(オリジナル)');

function reset_cal() { //カレンダーリセット関数
  //カレンダーの6行目以降を一旦リセットして、オリジナルから貼り付ける
  calss.getRange("C6:I50").clear();
  var origin_cal_range = Oricalss.getRange("C6:I50");
  origin_cal_range.copyTo(calss.getRange("C6:I50"));

  // 最後の週を消す
  var last_week_day = calss.getRange("C26").getValue().getMonth() + 1;
  var this_month = calss.getRange("I2").getValue();
  if (last_week_day != this_month) {
    calss.getRange("C26:I50").clear();
  }
}

ざっくりいうと、
カレンダーシートの分身であるカレンダー(オリジナル)シートを
予め下記のように作って、編集できないように保護をかけておきます。

そして、
→カレンダーシートの6行目以降を消す
→カレンダー(オリジナル)シートの6行目以降をコピペ
をします。

ついでに、26行目以降の6週目の表記はいらなかったら消します。

const Listss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('リスト');

function list_to_cal() { //｢リスト｣から｢カレンダー｣へ転記

  //カレンダーリセット  
  reset_cal();

  //リストからデータを抽出
  var LastRow = Listss.getRange(pandalistss.getMaxRows(), 3).getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
  var ProjectData = Listss.getRange(3, 3, LastRow - 2, 5).getValues();

  //日付の型を変換
  for (var i = 0; i < ProjectData.length; i++) {
    if (ProjectData[i][1] != "") {
      var NewDateVal = Utilities.formatDate(ProjectData[i][1], 'JST', 'yyyy/MM/dd');
      var NewStartVal = Utilities.formatDate(ProjectData[i][2], 'JST', 'HH:mm');
      var NewFinishVal = Utilities.formatDate(ProjectData[i][3], 'JST', 'HH:mm');
      ProjectData[i][1] = NewDateVal;
      ProjectData[i][2] = NewStartVal;
      ProjectData[i][3] = NewFinishVal;
    }
  }

  //1日の行数
  var MaxRow = 3;

  //カレンダーの日付格納配列　1週間ずつ
  var CalDateData = [];
  for (var i = 0; i < 5; i++) {
    CalDateData.push(calss.getRange(6 + (MaxRow + 1) * i, 3, 1, 7).getValues().flat());
  }
  //カレンダー表記上6週目がある場合
  if (calss.getRange(6 + (MaxRow + 1) * 5, 3).getValue() != "") {
    CalDateData.push(calss.getRange(6 + (MaxRow + 1) * 5, 3, 1, 7).getValues().flat());
  }

  //日付型変換
  for (var i = 0; i < CalDateData.length; i++) {
    for (var j = 0; j < 7; j++) {
      if (CalDateData[i][j] != "") {
        var newValues = Utilities.formatDate(CalDateData[i][j], 'JST', 'yyyy/MM/dd');
        CalDateData[i][j] = newValues;
      }
    }
  }

  //1日ずつカレンダーに内容(DayContents)をはめていく
  for (var i = 0; i < CalDateData.length; i++) { //1週ずつ

    //日付が入っている行の定義
    if (i == 0) {
      var DayRow = 6; //カレンダーの1週目の行
    } else {
      var DayRow = DayRow + MaxRow + 1; //2週目以降。
    }
    //1日の行数(再定義)
    var MaxRow = 3;

    //日付の上に線を引く
    calss.getRange(DayRow, 3, 1, 7).setBorder(true, null, null, null, null, null);


    for (var j = 0; j < 7; j++) { //日～土の1週間分
    
      //日付が一致するものを抽出
      var DayContents = ProjectData.filter(array => array[1] == CalDateData[i][j]); //1日分が入っている配列

      if (DayContents != "") {
        //行増やす場合
        if (DayContents.length > MaxRow) {
          while (DayContents.length > MaxRow) {
            calss.insertRowAfter(DayRow + MaxRow);
            MaxRow++;
          }
        }
        //カレンダーに1行ずつ貼り付け
        for (var k = 0; k < DayContents.length; k++) {
          //カレンダーに表示する内容
          var DisplayContents = DayContents[k][2] + "-" + DayContents[k][3] + " " + DayContents[k][4] + " " + DayContents[k][5];
          calss.getRange(DayRow + 1 + k, 3 + j, 1, 1).setValue(DisplayContents);
        }
      }
    }

  }

  //最後に罫線を付ける
  calss.getRange(5, 3, DayRow + MaxRow - 4, 7).setBorder(true, true, true, true, true, null);

}

ポイント① 日付の型を変更させる

var NewDateVal = Utilities.formatDate(ProjectData[i][1], 'JST', 'yyyy/MM/dd');

日付をフォーマットさせないと、
Wed May 25 2022 12:00:00 GMT+0900 (Japan Standard Time)
のデータとして扱われ、
カレンダーに貼り付ける際に日付の比較ができなくなります。
(.getTime()などを使って比較も可能です)

ポイント② .flat()を使う

CalDateData.push(calss.getRange(6 + (MaxRow + 1) * i, 3, 1, 7).getValues().flat());

1行の値取得を配列で行う場合、flat()で1次元配列化しておくと、後々やりやすいです。

ところで

変数の命名規則がバラバラですね…
次は揃えられるよう努力します