backlogの全PJをスプシに一括自動転記するGAS

こんにちは、サーバーサイドエンジニアのがんちゃんです。

現在新卒入社2年目（5期生）で、PHP(Laravel)、Vue.jsを中心に色々な言語を駆使しながら、AWSとも戯れつつ日々勉強しております。

今日はバックログ（backlog）を便利にスプレッドシートで管理できるようなGASを開発したので紹介していきます。

完成品

これはあるバックログのプロジェクトの課題一覧です。課題の各種情報が表示されています。

モザイクのため分かりづらいですが、バックログ上の複数プロジェクトが対応したシートに分かれていて閲覧可能です。

バックログの全PJをスプレッドシートに転記した完成品

対象

・GASを触ったことがある、何かしらAPIを叩いて表示ができる

・バックログを使っている

背景

bravesoftでは、プロジェクトの品質を高めるために、バックログを導入しタスク管理を行なっています。

しかし、複数プロジェクトを横断して統計的に把握することは現状ではできません。

ということで、マスターデータを作って誰でも好きなように活用できるようにできないかという要望が上がりました。

要件

・バックログ全プロジェクトごとにシートを作成、さらに一定時間おきに更新される。

・新しいプロジェクトが増えたら自動追加される。

初期設定

まずバックログAPIの初期設定を行なっていきます。

