やりたいこと
始業前に今日やるタスクをGoogleカレンダーにまとめたいが、一つずつ入力するのはめんどくさい。。。
スプレッドシートに記載した予定をGoogleカレンダーにまとめて反映させれば便利かも。
そこで以下のことをGASで実現したいと思います。
スプレッドシートに記載した予定をGoogleカレンダーにまとめて反映させたい。
Googleカレンダーに予定を一つずつ入力することもなくなり、スプレッドシートからサクッと予定を流し込み出来ます。
イメージ
上の添付の様なスプレッドシートを、次のようにボタンひとつで、Googleカレンダーに登録します。
0.事前にやること
①スプレッドシート作成
色とカレンダー種別はプルダウンで選択できるようにしておきます。
ちなみに色リストはこんな感じ
②スクリプトを設定
「拡張機能」 → Apps Script
Apps Scriptの画面になるのでコードを記載していきます。
ファイル名、プロジェクト名は何でもいいです。
1.スプレッドシートの内容をカレンダーへ登録するための記述
以下の関数を記述します。
①CreateSchedule()関数
・スプレッドシートから予定の情報を読み取り、Googleカレンダーに予定を追加します。
・予定の一覧をスプレッドシートから取得し、順番に予定を作成します。
・カレンダー名やイベントの開始日時・終了日時などの情報を使用して、予定を作成します。
・予定の作成が成功した場合、スプレッドシートの対応する行に予定のIDを記録し、ステータスを「済」に変更します。
/**
* Googleカレンダーに予定を追加する
*/
function CreateSchedule() {
// 読み取り範囲（表の始まり行と終わり列）
const topRow = 2;
const lastCol = 11;
const statusCellCol = 1;
const EventIDCol = 11; //K列
// 予定の一覧バッファ内の列(0始まり)
const statusNum = 0;
const startdayNum = 1;
const startNum = 2;
const enddayNum = 3;
const endNum = 4;
const titleNum = 5;
const locationNum = 6;
const descriptionNum = 7;
const colorNum = 8; //色の列
const calnameNum = 9; //カレンダー名の列
// シートを取得
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
// カレンダーの設定
let calendar = CalendarApp.getDefaultCalendar();
// 予定の最終行を取得
let lastRow = sheet.getLastRow();
// 予定の一覧をバッファに取得
const contents = sheet.getRange(topRow, 1, lastRow - topRow + 1, lastCol).getValues();
// 順に予定を作成
for (let i = 0; i < contents.length; i++) {
// 「済」と「ー」の場合は無視する
if (contents[i][statusNum] === "済" || contents[i][statusNum] === "ー") {
continue;
}
// 値をセット 日時はフォーマットして保持
let startday = contents[i][startdayNum];
let startTime = contents[i][startNum];
let endday = contents[i][enddayNum];
let endTime = contents[i][endNum];
let title = contents[i][titleNum];
let calendarName = contents[i][calnameNum]; //設定するカレンダー名
// 場所と詳細をセット
let options = { location: contents[i][locationNum], description: contents[i][descriptionNum] };
console.log(startday + " " + contents[i][titleNum]);
try {
let event;
if (calendarName !== "") {
let calendars = CalendarApp.getCalendarsByName(calendarName);
if (calendars.length > 0) {
calendar = calendars[0];
console.log(calendars[0].getName());
}
}
// 終了日時を作成
if (endday === '') {
endday = startday;
}
let startDate = new Date(startday);
let endDate = new Date(endday);
// 開始終了時刻が無ければ終日で設定
if (startTime === '' || endTime === '') {
//予定を作成
endDate.setDate(endDate.getDate() + 1); //★日付を1日プラスする必要あり★
event = calendar.createAllDayEvent(
title,
startday,
endDate,
options
);
} else {
// 開始日時を作成
startDate.setHours(startTime.getHours());
startDate.setMinutes(startTime.getMinutes());
endDate.setHours(endTime.getHours());
endDate.setMinutes(endTime.getMinutes());
// 予定を作成
event = calendar.createEvent(
title,
startDate,
endDate,
options
);
}
if (contents[i][colorNum] !== "") {
let color = contents[i][colorNum];
event.setColor(getColorNum(color));
}
console.log(event.getId());
sheet.getRange(topRow + i, EventIDCol).setValue(event.getId());
// 予定が作成されたら「済」にする
sheet.getRange(topRow + i, statusCellCol).setValue("済");
} catch (e) {
Logger.log(e);
}
}
}
②getColorNum(color)関数
引数として受け取った色の文字列に基づいて、対応する色の数値を返します。
/**
* スプレッドシートから引数の色を取得
*/
function getColorNum(color) {
let cnum;
switch (color) {
case "PALE_BLUE": cnum = 1; break;
case "PALE_GREEN": cnum = 2; break;
case "MAUVE": cnum = 3; break;
case "PALE_RED": cnum = 4; break;
case "YELLOW": cnum = 5; break;
case "ORANGE": cnum = 6; break;
case "CYAN": cnum = 7; break;
case "GRAY": cnum = 8; break;
case "BLUE": cnum = 9; break;
case "GREEN": cnum = 10; break;
case "RED": cnum = 11; break;
default: cnum = 0; break;
}
console.log(cnum);
return cnum;
}
③DeleteEvent()関数
・スプレッドシート内の空白の行に対応するイベントをGoogleカレンダーから削除します。
・予定の一覧をスプレッドシートから取得し、順番に予定を削除します。
・カレンダー名とイベントIDを使用して、対応するイベントを特定し削除します。
/**
* Googleカレンダーからイベントを削除する
* 先頭カラムが空白の行のイベントを削除する
*/
function DeleteEvent() {
// 読み取り範囲（表の始まり行と終わり列）
const topRow = 2;
const lastCol = 11;
// 予定の一覧バッファ内の列(0始まり)
const eventStatusColumnIndex = 0;
const calendarNameColumnIndex = 9; //カレンダー名の列
const eventIdColumnIndex = 10; //イベントID
// シートを取得
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
// カレンダーの設定
let calendar = CalendarApp.getDefaultCalendar();
// 予定の最終行を取得
let lastRow = sheet.getLastRow();
// 予定の一覧をバッファに取得
const contents = sheet.getRange(topRow, 1, lastRow - topRow + 1, lastCol).getValues();
// 順に予定を削除
for (let i = 0; i < contents.length; i++) {
// 「済」の場合は無視する
if (contents[i][eventStatusColumnIndex] === "済") {
continue;
}
let calendarName = contents[i][calendarNameColumnIndex]; //設定するカレンダー名
let eventId = contents[i][eventIdColumnIndex];
Logger.log('Deleting event ID ' + eventId + ' on row ' + (i + topRow + 1));
try {
// カレンダーの切り替え
if (calendarName !== "") {
let calendars = CalendarApp.getCalendarsByName(calendarName);
for (let j in calendars) {
calendar = calendars[j];
Logger.log('Switching to calendar: ' + calendars[j].getName());
}
}
// 保持していたイベントIDからイベントを削除する
let event = calendar.getEventById(eventId);
// イベントの削除
event.deleteEvent();
} catch (e) {
Logger.log('Error on row ' + (i + topRow + 1) + ': ' + e.toString());
}
}
}
④UpdateEvent()関数
DeleteEvent()関数を呼び出してイベントを削除し、CreateSchedule()関数を呼び出して新たな予定を追加します。
つまり、イベントを更新するために、まず既存のイベントを削除し、その後新しい予定を追加します。
//イベントの更新
function UpdateEvent() {
DeleteEvent() //イベントの削除
CreateSchedule() //イベントの追加
}
2.スプリプトをワンボタンで発火させる
今回はボタン一つでシートに記載してある予定をカレンダーに登録したいので、シート上に図形描画でボタンを作成します。
「拡張機能」 → 「マクロ」 → 「マクロをインポート」
からUpdateEventとDeleteEventを追加します。
先ほど作成したボタン画像の右クリックすると、「・・・」マークが表示されるので、そこから「スクリプトを割り当て」をクリック
割り当てたいスクリプトを入力します。
割り当てるスクリプトはそれぞれ以下の通り（先ほど追加した関数名）
・「追加・変更」ボタン・・・UpdateEvent
・「削除のみ」ボタン・・・DeleteEvent
これで作成したスクリプトをボタンクリックでショートカットで使用できるようになります。
初めてカレンダー連携を実行する時は認証画面が表示されます。
3.使い方
「追加・変更」ボタン
・項目を入力し、ボタンを押すとGoogleカレンダーに追加されます。
・追加出来たらA列に「済」と自動入力されます。
・変更したい場合
①項目を変更
②A列の「済」を消す
「削除のみ」ボタン
・A列の「済」を消してから、ボタンを押すとGoogleカレンダーから削除されます。