「仕事で、スプレッドシートで管理している予定をGoogleカレンダーに一括登録できるようにしたいんだけど...」
と頼まれたので初めてGASを使ってみました...
やりたいことなど...
・その日の配達の予定を下記のような感じでスプレッドシートで管理している。これをGoogleカレンダーにも登録して予定を見やすくしたい。
・スプレッドシートに記載した日付でカレンダーに予定を登録したい
・カレンダーの予定の開始終了時間は「配達時間」、タイトルに「お客様名」、場所に「配達場所」、説明欄に「備考」を入れて予定を登録できるようにしたい
・配達時間が入っていない予定は、終日の予定として登録したい
・スプレッドシートには、カレンダーには登録したくない予定も記載されている
・カレンダー連携後にスプレッドシートの予定を修正した場合、元の予定は削除し、修正した予定を新しく登録してほしい
・日付がまだ入っていない予定はカレンダーに登録されないようにしてほしい
スプレッドシートとカレンダーを連携する方法!
1. スプレッドシートの作成
・「連携ステータス」欄は、連携しない or 連携済をセレクトボックスで選択できるようにしておく
・カレンダーを連携後に予定を修正、再度カレンダー連携をした際に元の予定を削除できるようにするために「イベントID」を保持しておく列を作成
2. スクリプトを設定
スプレッドシートに「カレンダー連携」メニューを追加する関数を追加
/**
* スプレッドシート表示の際にカレンダー連携メニューを表示する
*/
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("連携が完了しました!");
}
3. カレンダー連携
スプレッドシートに戻り、リロードするとメニューに「カレンダー連携」ができる。
「連携する」をクリック!
初めてカレンダー連携を実行する時は認証画面が表示されます。許可しましょう.. !
連携実行後、スプレッドシートの連携ステータスが「連携済」に変わります
予定が登録されていました!
予定を変更して再度連携してみる!
連携ステータスを空欄にし、日付を変更 → 再度カレンダー連携を実行!
元の予定は削除されて、変更後の予定が登録されました!
参考にした記事はこちら...