スプレッドシート上の複数のシフトからUI入力で更新する月のシフトを選択する

はじめに

前回スプレッドシートから、Googleカレンダーに自動で予定を追加するスクリプトを作成しました。
そちらを読んでからこの記事を読むとわかりやすいと思います。この記事のリンクも置いておきます。

今回は、新しくシートを追加するのではなく、同じシート状に複数のシフト表を追加する場合を想定します。
毎回すべての予定を更新するのは時間がかかるので、月ごとに予定を更新できるようにしました。

目次

1. 今回の仕様
2. 完成時の使用方法
3. コード全体の流れ
4. さいごに

今回の仕様

• メニューのGASから「カレンダーに書き込む」をクリックすると起動する
• 起動後、スプレッドシート上に更新する月を選択するUIが出る
• スプレッドシート状にあるシフトの月がすべてプルダウン選択しとして現れる
• 月選択して、送信するとカレンダーへの書き込みが行われる
• 書き込み終了後、UIは消える

完成時の使用方法

1. 前回同様メニューにGASメニューが出るので選択し「シートからカレンダーに書き込み」をクリック
   

2. 次に更新したいシフトの月を選択し、送信をクリックする
   

3. 送信後カレンダーの書き込みが始まります
   

コード全体の流れ

今回、フロントエンドのUIをHTMLで、バックエンドをGASで作成しました。
そこで、HTMLとGAS間で情報の受け渡しが必要になり、互いのファイル内の関数を起動する必要がある。
その流れを大まかにまとめたほうがわかりやすいと思ったのでまとめます。

おおよそこの順でデータを通信しています。

実装

まず、AppScriptの内容です。
大まかな変更はshowMonthSelectionDialog()、createMonthsList()関数の追加とwriteMemberToCalendar()の引数の変更です。

AddToCalender.gs

const CALENDAR_SHIFT = "<カレンダーID>"


const SHEET_NAME = "sheetシフト2";//シート名を記入
const NUM_ROWS = 31;  // 31行分のデータを取得
const NUM_COLUMNS = 6;  // 5列分のデータを取得
const TITLE_TEXT = 'シフト';//カレンダー名を記入

function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();

  var myMenu=[
    {name: "シートからカレンダーに書き込み", functionName: "showMonthSelectionDialog"},
    //{name: "カレンダーからシートに書き込み", functionName: "writeMemberToSheet"},
  ];
  sheet.addMenu("GAS",myMenu);
}

function setCalendar(date,startTime,endTime) {
  var calendar = CalendarApp.getCalendarById(CALENDAR_SHIFT);
  if (!(date instanceof Date)) {
    date = new Date(date);
  }
  var events = calendar.getEventsForDay(date);
  for (const event of events){
    var eventTitle = event.getTitle();
    if (TITLE_TEXT == eventTitle){
      if (event.getStartTime() != startTime.getTime() || event.getEndTime() != endTime.getTime()) {
        event.setTime(startTime,endTime);
      }
    }

  }
  if (events.length == 0) {
    calendar.createEvent(TITLE_TEXT,startTime, endTime);
  }
}

function delEventfromCarender(date) {
  var calendar = CalendarApp.getCalendarById(CALENDAR_SHIFT);
  if (!(date instanceof Date)) {
    date = new Date(date);
  }
  var events = calendar.getEventsForDay(date);
  events.forEach(function(event) {
    if (event.getTitle() == TITLE_TEXT) {
      event.deleteEvent();
    }
  });
}
function writeMemberToCalendar(startRow,startColumn){
  var editSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);
  // const { startRow, startColumn } = fetchShiftRange(editSheet);
  var rangeValues = editSheet.getRange(startRow,startColumn,NUM_ROWS,NUM_COLUMNS).getValues();

  for(var i in rangeValues){
    var date = rangeValues[i][0];
    //カラ行はとばす
    if (date == ""){
      continue;
    }

    var startDate = new Date(date);
    startDate.setHours(rangeValues[i][2]);
    startDate.setMinutes(rangeValues[i][3]);

    var endDate = new Date(date);
    endDate.setHours(rangeValues[i][4]);
    endDate.setMinutes(rangeValues[i][5]);
    if (startDate.getHours() == 0 && endDate.getHours() == 0){
        delEventfromCarender(date);
        continue;
      } else {
        setCalendar(date,startDate,endDate);
      }
    
  }
}

