Google Apps Scriptで外部APIのデータを取得してGoogleカレンダーに登録する

社内の電子申請は、サテライトオフィス・ワークフローというのを使っているのですが、出張申請が承認されたら社員が共有しているGoogleカレンダーに自動で登録したい、という要望があったので、Google Apps Scriptで実装してみました。

やりたいこと

電子申請の内容がAPIで取得できるので、2週間以内に申請された承認済みの出張申請を取得してGoogleスプレッドシートに保存しておき、10分毎に取得して新しい(スプレッドシートにない)申請データがあったらシートに追加するとともにGoogleカレンダー(勤怠カレンダーと出張カレンダー)にも追加する。
シートにデータが溜まると検索(すでに登録されていないかどうか)に時間がかかるので、2週間を過ぎたら古いデータはシートから削除する。

ちなみに『2週間』というのは、申請されてからさすがに2週間経ってれば承認されてるだろう、という期間です。もうちょっと長くてもいいかも。
APIが承認日基準で検索できれば良かったのですが、申請日基準でしか取得できなかったので…。

準備

新しいスプレッドシートを作成し、シートの名前を『出張』にします。

ツール＞スクリプトエディタ
でスクリプトエディタを開きます。

サテライトオフィスのAPIキーを取得して、出張申請書のテンプレートIDを確認。
登録するGoogleカレンダーのカレンダーIDを確認。(カレンダーの設定画面で確認できます)

スクリプト

var trip_cal_id = '[e-mail]';
var kintai_cal_id = '[e-mail]';
var base_url = 'https://sateraito-apps-workflow.appspot.com/xxxxxxxx/api/public';
var api_key = 'xxxxxxxxxxxxxxxx';
var impersonate_email = '[e-mail]';
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アカウントでトリガーを作成すると良いです。(前述のように権限の許可が必要なので、一度手動で実行しておきます。)

ハマったところ

日付の扱いが上手く行かなくて苦労しました

• APIの日付がUTCだったので、それを考慮しないといけなかった
• 『2018-12-21 12:00:00』の形のままではnew Date()した時の引数として認識されず、『2018/12/21 12:00:00』にしないといけなかった
• deleteOld関数に渡した引数のDateオブジェクトが参照渡しになっていて、関数内で+1日したのが呼び出し元でも変わってしまっていた

Dateオブジェクトむずい。