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トークンを直貼りする想定です。セキュリティなど考慮したい場合は、別途プロパティサービスなど活用してください。

## 本プログラムのソースコード
https://github.com/iamakawa/kintone_sync_spreadsheet/blob/main/kintone_sync_spreadsheet.gs

## 必要な環境
### kintone
- スタンダードプラン(APIトークン、webhookを活用するため)

### Google Workspace
- 無料で実装可能
- 日付操作ライブラリとして、「Momentjs」を利用
	- スクリプトID：MHMchiX6c1bwSqGM1PZiW_PxhMjh3Sh48
	
### Google Looker Studio
- 無料で実装可能
- Googleスプレッドシートをデータソースとしてグラフ等を描画
- 今回の記事からは割愛

## 開発環境・全体の構成
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/90313/9e80d28a-a276-e099-ab00-e62a564dcbf3.png)


# 実装
## パラメータ設定
```
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の設定
![webhookの設定.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/90313/5a8d88b8-36ca-940b-7486-39de0c87a102.png)

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

### Google スプレッドシートの設定
![Googleスプレッドシート 作成例.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/90313/38f565c0-7a34-a99b-b2aa-1d0b2cf5b2d3.png)

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

## リフレッシュ処理について
### kintone APIトークンの設定
![kintone-apitoken.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/90313/f204a288-97db-d304-867c-e78eea274d12.png)

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

## ソースコード解説
### doPost関数(webhookの受け)
```javascript
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が格納される位置が異なるため注意

### データ追加/更新処理
```javascript
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を取り出し、該当する行を検索し、指定の値を更新

### データ削除
```javascript
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スプレッドシートの並べ替え
- クエリ側でデータ並べ替えを指定していないためデータを並べ替える
```javascript
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);
  }
}
```

### リフレッシュ処理
```javascript
// 現在の日付から、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からデータ取得
```javascript
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件までしかデータを取得できないため、取得し終わるまで取得処理を繰り返す

### データをまとめて登録する
```javascript
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が該当している箇所があれば、その行を更新する

### データをまとめて削除する
```javascript
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分)を超えてしまった場合の処理を検討したい