ใใฎ่จไบใฏใ ใใใใฃใฐใซใผใ 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ใใใฏใปใซใซในใใฌใใใทใผใ้ขๆฐใฎๆๅญๅใๆธใ่พผใใใจใงๅฎ็พใใพใใ ไธ่จใฎใณใผใใฏ่จญๅฎใทใผใใซ่จ่ผใใใๆดๆฐๆฅใ้ใใใใๆฌกใฎในใใชใณใๆฅๆฐๅใฎ่กใ่ฟฝๅ ใใฆใๅใปใซใซในใใฌใใใทใผใ้ขๆฐใๅใ่พผใใงใใพใใ๏ผๅฎ่ฃ
ใๅฆฅๅใใฆใใใฎใงใใทใผใใฎใใใใผใๅคใใฃใใๅดฉใใฆใใพใใพใโฆ๏ผ 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 ใงใใใๆฅฝใใฟใซ๏ผ We are hiring! ใใใใฃใงใฏใใใพใใพใชใใญใใฏใใธๆๆฆใใใจใณใธใใขใ็ตถ่ณๅ้ไธญใงใ๏ผ ใ่ๅณใฎใใๆนใฏไปฅไธใฎๆก็จใตใคใใใใๆฐ่ปฝใซใ้ฃ็ตกใใ ใใ๏ผ ใใใใฃๆ ชๅผไผ็คพๆก็จๆ
ๅ ฑ Tech TalkใMeetUpใ้ๅฌใใฆใใใพใ๏ผ ใใกใใใๆฐ่ปฝใซใๅฟๅใใ ใใ๏ผ Event – NIFTY engineering