Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
Help us understand the problem. What is going on with this article?

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

More than 1 year has passed since last update.

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

やりたいこと

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

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

準備

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

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

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

スクリプト

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オブジェクトむずい。

ran
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away