1
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

【GAS入門】スプレッドシートからGoogleカレンダーに予定を一括登録!

Posted at

「仕事で、スプレッドシートで管理している予定をGoogleカレンダーに一括登録できるようにしたいんだけど...」
と頼まれたので初めてGASを使ってみました...:information_desk_person:

やりたいことなど...

・その日の配達の予定を下記のような感じでスプレッドシートで管理している。これをGoogleカレンダーにも登録して予定を見やすくしたい。
スクリーンショット 2023-01-11 23.25.44.png

・スプレッドシートに記載した日付でカレンダーに予定を登録したい

・カレンダーの予定の開始終了時間は「配達時間」、タイトルに「お客様名」、場所に「配達場所」、説明欄に「備考」を入れて予定を登録できるようにしたい

・配達時間が入っていない予定は、終日の予定として登録したい

・スプレッドシートには、カレンダーには登録したくない予定も記載されている

・カレンダー連携後にスプレッドシートの予定を修正した場合、元の予定は削除し、修正した予定を新しく登録してほしい

・日付がまだ入っていない予定はカレンダーに登録されないようにしてほしい

スプレッドシートとカレンダーを連携する方法!

1. スプレッドシートの作成

スクリーンショット 2023-02-12 11.28.15.png

・「連携ステータス」欄は、連携しない or 連携済をセレクトボックスで選択できるようにしておく
・カレンダーを連携後に予定を修正、再度カレンダー連携をした際に元の予定を削除できるようにするために「イベントID」を保持しておく列を作成

2. スクリプトを設定

「拡張機能」 → Apps Script
スクリーンショット 2023-02-12 11.30.24.png

Apps Scriptの画面になるのでここにコードを書く!
スクリーンショット 2023-02-12 11.33.32.png

スプレッドシートに「カレンダー連携」メニューを追加する関数を追加

/**
 * スプレッドシート表示の際にカレンダー連携メニューを表示する
 */
function onOpen() {
  
  // アクティブなスプレッドシートを取得
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // スプレッドシートのメニューにカスタムメニュー「カレンダー連携 > 連携する」を作成
  var subMenus = [];
  subMenus.push({
    name: "連携する",
    functionName: "createSchedule"  //実行で呼び出す関数を指定
  });
  ss.addMenu("カレンダー連携", subMenus);
}

Googleカレンダーに予定を作成する関数を追加

/**
 * Googleカレンダーに予定を作成する
 */
function createSchedule() {

  // 連携するアカウント
  const gAccount = "xxxxxx@gmail.com"; // ★★ここに連携するカレンダーのアカウントのGmailアドレスを入れる
  
  // 読み取り範囲(表の始まり行と終わり列)
  const topRow = 5;
  const topCol = 2;
  const lastCol = 8;

  // 0始まりで列を指定しておく
  const statusCellNum = 0;
  const eventIdCellNum = 1;
  const dayCellNum = 2;
  const startCellNum = 3;
  const endCellNum = 4;
  const titleCellNum = 5;
  const locationCellNum = 6;
  const descriptionCellNum = 7;

  // シートを取得
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  // 予定の最終行を取得
  var lastRow = sheet.getLastRow();

  //予定の一覧を取得
  var contents = sheet.getRange(topRow, topCol, lastRow - topRow + 1, lastCol).getValues();

  // googleカレンダーの取得
  var calender = CalendarApp.getCalendarById(gAccount);

  // 予定を作成
  for (i=0; i <= lastRow - topRow; i++) {

    //「連携済」、「連携しない」、日付が空の場合は飛ばす
    let status = contents[i][statusCellNum];

    if (status == "連携済" || status == "連携しない" || contents[i][dayCellNum] == "") {
      continue;
    }

    const eventId = contents[i][eventIdCellNum];

    // イベントIDが既にある場合(編集の場合)
    if (eventId && status !== "連携済") {
      try {
        const existingEvent = calender.getEventById(eventId);
        // 既存のイベントを削除してセルを空にする
        existingEvent.deleteEvent();
        sheet.getRange(topRow + i, 3).setValue('');
      }
      catch(e) {
        Logger.log(e);
      }
    }

    // 値をセット 日時はフォーマットして保持
    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] };

    try {
      // 開始・終了時間が無ければ終日で設定
      if (startTime == '' || endTime == '') {
        // 予定を作成
        const newEvent = calender.createAllDayEvent(
          title,
          new Date(day),
          options
        );
        const newEventId = newEvent.getId();
        // イベントIDを書き込む
        sheet.getRange(topRow + i, eventIdCellNum + 2).setValue(newEventId);
        
      // 開始・終了時間があれば範囲で設定
      } 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());
        // 予定を作成
        const newEvent = calender.createEvent(
          title,
          startDate,
          endDate,
          options
        );
        const newEventId = newEvent.getId();
        // イベントIDを書き込む
        sheet.getRange(topRow + i, eventIdCellNum + 2).setValue(newEventId);
      }

      // 無事に予定が作成されたら「連携済」にする
      sheet.getRange(topRow + i, statusCellNum + 2).setValue("連携済");

    // エラーの場合(今回はログ出力のみ)
    } catch(e) {
      Logger.log(e);
    }
    
  }
  // ブラウザへ完了通知
  Browser.msgBox("連携が完了しました!");
}

スクリプト名を決めて保存する
スクリーンショット 2023-02-12 12.14.36.png

3. カレンダー連携

スプレッドシートに戻り、リロードするとメニューに「カレンダー連携」ができる。
「連携する」をクリック!
スクリーンショット 2023-02-12 12.23.18.png

初めてカレンダー連携を実行する時は認証画面が表示されます。許可しましょう.. !

連携が完了しました! のポップアップが出たら実行完了!
スクリーンショット 2023-02-12 12.34.49.png

連携実行後、スプレッドシートの連携ステータスが「連携済」に変わります
スクリーンショット 2023-02-12 12.35.11.png

カレンダーを確認してみると...
スクリーンショット 2023-02-12 13.13.26.png
スクリーンショット 2023-02-12 13.22.41.png

予定が登録されていました!

予定を変更して再度連携してみる!
連携ステータスを空欄にし、日付を変更 → 再度カレンダー連携を実行!
スクリーンショット 2023-02-12 13.24.48.png
スクリーンショット 2023-02-12 13.28.03.png
元の予定は削除されて、変更後の予定が登録されました!

:clap::clap::clap:

参考にした記事はこちら...:pray:

1
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
1
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?