やりたいこと

スケジュール管理とリマインドメールを一括で管理したい

理想

スケジュールをスプレッドシートに入力
→Googleカレンダーに連携：自動で予定登録・・・・・・・・・・・・・・・・　・①
→Gmailに連携：予定の1か月前に自動で担当者宛にリマインダーメールを送る・・・②

今回は①についての内容です。

①スプレッドシートとGoogleカレンダーを連携

1.スプレッドシートに一覧を作成

このサイトを参考にしたので、カレンダーに反映される部分（B～I列）はほとんどそのままで、リマインダーメールに必要な情報はK~Q列に入れています。

参考サイト：https://www.whizz-tech.co.jp/3996/

スプレッドシート入力方法

手入力

・K列（店舗No）
・L列（店舗名）
・M列（日付）
・N列（新店/閉店など）
・O列（担当者名）
　→情報を手動で入力（他部署作成の資料からコピペ）

・E列（開始時間）
・F列（終了時間）
　→開始時間終了時間を設定したい場合手入力（終日の予定は空欄でOK）

・H列（場所）
・I列（備考欄）
　→場所や備考など予定に関する情報を手入力
　参考にしたサイトにあったのでそのままいれたけど今回の場合なくても良かった

関数を入れる

・C列（日付）：=M6、表示形式は「yyyy-mm-dd」に設定

・D列（曜日）：=M6、表示形式は曜日

・G列（カレンダーのタイトルになる部分）：
="【"&N6&"】"&L6　”【新店/閉店など】店舗名” の形になるように設定

・J列（イベントID）：カレンダー連携した際に自動生成されるので空欄にしておく
　→参考にしたサイトにはなかったが、一度予定を登録した後に日付を変えることが多いため予定の上書き保存をするために追加

・P列（担当者アドレス）：別途用意している担当者アドレス一覧のマスターシートからXlookupで引っ張って表示

・Q列（直営、FC区分）：店名に"FC"と入っている場合「FC」、入っていない場合「直営」と表示するように関数を入力
=if(countif(L6,"*FC*")=1,"FC","直営")

条件付き書式の設定

このスプレッドシートはカレンダー登録用の入力フォームとしてだけではなく、新店開店情報の一覧資料としても使いたいのでちょっと見やすくしています

・今日以前の日付はグレーアウト（B列）
・新店の予定は赤字（G列）

2.GASでGoogleカレンダー連携

参考にしたコード
https://www.whizz-tech.co.jp/3996/

修正点：
イベントID列を追加
→予定の日付などを修正したとき、別の予定として保存せず、既存の予定を修正して上書き保存するため

/**
 * スプレッドシート表示の際に呼出し
 */
function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var subMenus = [];
  subMenus.push({
    name: "実行",
    functionName: "createSchedule"
  });
  ss.addMenu("カレンダー連携", subMenus);
}

/**
 * 予定を作成する
 */
function createSchedule() {
  const gAccount = "★★"; // ★★連携するカレンダーのアドレス
  const topRow = 6;
  const lastCol = 10; // イベントID用に列を追加
  const statusCellNum = 1;
  const dayCellNum = 2;
  const startCellNum = 4;
  const endCellNum = 5;
  const titleCellNum = 6;
  const locationCellNum = 7;
  const descriptionCellNum = 8;
  const eventIdCellNum = 9; // イベントIDを格納する列

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = sheet.getLastRow();
  var contents = sheet.getRange(topRow, 1, lastRow - topRow + 1, lastCol).getValues();
  var calendar = CalendarApp.getCalendarById("★★★");　//★★★カレンダーIDを入れる

  for (var i = 0; i <= lastRow - topRow; i++) {
    var status = contents[i][statusCellNum];
    if (
      status == "済" ||
      status == "済み" ||
      status == "OK" ||
      contents[i][dayCellNum] == ""
    ) {
      continue;
    }

    var day = new Date(contents[i][dayCellNum]);
    var startTime = contents[i][startCellNum];
    var endTime = contents[i][endCellNum];
    var title = contents[i][titleCellNum];
    var options = { location: contents[i][locationCellNum], description: contents[i][descriptionCellNum] };
    var eventId = contents[i][eventIdCellNum];

    try {
      if (eventId) {
        // 既存のイベントを削除
        var event = calendar.getEventById(eventId);
        if (event) {
          event.deleteEvent();
        }
      }

      var newEvent;
      if (startTime == '' || endTime == '') {
        newEvent = calendar.createAllDayEvent(title, new Date(day), options);
      } else {
        var startDate = new Date(day);
        startDate.setHours(startTime.getHours());
        startDate.setMinutes(startTime.getMinutes());
        var endDate = new Date(day);
        endDate.setHours(endTime.getHours());
        endDate.setMinutes(endTime.getMinutes());
        newEvent = calendar.createEvent(title, startDate, endDate, options);
      }

      // イベントIDをスプレッドシートに記録
      sheet.getRange(topRow + i, eventIdCellNum + 1).setValue(newEvent.getId());
      sheet.getRange(topRow + i, 2).setValue("済");
    } catch (e) {
      Logger.log(e);
    }
  }
  Browser.msgBox("完了");
}

上記のコードを実行すると、スプレッドシート内に 「カレンダー連携」 のタブが表示され、クリックすると 「実行」 ボタンが出てきます。

その他

改善点

スプレッドシートが結局見にくい
本来、カレンダーのタイトルにしたい部分（G列）とかはスプレッドシート内にわざわざ作らず、GASの中でN列とL列を組み合わせてタイトルを作ればいいけど、そんな風にコードをいじれる自信がなかったのであきらめた。

他の使い道

社内行事や全社的な予定も年間スケジュールとしてExcelで配信されているので、この方法でスプレッドシート化→Googleカレンダー連携すれば変更があっても便利そう。
どうしてもExcelをパブリックフォルダに入れておきたい場合、パワークエリを使ってスプレッドシートからデータを表示するみたいな形にして、元データはスプレッドシートにしておくと一元管理できるのでは。そこまでしてExcelがいるのかはわからない

感想

ほぼすべてChatGPTにコード書いてもらった。人間としてはGoogleアカウントのIDとカレンダーのIDを入れただけ。
なんでもできそう。
スプレッドシートに「カレンダー連携」というタブが出てきてとてもびっくりした。ここをこんな感じでカスタマイズできるのしらなかったので、ほかにもなんかに使えそう