1. ran

    Posted

    ran
Changes in title
+Google Apps Scriptで外部APIのデータを取得してGoogleカレンダーに登録する
Changes in tags
Changes in body
Source | HTML | Preview
@@ -0,0 +1,197 @@
+社内の電子申請は、[サテライトオフィス・ワークフロー](https://sites.google.com/a/sateraito.jp/sateraito-dounyuu/Home/wakufuro-ji-neng)というのを使っているのですが、出張申請が承認されたら社員が共有しているGoogleカレンダーに自動で登録したい、という要望があったので、Google Apps Scriptで実装してみました。
+
+# やりたいこと
+電子申請の内容がAPIで取得できるので、2週間以内に申請された承認済みの出張申請を取得してGoogleスプレッドシートに保存しておき、10分毎に取得して新しい(スプレッドシートにない)申請データがあったらシートに追加するとともにGoogleカレンダー(勤怠カレンダーと出張カレンダー)にも追加する。
+シートにデータが溜まると検索(すでに登録されていないかどうか)に時間がかかるので、2週間を過ぎたら古いデータはシートから削除する。
+
+ちなみに『2週間』というのは、申請されてからさすがに2週間経ってれば承認されてるだろう、という期間です。もうちょっと長くてもいいかも。
+APIが承認日基準で検索できれば良かったのですが、申請日基準でしか取得できなかったので…。
+
+# 準備
+新しいスプレッドシートを作成し、シートの名前を『出張』にします。
+
+ツール>スクリプトエディタ
+でスクリプトエディタを開きます。
+
+サテライトオフィスのAPIキーを取得して、出張申請書のテンプレートIDを確認。
+登録するGoogleカレンダーのカレンダーIDを確認。(カレンダーの設定画面で確認できます)
+
+# スクリプト
+
+```javascript
+var trip_cal_id = 'aaaaaaaa@group.calendar.google.com';
+var kintai_cal_id = 'bbbbbbbb@group.calendar.google.com';
+var base_url = 'https://sateraito-apps-workflow.appspot.com/xxxxxxxx/api/public';
+var api_key = 'xxxxxxxxxxxxxxxx';
+var impersonate_email = 'mine@example.com';
+var keep_days = 14;
+var access_token;
+var sheet_name = "出張";
+var template_id = "template-cccccccc";
+
+function main() {
+ var ss = SpreadsheetApp.getActiveSpreadsheet();
+ var sheet = ss.getSheetByName(sheet_name);
+
+ var payload = {
+ "api_key" : api_key,
+ }
+ access_token = getFromSateraito('/auth', payload).access_token;
+
+ var d = new Date();
+ d.setDate(d.getDate() - keep_days);
+
+ // 古いデータを削除
+ deleteOld(d, sheet);
+
+ // 決裁済みの申請のみ取得
+ payload = {
+ "status" : "final_approved",
+ "from_date" : Utilities.formatDate(d, 'UTC', 'yyyy-MM-dd'),
+ "template_id" : template_id
+ }
+ var list = getFromSateraito('/docs/list', payload);
+ // 申請日降順で返ってくるので、古い順に処理
+ for (var i = list.datas.length - 1; 0 <= i; i--) {
+ if (!findDoc(list.datas[i].doc_id, sheet)) {
+ // シートにまだ入ってなければ挿入してカレンダーにも追加
+ var row = sheet.getLastRow() + 1;
+ var detail = getFromSateraito('/docs/get', {"doc_id":list.datas[i].doc_id});
+ // 日時がUTCなので+9時間する
+ var submitd = new Date(detail.data.submit_date.replace('-', '/', 'g'));
+ submitd.setHours(submitd.getHours() + 9);
+ var appd = new Date(detail.data.final_approved_or_rejected_date.replace('-', '/', 'g'));
+ appd.setHours(appd.getHours() + 9);
+ // 挿入データを準備
+ var line = [
+ detail.data.doc_id,
+ submitd,
+ appd,
+ detail.data.doc_title,
+ detail.data.doc_values.syucchou_sha,
+ detail.data.doc_values.syuppatsu_date,
+ detail.data.doc_values.kichaku_date,
+ detail.data.doc_values['syukuhaku-chi']
+ ];
+ // カレンダーに追加してidを挿入データ末尾に追加
+ line = line.concat(insertCal(detail.data));
+ // 現在日時(挿入日時)を末尾に追加
+ line.push(new Date());
+ // シートに挿入
+ sheet.getRange(row, 1, 1, line.length).setValues([line]);
+ }
+ }
+}
+
+// サテライトオフィス・ワークフローから情報を取得する
+function getFromSateraito(api, payload) {
+ if (api !== '/auth') {
+ payload.access_token = access_token;
+ }
+ payload.impersonate_email = impersonate_email;
+ Logger.log(payload);
+ var options = {
+ "method" : "POST",
+ "payload" : payload
+ }
+
+ var response = UrlFetchApp.fetch(base_url + api, options);
+ var json = JSON.parse(response.getContentText('UTF-8'));
+ var datas = json.datas;
+ while (json.next_cursor) {
+ payload.start_cursor = json.next_cursor;
+ Logger.log(payload);
+ response = UrlFetchApp.fetch(base_url + api, options);
+ json = JSON.parse(response.getContentText('UTF-8'));
+ datas = datas.concat(json.datas);
+ }
+ json.datas = datas;
+ return json;
+}
+
+// シートの1列目に格納されているID(サテライトオフィス・ワークフローのdoc_id)に引数で指定したものがあるか調べる
+function findDoc(id, sheet) {
+ var lastRow = sheet.getLastRow();
+ for (var i = lastRow; 0 < i; i--) {
+ if (sheet.getRange(i, 1).getValue() === id) {
+ return true;
+ }
+ }
+ return false;
+}
+
+// 申請日(シート2列目)がdateより古いデータを削除する
+function deleteOld(date, sheet) {
+ // 時差分バッファを持たせて1日前にする
+ var d = new Date(date.getTime());
+ d.setDate(d.getDate() - 1);
+ var lastRow = sheet.getLastRow();
+ var delMax = 0;
+ for (var i = 1; i <= lastRow; i++) {
+ if (sheet.getRange(i, 2).getValue() < d) {
+ delMax = i;
+ } else {
+ break;
+ }
+ }
+ if (0 < delMax) {
+ sheet.deleteRows(1, delMax);
+ }
+}
+
+// Googleカレンダー(出張および勤怠)に出張申請のデータを入れる
+function insertCal(data) {
+ var end = new Date(data.doc_values.kichaku_date);
+ end.setDate(end.getDate() + 1);
+ var now = new Date();
+ if (end < now) {
+ // 過去のものは入れないで終了
+ return [null, null];
+ }
+
+ var event = {
+ "summary" : data.doc_values.syucchou_sha + ':出張(' + data.doc_values['syukuhaku-chi'] + ')',
+ "start" : {
+ "date" : data.doc_values.syuppatsu_date,
+ "timeZone" : 'Asia/Tokyo'
+ },
+ "end" : {
+ "date" : Utilities.formatDate(end, 'Asia/Tokyo', 'yyyy-MM-dd'),
+ "timeZone" : 'Asia/Tokyo'
+ },
+ "transparency" : "transparent",
+ "guestsCanModify" : true
+ }
+
+ // 勤怠カレンダーに入力
+ var kintai = Calendar.Events.insert(event, kintai_cal_id);
+
+ // 出張カレンダーに入力
+ event.summary = data.doc_values.syucchou_sha + ':' + data.doc_values['syukuhaku-chi'];
+ event.description = data.doc_values.koudouyotei;
+ var trip = Calendar.Events.insert(event, trip_cal_id);
+ return [kintai.id, trip.id];
+}
+```
+
+main関数を実行すると各種権限の許可を求められるので、許可するとスプレッドシートにデータが入ってカレンダーにも登録されます。
+
+
+# 10分毎に起動させる
+スクリプトエディタのメニューから
+編集>現在のプロジェクトトリガー
+を開き、新しいトリガーを作成します。
+
+『イベントのソースを選択』で『時間主導型』を選び、『分ベースのタイマー』で『10分おき』を選びます。
+
+カレンダーに登録した各イベントの『作成者』は、このトリガーの実行者になるので、作成者にしたいGoogleアカウントでトリガーを作成すると良いです。(前述のように権限の許可が必要なので、一度手動で実行しておきます。)
+
+
+# ハマったところ
+日付の扱いが上手く行かなくて苦労しました:sweat_drops:
+
+- APIの日付がUTCだったので、それを考慮しないといけなかった
+- 『2018-12-21 12:00:00』の形のままでは`new Date()`した時の引数として認識されず、『2018/12/21 12:00:00』にしないといけなかった
+- `deleteOld`関数に渡した引数のDateオブジェクトが参照渡しになっていて、関数内で+1日したのが呼び出し元でも変わってしまっていた
+
+Dateオブジェクトむずい。