space_idはバックログに普段アクセスする際のサードレベルドメインです。(https://bravesoft.backlog.jp/なら’bravesoft’の部分)

api_keyは、個人設定のAPIという項目から取得できます。

自分がアクセスできるPJの分しか取れないので、用途に応じてマスターアカウントのようなものを作ってもいいかもしれません。

sheet_idは下記のようなスプレッドシートのURLのhogeの部分です。

https://docs.google.com/spreadsheets/d/hoge/edit#gid=123

// バックログのスペースIDを入れます。 以下のhogeのような部分です。
// https://hoge.backlog.jp/
const space_id = "bravesoft";
// バックログのAPIキーです。 プロフィールアイコン→ 個人設定→ APIから発行できます
// https://space_id.backlog.jp/EditApiSettings.action
const api_key = "hogehoge";
// スプレッドシートのIDを入れます。
const sheet_id = "fugafuga";

// バックログのスペースIDを入れます。以下のhogeのような部分です。

// https://hoge.backlog.jp/

const space_id = "bravesoft";

// バックログのAPIキーです。プロフィールアイコン→ 個人設定→ APIから発行できます

// https://space_id.backlog.jp/EditApiSettings.action

const api_key = "hogehoge";

// スプレッドシートのIDを入れます。

const sheet_id = "fugafuga";

プロジェクト一覧の取得

バックログのプロジェクト一覧のイメージ

次にバックログのPJ一覧をとってきます。これがメインの関数になります。1時間に1回更新とかであればこのメソッドをトリガーで呼びます。

apiは先ほどのspace_id,api_keyを使って作成します。もし何かエラーが出た場合はまずここを疑ってください。

pj_listにPJのID、名前などが入ってきます。それを利用してシートを作成、課題一覧を転記します。

//　バックログのプロジェクト一覧をとってくる
function getPjList() {
  const api = "https://" + space_id + ".backlog.jp/api/v2/projects?apiKey=" + api_key;
  // apiを叩いてレスポンスを変数に入れます。
  let response = UrlFetchApp.fetch(api);
  // PJ一覧を配列にして変数に入れます。
  let pj_list = JSON.parse(response.getContentText());
  for (let i = 0; i < Object.keys(pj_list).length; i++) {
    getBackLogData(pj_list[i].id, pj_list[i].name);
  }
}

//　バックログのプロジェクト一覧をとってくる

function getPjList() {

const api = "https://" + space_id + ".backlog.jp/api/v2/projects?apiKey=" + api_key;

// apiを叩いてレスポンスを変数に入れます。

let response = UrlFetchApp.fetch(api);

// PJ一覧を配列にして変数に入れます。

let pj_list = JSON.parse(response.getContentText());

for (let i = 0; i < Object.keys(pj_list).length; i++) {

getBackLogData(pj_list[i].id, pj_list[i].name);

}

ヘッダーを取得、挿入

まず、バックログAPIの課題一覧は負荷軽減のため100件ずつしか取れません。なのでオフセットします。

最初はoffset = 1で1件目から取得します。

insertOrSelectSheetでシートを追加or対象のシートを決定します。このメソッドについては最後に話します。

titleは後で順番を変えたり、他の要素を追加したいときのために配列にしておくと便利です。

for文で全てシートに転記します。

function getBackLogData(project_id, project_name) {
  //100件ずつしか取得できないのでオフセットを加算して全件取得します。
  let offset = 1;
  //loopがtrueなら以下のwhile文を繰り返します
  let loop = true;
  // sheetが既にあれば追加、なければ追加すべきシートを受け取る
  let sheet = insertOrSelectSheet(project_name);
  //1行目はタイトルを入れます。これでシートの中身を全部消してもOKです。
  const title = [
    "種別",
    "状態",
    "件名",
    "担当者",
    "カテゴリー",
    "期限",
    "課題番号",
    "優先度",
    "内容",
    "URL"
  ];
  for (let i = 1;i <= title.length;i++) {
    sheet.getRange(1, i).setValue(title[i - 1]);
  }

function getBackLogData(project_id, project_name) {

//100件ずつしか取得できないのでオフセットを加算して全件取得します。

let offset = 1;

//loopがtrueなら以下のwhile文を繰り返します

let loop = true;

// sheetが既にあれば追加、なければ追加すべきシートを受け取る

let sheet = insertOrSelectSheet(project_name);

//1行目はタイトルを入れます。これでシートの中身を全部消してもOKです。

const title = [

"種別",

"状態",

"件名",

"担当者",

"カテゴリー",

"期限",

"課題番号",

"優先度",

"内容",

"URL"

];

for (let i = 1;i <= title.length;i++) {

sheet.getRange(1, i).setValue(title[i - 1]);

}

whileで課題一覧を挿入

apiについて色々とありますが、詳しくは課題一覧の取得 | Backlog Developer API | Nulab をご覧ください。

課題をissue_listに格納し、for文を回します。

++jとしているので、順番を容易に入れ替えられます。

最後に、100件取れたときは、もう一度ループさせます。

このときスリープを入れてあげないと、バックログAPIのレート制限に引っかかって処理が止まってしまいます。

while (loop) {
    //sort = statusでステータス順で取得します
    //statusId[] = でステータスが4=完了,3=処理済み,2=処理中,1=未対応で取得できます。
    //countは設定しないと20になります。
    //offsetで初期は1,2周目は101から取得されます
    const api = "https://" + space_id + ".backlog.jp/api/v2/issues?apiKey=" + api_key + "&projectId[]=" + project_id + "&sort=status&order=&statusId[]=3&statusId[]=2&statusId[]=1&count=100&offset=" + offset;
    //apiを叩いてレスポンスを変数に入れます。
    let response = UrlFetchApp.fetch(api);
    //課題一覧を配列にして変数に入れます。
    let issue_list = JSON.parse(response.getContentText());
    //2行目からは課題情報が入ります。
    let range = 1 + offset;
    for (let i = 0; i < Object.keys(issue_list).length; i++) {
      let j = 0;
      sheet.getRange(i + range, ++j).setValue(issue_list[i].issueType.name);
      //担当者が未設定だとその上の階層のassigneeが空になってエラーになるのでこのように三項演算子でnullのときの処理を書きます。
      sheet.getRange(i + range, ++j).setValue(issue_list[i].assignee ? issue_list[i].assignee.name : "未設定");
      sheet.getRange(i + range, ++j).setValue(issue_list[i].dueDate ? formatDate(new Date(issue_list[i].dueDate)) : "未設定");
      ...
      sheet.getRange(i + range, ++j).setValue("https://" + space_id + ".backlog.jp/view/" + issue_list[i].issueKey);
    }
    //取ってきた配列が100の時は続きがある可能性があるので100ずらしてもう一度ループします
    if (Object.keys(issue_list).length == 100) {
      offset += 100;
      // バックログAPIは制限があるのでスリープする
      Utilities.sleep(1000);
    } else {
      loop = false;
    }
  }

while (loop) {

//sort = statusでステータス順で取得します

//statusId[] = でステータスが4=完了,3=処理済み,2=処理中,1=未対応で取得できます。

//countは設定しないと20になります。

//offsetで初期は1,2周目は101から取得されます

const api = "https://" + space_id + ".backlog.jp/api/v2/issues?apiKey=" + api_key + "&projectId[]=" + project_id + "&sort=status&order=&statusId[]=3&statusId[]=2&statusId[]=1&count=100&offset=" + offset;

//apiを叩いてレスポンスを変数に入れます。

let response = UrlFetchApp.fetch(api);

//課題一覧を配列にして変数に入れます。

let issue_list = JSON.parse(response.getContentText());

//2行目からは課題情報が入ります。

let range = 1 + offset;

for (let i = 0; i < Object.keys(issue_list).length; i++) {

let j = 0;

sheet.getRange(i + range, ++j).setValue(issue_list[i].issueType.name);

//担当者が未設定だとその上の階層のassigneeが空になってエラーになるのでこのように三項演算子でnullのときの処理を書きます。

sheet.getRange(i + range, ++j).setValue(issue_list[i].assignee ? issue_list[i].assignee.name : "未設定");

sheet.getRange(i + range, ++j).setValue(issue_list[i].dueDate ? formatDate(new Date(issue_list[i].dueDate)) : "未設定");

...

sheet.getRange(i + range, ++j).setValue("https://" + space_id + ".backlog.jp/view/" + issue_list[i].issueKey);

}

//取ってきた配列が100の時は続きがある可能性があるので100ずらしてもう一度ループします

if (Object.keys(issue_list).length == 100) {

offset += 100;

// バックログAPIは制限があるのでスリープする

Utilities.sleep(1000);

} else {

loop = false;

}

細かいメソッド

細かい2つのメソッドを紹介します。

formatDateはその名の通り、バックログから渡ってくるISO形式日付型をGASの日付型に変換します。

insertOrSelectSheetはシートがあればそのシートを、なければ追加したシートを返します。

function formatDate(date) {
  let format = 'YYYY/MM/DD';
  format = format.replace(/YYYY/g, date.getFullYear());
  format = format.replace(/MM/g, ('0' + (date.getMonth() + 1)).slice(-2));
  format = format.replace(/DD/g, ('0' + date.getDate()).slice(-2));
  return format;
}
function insertOrSelectSheet(pj_name) {
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let spreadsheet = SpreadsheetApp.openById(sheet_id);
  let sheet = spreadsheet.getSheetByName(pj_name);
  if (sheet === null){
    return ss.insertSheet(pj_name);//　追加したシートを返す
  }
  return sheet;
}

function formatDate(date) {

let format = 'YYYY/MM/DD';

format = format.replace(/YYYY/g, date.getFullYear());

format = format.replace(/MM/g, ('0' + (date.getMonth() + 1)).slice(-2));

format = format.replace(/DD/g, ('0' + date.getDate()).slice(-2));

return format;

}

function insertOrSelectSheet(pj_name) {

let ss = SpreadsheetApp.getActiveSpreadsheet();

let spreadsheet = SpreadsheetApp.openById(sheet_id);

let sheet = spreadsheet.getSheetByName(pj_name);

if (sheet === null){

return ss.insertSheet(pj_name);//　追加したシートを返す

}

return sheet;

}

最後に

コード全文を載せておきます。何かのお役に立てれば幸いです。

const space_id = "bravesoft";
const api_key = "hogehoge";
const sheet_id = "fugafuga";
function getPjList() {
  const api = "https://" + space_id + ".backlog.jp/api/v2/projects?apiKey=" + api_key;
  let response = UrlFetchApp.fetch(api);
  let pj_list = JSON.parse(response.getContentText());
  for (let i = 0; i < Object.keys(pj_list).length; i++) {
    getBackLogData(pj_list[i].id, pj_list[i].name);
  }
}
function getBackLogData(project_id, project_name) {
  let offset = 1;
  let loop = true;
  let sheet = insertOrSelectSheet(project_name);
  const title = [
    "種別",
    "状態",
    "件名",
    "担当者",
    "カテゴリー",
    "期限",
    "課題番号",
    "優先度",
    "内容",
    "URL"
  ];
  for (let i = 1;i <= title.length;i++) {
    sheet.getRange(1, i).setValue(title[i - 1]);
  }
  while (loop) {
    const api = "https://" + space_id + ".backlog.jp/api/v2/issues?apiKey=" + api_key + "&projectId[]=" + project_id + "&sort=status&order=&statusId[]=3&statusId[]=2&statusId[]=1&count=100&offset=" + offset;
    let response = UrlFetchApp.fetch(api);
    let issue_list = JSON.parse(response.getContentText());
    let range = 1 + offset;
    for (let i = 0; i < Object.keys(issue_list).length; i++) {
      let j = 0;
      sheet.getRange(i + range, ++j).setValue(issue_list[i].issueType.name);
      sheet.getRange(i + range, ++j).setValue(issue_list[i].status.name);
      sheet.getRange(i + range, ++j).setValue(issue_list[i].summary);
      sheet.getRange(i + range, ++j).setValue(issue_list[i].assignee ? issue_list[i].assignee.name : "未設定");
      sheet.getRange(i + range, ++j).setValue(issue_list[i].category ? issue_list[i].category.name : "未設定");
      sheet.getRange(i + range, ++j).setValue(issue_list[i].dueDate ? formatDate(new Date(issue_list[i].dueDate)) : "未設定");
      sheet.getRange(i + range, ++j).setValue(issue_list[i].issueKey);
      sheet.getRange(i + range, ++j).setValue(issue_list[i].priority.name);
      sheet.getRange(i + range, ++j).setValue(issue_list[i].description);
      sheet.getRange(i + range, ++j).setValue("https://" + space_id + ".backlog.jp/view/" + issue_list[i].issueKey);
    }
    if (Object.keys(issue_list).length == 100) {
      offset += 100;
      Utilities.sleep(1000);
    } else {
      loop = false;
    }
  }
}
function formatDate(date) {
  let format = 'YYYY/MM/DD';
  format = format.replace(/YYYY/g, date.getFullYear());
  format = format.replace(/MM/g, ('0' + (date.getMonth() + 1)).slice(-2));
  format = format.replace(/DD/g, ('0' + date.getDate()).slice(-2));
  return format;
}
function insertOrSelectSheet(pj_name) {
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let spreadsheet = SpreadsheetApp.openById(sheet_id);
  let sheet = spreadsheet.getSheetByName(pj_name);
  if (sheet === null){
    return ss.insertSheet(pj_name);
  }
  return sheet;
}

const space_id = "bravesoft";

const api_key = "hogehoge";

const sheet_id = "fugafuga";

function getPjList() {

const api = "https://" + space_id + ".backlog.jp/api/v2/projects?apiKey=" + api_key;

let response = UrlFetchApp.fetch(api);

let pj_list = JSON.parse(response.getContentText());

for (let i = 0; i < Object.keys(pj_list).length; i++) {

getBackLogData(pj_list[i].id, pj_list[i].name);

}

function getBackLogData(project_id, project_name) {

let offset = 1;

let loop = true;

let sheet = insertOrSelectSheet(project_name);

const title = [

"種別",

"状態",

"件名",

"担当者",

"カテゴリー",

"期限",

"課題番号",

"優先度",

"内容",

"URL"

];

for (let i = 1;i <= title.length;i++) {

sheet.getRange(1, i).setValue(title[i - 1]);

}

while (loop) {

let response = UrlFetchApp.fetch(api);

let issue_list = JSON.parse(response.getContentText());

let range = 1 + offset;

for (let i = 0; i < Object.keys(issue_list).length; i++) {

let j = 0;

sheet.getRange(i + range, ++j).setValue(issue_list[i].issueType.name);

sheet.getRange(i + range, ++j).setValue(issue_list[i].status.name);

sheet.getRange(i + range, ++j).setValue(issue_list[i].summary);

sheet.getRange(i + range, ++j).setValue(issue_list[i].assignee ? issue_list[i].assignee.name : "未設定");

sheet.getRange(i + range, ++j).setValue(issue_list[i].category ? issue_list[i].category.name : "未設定");

sheet.getRange(i + range, ++j).setValue(issue_list[i].dueDate ? formatDate(new Date(issue_list[i].dueDate)) : "未設定");

sheet.getRange(i + range, ++j).setValue(issue_list[i].issueKey);

sheet.getRange(i + range, ++j).setValue(issue_list[i].priority.name);

sheet.getRange(i + range, ++j).setValue(issue_list[i].description);

sheet.getRange(i + range, ++j).setValue("https://" + space_id + ".backlog.jp/view/" + issue_list[i].issueKey);

}

if (Object.keys(issue_list).length == 100) {

offset += 100;

Utilities.sleep(1000);

} else {

loop = false;

}

function formatDate(date) {

let format = 'YYYY/MM/DD';

format = format.replace(/YYYY/g, date.getFullYear());

format = format.replace(/MM/g, ('0' + (date.getMonth() + 1)).slice(-2));

format = format.replace(/DD/g, ('0' + date.getDate()).slice(-2));

return format;

}

function insertOrSelectSheet(pj_name) {

let ss = SpreadsheetApp.getActiveSpreadsheet();

let spreadsheet = SpreadsheetApp.openById(sheet_id);

let sheet = spreadsheet.getSheetByName(pj_name);

if (sheet === null){

return ss.insertSheet(pj_name);

}

return sheet;

}

あとがき

Googleスプレッドシートに上記のようなデータを作成できると、活用の幅が広がります。

例えば、バックログ上のプロジェクトを横断して

・誰が何タスク処理中を抱えてしまっているのか

・週次でタスク完了数が多かったのは誰なのか

・課題追加数が多いのは誰なのか

などを把握できます！

お知らせ

ここまでお読みいただきありがとうございます！

bravesoftでは新卒社員を募集しています。まずはインターンシップからでもOKなので是非ご連絡ください！

場合によってはリモート勤務も可能です。

Follow me!

投稿者プロフィール

がんちゃん

完成品

対象

背景

要件

初期設定

プロジェクト一覧の取得

ヘッダーを取得、挿入

whileで課題一覧を挿入

細かいメソッド

最後に

あとがき

お知らせ

投稿者プロフィール

最新の投稿