✍️

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

2020/12/18に公開

Google Apps Script

Google スプレッドシート

Google Calendar

tech

はじめに

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

手順

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

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

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

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

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

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

メニューを作成する

onOpenなどの特定のシートだけで実行できるトリガーは
シートに紐づけ(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のメンバー名が異なる場合はカレンダーのメンバー名を更新する

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);
    }
  }
}

さいごに

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

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

シートを整備して

通知の仕方を調整

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