GoogleスプレッドシートでBacklog複数プロジェクトの課題情報を一括出力

みなさん、こんにちは。
デバイスソフトウェア開発部のnomuraです。

プロジェクト管理・タスク管理ツールは数多く存在しますが、当社では直感的で使いやすいBacklogを採用しています。
Backlog単体でもプロジェクトの進捗管理やスケジュール確認が可能ですが、日々のプロジェクト全体の状況を簡潔にサマリー表示するために、Googleスプレッドシートを活用しています。
この記事では、Googleスプレッドシートを使用してBacklogの課題情報を一括出力する方法についてご紹介いたします。

スプレッドシートとGASについて

データを整理し情報を見やすく管理するためには、「表計算ソフト」が便利です。
その1つにGoogleスプレッドシート（スプレッドシート）があります。
スプレッドシートは複数人での共有や同時編集が容易であり、またGoogle Apps Script（GAS）を使用してWeb上からデータを取得することも可能です。
他にも同様の「表計算ソフト」には、ExcelやVBAマクロがありますが、VBAマクロはその利便性からサイバー攻撃に利用されることがあり、それに伴う様々なセキュリティリスクを考慮する必要があります。
本記事では、スプレッドシートとGASを活用して、Backlogの課題情報を取得して一括出力する方法を紹介します。

Backlog APIについて

Backlog上の課題を取得するためには、GAS並びにBacklog APIを活用します。
Backlog APIは、ブラウザ上で行える操作の一部をAPIから実行可能とし、各プロジェクトの課題情報を一括取得するなどの操作も可能です。
具体的に、Backlogから課題一覧を取得する場合は、下記リンクにある「/api/v2/issues」APIを使用します。
このAPIを呼び出すことで、プロジェクトごとの課題情報を取得することができます。
https://developer.nulab.com/ja/docs/backlog/api/2/get-issue-list/#

Backlog APIの使用例

例として、下記リンクにある「/api/v2/projects/:projectIdOrKey」APIを使用して、プロジェクト情報を取得してみましょう。
https://developer.nulab.com/ja/docs/backlog/api/2/get-project/#
APIでプロジェクト情報を取得するには、Backlogのドメイン、プロジェクトキー、APIキーを指定する必要があります。

Backlog ドメイン

Backlogドメインは、ログイン後のURLから確認できます。
URLが下記の場合、ドメインは「test.backlog.com」となります。
「https://test.backlog.com/dashboard」

Backlog プロジェクトキー

Backlogプロジェクトキーは、ログイン後プロジェクトを開いた後のURLから確認できます。
URLが下記の場合、プロジェクトキーは「100_PROJECT」となります。
「https://test.backlog.com/projects/100_PROJECT」

Backlog APIキー

Backlog APIキーは、ログイン後「個人設定 > API」と進み、登録ボタンをクリックすることで新しいAPIキーを取得できます。
下記画像の赤枠にある文字列がAPIキーとなります。
Backlogヘルプセンターにも詳しい説明が掲載されていますので、合わせてご参照ください。
https://support-ja.backlog.com/hc/ja/articles/360035641754-API%E3%81%AE%E8%A8%AD%E5%AE%9A
【注意】
APIキーはパスワードと同様、厳重に管理してください。
APIキーを他者に公開すると、その者がBacklogにアクセスし情報を取得・変更できる状態となります。

ブラウザでプロジェクト情報を取得

下記隅括弧を指定値に書き換えることで、プロジェクト情報を取得できます。
https://【ドメイン】/api/v2/projects/【プロジェクトキー】?apiKey=【APIキー】
下記画像で表示されている内容がプロジェクト情報です。
特に、指定したプロジェクトキー(projectKey)、およびプロジェクトID(id)が記載されていることを確認してください。
プロジェクトIDは、「/api/v2/issues」APIで課題一覧を取得する際に必要なパラメータ値となります。

GASでプロジェクト情報を取得

Googleにログインした後、下記リンクから新しいスプレッドシートを作成してください。
“+”アイコンをクリックすると、新しいスプレッドシートが作成されて開きます。
https://sheets.google.com/

ツールバーから「拡張機能 > App Script」を選択してください。

AppsScript画面が表示され、GASのコード入力画面が表示されます。
下記コードをコピー&ペーストなどで入力してください。
変数DOMAIN、API_KEY、PROJECT_KEYには、上記で確認したドメイン、APIキー、プロジェクトキーを記載してください。

