kintoneのwebhook/APIトークンを用いて、Googleスプレッドシートと自動更新させる

はじめに

今回の要約

• kintone webhookを利用し、kintoneレコードのCreate/Update/Deleteと連動し、Googleスプレッドシートを更新する処理を作成しました。

なぜこれを作ったか

• kintone標準機能でのグラフ描画機能では、達成できないことがあった
• 筆者は、Google のBIツール「Google Looker Studio」の活用経験があり、kintoneのデータを集計して、BIツールで表示させることを考えた
• 手動でデータをコピペするのではなく、最新のデータを常に表示させたかった。そこで、Googleスプレッドシートと自動連携できる仕組みを構築した。

おことわり

• Googleスプレッドシート側の都合で、たまにミスが発生します。
• そうなってもいいように、1週間に1度、Googleスプレッドシート側のデータをリフレッシュし、kintoneからまとめてデータを取得するような処理が入っています。
• その処理がミスしてしまった場合は、手動で復帰させます。
• 本ソースコードは、kintoneのAPIトークンを直貼りする想定です。セキュリティなど考慮したい場合は、別途プロパティサービスなど活用してください。

本プログラムのソースコード

必要な環境

kintone

• スタンダードプラン(APIトークン、webhookを活用するため)

Google Workspace

• 無料で実装可能
• 日付操作ライブラリとして、「Momentjs」を利用
  • スクリプトID：MHMchiX6c1bwSqGM1PZiW_PxhMjh3Sh48

Google Looker Studio

• 無料で実装可能
• Googleスプレッドシートをデータソースとしてグラフ等を描画
• 今回の記事からは割愛

開発環境・全体の構成

実装

パラメータ設定

var appConfig = {
  '1':   { sheetName: '企業DB', 
           apiToken : 'xxxx', 
           fields   : ['企業ID', '名前', '業種'],
           orderBy  : '企業ID'  
         },
  '2':   { sheetName: '支援実績', 
           apiToken : 'yyyy', 
           fields   : ['支援日', '企業ID', '支援分類'],
           orderBy  : '支援日'  
         }
};

パラメータ名	設定内容
インデックス	同期したいアプリIDの数値と同じにする
sheetName	Googleスプレッドシートのシート名
apiToken	アプリごとのAPIトークンを記載
fields	取得したいkintoneのフィールド名を列挙する
orderBy	並べ替えしたい順序(降順で並べ替え)。番号および日付を入力する。なお、後述のリフレッシュ処理においては、kintone側のデータ取得の日付クエリとしても活用する

webhookによるkintoneとGoogle Spreadsheetとの同期

Google App Script　の設定(ウェブアプリのデプロイ)

• すべてのプログラムを作成したら、デプロイを実施。
• 下記を参照。
  • https://programming-zero.net/gas-webapp-deploy/
  • webアプリのURL https://script.google.com/macros/s/xxxxx/exec をコピー

kintone webhookの設定

• スクリプトの入力欄に、「script.google.com/macros/s/xxxxx/exec」を貼り付け
• https:// はすでに入力されており、GoogleのUIからコピーしてそのまま貼り付けると動作しないため注意

Google スプレッドシートの設定

• appConfigのsheetNameを記載すること
• A列はレコード番号で固定。B列以降は、appConfigのfieldsに記載した要素名を入力する

リフレッシュ処理について

kintone APIトークンの設定

• アプリごとに取得する。
• appConfig の　apiTokenに設定

ソースコード解説

doPost関数(webhookの受け)

function doPost(e) {
  var data = JSON.parse(e.postData.contents);
  var appId = data.app.id;
  var config = appConfig[appId];
  var sheet = ss.getSheetByName(config.sheetName);
  var action = data.type;

  switch (action) {
    case 'ADD_RECORD':
    case 'UPDATE_RECORD':
      processAddOrUpdateRecord(data, sheet, config, action);
      break;
    case 'DELETE_RECORD':
      var recordId = data.recordId;
      deleteRecord(sheet, recordId);
      break;
  }
  sortSheet(appId)
}

• kintoneのwebhookの内容は以下を参照
  • https://jp.cybozu.help/k/ja/user/app_settings/set_webhook/webhook_notification.html
• ADD/UPDATE/DELETEによって、処理を切り替え
  • レコードIDが格納される位置が異なるため注意

