GitHub Projects(classic)のバーンダウンチャートをつくった話

2022.12.23Update

川上

この記事は、ニフティグループ Advent Calendar 2022 21日目の記事です。

はじめに

会員システムグループ第二開発チームの川上です。普段はニフティ会員向けiOS/Androidアプリの開発や運用を担当しています。

私のチームはスクラムで開発しており、GitHub Projects(classic)でタスク管理しています。スプリント内の進捗を管理する上でプランニングポーカーでつけたポイントを可視化したいという話がありました。そこで、定期的に自動更新するバーンダウンチャートをGAS(Google Apps Script)で簡易的に用意してみました。

本記事ではこのバーンダウンチャート作成で行った実装について紹介します。

要件

現時点までの残りポイントについて折れ線が表示される
スプリント終了日までの予測ポイントについて折れ線が表示される
- 予測線は残日数の割合で計算
- 休日はポイント消費しない
定期的に自動更新する

構成

リソース管理や運用の手間を少なくするため、GASでタスクデータを取得してLooker Studioに表示しています。

前提

GitHubのIssueにはチーム独自の運用として下記が設定されており、一部実装はこの内容を前提としています。

Title
- タスクのタイトル
- 「:」後にポイントを記載
- 例) 「〇〇のテストを作成する:3」、「〇〇のインターフェースを追加する:2」
Milestones
- スプリントを設定
- 例）「Sprint5」、「Sprint11」
Labels
- チケットの種類を設定
- 「PR」「Epic」以外がポイント集計対象のタスク
- 例）「android」、「ios」、「PR」、「Epic」

実装

1. スプレッドシートを用意

スプレッドシートを新規作成してシートを追加して4つ用意します。

タスク一覧シート

GitHubから取得した情報を保存しておくシート

Sprint集計シート

タスク一覧をスプリントごとに集計したシート
実際にLooker Studioから参照してバーンダウンチャート化する

設定用シート

スプリント期間などの情報が記載されたシート

一時計算用シート

GASから一時的に書き込む空のシート

2. GitHub API v4でデータ取得してスプレッドシートに収集する

GitHub API v4でデータを取得するために、下記の関数を用意します。

