Zenn
🍊

Pleasanter x Google 連携:スプレッドシートにデータを取り込む

2022/12/07に公開
Google Sheets
pleasanter
tech

2022 個人アドベントカレンダー の記事です。

ゴール

  • Pleasanter の外部連携として Google SpreadSheet と繋ぎ込みを考える
  • まずは、データを取り込む基本処理をおさえる

連携方法の検討

Pleasanter が外部連携するとき

  1. 外部システム側から Pleasanter に取りに来る
  2. Pleasanter から送り出す

の 2 つの方法が考えられます。

使い分けをどう考えるか

どちらの方法を使うかは

  • 権限の問題
  • 実行タイミング

の 2 つから考えるのが適切だと思います。

つまり

  • 外部システム側の権限がどのようで誰が実行したことになるか
  • Pleasater 側のアクセス権限がどの範囲で与えられるか

と、

  • ダンプ的に断面で取るか
  • 差分の発生に応じたイベントで実行するか

の両面が考慮要素となります。
特に現時点では Pleasanter にはスクリプトの定時実行の機能がなく、スクリプト処理はイベント駆動となること、また仮に今後定時実行機能ができたとしてもユーザの「編集」をトリガーとした、その場その場でのアクションは、データを入力している側で監視するのが妥当と考えられます。

本記事でのターゲット

ということで、技術的には Pleasanter のスクリプトでも、外部システム側でも自由にコードが書ける状態として、目的に対して最適の解を選択できる状態でなければなりません。

本記事では、Google SpreadSheet 側から Google Apps Script を使ってデータを取り出す基本的なコードを書いてみます。

スプレッドシートを起点としたデータ連携

前提として

  • demo.pleasanter.org に登録してください
  • 管理者ユーザでデモサイトにログインして API キーを作成して取得してください
  • "交通費清算の例"からのデータ取得をしましたが、適当なサイトのサイト ID を確認してください

データを読み出す

  • Google SpreadSheet を作成し、拡張機能メニューの「Apps Script」を選択してエディタを開きます。
  • コード.gs に以下のコードを貼りつけます。
  • apikey と siteId を前提で確認したものに書き換えて保存します。
  • 「デバッグ」の右にあるプルダウンから main を選択し、「実行」をクリックします。
const apikey = ''; //apikey を記入します
const host = 'https://demo.pleasanter.org';
const siteId = 123456; //サイト ID を記入します

const controlDefiner = (control, act, id) => {
  const controlName = normalizeControl(control, act, id);
  const actionName = normalizeAction(control, act);
  if (controlName === '' || actionName === '')
    throw new Error(`${control} ${act} is not implemented`);
  return { controller: controlName, action: actionName };
};
const normalizeControl = (control, act, id) => {
  switch (control) {
    case 'records':
    case 'record':
    case 'site':
    case 'table':
      if (act[0] === 'u' || act[0] === 'd') {
        return 'items';
      }
      return `items/${id}`;
    case 'users':
    case 'user':
      return 'users';
    case 'depts':
    case 'dept':
      return 'depts';
    case 'groups':
    case 'group':
      return 'groups;';
    default:
      return '';
  }
};
const normalizeAction = (target, action) => {
  switch (target) {
    case 'site':
    case 'table':
      return `${action}site`;
    case 'records':
    case 'record':
    case 'users':
    case 'user':
    case 'depts':
    case 'dept':
    case 'groups':
    case 'group':
      return action;
    default:
      return '';
  }
};

const urlBuilder = (host, control, act, id) => {
  const { controller, action } = controlDefiner(control, act, id);
  switch (action[0]) {
    case 'u':
    case 'd':
      return `${host}/api/${controller}/${id}/${action}`;
    case 'c':
    case 'g':
    default:
      return `${host}/api/${controller}/${action}`;
  }
};
const post = (host, control, act, id) => {
  const param = {
    contentType: 'application/json',
    method: 'post',
    payload: JSON.stringify({ ApiVersion: 1.1, ApiKey: apikey }),
    muteHttpExceptions: true,
  };
  const url = urlBuilder(host, control, act, id);
  const res = UrlFetchApp.fetch(url, param);
  if (res.getResponseCode() !== 200) return [];
  return JSON.parse(res.getContentText()).Response.Data;
};
const flatten = (data, parent = {}) =>
  Object.entries(data).reduce(
    (a, c) =>
      c[0].startsWith('Attachments')
        ? a
        : c[0].endsWith('Hash')
        ? flatten(c[1], a)
        : { ...a, [c[0]]: c[1] },
    parent,
  );
const headerFromFirst = (data) => Object.keys(data[0]);
const writeToSheet = (data, sheetName = 'シート1') => {
  const sheet =
    sheetName === undefined
      ? SpreadsheetApp.getActiveSheet()
      : SpreadsheetApp.getActive().getSheetByName(sheetName);
  if (sheet === undefined || sheet === null) {
    console.error(`no sheet ${sheetName}`);
    return;
  }
  if (data.length < 1) return;
  sheet.clearContents();
  const rows = data.map(flatten);
  const header = headerFromFirst(rows);
  const values = [header].concat(rows.map((r) => header.map((c) => r[c])));
  sheet.getRange(1, 1, values.length, header.length).setValues(values);
};
const main = () => {
  writeToSheet(post(host, 'records', 'get', siteId));
};

おわりに

  • 今回はひとまずデータを取り出すだけとなりました。
    • とりあえず users と depts も取得できることまでは確認しています。
    • 動かない、などありましたらコメントくださればベストエフォートで対応します。
  • 書き込みや具体的な利用に繋がる発展はまた考えたいと思います。
GitHubで編集を提案

Discussion