//月選択用HTMLフォーム
function showMonthSelectionDialog() {
  const html = HtmlService.createHtmlOutputFromFile('SelectMonthForm')
      .setWidth(400)
      .setHeight(300);
  SpreadsheetApp.getUi().showModalDialog(html, 'カレンダーに記入する');
}

//月を取得して返す関数
function createMonthsList() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);

  var textFinder = sheet.createTextFinder("日付");
  var foundCell = textFinder.findAll();
  if (!foundCell) {
    throw new Error('"日付"というテキストを含むセルが見つかりませんでした。');
  }
  var months = {};
  foundCell.forEach(function(day) {
    // console.log(day.getRow())
    var startRow = day.getRow() + 2;
    var startColumn = day.getColumn();
    var date = new Date(sheet.getRange(startRow,startColumn,1,1).getValues())
    months[date.getMonth()] = [startRow,startColumn];
    // console.log(startRow)

  })

  return months;
}

次にHTMLのコードでは、<script>内に二つの関数で成り立っています。起動時に、スプレッドシート内のシフトの月を取得し、プルダウンリストに表示する関数と、送信ボタンクリック時に、選択した月のカレンダーを更新するようにする関数です。

SelectMonthForm.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <!-- CSSを記載 -->
    <style>
      body {
      text-align: center;
      font-family: Arial, sans-serif;
    }
    select, button {
      display: inline-block; /* インラインブロック要素として表示 */
      padding: 10px 15px; /* パディングを適用 */
      margin: 10px;
      border: 1px solid #ccc; /* 境界線を設定 */
      border-radius: 5px; /* 角を丸くする */
      box-shadow: 0 2px 4px rgba(0,0,0,0.2); /* 影を追加 */
      cursor: pointer; /* ホバー時のカーソルをポインタに */
    }
    button {
      background-color: #007bff; /* ボタンの背景色 */
      color: white; /* ボタンのテキスト色 */
      border: none; /* ボタンの境界線を削除 */
    }
    button:hover {
      background-color: #0056b3; /* ホバー時の背景色 */
    }
    .status-message, .selected-month {
      display: none;
      background: pink;
      color: slategray;
      border-radius: 10px;
      padding: 10px;
      margin: 10px auto;
      width: 200px;
    }
    </style>
  </head>
  <body>
    <h2>カレンダーを記入する月を<br>選択してください</h2>
    <select id="month-select">
      <!-- JavaScriptを使用して動的にオプションを追加 -->
    </select>
    <button id="submit" onclick="submitMonth()">送信</button>
    <div id="status" class="status-message">カレンダー記入中...</div>

    <script>
      let months = {};
      document.addEventListener('DOMContentLoaded', function() {
        google.script.run.withSuccessHandler(function(fetchedMonths) {
          months = fetchedMonths;
          const select = document.getElementById('month-select');
          Object.keys(months).forEach(function(month) {
            let option = document.createElement('option');
            option.value = month;
            var monthNum = Number(month) + 1;
            option.textContent = monthNum.toString()  + '月';
            select.appendChild(option);
          });
        }).createMonthsList();
      });
      function submitMonth() {
        const selectedMonth = document.getElementById('month-select').value;
        const selectedMonthStr = (Number(selectedMonth) + 1).toString();
        const selectedMonthText = selectedMonthStr + '月';
        // 選択した月をテキストとして表示
        const selectedMonthDiv = document.createElement('div');
        selectedMonthDiv.textContent = '選択した月: ' + selectedMonthText;
        selectedMonthDiv.className = 'selected-month';
        document.body.appendChild(selectedMonthDiv);

        document.getElementById('status').style.display = 'block';
        document.getElementById('month-select').style.display = 'none';
        document.querySelector('button').style.display = 'none';
        selectedMonthDiv.style.display = 'block';
        var [startRow, startColumn] = months[selectedMonth];
        google.script.run
          .withSuccessHandler(function() {
            document.getElementById('status').style.display = 'none';
            google.script.host.close();
          })
          .writeMemberToCalendar(startRow,startColumn);
      }
    </script>
  </body>
</html>

さいごに

今回はシフトを同じシート内に複製して、作成していくことを想定しました。
月ごとに新しくシートを作成する場合は異なるロジックが必要になるので、このコードでは使用できません。