function fetchGithubTasks() {

const graphql_query =

query { \

search(type: ISSUE, query: "is:issue org:organization_name project:project_name", last: 100) { \

issueCount \

nodes {

... on Issue { \

id \

milestone { title } \

number \

title \

closed \

closedAt \

createdAt \

author { login } \

assignees(first: 100){ nodes { login } } \

labels(first: 100){ nodes { name } } \

} \

}

// スクリプトプロパティに登録されたトークンを取得

const token = PropertiesService.getScriptProperties().getProperty("GITHUB_ACCESS_TOKEN");

const option = {

method: "post",

contentType: "application/json",

headers: {

Authorization: "bearer " + token

payload: JSON.stringify({ query: graphql_query })

};

return UrlFetchApp.fetch("https://api.github.com/graphql", option);

}

「graphql_query」の文字列で指定している「organization_name」と「project_name」は環境にあった文字列に置き換えてください。また、定期実行されるまでの期間に100件以上更新されることがなかったため、一回の実行でIssueの取得件数は更新日時が新しい順に100件としています。

この関数を利用してデータを取得し、データ変換とスプレッドシートへの書き込みを行います。

const SPREAD_SHEET_ID = "スプレッドシートのID"

const TASK_SHEAT_NAME = "タスク一覧シート"

const TMP_SHEAT_NAME = "一時計算用のシート"

const ignore_labels = [

"Epic",

"PR",

]

// タスク一覧を取得してスプレッドシートに書き込む関数

function updateTasks() {

// GitHubからタスクを取得

const response = fetchGithubTasks()

// レスポンスをタスク形式に変換して、一部のlabelに該当するタスクを除去

const result = JSON.parse(response)

const sbis = result.data.search.nodes

.map(x => convertToTask(x))

.filter(x => !ignore_labels.includes(x.label))

// keyとvalueを分離

const keys = Object.keys(sbis[0])

const records = sbis.map(x => Object.values(x))

// スプレッドシートに書き込み

const sheet = SpreadsheetApp.openById(SPREAD_SHEET_ID).getSheetByName(TASK_SHEAT_NAME)

const tmpSheet = SpreadsheetApp.openById(SPREAD_SHEET_ID).getSheetByName(TMP_SHEAT_NAME)

// 取得したデータからスプレッドシートを更新

records.forEach(x => {

// idから行番号を取得

const row = getRow(tmpSheet, TASK_SHEAT_NAME, x[0])

// IDが存在しない場合は新規追加

if (row == null) {

sheet.appendRow(x)

return

}

// 存在する場合は置き換え

sheet.getRange(row, 1, 1, x.length).setValues([x])

})

}

// スプレッドシートのQUERY関数でIDを検索する関数

// データが多くなったときに線形探索より高速

function getRow(tmpSheet, targetSheetName, id) {

tmpSheet.getRange(1,1).setValue(`=QUERY({${targetSheetName}!A:A, ARRAYFORMULA(ROW(${targetSheetName}!A:A))},"WHERE Col1 = '${id}'")`)

const row = tmpSheet.getRange(1, 2).getValue()

return row != "" ? row : null

}

// GitHubから取得したデータを整形する関数

function convertToTask(item) {

const title = item.title.split(':', 2)[0]

const point = parseInt(item.title.split(':', 2)[1] ?? 0)

const closedAtJST = item.closedAt ? Utilities.formatDate(new Date(item.closedAt), "JST", "yyyy-MM-dd HH:mm:ss") : undefined

const createdAtJST = item.createdAt ? Utilities.formatDate(new Date(item.createdAt), "JST", "yyyy-MM-dd HH:mm:ss") : undefined

return {

id: item.id,

title: title,

point: point,

closed: item.closed,

closedAt: closedAtJST,

author: item.author.login,

assignee: item.assignees.nodes[0]?.login ?? "",

label: item.labels.nodes[0]?.name ?? "",

milestone: item.milestone?.title ?? "",

createdAt: createdAtJST,

}

GASのトリガーにupdateTasks関数を定期実行するように設定します。実行後は次のようなデータがスプレッドシートに書き込まれます。

3. Sprint用のデータに変換する

タスク一覧シートにデータ取得できましたが、Looker Studioでバーンダウンチャートのようなグラフを表示するにはこのデータを元に値の加工が必要です。Looker Studio上でも値の加工はできますが、データソースにスプレッドシートを使う場合は複雑な加工ができません。そのため、スプレッドシート側の別シート（Sprint集計シート）で加工を行います。

また、タスク一覧シートの変更を即時にSprint集計シートに反映する処理が必要です。ただ、変更したデータの取得→加工→反映を愚直に実装するのは少し手間がかかるため、GASからはセルにスプレッドシート関数の文字列を書き込むことで実現します。

下記のコードは設定シートに記載された更新日を過ぎたら、次のスプリント日数分の行を追加して、各セルにスプレッドシート関数を埋め込んでいます。（実装を妥協しているので、シートのヘッダーが変わったら崩れてしまいます…）

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

const CONFIG_SHEAT_NAME = "設定用シート"

const ACTIVITY_SHEAT_NAME = "Sprint集計シート"

// 設定用シートを読み込む関数

function readConfig(sheet) {

const rows = sheet.getDataRange().getValues()

// Configデータをマップに読み込み

const config = {}

rows.forEach( (x, i) => config[x[0]] = { value: x[1], index: i } )

return config

}

// 休日判定用の関数

function isHoliday(date) {

// 土日

const day = date.getDay()

if (day === 0 || day === 6) return true

// 祝日

const id = 'ja.japanese#holiday@group.v.calendar.google.com'

const cal = CalendarApp.getCalendarById(id)

const events = cal.getEventsForDay(date)

if (events.length) return true

// その他休日

const otherHoliday = [

'12/28',

'12/29',

'12/30',

'12/31',

'01/01',

'01/02',

'01/03',

];

const fd = Utilities.formatDate(date, 'JST', 'MM/dd')

return otherHoliday.some(value => value === fd)

}

// 次回のスプリントデータをスプレッドシートに書き込む関数

function nextSprint() {

const configSheet = SpreadsheetApp.openById(SPREAD_SHEET_ID).getSheetByName(CONFIG_SHEAT_NAME)

config = readConfig(configSheet)

const today = new Date()

// 更新日前は何もしない

if (today < config.next_sprint_update_date.value) {

return

}

// 設定シートのスプリント番号を更新、次回更新日を設定

const nextSprintNumber = config.current_sprint_number.value + 1

const nextUpdateTime = new Date(config.next_sprint_update_date.value.getTime());

nextUpdateTime.setDate(nextUpdateTime.getDate() + 7 * config.sprint_week_span.value)

configSheet.getRange(config.current_sprint_number.index+1, 1+1).setValue(nextSprintNumber)

configSheet.getRange(config.next_sprint_update_date.index+1, 1+1).setValue(Utilities.formatDate(nextUpdateTime, "JST", "yyyy-MM-dd HH:mm:ss"))

const activitySheet = SpreadsheetApp.openById(SPREAD_SHEET_ID).getSheetByName(ACTIVITY_SHEAT_NAME)

const lastRowNumber = activitySheet.getLastRow()

const sprintStartDate = new Date(config.next_sprint_update_date.value.getTime());

sprintStartDate.setDate(sprintStartDate.getDate() + 1)

// 前処理（日割計算のため、スプリントの実働日数を計算しておく）

let working_day_num = 0

for (var d = new Date(sprintStartDate.getTime()); d <= nextUpdateTime; d.setDate(d.getDate() + 1)) {

working_day_num = !isHoliday(d) ? working_day_num + 1 : working_day_num

}

// DailyActivityシートに次のスプリント分のデータを追加する

let index = 0

let sprint_elapsed_day = -1

for (var d = new Date(sprintStartDate.getTime()); d <= nextUpdateTime; d.setDate(d.getDate() + 1)) {

const currentRowNumber = lastRowNumber + index + 1

// 営業日判定

const is_business_day = !isHoliday(d)

// スプリント経過日数（初日を0とする）

sprint_elapsed_day = !isHoliday(d) ? sprint_elapsed_day + 1 : sprint_elapsed_day

// 特定のmilestoneのうちdの日付に作成されたポイント

const today_created_point = `=SUMIFS(${TASK_SHEAT_NAME}!$C:$C,${TASK_SHEAT_NAME}!$J:$J,">="&$A${currentRowNumber}, ${TASK_SHEAT_NAME}!$J:$J,"<"&($A${currentRowNumber}+1), ${TASK_SHEAT_NAME}!$I:$I,"="&$B${currentRowNumber})`

// 特定のmilestoneのうちdの日付に完了したポイント

const today_closed_point = `=SUMIFS(${TASK_SHEAT_NAME}!$C:$C,${TASK_SHEAT_NAME}!$E:$E,">="&$A${currentRowNumber}, ${TASK_SHEAT_NAME}!$E:$E,"<"&($A${currentRowNumber}+1), ${TASK_SHEAT_NAME}!$I:$I,"="&$B${currentRowNumber})`

// 特定のmilestoneのうちdの日付まで作成されたポイント合計

const total_created_point = `=SUMIFS(${TASK_SHEAT_NAME}!$C:$C, ${TASK_SHEAT_NAME}!$J:$J,"<"&($A${currentRowNumber}+1), ${TASK_SHEAT_NAME}!$I:$I,"="&$B${currentRowNumber})`

// 特定のmilestoneのうちdの日付まで完了したポイント合計

const total_closed_point = `=SUM($F${lastRowNumber+1}:$F${currentRowNumber})`

// 予測線用の残りポイント（残日数の割合で計算）

const focast_remaining_point = index == 0 ? `=$G${currentRowNumber}` : `=MAX(($I${currentRowNumber-1}+$E${currentRowNumber})-ROUNDUP(($I${currentRowNumber-1}+$E${currentRowNumber})/(${working_day_num}-$D${currentRowNumber})) * ($D${currentRowNumber}-$D${currentRowNumber-1}), 0)`

// 残りポイント（未来の日付は空白を入力）

const remaining_point = `=IF(TODAY()+1>A${currentRowNumber},MAX($G${currentRowNumber}-$H${currentRowNumber}, 0), "")`

// スプレッドシートに書き込み

activitySheet.appendRow([

Utilities.formatDate(d, "JST", "yyyy-MM-dd"),

`Sprint${nextSprintNumber}`,

is_business_day,

sprint_elapsed_day,

today_created_point,

today_closed_point,

total_created_point,

total_closed_point,

focast_remaining_point,

remaining_point,

])

index += 1

}

収集処理と同様にGASのトリガーにnextSprint関数を定期実行するように設定します。実行すると次のようなデータを書き込みます。

4. Looker Studioで表示する

Sprint集計シート（下記の図ではdaily_activity）をLooker Studioのデータソースに追加して、折れ線グラフを作成します。フィルタ機能でmilestoneを設定することによりSprint単位に表示を絞ることができます。

設定を完了すると次のようなグラフが表示されます。

おわりに

今回はGAS + Looker Studioで簡易的なバーンダウンチャートを作る方法を紹介しました。バーンダウンチャートがあることで視覚的に進捗が把握しやすくなります。そして、スプリントゴールに間に合うかどうかを早めに見極めて、プロダクトオーナーへの相談や作業自体の見直しがしやすくなります。もしバーンダウンチャートを利用していない場合はぜひ導入を検討してみてください。

明日は、@penpenpenさんのお金をかけずに学ぶRustです。お楽しみに！