Google Apps Script(GAS)でバーンダウンチャートを自動作成してみた

はじめに

はじめまして。NTTドコモサービスデザイン部の大橋です。
本記事では、私達がGoogle Apps Script（以降GASと略す）を使用しJiraのAPIからバーンダウンチャートの作成を自動化した事例についてご紹介します。

取り組みの経緯

私達は、おすすめアプリ※というシステムのサーバの開発・運用をしているチームです。
※おすすめアプリ | サービス・機能 | NTTドコモ
私達のチームではアジャイル開発を進めており、タスク管理ツールとして Jiraを使用しています。Jiraとは、アトラシアン社が提供するプロジェクト管理ソフトウェアです。スクラムボードやバックログ、スプリントなどの機能が豊富で、アジャイル開発に適しています。 Jiraには「課題」と、それをさらに細分化した「サブタスク」のチケットがあり、私達はそれらのチケットを作成しタスクの管理をしています。また、Jiraにはバーンダウンチャートを「課題」単位で管理する機能があり、進捗状況を可視化することができます。

しかし「課題」単位のバーンダウンチャートでは、順調に「サブタスク」がクローズされて行っていても、以下の画像のように最終日付近まで進捗がないかのように見えてしまいます。「サブタスク」単位でタスクを管理している場合は、「課題」より「サブタスク」単位で進捗を測ることがより適切です。そのため、「サブタスク」単位のバーンダウンチャートを作成する必要がありました。

最初の取り組み

最初に私達が行ったのは手動による「サブタスク」単位のバーンダウンチャートの作成です。私達はスプリント情報を管理するためConfluenceを用いてファイル共有などを行っています。スプリントのスケジュールや振り返りの内容、バックログの詳細などの情報をまとめています。当初は、Excelでバーンダウンチャートを作成し、それをConfluenceにアップロードするという流れでした。しかし、この方法には以下の３つの手間がありました。

手間①　 Jiraからその日までに完了した「サブタスク」数を検索
手間② 　取得した数値をExcelファイルに記載
手間③　ExcelファイルをConfluenceへのダウンロードとアップロード

自動運用の取り組み

これらの問題を解決するために、GASによる自動運用を試みました。Googleスプレッドシートを使用すれば、前述「手間③」のExcelファイルをConfluenceへダウンロードとアップロードする手間が省けます。加えて、GASを使用してJiraのAPIを用いることで、「手間①」および「手間②」を省略できます。
以下はバーンダウンチャート作成の流れです。

【バーンダウンチャート作成の流れ】
スプリント開始時
①営業日数とスプリント番号(シート内ではspと記載)をシートに記入
②「シート追加」ボタンを押下
　　→新しいスプリント用のシートが作成される
進捗確認時
③「更新」ボタンを押下
　　→シートにタスク数が記入され、グラフが更新される

成果物

以下の画像がスプレッドシートの内容です。例として日付と数字は適当に入れています。スプレッドシートに、「シート追加」ボタンと「更新」ボタンを挿入し、それぞれにwriteToGoogleSheet() 関数とaddSheet ()関数を設定しています。以下で各関数を説明します。

【writeToGoogleSheet() 関数（シート追加）】
新しいスプリント開始時には「シート追加」ボタンを押下することで新しいシートを作成しテンプレートを書き込みます。以下が新しいシートの画像と実際のコードです。(バーンダウンチャート作成の流れ②)

function addSheet() { 
  //スプレッドシートとシートを取得 
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); 
  var sheet = spreadsheet.getActiveSheet(); 
 
  //次回スプリント情報取得 
  var workingDays = sheet.getRange(ここに営業日数を記入したセルを指定).getValue(); 
  var sp = sheet.getRange(ここにスプリント番号を記入したセルを指定).getValue(); 
 
//スプレッドシートに新しいシートを追加挿入、名前を設定 
var sheetName = "sp" + sp; 
var templeteSheet = spreadsheet.getSheetByName("templete"); 
templeteSheet.copyTo(spreadsheet).setName(sheetName); 
 
//テンプレート挿入 
templete(workingDays,sheetName,sp); 
}

//テンプレート挿入関数 
function templete(workingDays,sheetName,sp){ 
  //追加したスプレッドシートとシートを取得 
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); 
  var sheet = spreadsheet.getSheetByName(sheetName); 

　//　スプリント情報をコピー 
  sheet.getRange(ここに営業日数を記入したセルを指定).setValue(workingDays); 
  sheet.getRange(ここにスプリント番号を記入したセルを指定).setValue(sp); 
  sheet.getRange(ここにスプリント番号を記入するセルを指定).setValue(sp+1); 

　//　総タスク数を書き込む 
  var totalCount = getTaskCountFromJira(sp).total; 
  sheet.getRange(ここに総サブタスク数を記入するセルを指定).setValue(totalCount); 
  sheet.getRange(ここに完了サブタスク数を記入するセルを指定).setValue(totalCount); 

  // Day追加 
  for(let i=1;i<=workingDays;i++){ 
    sheet.getRange(i+5,1).setValue("Day" + i); 
  } 

