Google Sheetのシフト表をGoogle Calendarと同期をとる

公開:2020/12/17
更新:2020/12/17
5 min読了の目安(約5100字TECH技術記事

はじめに

QiitaのGoogle Apps Script Advent Calendar 2020 | 4日目の記事として書きました。

こういうシートをシフト用に作成したGoogle Calendarに反映することを想定しました。

手順

Googleのユーザの持っているデフォルトのカレンダーは使わず

  1. シフト管理用のCalendarを新規に作成
  2. メニューを作成する
  3. シートに書いたシフト表をCalendarに同期
  4. Calendarからシフト表に反映する

シートは管理用に、Calendarはシフト調整される人の入力・表示用です。

シフト管理用のCalendarを新規に作成

カレンダー名を入力し、カレンダーを作成ボタンで作成します。

カレンダーを作成したら、カレンダーIDをメモります。

メニューを作成する

onOpenなどの特定のシートだけで実行できるトリガーは
シートに紐づけ(bound)されたスクリプトにする必要があります。

https://developers.google.com/apps-script/guides/triggers/

https://developers.google.com/apps-script/guides/bound

ツール > スクリプト エディタからスクリプトエディタを起動します。

const CALENDAR_SHIFT = "<作成したカレンダーのID>";
//日付計算用の定数
const MILLIS_PER_DAY = 1000 * 60 * 60 * 24;
const RANGE_A1_CALENDAR = "B2:E400";//日付,曜日,祝日,担当者
const RANGE_A1_CALENDAR_START_DATE = "B2";
const SHEET_NAME = "シート1";

function onOpen(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  //メニュー配列
  var myMenu=[
    {name: "シートからカレンダーに書き込み", functionName: "writeMemberToCalendar"},
    {name: "カレンダーからシートに書き込み", functionName: "writeMemberToSheet"},
  ];
  sheet.addMenu("GAS",myMenu); //メニューを追加
}

ブラウザからシートを読み込みすると表示されました。

シートに書いたシフト表をCalendarに同期

  • カレンダー中のイベントは常に1つ
  • シートにメンバー名がなかったらCalendarからイベントを削除
  • シートとCalendarのメンバー名が異なる場合はカレンダーのメンバー名を更新する

シートのメンバーを全部消してカレンダーに反映させる操作で
カレンダーのEventをまとめて100件くらい削除したあたり、削除対象が多すぎてエラーが発生しました。

function setCalendar(member,date) {
  var calendar = CalendarApp.getCalendarById(CALENDAR_SHIFT);
  var events = calendar.getEventsForDay(date);
  //イベントがあったら書き換えるか、そのままか、消す
  for (const event of events){
    if (member){
      var eventTitle = event.getTitle();
      if (member == eventTitle){
        continue;
      } else {
        event.setTitle(member);
      }
    } else {
      event.deleteEvent()
    }
  }
  if (events.length == 0 && member) {
    calendar.createAllDayEvent(member,date);
  }
}

function writeMemberToCalendar(){
  const calendar = CalendarApp.getCalendarById(CALENDAR_SHIFT);
  var editSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);
  var rangeValues = editSheet.getRange(RANGE_A1_CALENDAR).getValues();
  for(var i in rangeValues){
    var date = rangeValues[i][0];
    //カラ行はとばす
    if (date == ""){
      continue;
    }
    //メンバー状況をかきこむ
    var member = rangeValues[i][3];
    Logger.log('i:' + i + ' value:' + member + ' date:' + date);
    setCalendar(member,date);
  }
}

Calendarからシフト表に反映する

  • Calendarからはシートに記載した日付全部のデータを取得
  • 過去のメンバー名変更も反映
  • シフト表とカレンダーに書いたメンバー名が同じだったら処理しない
function readMembersFromCalendar(){
  var editSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);
  var startDate = editSheet.getRange(RANGE_A1_CALENDAR_START_DATE).getValue();

  //シートの先頭日付から一年間分くらい
  var FromNow = new Date(startDate.getTime() + MILLIS_PER_DAY * 365);
  const calendar = CalendarApp.getCalendarById(CALENDAR_SHIFT);
  const events = calendar.getEvents(startDate, FromNow);
  // Logger.log('Number of events: ' + events.length);
  var dateMember = {};
  for(const event of events){
    var start = event.getStartTime();
    var member = event.getTitle();
    dateMember[start] = member;
  }
  return dateMember;
}

function getSheetDate() {
  var editSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);
  var rangeValues = editSheet.getRange(RANGE_A1_CALENDAR).getValues();
  var sheetDates = new Array();
  for (var v of rangeValues){
    sheetDates.push(v[0]);
  }
  return sheetDates;
}

function writeMemberToSheet(){
  var calendarMembers = readMembersFromCalendar();
  var editSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);
  var rangeValues = editSheet.getRange(RANGE_A1_CALENDAR).getValues();
  for(var i in rangeValues){
    //日付データにメンバーがいるか
    var calMember = calendarMembers[rangeValues[i][0]];
    //undefinedはカラ文字に
    if (!calMember){
      calMember ="";
    }
    //カレンダー情報とシフト表の記載メンバーが違えば更新
    if (calMember != rangeValues[i][3]){
      Logger.log('i' + i + 'value: ' + rangeValues[i][0]);
      var rowNo = Number(i) + 2;
      var rowNoA1 = "E" + rowNo;
      editSheet.getRange(rowNoA1).setValue(calMember);
    }
  }
}

さいごに

ここら辺を意識したのは初めてでした。

https://developers.google.com/apps-script/guides/triggers/#available_types_of_triggers

Calendarに登録するメリットは、Calendarアプリで表示することもですが
PCやスマホでの通知を個人で調整できるところです。

シートを整備して

https://zenn.dev/fuyu/articles/fa3a7e5fea4292
通知の仕方を調整
https://zenn.dev/fuyu/articles/d8e44aaec09fda

日単位でチームで作業をするときはこれで一通り回せる感じがします。
あとは時間を決めて登録するときや、複数メンバーを入れるとき、状況に合わせて
変えてほしいです。