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

やりたいこと

以下のような感じでBacklogのあるプロジェクトの課題を全てGoogleスプレッドシートに吐き出したい。BacklogにはCSV出力機能があるが、頻繁に最新の状態にしたい場合は少し手間。

今回はGoogle Apps Scriptを使ってBacklog APIを呼び出してスプレッドシートに出力し、自動で最新の状態に保たれるように自動実行の設定を行う。

とりあえず吐き出したい方へ

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

1. こちらのリンクからスクリプト付きのスプレッドシートをコピーする
2. 拡張機能 > Apps Script からスクリプトの管理画面へ移動
   
3. プロジェクトの設定 > [スクリプト プロパティを追加] から以下の3つのスクリプトプロパティを追加する
   • BACKLOG_API_KEY
     • BacklogのAPIキー
       • BacklogのAPIキーの発行の方法
   • BAKLOG_PROJECT_ID
     • 吐き出したい課題があるBacklogのプロジェクトID (数値)
       • Backlogの"プロジェクト設定"のURLの?project.id=xxxxの部分から確認可能
   • BACKLOG_SPACE_FQDN
     • あなたの組織のBacklogのURLのFQDN (xxx.backlog.com)のような値
       
4. エディタ画面に移って、Backlog課題から取り出したい属性をfieldsToRetrieve変数に列挙する。(.区切りで入れ子になってるJSONオブジェクトの中の値を指定できる)
   • Backlog課題にどういった属性があるかはBacklog APIのDeveloperガイドの"課題一覧の取得"のレスポンスボディの例からご確認ください。
     
5. [▷実行]ボタンを押してスクリプトを実行
   • 権限の認可を求められたら承諾する
6. スプレッドシートの"Issues"シートに課題の情報が吐き出されていたら完了です

コード例

Backlog APIからあるプロジェクトのすべての課題を取得してスプレッドシートに反映するコード例です。適宜パラメータを追加すると課題を絞り込むことも可能です。指定可能なパラメータ(Backlog API)

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はスクリプトを自動実行するトリガーを設定することもできます。数分おきや、シートを開いた時など柔軟に選べます。