GoogleSheetからGoogleカレンダーに自動で予定を追加する

はじめに

背景

アルバイトのシフトをスプレッドシートで提出していく中で、毎回プライベートカレンダーに手作業で予定を追加していくのが大変で二度手間だったので、作成してみました。

※適宜変更予定です。追加でほしい機能があればコメントしていただければ追加します！

仕様

• スプレッドシートのメニューバーにGASバーを追加し、「シートからカレンダーに書き込み」をクリックすると、自動で予定が追加される。
• シート上の予定を書き換え、「シートからカレンダーに書き込み」をクリックすると書き換えた部分の予定が変更される。
• シフトのフォーマット
  

手順

1. Googleカレンダーの作成
2. カレンダーIDの取得
3. GASコードの追加

Googleカレンダーの作成

• chromeでカレンダーを開く

• 歯車マークから設定を開く
  

• カレンダーを追加の項目の中から新しいカレンダーを作成をクリック

• 適当なカレンダー名を入力し、カレンダーを作成をクリックしてください。ここではカレンダー名を「シフト」とします。

カレンダーIDを取得

このカレンダーにアクセスするためにはカレンダーIDをコード内に追記する必要があります。
ここではその取得方法を記載します。

• 初めのカレンダーに戻るとマイカレンダーに先ほどの「シフト」というカレンダーが追加されています。この右にある三点メニューをクリックしてください

• 開いたページをスクロールしていくと下の方にカレンダーIDが記載されているのでこれをコピーして使用します。後々使用するので確認方法を覚えておいてください。
  

実装

まず、シフト表を作成してください。
仕様に添付した画像の通りの構成で作成すれば下記のコードをそのまま使用し、実装できます。

コード

calenderAddShift.gs

const CALENDAR_SHIFT = "＜カレンダーIDを記載＞"


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

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

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

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

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

function delEventfromCarender(date) {
  var calendar = CalendarApp.getCalendarById(CALENDAR_SHIFT);
  var events = calendar.getEventsForDay(date);
  events.forEach(function(event) {
    if (event.getTitle() == TITLE_TEXT) {
      event.deleteEvent();
    }
  });
}

function writeMemberToCalendar(){
  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);
      }
    
  }
}

//シフト予定セルの範囲の取得
function fetchShiftRange(sheet) {
  var textFinder = sheet.createTextFinder("日付");
  var foundCell = textFinder.findNext();
  if (!foundCell) {
    throw new Error('"日付"というテキストを含むセルが見つかりませんでした。');
  }
  var startRow = foundCell.getRow() + 1;
  var startColumn = foundCell.getColumn();

  return { startRow: startRow, startColumn: startColumn };
}

GASをスプレッドシートに追加

• スプレッドシートのメニューの拡張機能からApps Scriptをクリック
  

• 無題.gsがファイル内に作成されているのでコード欄に上記コードを添付し、カレンダーIDを下記の「""」の中にコピー&ペーストしてください。

const CALENDAR_SHIFT = "＜カレンダーIDを記載＞"

• もしシート名やカレンダー名をほかの名前にしている場合は下記の部分の書き換えが必要です。

const SHEET_NAME = "sheetシフト";//シート名を記載
const TITLE_TEXT = 'シフト';//カレンダー名を記載

実行

• プロジェクトを保存し実行
  

• シフトのスプレッドシートに戻りリロード
  

• メニューのGASから「シートからカレンダーに書き込み」をクリック
  

• カレンダーを開き、予定が追加されていればOK

予定が変更になったとき
スプレッドシートを書き換え「シートからカレンダーに書き込み」をクリックすれば、自動で予定が書き換えられます

さいごに

また何か欲しい機能とかあれば作成していこうと思います。

参考