Backlogの課題情報をGoogleスプレッドシートに一括出力・自動更新

# やりたいこと
以下のような感じでBacklogのあるプロジェクトの課題を全てGoogleスプレッドシートに吐き出したい。Backlogには[CSV出力](https://support-ja.backlog.com/hc/ja/articles/360035642534-%E8%AA%B2%E9%A1%8C%E6%A4%9C%E7%B4%A2%E7%B5%90%E6%9E%9C%E4%B8%80%E8%A6%A7%E3%81%AE%E5%87%BA%E5%8A%9B)機能があるが、頻繁に最新の状態にしたい場合は少し手間。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/130043/a472b2f8-b90a-6319-d00b-73ca90ca89f4.png)

今回はGoogle Apps Scriptを使って[Backlog API](https://developer.nulab.com/ja/docs/backlog/#)を呼び出してスプレッドシートに出力し、自動で最新の状態に保たれるように自動実行の設定を行う。


# とりあえず吐き出したい方へ
課題吐き出し用スクリプト付きのスプレッドシートを用意しました。以下の手順ですぐ吐き出せます。(スクリプトの実行は自己責任でお願いします)

1. こちらのリンクから[スクリプト付きのスプレッドシートをコピーする](https://docs.google.com/spreadsheets/d/1p9Z3wmlpOlg1fBlOzsfB2uivlNb0h6Uk_DiQ9Y2HCWI/copy)
1. 拡張機能 > Apps Script からスクリプトの管理画面へ移動
![スクリーンショット 2022-09-02 23.06.27.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/130043/4c2f0d49-5b1e-c40c-bd1d-8bb9bfa030a8.png)
3. プロジェクトの設定 > [スクリプト プロパティを追加] から以下の3つのスクリプトプロパティを追加する
    - `BACKLOG_API_KEY`
        - BacklogのAPIキー
            - [BacklogのAPIキーの発行の方法](https://support-ja.backlog.com/hc/ja/articles/360035641754-API%E3%81%AE%E8%A8%AD%E5%AE%9A)
    - `BAKLOG_PROJECT_ID`
        - 吐き出したい課題があるBacklogのプロジェクトID (数値)
            - Backlogの"プロジェクト設定"のURLの`?project.id=xxxx`の部分から確認可能
    - `BACKLOG_SPACE_FQDN`
        - あなたの組織のBacklogのURLのFQDN (xxx.backlog.com)のような値
![スクリーンショット 2022-09-02 23.12.54.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/130043/04b93a9e-0d61-8b01-d65b-6881d1b40fe7.png)
4. エディタ画面に移って、Backlog課題から取り出したい属性を`fieldsToRetrieve`変数に列挙する。(`.`区切りで入れ子になってるJSONオブジェクトの中の値を指定できる)
    - Backlog課題にどういった属性があるかは[Backlog APIのDeveloperガイドの"課題一覧の取得"のレスポンスボディの例](https://developer.nulab.com/ja/docs/backlog/api/2/get-issue-list/#%E3%83%AC%E3%82%B9%E3%83%9D%E3%83%B3%E3%82%B9%E3%83%9C%E3%83%87%E3%82%A3)からご確認ください。
![スクリーンショット 2022-09-02 23.37.41.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/130043/021cc31d-e79d-d63d-1302-170fcd8e0a53.png)
5. [▷実行]ボタンを押してスクリプトを実行
    - 権限の認可を求められたら承諾する
5. スプレッドシートの"Issues"シートに課題の情報が吐き出されていたら完了です

# コード例
[Backlog API](https://developer.nulab.com/ja/docs/backlog/#)からあるプロジェクトのすべての課題を取得してスプレッドシートに反映するコード例です。適宜パラメータを追加すると課題を絞り込むことも可能です。[指定可能なパラメータ(Backlog API)](https://developer.nulab.com/ja/docs/backlog/api/2/get-issue-list/#%E3%82%AF%E3%82%A8%E3%83%AA%E3%83%91%E3%83%A9%E3%83%A1%E3%83%BC%E3%82%BF%E3%83%BC)

```javascript
function dumpBacklogIssues() {
  const scriptProperties = PropertiesService.getScriptProperties();
  const backlogSpaceFqdn = scriptProperties.getProperty('BACKLOG_SPACE_FQDN');
  const backlogApiKey = scriptProperties.getProperty('BACKLOG_API_KEY');
  const backlogProjectId = scriptProperties.getProperty('BACKLOG_PROJECT_ID');
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = spreadsheet.getSheetByName('Issues');
  const fieldsToRetrieve = [
    'id',
    'projectId',
    'issueKey',
    'keyId',
    'issueType.id',
    'issueType.name',
    'summary',
    'description',
    'resolution',
    'status.id',
    'status.name',
    'actualHours',
    'estimatedHours',
    'assignee.name',
    'assignee.nulabAccount.uniqueId',
    'milestone.0.id',
    'milestone.0.name',
    'milestone.1.id',
    'milestone.1.name',
    'milestone.2.id',
    'milestone.2.name'
  ];
  const issues = getIssues();
  const records = issues.map((issue) =>
    fieldsToRetrieve.map(fieldToRetrieve =>
      fieldToRetrieve.split('.').reduce((obj, field) => obj ? obj[field] : '', issue)
    )
  );

  sheet.clear();
  sheet.getRange(1, 1, 1, fieldsToRetrieve.length).setValues([fieldsToRetrieve]);
  sheet.getRange(2, 1, records.length, fieldsToRetrieve.length).setValues(records);

  
  function getIssues() {
    const maxChunkSize = 100;
    let issues = [];
    let offset = 0;
    let hasNext = true;
    while (hasNext) {
      const response = UrlFetchApp.fetch(`https://${backlogSpaceFqdn}/api/v2/issues?apiKey=${backlogApiKey}&projectId[]=${backlogProjectId}&offset=${offset}&count=${maxChunkSize}`);
      const issuesChunk = JSON.parse(response.getContentText());
      const responseHeaders = response.getHeaders();

      if (Number(responseHeaders['x-ratelimit-remaining']) <= 0) {
        const timeToWait = Number(responseHeaders['x-ratelimit-reset']) - new Date().getTime();
        Utilities.sleep(timeToWait);
      }

      issues = issues.concat(issuesChunk);
      offset = offset + issuesChunk.length;
      hasNext = issuesChunk.length >= maxChunkSize;
    }
    return issues;
  }
}
```

# 任意のタイミングで自動実行
Google Apps Scriptはスクリプトを自動実行するトリガーを設定することもできます。数分おきや、シートを開いた時など柔軟に選べます。

![スクリーンショット 2023-05-13 15.32.29.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/130043/9c21c653-c0a5-ffaf-6277-e08cf2a12df2.png)