便利なGASレシピ集！に戻る場合は以下から
はじめに
今回のビジボットでは、タイトル通り『GASを使って複数メンバーのGoogleカレンダーをスプレッドシートに取り込む』方法を紹介していきます！主に昨日書いた記事の続きで、昨日は一人のカレンダー情報を取得でしたが、本日は一つのシートにCalendar IDを複数記載すると、IDの情報を一括でシートに出力するコードです！
それでは早速始めていきます！
1.完成コード
POINT
まずはカレンダーIDを指定するシートを見ていきます。
GoogleカレンダーIDの取得は上「はじめに」で紹介した関連記事から確認する事ができます。
完成コードを記載していきます。
概要
Calendar IDをシートに登録します。登録されているCalendar IDを一つずつ取得し、Googleカレンダーから情報を取得。
そしてその処理を繰り返す。という動きです。
詳細については後ほど説明していきます！
GASコード
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 |
//対象の日付を範囲指定 var date = '2019/01/01'; //アクティブなシートを取得 var sheet = SpreadsheetApp.getActiveSpreadsheet(); //「Calendar_ID」のシートをアクティブにして、シート情報をcalendarIdに取得 var calendarId = sheet.setActiveSheet(sheet.getSheetByName("Calendar_ID")); //「Calendar_Item」のシートをアクティブにして、シート情報をcalendarItemに取得 var calendarItem = sheet.setActiveSheet(sheet.getSheetByName("Calendar_Item")); function getId(){ //現在の入力されてる値を初期化 calendarItem.clear(); //ヘッダ行に入る値を指定（誤って削除しても都度指定） calendarItem.getRange('A'+(1)).setValue('カレンダーID'); calendarItem.getRange('B'+(1)).setValue('タイトル'); calendarItem.getRange('C'+(1)).setValue('開始時刻'); calendarItem.getRange('D'+(1)).setValue('終了時刻'); calendarItem.getRange('E'+(1)).setValue('所要時間'); calendarItem.getRange('F'+(1)).setValue('詳細'); calendarItem.getRange('G'+(1)).setValue('住所'); //CalendarIDが入っている最終行を取得（固定） var lastR1 = calendarId.getLastRow(); Logger.log("【lastR】：" + lastR1); //セットされているIDのリストを順番に取得してgetCalに引数として渡す for (var i = 1; i < lastR1 ; i++){ //予定表が入っている最終行を取得（可変） var lastR2 = calendarItem.getLastRow(); Logger.log("【lastR】：" + lastR2); //カレンダーIDを指定して、カレンダーを取得 var calId = calendarId.getRange('A'+(i+1)).getValue(); Logger.log('【calId】' + calId); getCal(calId,lastR2); } } function getCal(id,lastR) { Logger.log('【id】' + id); Logger.log('【lastR】' + lastR); var calendar = CalendarApp.getCalendarById(id); //この時はまだfrom,end両方に変数dateで指定した日付が入っている var from= new Date(date); var end = new Date(date); //月を指定するときは必ず大文字のMMを指定します（半角mmだとminutesを指してしまう） //endのMonthｍ（月）に+1をして翌月を指定 end.setMonth(end.getMonth()+1); //getEventsで取得したいカレンダーの始まりと終わりを指定 var events = calendar.getEvents(from, end); //eventsに入っている要素の数だけ実行 for (var i = 0; i < events.length; i++) { Logger.log('events要素' + i + '目'); var title = events[i].getTitle(); var startTime = events[i].getStartTime(); var endTime = events[i].getEndTime(); var duration = (endTime - startTime) /(1000*60)/(60); var detail = events[i].getDescription(); var location = events[i].getLocation(); //取得した値をスプレッドシートに反映 calendarItem.getRange('A'+(lastR+1)).setValue(id); calendarItem.getRange('B'+(lastR+1)).setValue(title); calendarItem.getRange('C'+(lastR+1)).setValue(startTime).setNumberFormat('yyyy/mm/dd hh:mm'); calendarItem.getRange('D'+(lastR+1)).setValue(endTime).setNumberFormat('yyyy/mm/dd hh:mm'); calendarItem.getRange('E'+(lastR+1)).setValue(duration); //calendarItem.getRange('E'+(lastR+1)).setValue(detail); calendarItem.getRange('G'+(lastR+1)).setValue(location); lastR = lastR + 1; } } |
以下はLogger.log()で出力されたログ結果です。
GASログ
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 |
[19-02-21 02:27:45:564 JST] 【lastR】：3 [19-02-21 02:27:45:653 JST] 【lastR】：1 [19-02-21 02:27:45:738 JST] 【calId】***XX1@gmail.com [19-02-21 02:27:45:739 JST] 【id】***XX1@gmail.com [19-02-21 02:27:45:740 JST] 【lastR】1 [19-02-21 02:27:45:947 JST] ***XX1@gmail.comのevents要素0目 [19-02-21 02:27:45:960 JST] ***XX1@gmail.comのevents要素1目 [19-02-21 02:27:45:971 JST] ***XX1@gmail.comのevents要素2目 [19-02-21 02:27:45:983 JST] ***XX1@gmail.comのevents要素3目 [19-02-21 02:27:45:993 JST] ***XX1@gmail.comのevents要素4目 [19-02-21 02:27:46:004 JST] ***XX1@gmail.comのevents要素5目 [19-02-21 02:27:46:015 JST] ***XX1@gmail.comのevents要素6目 [19-02-21 02:27:46:027 JST] ***XX1@gmail.comのevents要素7目 [19-02-21 02:27:46:039 JST] ***XX1@gmail.comのevents要素8目 [19-02-21 02:27:46:051 JST] ***XX1@gmail.comのevents要素9目 [19-02-21 02:27:46:062 JST] ***XX1@gmail.comのevents要素10目 [19-02-21 02:27:46:072 JST] ***XX1@gmail.comのevents要素11目 [19-02-21 02:27:46:082 JST] ***XX1@gmail.comのevents要素12目 [19-02-21 02:27:46:092 JST] ***XX1@gmail.comのevents要素13目 [19-02-21 02:27:46:340 JST] 【lastR】：15 [19-02-21 02:27:46:427 JST] 【calId】***YY1@gmail.com [19-02-21 02:27:46:428 JST] 【id】***YY1@gmail.com [19-02-21 02:27:46:428 JST] 【lastR】15 [19-02-21 02:27:47:047 JST] ***YY1@gmail.comのevents要素0目 [19-02-21 02:27:47:056 JST] ***YY1@gmail.comのevents要素1目 [19-02-21 02:27:47:067 JST] ***YY1@gmail.comのevents要素2目 [19-02-21 02:27:47:076 JST] ***YY1@gmail.comのevents要素3目 [19-02-21 02:27:47:088 JST] ***YY1@gmail.comのevents要素4目 [19-02-21 02:27:47:099 JST] ***YY1@gmail.comのevents要素5目 [19-02-21 02:27:47:108 JST] ***YY1@gmail.comのevents要素6目 [19-02-21 02:27:47:118 JST] ***YY1@gmail.comのevents要素7目 [19-02-21 02:27:47:128 JST] ***YY1@gmail.comのevents要素8目 [19-02-21 02:27:47:137 JST] ***YY1@gmail.comのevents要素9目 [19-02-21 02:27:47:148 JST] ***YY1@gmail.comのevents要素10目 [19-02-21 02:27:47:157 JST] ***YY1@gmail.comのevents要素11目 [19-02-21 02:27:47:169 JST] ***YY1@gmail.comのevents要素12目 [19-02-21 02:27:47:179 JST] ***YY1@gmail.comのevents要素13目 [19-02-21 02:27:47:189 JST] ***YY1@gmail.comのevents要素14目 [19-02-21 02:27:47:199 JST] ***YY1@gmail.comのevents要素15目 [19-02-21 02:27:47:209 JST] ***YY1@gmail.comのevents要素16目 [19-02-21 02:27:47:220 JST] ***YY1@gmail.comのevents要素17目 [19-02-21 02:27:47:230 JST] ***YY1@gmail.comのevents要素18目 [19-02-21 02:27:47:239 JST] ***YY1@gmail.comのevents要素19目 [19-02-21 02:27:47:250 JST] ***YY1@gmail.comのevents要素20目 [19-02-21 02:27:47:260 JST] ***YY1@gmail.comのevents要素21目 [19-02-21 02:27:47:270 JST] ***YY1@gmail.comのevents要素22目 [19-02-21 02:27:47:280 JST] ***YY1@gmail.comのevents要素23目 [19-02-21 02:27:47:290 JST] ***YY1@gmail.comのevents要素24目 [19-02-21 02:27:47:301 JST] ***YY1@gmail.comのevents要素25目 [19-02-21 02:27:47:310 JST] ***YY1@gmail.comのevents要素26目 [19-02-21 02:27:47:322 JST] ***YY1@gmail.comのevents要素27目 [19-02-21 02:27:47:333 JST] ***YY1@gmail.comのevents要素28目 [19-02-21 02:27:47:343 JST] ***YY1@gmail.comのevents要素29目 [19-02-21 02:27:47:353 JST] ***YY1@gmail.comのevents要素30目 [19-02-21 02:27:47:363 JST] ***YY1@gmail.comのevents要素31目 [19-02-21 02:27:47:372 JST] ***YY1@gmail.comのevents要素32目 |
2.コード説明
POINT
まず、取得したい期間を指定します。
あまり推奨はされませんが、グローバル変数でいくつかの変数を作成してます。
日付を取得
GASコード
|
1 2 3 4 5 6 7 8 |
//対象の日付を範囲指定 var date = '2019/01/01'; //アクティブなシートを取得 var sheet = SpreadsheetApp.getActiveSpreadsheet(); //「Calendar_ID」のシートをアクティブにして、シート情報をcalendarIdに取得 var calendarId = sheet.setActiveSheet(sheet.getSheetByName("Calendar_ID")); //「Calendar_Item」のシートをアクティブにして、シート情報をcalendarItemに取得 var calendarItem = sheet.setActiveSheet(sheet.getSheetByName("Calendar_Item")); |
グローバル変数に宣言した処理が実行後、getIdが実行され取得して反映するスプレッドシートのフォーマット準備、およびGoogleカレンダーから情報を取得処理が実行されます。
GASコード
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
function getId(){ //現在の入力されてる値を初期化 calendarItem.clear(); //ヘッダ行に入る値を指定（誤って削除しても都度指定） calendarItem.getRange('A'+(1)).setValue('カレンダーID'); calendarItem.getRange('B'+(1)).setValue('タイトル'); calendarItem.getRange('C'+(1)).setValue('開始時刻'); calendarItem.getRange('D'+(1)).setValue('終了時刻'); calendarItem.getRange('E'+(1)).setValue('所要時間'); calendarItem.getRange('F'+(1)).setValue('詳細'); calendarItem.getRange('G'+(1)).setValue('住所'); //CalendarIDが入っている最終行を取得（固定） var lastR1 = calendarId.getLastRow(); Logger.log("【lastR】：" + lastR1); //セットされているIDのリストを順番に取得してgetCalに引数として渡す for (var i = 1; i < lastR1 ; i++){ //予定表が入っている最終行を取得（可変） var lastR2 = calendarItem.getLastRow(); Logger.log("【lastR】：" + lastR2); //カレンダーIDを指定して、カレンダーを取得 var calId = calendarId.getRange('A'+(i+1)).getValue(); Logger.log('【calId】' + calId); getCal(calId,lastR2); } } |
要素数を取得
function getId()が実行して情報の取得が終わるとfunction getCal()を呼び出します。この時、引数としてcalId,lastR2（calendarItemシートに入っている最終行）が渡されて、取得した情報を最終行の+1行下から追加していきます。
GASコード
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
function getCal(id,lastR) { Logger.log('【id】' + id); Logger.log('【lastR】' + lastR); var calendar = CalendarApp.getCalendarById(id); //この時はまだfrom,end両方に変数dateで指定した日付が入っている var from= new Date(date); var end = new Date(date); //月を指定するときは必ず大文字のMMを指定します（半角mmだとminutesを指してしまう） //endのMonthｍ（月）に+1をして翌月を指定 end.setMonth(end.getMonth()+1); //getEventsで取得したいカレンダーの始まりと終わりを指定 var events = calendar.getEvents(from, end); //eventsに入っている要素の数だけ実行 for (var i = 0; i < events.length; i++) { Logger.log(id + 'のevents要素' + i + '目'); var title = events[i].getTitle(); var startTime = events[i].getStartTime(); var endTime = events[i].getEndTime(); var duration = (endTime - startTime) /(1000*60)/(60); var detail = events[i].getDescription(); var location = events[i].getLocation(); //取得した値をスプレッドシートに反映 calendarItem.getRange('A'+(lastR+1)).setValue(id); calendarItem.getRange('B'+(lastR+1)).setValue(title); calendarItem.getRange('C'+(lastR+1)).setValue(startTime).setNumberFormat('yyyy/mm/dd hh:mm'); calendarItem.getRange('D'+(lastR+1)).setValue(endTime).setNumberFormat('yyyy/mm/dd hh:mm'); calendarItem.getRange('E'+(lastR+1)).setValue(duration); //calendarItem.getRange('E'+(lastR+1)).setValue(detail); calendarItem.getRange('G'+(lastR+1)).setValue(location); lastR = lastR + 1; } } |
上記コードを実行すると、以下結果取得結果が表示されます。
A列には『Calendar_IDシート』でセットしていたIDをそのまま追加して、誰の予定かを判断できるようにしています！
しっかりと作成した予定の数と、取り込まれた予定の数はマッチしてました。
ここまでやったら次はこの予定表データに則って日時でカレンダーのタスクをSlackで自動報告するコードも追加したいですね。
↓↓作りました（笑）
とりあえず今回はこれで完了です！
最後までお付き合い頂きありがとうございました！
