全てのPJを取得してバックログの課題をスプシに出力するGAS

2021年7月23日2021年9月4日GAS,ツール

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

【GAS】バックログAPIを使ってみたのレベルアップ版になります。まだ読んでいない方はぜひお読みになってからこちらにお戻りください！

【GAS】バックログAPIを使ってみた。

バージョンアップを公開! 全てのPJを取得してバックログの課題をスプシに出力するGAS GASとバックログA ...

https://aws.riikuu.world/2021/01/01/post-100/

下記に目次を設置してますので、忙しい方は初期設定と、最後にのみご覧ください。

1. 対象
2. 初期設定
3. プロジェクト一覧の取得
4. ヘッダーを取得、挿入
5. whileで課題一覧を挿入
6. 細かいメソッド
7. 最後に

対象

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

・バックログでたくさんのPJに入っている

初期設定

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

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

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 = "hoge";
// バックログのAPIキーです。 プロフィールアイコン→ 個人設定→ APIから発行できます
// https://space_id.backlog.jp/EditApiSettings.action
const api_key = "hogehoge";
// スプレッドシートのIDを入れます。
const sheet_id = "fugafuga";

プロジェクト一覧の取得

次にバックログのPJ一覧をとってきます。これがメインの関数になります。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);
  }
}

ヘッダーを取得、挿入

まず、バックログ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]);
  }

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;
    }
  }

細かいメソッド

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

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

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

function formatDate(date) {
  var 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) {
  var 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;
}

最後に

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

上記のコードより若干の改良を行なっています。

・setValuesを使った高速化
ざっくり2倍くらいの速度になります。

・API通信に失敗した際も自動で任意の秒数後(sleep_second)に取得し直すことができます。
バックログAPIはよくunreachableになるので、ループで呼び出せるようにして問題を解決しました。
20プロジェクトあっても30分に1回更新で問題なく作動しています。

・最後の列にGAS側の最終同期時間の追加
いちいち最終更新履歴を見ないといつ更新されたのか分からなくなるため、更新時に時間を入れています。
その際、フォーマットも追加しています。

・シート削除機能追加
deleteALLSheetsという関数を動かすとシート1以外のシートを消してくれます。
使い方としてはシート1が既にある場合は手動で消し、deleteALLSheetsを動かします。
すると、シート1というシートを作成して他を削除します。
シート１が残るのは、Googleスプレッドシートの仕様で全てのシートを消すことができないので、追加しています。