　// 基準値追加 
  var temp = totalCount; 
  var progress = totalCount/workingDays; 
  for(let i=0; i<=workingDays;i++){ 
    if(temp<0){ 
      temp = 0; 
    }
    sheet.getRange(i+5,4).setValue(temp); 
    temp =  Math.floor(temp - progress); 
  } 
}

【addSheet ()関数（更新）】
「更新」ボタンを押すと、その時点のタスク数をJiraから取得し日付とタスク数を書き込みます(画像の赤線)。以下が画像と実際のコードです。 (バーンダウンチャート作成の流れ③)

function writeToGoogleSheet() { 
  //現在のスプレッドシートを取得 
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); 
  var sheet = spreadsheet.getActiveSheet(); 
 
  //スプリント番号を取得 
  var sp = sheet.getRange(ここにスプリント番号を記入したセルを指定).getValue(); 

  // Jira APIからタスク数の情報を取得 
  var taskCountData = getTaskCountFromJira(sp); // スプリント番号から総タスク数と完了タスク数を取得 
  var completedCount = taskCountData.completed; //完了タスク数 
  var totalCount = taskCountData.total; //総タスク数 

  // シートに実行時の日付と曜日を書き込む 
  var today = new Date(); 
  var lastRowB =　sheet.getRange(4,2).getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow() +1;　// B列の最終行を取得 
  Logger.log(lastRowB); 
  sheet.getRange(lastRowB, 2).setValue(today); // B列に日付を追加 
  sheet.getRange(lastRowB, 3).setValue(getDayOfWeek(today)); // C列に曜日を追加 

  // シートにタスク数を書き込む 
  sheet.getRange(lastRowB, 5).setValue(totalCount-completedCount); // E列に残りタスク数を追加 
  sheet.getRange(lastRowB, 6).setValue(totalCount); // F列に総タスク数を追加 
  sheet.getRange(lastRowB, 7).setValue(completedCount); // G列に完了タスク数を追加 

  // 日付の曜日を取得 
  function getDayOfWeek(date) { 
    var days = ['日', '月', '火', '水', '木', '金', '土']; 
    return days[date.getDay()]; 
  } 
}

以下は、上記関数内で使用している、Jira APIからタスク数を取得する関数です。

// Jira APIからタスク数の情報を取得 
function getTaskCountFromJira(sp) { 
  //現在のスプレッドシートとシートを取得 
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); 
  var sheet = spreadsheet.getActiveSheet(); 
 
  // Jira APIに必要な情報を設定する 
  var JIRA_USER_NAME = ここにJiraのユーザー名を入力; 
  var API_TOKEN = ここにAPIトークンを入力; 
  var baseURL = ここにJiraのベースURLを入力; 
  var jqlTotal = ここに総サブタスク数を取得するjqlを入力; 
  var jqlCompleted = ここに完了サブタスク数を取得するjqlを入力; 
  var encCred = Utilities.base64Encode(JIRA_USER_NAME + ":" + API_TOKEN); 

  // Jira APIからタスク数の情報を取得する 
  var urlTotal = encodeURI(baseURL + "?jql=" + jqlTotal); 
  var dataTotal = fetchDataFromJiraApi(urlTotal, encCred); 
  var totalCount = dataTotal.total; 
  var urlCompleted = encodeURI(baseURL + "?jql=" + jqlCompleted); 
  var dataCompleted = fetchDataFromJiraApi(urlCompleted, encCred); 
  var completedCount = dataCompleted.total; 

  return { 
    total: totalCount, 
    completed: completedCount, 
  }; 
}

// Jira APIのhttpResponseを監視 
function fetchDataFromJiraApi(url, encCred) { 
  var fetchArgs = { 
    contentType: "application/json", 
    headers: { "Authorization": "Basic " + encCred }, 
    muteHttpExceptions: true 
  }; 
  var httpResponse = UrlFetchApp.fetch(url, fetchArgs); 
  if (httpResponse) { 
    var rspns = httpResponse.getResponseCode(); 
    switch (rspns) { 
      // リクエスト成功 
      case 200:   
        return JSON.parse(httpResponse.getContentText()); 
      // リクエストエラー 
      case 404: 
        Logger.log("Request error, No item found"); 
        return false; 
      // サーバエラー 
      case 503: 
        Logger.log("Server error, Service Unavailable"); 
        return false; 
      default: 
        Logger.log("Error"); 
        return false; 
    } 
  } else { 
    Logger.log("Jira Error", "Unable to make requests to Jira!"); 
    return false; 
  } 
}

生成AIの活用

私達の担当はサービス開発運用業務の効率化に向けて、生成AIであるLLM付加価値基盤※の活用促進をしています。 ※生成AIを活用した業務のDX推進および付加価値サービス提供に向けた実証実験を開始
今回はGAS完全未経験のメンバーでしたので、LLM基盤を利用しながらコードを実装しました。実装してもらいたい関数を説明することで、コメント付きのコードを生成することができ、実装時間の短縮に役に立ちました。ちなみにこの記事のたたきもLLM基盤を利用しながら作成してもらいました。記事の要点を入力するだけで、接続詞などを補って文章を作成してくれるのでこちらも大変役立ちました。今後もますますLLM基盤を業務に活用し、効率化に繋げていきたいと考えています。