Google App Scriptを使っていい感じに予定の作成&取得をする

この記事はチームスピリット Advent Calendar 2020の記事です。

背景

メリークリスマス!皆さんは、Gカレンダーの予定を一覧で取得・作成したいと思っことはありませんか?

私は資格試験取得するために学習計画を立てていた時に予定を一覧で取得作成したいと思いました。

調べたところ、Google App Scriptを使えば、面倒な認証のコード等を書かずに簡単に予定を出力・作成できることができることがわかりました。

作成のお手本はこちらです。
www.whizz-tech.co.jp
取得のお手本はこちらです。
developers.google.com
拙作ながら、作成したコードのリンクも記載しておきます。
github.com

計画(予定作成)編

予定作成用シートの作成

上記の作成のお手本を元に予定作成用のシートのフォーマットを作成します。

f:id:a_nakaya:20201215075812p:plain
Create Plan Sheet

ちなみに今回入力する予定は、Salesforceのアプリケーションビルダー資格の学習計画です。
Trailheadと無償ウェビナーの予定を入力しました。
www.salesforce.com
trailhead.salesforce.com

アカウントの連携

アカウントの連携はほぼ不要です。
↓の感じでCalendarAppのデフォルトカレンダーを取得をすると、自分のカレンダーを取得してくれます。

const gAccount = CalendarApp.getDefaultCalendar().getId(); //デフォルトのカレンダーを取得

スプレッドシートから入力した情報を取り込む

ここはあまりお手本にしない方がいいかもですが、シートの入力した行までを取得します。
ヘッダ名でで列を指定する方法に修正したいなと思っています。今回は簡単に列を番号指定しました。

// 読み取り範囲(表の始まり行と終わり列)
  const topRow = 5;
  const lastCol = 12;

  // 0始まりで列を指定しておく
  const statusCellNum = 4;

  // シートを取得
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Plan");
  Logger.log("===sheet:"+sheet);

// 予定の最終行を取得
  var lastRow = sheet.getLastRow();
  
  //予定の一覧を取得
  var contents = sheet.getRange(topRow, 1, sheet.getLastRow(), lastCol).getValues();

  //順に予定を作成(今回は正しい値が来ることを想定)
  for (i = 0; i <= lastRow - topRow; i++) {

    var status = contents[i][statusCellNum];
  }

現在のシートは、SpreadsheetAppから取得できます。
私はシートは「Plan」シート固定で取得しました。
developers.google.com

予定の作成

さて、いよいよ予定の作成実処理です。
予定の作成にはCalendarApp.createEventを利用し、↓の感じで記載しました。

        // 予定を作成
        calender.createEvent(
          title,
          startDate,
          endDate,
          options
        );

options項目には前段で説明を追加しています。

// 詳細をセット
    var options = {description: contents[i][descriptionCellNum]};

予定取り込みボタンの作成

今回はスプレッドシートにプルダウンメニューを追加して、予定取り込みを実行できる様にしました。
↓の感じで定義すると、

/**
 * スプレッドシート表示の際に呼出し
 */
function onOpen() {
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();

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

こんな感じに表示してくれます。

f:id:a_nakaya:20201215082917p:plain
サブメニュー | Sub Menu

実行

あとはスクリプトのアクセスを許可して実行します。
無事成功しました!

f:id:a_nakaya:20201215083122p:plain
作成結果 | Result

気付き

やっぱり、列指定の部分が複雑になっているので改善したいなと思いました。
またダイアログを出す方法↓を知らなかったので、勉強になりました。

Browser.msgBox("完了");

評価(予定取得)編

スプレッドシートの作成

今度は取得用のスプレッドシートを作成して行きます。

f:id:a_nakaya:20201215083548p:plain
Check Schedule

今回は↓の様に自分のカレンダーではなく、Salesforce用にカレンダーを紐付け、そのカレンダーに登録されている期間の予定を取得します。
(取り込んだ予定は全てSalesforceのマイカレンダに変更しました。。。予定の作成時にカレンダーを登録できる様にしたいものです)

f:id:a_nakaya:20201215083803p:plain
Schedule

予定の取得

シートから設定を取り込む方法は割愛して、予定の取得の説明に入りたいと思います。
今回予定はCalendarApp.getCalendarByNameを利用して取得しました。

function getSalesforceCalendars() {
  var calendars = CalendarApp.getCalendarsByName('Salesforce');
  Logger.log('Found %s matching calendars.', calendars.length);
  return calendars[0];// とりあえず1行のみ返す
}

↓の様に取得ロジックを書くと全てのカレンダを取得して、あとはIDまたは名前指定で予定を取得することもできます。

function init() {
  var calendars = getAllCalendars();
  calendars.forEach(function(calendar) {
    getEvents(calendar)
  });
}
function getEvents(calendar) {
  var events = calendar.getEvents(startDate, endDate);
}
function getAllCalendars() {
  var calendars = CalendarApp.getAllCalendars();
  Logger.log('Found %s matching calendars.', calendars.length);
  return calendars;// とりあえず1行のみ返す
}

実行

プルダウンメニューを編集して予定の出力もできる様にします。

/**
 * スプレッドシート表示の際に呼出し
 */
function onOpen() {
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  //スプレッドシートのメニューにカスタムメニュー「計画取込 > 実行」を作成
  var subMenus = [];
  subMenus.push({
    name: "Migrate Plan| 計画取込",
    functionName: "createSchedule"  //実行で呼び出す関数を指定
  });
  subMenus.push({
    name: "Export Sche.| 予定取得",
    functionName: "exportSchedule"  //実行で呼び出す関数を指定
  });
  ss.addMenu("Plan & Schedule| 計画と予定", subMenus);
}

無事作成できました!

f:id:a_nakaya:20201215084725p:plain
取得結果 | Select Result

気付き

今は自分の予定だけですが、チームメンバーの予定を取得したり、個人の作業時間なのか?MTGなのか?などを取得できる様になれたら良いなと思いました。

まとめ

予定の取得や作成がエクセルベースでできる様になるとタスクの登録などが便利になるなと思います。
参考になれば、幸いです。