// Backlog複数プロジェクトの課題情報を一括出力
function backlogIssues() {
  const DOMAIN = "test.backlog.com";
  const API_KEY = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx";
  const PROJECT_KEY = "100_PROJECT";

  const projectId = getProjectID();
  console.log(projectId);
  
  // プロジェクトIDを取得
  function getProjectID() {
    const url = `https://${DOMAIN}/api/v2/projects/${PROJECT_KEY}?apiKey=${API_KEY}`;
    const response = JSON.parse(UrlFetchApp.fetch(url));
    return response.id;
  }
}

// Backlog複数プロジェクトの課題情報を一括出力

function backlogIssues() {

const DOMAIN = "test.backlog.com";

const API_KEY = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx";

const PROJECT_KEY = "100_PROJECT";

const projectId = getProjectID();

console.log(projectId);

// プロジェクトIDを取得

function getProjectID() {

const url = `https://${DOMAIN}/api/v2/projects/${PROJECT_KEY}?apiKey=${API_KEY}`;

const response = JSON.parse(UrlFetchApp.fetch(url));

return response.id;

}

上記コードの内容は「ブラウザでプロジェクト情報を取得」とほぼほぼ同様で、GASでHTTPリクエスト（Backlog API）を実行しプロジェクトIDを取得・ログ出力しています。
コード入力完了後、”実行”ボタンからGASを実行します。

上記GASを実行後、承認が必要な旨のポップアップが表示されます。”権利を確認”を押下し内容を確認します。
本GASはBacklogへアクセスしますので、下記のような「外部サービスへの接続」の承認画面が表示されます。
Backlogへアクセスして問題ない場合は、許可ボタンを押下してください。

特にコードに誤りなどがなければ、”実行ログ”画面にプロジェクトIDが表示されます。

GASで課題一覧を取得

次に、ソースコードを下記のように書き換えてください。
変数DOMAIN、API_KEY、PROJECT_KEYには、上記で確認したドメイン、APIキー、プロジェクトキーを記載してください。

// Backlog複数プロジェクトの課題情報を一括出力
function backlogIssues() {
  const DOMAIN = "test.backlog.com";
  const API_KEY = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx";
  const PROJECT_KEY = "100_PROJECT";
  
  // プロジェクトIDを取得
  const projectId = getProjectID();
  function getProjectID() {
    const url = 'https://' + DOMAIN + '/api/v2/projects/' + PROJECT_KEY + '?apiKey=' + API_KEY;
    const response = JSON.parse(UrlFetchApp.fetch(url));
    return response.id;
  }

  // 課題一覧を取得・表示（課題取得上限があり20件まで取得可能）
  const url = 'https://' + DOMAIN + '/api/v2/issues?apiKey=' + API_KEY + '&projectId[]=' + projectId;
  const response = JSON.parse(UrlFetchApp.fetch(url));
  for(i=0; i<response.length; i++){
    console.log(response[i].issueKey + ":" + response[i].summary);
  }
}

// Backlog複数プロジェクトの課題情報を一括出力

function backlogIssues() {

const DOMAIN = "test.backlog.com";

const API_KEY = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx";

const PROJECT_KEY = "100_PROJECT";

// プロジェクトIDを取得

const projectId = getProjectID();

function getProjectID() {

const url = 'https://' + DOMAIN + '/api/v2/projects/' + PROJECT_KEY + '?apiKey=' + API_KEY;

const response = JSON.parse(UrlFetchApp.fetch(url));

return response.id;

}

// 課題一覧を取得・表示（課題取得上限があり20件まで取得可能）

const url = 'https://' + DOMAIN + '/api/v2/issues?apiKey=' + API_KEY + '&projectId[]=' + projectId;

const response = JSON.parse(UrlFetchApp.fetch(url));

for(i=0; i<response.length; i++){

console.log(response[i].issueKey + ":" + response[i].summary);

}

上記GASを実行しますと、課題一覧を20件分取得し実行ログに表示します。

下記リンクの「/api/v2/issues」APIの”count”の項目を確認しますと、デフォルトで20件指定となっていることが分かります。
https://developer.nulab.com/ja/docs/backlog/api/2/get-issue-list/#

全課題を取得したい場合は、offsetやcountなどのパラメータを適切に指定する必要があります。
countで指定可能な課題数は100件になりますので、100件以上課題がある場合はHTTPリクエストを数回に分けoffsetも指定して実行する必要があります。

Backlog複数プロジェクトの課題情報を一括出力

上記まで理解できていれば、残りの作業はGASの基本や関連情報を調べながら、以下を黙々と実装することとなります。
・HTTPリクエストを数回に分けて実行し、全課題一覧を取得できるようにする
・取得した課題情報をスプレッドシート上に出力する
・複数のプロジェクトを指定できるようにする
・実行の進捗状況を表示する
など。

各々実装したGASが下記となります。
変数DOMAIN、API_KEYには、上記で確認したドメイン、APIキーを記載してください。
下記コードではプロジェクトキーを複数定義可能としていますので、変数PROJECT_KEYSにキーを記載してください。

// Backlog複数プロジェクトの課題情報を一括出力
function backlogIssues() {
  // 定数
  const DOMAIN = "test.backlog.com";
  const API_KEY = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx";
  const PROJECT_KEYS = [
    '100_PROJECT',
    '101_PROJECT',
  ];

  for(let i=0; i<PROJECT_KEYS.length; i++){
    // 進捗表示
    var toastStr = "";
    var projectCnt = i + 1;
    toastStr += projectCnt + "/" + PROJECT_KEYS.length + "　　　　　　　";
    toastStr += PROJECT_KEYS[i] +  "　取得中" + "　　　　　　　";
    SpreadsheetApp.getActiveSpreadsheet().toast(toastStr, '実行中', 600);
    // 課題情報を一括出力
    backlogProjectIssues(DOMAIN, API_KEY, PROJECT_KEYS[i], toastStr);
  }

  // 終了処理
  SpreadsheetApp.getActiveSpreadsheet().toast('完了しました。', '完了', 10);
}



// 指定したプロジェクトの課題情報を一括出力
function backlogProjectIssues(domain, apiKey, projectKey, toastStr) {
  // Backlog項目
  const BACKLOG_ITEMS = [
    'キー',
    '種別',
    'カテゴリー名',
    '発生バージョン',
    '件名',
    '状態',
    '優先度',
    'マイルストーン',
    '完了理由',
    '担当者',
    '登録者',
    '登録日',
    '開始日',
    '期限日',
    /*
    // 取得せずとした項目
    '親課題キー',
    '詳細',
    'ID',
    'プロジェクトID',
    'キーID',
    '種別ID',
    'カテゴリーID',
    'バージョンID',
    '状態ID',
    '優先度ID',
    'マイルストーンID',
    '完了理由ID',
    '担当者ID',
    '登録者ID',
    */
  ]

  const RESPONSE_VAL = [
    'issueKey',
    'issueType.name',
    'category.0.name',
    'versions.0.name',
    'summary',
    'status.name',
    'priority.name',
    'milestone.0.name',
    'resolution.name',
    'assignee.name',
    'createdUser.name',
    'created',
    'startDate',
    'dueDate',
    /* 
    // 取得せずとした項目
    'parentIssueId',
    'description',
    'id',
    'projectId',
    'keyId',
    'issueType.id',
    'category.0.id',
    'versions.0.id',
    'status.id',
    'priority.id',
    'milestone.0.id',
    'resolution.id',
    'assignee.id',
    'createdUser.id',
    */
  ]

  // APIリクエスト
  function request(url) {
    const response = UrlFetchApp.fetch(url);
    const headers = response.getHeaders();
    const responseJson = JSON.parse(response.getContentText());

    // リクエスト制限対応
    // https://backlog.com/ja/blog/backlog-api-rate-limit-announcement/
    const requestLimit = Number(headers['x-ratelimit-remaining']);
    if(requestLimit = COUNT_SIZE;
      // 進捗表示
      var outputStr = toastStr + "課題取得状況： " + offset + "/" + issuesTotal + "　　　　　　　";
      SpreadsheetApp.getActiveSpreadsheet().toast(outputStr, '実行中', 600);
      console.log(outputStr);
    }

    // 取得データをレコード化
    var records = issues.map(function (issue) {
      return RESPONSE_VAL.map(function (response) {
        return response.split('.').reduce(function (obj, field) {
          return obj ? obj[field] : '';
        }, issue);
      });
    });

    // レコード内データを置換
    recordDateReplace(records);
    function recordDateReplace(records) {
      let num;
      for(let i=0; i 0){ 
    sheet.getRange(2, 1, records.length, RESPONSE_VAL.length).setValues(records);
  }
  // シートの幅を調整
  sheet.autoResizeColumns(1, sheet.getMaxColumns());
}

// 特定セルから行最後尾のセルまでの範囲を取得
function getRangeFromSpecificCellDownwardLastCell (sheet, specificCell) {
  var initCell = sheet.getRange(specificCell);
  var lastCell = initCell.getNextDataCell(SpreadsheetApp.Direction.DOWN);
  var range = sheet.getRange(initCell.getRow(), initCell.getColumn(), (lastCell.getRow() - initCell.getRow()+1), (lastCell.getColumn() - initCell.getColumn()+1));
  //console.log(range.getA1Notation());
  return range;
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

// Backlog複数プロジェクトの課題情報を一括出力

function backlogIssues() {

// 定数

const DOMAIN = "test.backlog.com";

const API_KEY = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx";

const PROJECT_KEYS = [

'100_PROJECT',

'101_PROJECT',

];

for(let i=0; i<PROJECT_KEYS.length; i++){

// 進捗表示

var toastStr = "";

var projectCnt = i + 1;

toastStr += projectCnt + "/" + PROJECT_KEYS.length + "　　　　　　　";

toastStr += PROJECT_KEYS[i] + "　取得中" + "　　　　　　　";

SpreadsheetApp.getActiveSpreadsheet().toast(toastStr, '実行中', 600);

// 課題情報を一括出力

backlogProjectIssues(DOMAIN, API_KEY, PROJECT_KEYS[i], toastStr);

}

// 終了処理

SpreadsheetApp.getActiveSpreadsheet().toast('完了しました。', '完了', 10);

}

// 指定したプロジェクトの課題情報を一括出力

function backlogProjectIssues(domain, apiKey, projectKey, toastStr) {

// Backlog項目

const BACKLOG_ITEMS = [

'キー',

'種別',

'カテゴリー名',

'発生バージョン',

'件名',

'状態',

'優先度',

'マイルストーン',

'完了理由',

'担当者',

'登録者',

'登録日',

'開始日',

'期限日',

// 取得せずとした項目

'親課題キー',

'詳細',

'ID',

'プロジェクトID',

'キーID',

'種別ID',

'カテゴリーID',

'バージョンID',

'状態ID',

'優先度ID',

'マイルストーンID',

'完了理由ID',

'担当者ID',

'登録者ID',

]

const RESPONSE_VAL = [

'issueKey',

'issueType.name',

'category.0.name',

'versions.0.name',

'summary',

'status.name',

'priority.name',

'milestone.0.name',

'resolution.name',

'assignee.name',

'createdUser.name',

'created',

'startDate',

'dueDate',

// 取得せずとした項目

'parentIssueId',

'description',

'id',

'projectId',

'keyId',

'issueType.id',

'category.0.id',

'versions.0.id',

'status.id',

'priority.id',

'milestone.0.id',

'resolution.id',

'assignee.id',

'createdUser.id',

]

// APIリクエスト

function request(url) {

const response = UrlFetchApp.fetch(url);

const headers = response.getHeaders();

const responseJson = JSON.parse(response.getContentText());

// リクエスト制限対応

// https://backlog.com/ja/blog/backlog-api-rate-limit-announcement/

const requestLimit = Number(headers['x-ratelimit-remaining']);

if(requestLimit = COUNT_SIZE;

// 進捗表示

var outputStr = toastStr + "課題取得状況： " + offset + "/" + issuesTotal + "　　　　　　　";

SpreadsheetApp.getActiveSpreadsheet().toast(outputStr, '実行中', 600);

console.log(outputStr);

}

// 取得データをレコード化

var records = issues.map(function (issue) {

return RESPONSE_VAL.map(function (response) {

return response.split('.').reduce(function (obj, field) {

return obj ? obj[field] : '';

}, issue);

});

// レコード内データを置換

recordDateReplace(records);

function recordDateReplace(records) {

let num;

for(let i=0; i 0){

sheet.getRange(2, 1, records.length, RESPONSE_VAL.length).setValues(records);

}

// シートの幅を調整

sheet.autoResizeColumns(1, sheet.getMaxColumns());

}

// 特定セルから行最後尾のセルまでの範囲を取得

function getRangeFromSpecificCellDownwardLastCell (sheet, specificCell) {

var initCell = sheet.getRange(specificCell);

var lastCell = initCell.getNextDataCell(SpreadsheetApp.Direction.DOWN);

var range = sheet.getRange(initCell.getRow(), initCell.getColumn(), (lastCell.getRow() - initCell.getRow()+1), (lastCell.getColumn() - initCell.getColumn()+1));

//console.log(range.getA1Notation());

return range;

}

おわりに

以上、Googleスプレッドシートを用いてBacklogの課題情報を一括出力する方法についてご紹介いたしました。
課題を出力後は、スプレッドシートにサマリー用のシートを作成することで、日々のプロジェクト全体の状況などを把握することができます。
プロジェクト管理・タスク管理ツールやGASなど、日々進化していますので、これらのツールを活用し、業務の効率向上にお役立ていただければ幸いです。
それでは、またお会いしましょう。

GoogleスプレッドシートでBacklog複数プロジェクトの課題情報を一括出力

スプレッドシートとGASについて

Backlog APIについて

Backlog APIの使用例

Backlog ドメイン

Backlog プロジェクトキー

Backlog APIキー

ブラウザでプロジェクト情報を取得

GASでプロジェクト情報を取得

GASで課題一覧を取得

Backlog複数プロジェクトの課題情報を一括出力

おわりに

最近の投稿

人気の投稿

カテゴリー

採用情報