データ追加/更新処理

function processAddOrUpdateRecord(data, sheet, config, action) {
  var record = data.record;
  var recordId = record.$id.value;
  var values = [recordId];

  config.fields.forEach(function(fieldName) {
    let valueToAdd = ''; // デフォルト値として空文字を設定
    if (record[fieldName] && record[fieldName].value !== undefined) {
      // record[fieldName].valueが配列かどうかを確認
      if (Array.isArray(record[fieldName].value)) {
        if (record[fieldName].value.length === 0) {
          // 配列が空の場合は空文字を設定
          valueToAdd = '';
        } else {
          // 配列の値がある場合はコンマで繋ぐ
          valueToAdd = record[fieldName].value.join(', ');
        }
      } else {
        // 配列ではない場合は元の値をそのまま設定
        valueToAdd = record[fieldName].value;
      }
    }
    // 決定された値をvaluesに追加
    values.push(valueToAdd);
  });


  if (action === 'ADD_RECORD') {
    sheet.appendRow(values);
  } else {
    updateRecord(sheet, values);
  }
}

function updateRecord(sheet, values) {
  var range = sheet.getDataRange();
  var data = range.getValues();
  var recordId = values[0];

  for (var i = 0; i < data.length; i++) {
    if (data[i][0] == recordId) {
      sheet.getRange(i + 1, 1, 1, values.length).setValues([values]);
      return;
    }
  }
  // 一致するレコードがない場合は追加
  sheet.appendRow(values);
}

・データ追加の場合は、末端に行追加
・データ更新の場合は、webhookからレコードIDを取り出し、該当する行を検索し、指定の値を更新

データ削除

function deleteRecord(sheet, recordId) {
  var range = sheet.getDataRange();
  var data = range.getValues();

  for (var i = 0; i < data.length; i++) {
    if (data[i][0] == recordId) {
      sheet.deleteRow(i + 1);
      return;
    }
  }
}

• データ削除の場合は、レコードIDのみ通知される。
• レコードIDと一致する行を検索し、削除する

Googleスプレッドシートの並べ替え

• クエリ側でデータ並べ替えを指定していないためデータを並べ替える

function sortSheet(recordId) {
  var sheetName = appConfig[recordId].sheetName;
  var orderBy = appConfig[recordId].orderBy;
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var range = sheet.getDataRange();
  var data = range.getValues();
  
  var columnIndex = appConfig[recordId].fields.indexOf(orderBy) + 2; // インデックスは1ベースで計算
  if (columnIndex > 0) {
    // 降順にソートする
    range.sort({column: columnIndex, ascending: false});
    console.log("Sorted sheet " + sheetName + " by " + orderBy + " in descending order.");
  } else {
    console.log("Sort column " + orderBy + " not found in the sheet " + sheetName);
  }
}

リフレッシュ処理

// 現在の日付から、1ヶ月前の月初～今月末までのデータを取得する。
// 月初に実行
function monthlyRefleshKintoneRecords() {
  var daterange_start = Moment.moment().subtract(1, 'months').startOf("months").format("YYYY-MM-DD");
  var daterange_end = Moment.moment().endOf("months").format("YYYY-MM-DD");
  refleshKintoneRecords(daterange_start,daterange_end)
}