const space_id = "piyo";
const api_key = "hogehoge";
const sheet_id = "fugafuga";
const title = [
  "種別",
  "状態",
  "件名",
  "担当者",
  "カテゴリー",
  "期限",
  "課題番号",
  "優先度",
  "内容",
  "URL",
  "更新日（GAS）"
];
const sleep_second = 1;
function getPjList() {
  const api = "https://" + spaceID + ".backlog.jp/api/v2/projects?apiKey=" + apiKey;
  //apiを叩いてレスポンスを変数に入れます。
  const response = fetchApi(api);
  //課題一覧を配列にして変数に入れます。
  const pj_list = JSON.parse(response.getContentText());
  for (let i = 0; i < Object.keys(pj_list).length; i++) {
    console.log("PJ名：" + pj_list[i].name + "の取得を開始");
    getBackLogData(pj_list[i].id, pj_list[i].name);
  }
}
function getBackLogData(project_id, project_name) {
  //100件ずつしか取得できないのでオフセットを加算して全件取得します。
  let offset = 1;
  //loopがtrueなら以下のwhile文を繰り返します
  let loop = true;
  // sheetが既にあれば追加、なければ追加すべきシートを受け取る
  let sheet = insertOrSelectSheet(project_name);
  // タイトルの分、範囲を取得
  let get_title_range = sheet.getRange(1, 1, 1, title.length);
  // 配列ごと反映する
  get_title_range.setValues([title]);
  while (loop) {
    //sort = statusでステータス順で取得します
    //statusId[] = でステータスが4=完了,3=処理済み,2=処理中,1=未対応で取得できます。
    //countは設定しないと20になります。
    //offsetで初期は1,2周目は101から取得されます
    const api = "https://" + spaceID + ".backlog.jp/api/v2/issues?apiKey=" + apiKey
      + "&projectId[]=" + project_id + "&sort=status&order=&statusId[]=4&statusId[]=3&statusId[]=2&statusId[]=1&count=100&offset=" + offset;
    //apiを叩いてレスポンスを変数に入れます。
    let response = fetchApi(api);
    
    //課題一覧を配列にして変数に入れます。
    while (response === undefined) {
      let response = fetchApi(api);
    }
    let issue_list = JSON.parse(response.getContentText());
    let issue_lenth = Object.keys(issue_list).length
    // 0件の時は早期リターン
    if (issue_lenth === 0) break;
    //2行目からは課題情報が入ります。
    let range = 1 + offset;
    let set_value = [];
    // 最大100件ずつデータを配列にする
    for (let i = 0; i < issue_lenth; i++) {
      let issue = issue_list[i];
      set_value.push([
        issue.issueType.name,
        issue.status.name,
        issue.summary,
        issue.assignee ? issue.assignee.name : "未設定",
        issue.category ? issue.category.name : "未設定",
        issue.dueDate ? formatDate(new Date(issue.dueDate)) : "未設定",
        issue.issueKey,
        issue.priority.name,
        issue.description,
        "https://" + spaceID + ".backlog.jp/view/" + issue.issueKey,
        new Date()
      ]);
    }
    // 課題数を計算
    let num_rows = set_value.length;
    // 1つ目の課題の項目数を計算
    let num_columns = set_value[0].length;
    let get_columns = sheet.getRange(range, num_columns, num_rows, 1);
    get_columns.setNumberFormat('yyyy/MM/dd H:mm:ss');
    // 課題数と項目数の分、範囲を取得
    let get_range = sheet.getRange(range, 1, num_rows, num_columns);
    // 配列ごと反映する
    get_range.setValues(set_value);
    //取ってきた配列が100の時は続きがある可能性があるので100ずらしてもう一度ループします
    if (issue_lenth === 100) {
      offset += 100;
    } else {
      loop = false;
    }
  }
}
function formatDate(date) {
  var 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) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  let spreadsheet = SpreadsheetApp.openById(sheetId);
  let sheet = spreadsheet.getSheetByName(pj_name);
  if (sheet === null){
    return ss.insertSheet(pj_name);//　追加したシートを返す
  }
  return sheet;
}
function sleep(sleep) {
  if (sleep !== 0) {
    Utilities.sleep(sleep * 1000);
  }
}
function fetchApi(url){
  return fetchUrl(url);
}
function fetchUrl(url) {
  try {
    return UrlFetchApp.fetch(url);
  } catch(e) {
    console.log(e)
    sleep(sleep_second);
    fetchApi(url)
  }
}
//削除除外シートor末尾のシート1つを残して、すべてのシートを削除する関数
function deleteALLSheets() {
  //あらかじめ削除したくないシート名を記載してください。例「["シート１","シート5","シート10"]」
  const notDelSheet = [""];
  // 現在アクティブなスプレッドシートを取得
  let mySheet = SpreadsheetApp.getActiveSpreadsheet();
  //取得したスプレッドシートにある全てのシートを配列として取得
  let sheetData = mySheet.getSheets();
  //末尾のシートを削除するかを決めるフラグ
  let flag = 1;
  if (flag === 1) {
    var sheet = mySheet.getSheetByName("シート1");
    sheet ? mySheet.deleteSheet(sheet) : '';
    mySheet.insertSheet("シート1");
  }
  //forループでシートを削除する削除を行う
  for(i=0;i<sheetData.length;i++){
    //削除したくないシート存在しない場合、末尾のシートは削除せずスキップする
    if(flag ==0 && i == sheetData.length-1){
      break;
    }
    //削除対象から除外するシートにヒットした場合は、削除処理は行わず、フラグを立てる
    if(notDelSheet.indexOf(sheetData[i].getSheetName()) != -1){
      flag = 1;
    }
    //削除除外シートではない場合は、削除処理を実行する
    else{
      mySheet.deleteSheet(sheetData[i]);
    }
  }
}

対象

初期設定

プロジェクト一覧の取得

ヘッダーを取得、挿入

whileで課題一覧を挿入

細かいメソッド

最後に

関連ページはこちらです