ãã®èšäºã¯ã ãããã£ã°ã«ãŒã 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