function refleshKintoneRecords(daterange_start, daterange_end) {
  // APIトークンをappConfigから動的に取得
  Object.keys(appConfig).forEach(function(appId) {
    var config = appConfig[appId];
    var sheetName = config.sheetName;
    var fields = ['レコード番号', ...config.fields]; // レコード番号を先頭に追加
    var apiToken = config.apiToken; // APIトークンを取得
    var query = '';
    if (sheetName === '支援実績') {
      query = `${config.orderBy} >= "${daterange_start}" and ${config.orderBy} < "${daterange_end}"`;
      deleteSeetDataBetween(appId,daterange_start,daterange_end)
    }
    writeRecordsToSheet(sheetName, appId, apiToken, query, fields);
    sortSheet(appId)
  });

• Moment.jsライブラリで日付指定(便利)
• 特定のシートに応じて日付指定のクエリを追記している。

REST APIでkintoneからデータ取得

function getKintoneRecord(appId, apiToken, query) {
  var offset = 0;
  var limit = 100;
  var records = [];
  var params = {
    method: "GET",
    headers: { "X-Cybozu-API-Token": apiToken },
    muteHttpExceptions: true
  };

  do {
    var fetchUrl = `${url}?app=${appId}&query=${encodeURIComponent(query)} order by $id asc limit ${limit} offset ${offset}`;
    var response = UrlFetchApp.fetch(fetchUrl, params);
    var data = JSON.parse(response.getContentText());

    if (data.records.length > 0) {
      records = records.concat(data.records);
      offset += data.records.length;
    } else {
      break;
    }
  } while (data.records.length === limit);

  return records;
}

• 対象の期間のスプレッドシートのデータを削除したのちに、データをまとめて追加
• UrlFetchApp.fetchで取得
• kintoneのAPIは、1回につき100件までしかデータを取得できないため、取得し終わるまで取得処理を繰り返す

データをまとめて登録する

function writeRecordsToSheet(sheetName, appId, apiToken, query, fieldNames) {
  var sheet = ss.getSheetByName(sheetName);
  var records = getKintoneRecord(appId, apiToken, query);
  records.forEach(record => {
    var rowValues = fieldNames.map(fieldName => {
      // record[fieldName] が存在し、valueがあるか確認
      if (record[fieldName] && record[fieldName].value !== undefined) {
        // valueが配列の場合、要素をコンマで繋げる
        if (Array.isArray(record[fieldName].value)) {
          return record[fieldName].value.join(', ');
        } else {
          // 配列ではない場合はそのまま文字列に変換
          return String(record[fieldName].value);
        }
      } else {
        // record[fieldName] が存在しないかvalueがない場合は空文字
        return '';
      }
    });
    var desRow = findRow(sheet, Number(record['$id'].value), 1) || sheet.getLastRow() + 1;
    sheet.getRange(desRow, 1, 1, rowValues.length).setValues([rowValues]);
  });
}

function findRow(sheet, val, col) {
  var data = sheet.getDataRange().getValues();
  for (var i = 1; i < data.length; i++) {
    if (data[i][col-1] === val) return i + 1;
  }
  return 0;
}

• 1レコードずつ追加処理を行う
• レコードIDが該当している箇所があれば、その行を更新する

データをまとめて削除する

function deleteSeetDataBetween(recordId, daterange_start, daterange_end) {
  var sheet = ss.getSheetByName(appConfig[recordId].sheetName);
  var orderBy = appConfig[recordId].orderBy;
  var columnIndex = appConfig[recordId].fields.indexOf(orderBy) + 2; // インデックスは1ベースで計算
  var lastRow = sheet.getLastRow();
  var resRange_top = 1;
  var resRange_bottom = 1;
  
  for (var i = 2; i < lastRow; i++) {
    var cur_date = Moment.moment(sheet.getRange(i, columnIndex).getValue());
    
    if (resRange_top == 1 && cur_date.isBefore(daterange_end)) {
      resRange_top = i;
    } else if (cur_date.isBefore(daterange_start)) {
      resRange_bottom = i - 1;
      break;
    }
  }

  if (resRange_top != 1 && resRange_bottom == 1) {
    resRange_bottom = lastRow;  // Adjust to delete to the end if no end point is found
  }

  if (resRange_top != 1 && resRange_bottom != 1) {
    sheet.deleteRows(resRange_top, resRange_bottom - resRange_top + 1);
  }
}

• 指定した日付区間について、orderBy(降順)で登録されたデータをまとめて削除する

おわりに

• kintoneからデータを自動取得し、Google Looker Studioでグラフ描画することができました。
• sortSheet関数を作成したが、そもそもクエリ指定時に並び替えできるので、この関数自体不要なので直したいです
• Google App Scriptはだいぶ安定しているが、安定していないときもある。「こういったもの」くらいの気持ちで運用するくらいがちょうどいい
• kintoneのAPI制限(1回100件)があるので、大規模なシステムを使うには向いていない。（そもそもそのレベルだと、kintoneが不向きという話もある)
• まとめてデータを登録する処理と、都度データ更新の処理を別々のタイミングで作ってしまったので、かなり処理が冗長で分かりづらい。いつか直したい
• orderByを2つの用途で使用してしまっている。いい感じの名前にしたい
• Google App Scriptの実行時間制限(最大6分)を超えてしまった場合の処理を検討したい