GoogleAppScript(GAS)でGithubとWBS スプレッドシートを同期

背景

細かいタスク管理を嫌うメンバーもいると思うので、
Githubのシンプルなissueのみで完結できるといいのですが、

チーム全体の状態を把握した上で、差込や要望にどれくらいで対応できるかを
返答出来るようにしておくためには、期限付きタスクが増えてくると、
Githubだけでは、工数、時間軸が見えず分かり辛いという
もどかしさがありました。

そこで！

Githubでのissue管理方式は維持しつつ、
自由にカスタマイズしやすいGoogle スプレッドシートのWBS連携管理を考えました。
(有料のGithub連携ガンチャートサービスも検討しましたが、自由度&コストを考えて)

ただ、日々追加されていくissueをコピペするのは辛いので、
GoogleAppScript(GAS) を使って、 ポチッと押せば、最新が反映されるもの が欲しいと思ったので
それを叶えるためのコードを今回はまとめて見ました☆(GASは、今回初利用)

背景・叶えたいことを整理すると下記な感じ。
↓

目的/理想：

チーム全体、各自が
今手にするべきタスクが分かって、どれくらいのスピード感でやればいいか分かる
→よって、各自が今の開発に集中できる状態

具体的には、次のことがすぐ分かるようになりたい：

• 期間内に達成したいタスクがちゃんと今のスピードで終わるか
  • →〆切ギリギリになって無理をしないと間に合わないと悟ると精神衛生上/リスク的によくない
• 差込やイレギュラーはどれくらい受け入れられる余裕があるのか
  • →差込依頼者の交渉、調整に必要
• 急ぎじゃないけどやっておきたい投資タスクもどれくらい時間を避けるのか
• →少し先のための改善は大事だけれど、間に合わないのはいけないので
• 負担は誰かに寄りすぎていないか
  • →分かれば再分担して助け合える
• 早めに進めておいたほうがいいタスクは何か
  • →大事なところを逃さないように
• 早めに誰かに依頼しておいたほうが良いものは何か
  • →後戻りなどを避ける

要件

Github→ GoogleAppScript→ スプレッドシート WBS同期

• スプレッド メニューにissue同期(GAS実行)リンクを追加
• issue同期(GAS実行)リンクを押すと最新の状態が反映される
  • シートに記載がない新しいissueが追記される
  • 「タイプ 作成日 タイトル リンク(issue No表記) 担当 優先度」の列項目を追記
  • closeしたissueは、削除される
  • 今回は、一度追加したissueのタイトル、担当、優先度がGithubで変更されてもそれを反映するところまではしない。(そんなに難しくないがそこまで今は必要なし)

※あとは、
上記で用意する列項目のとなり列に、
見積工数や日付列等を追加、excel 関数を活用して、営業日とチーム全体の残り可能工数を出すのもを用意。
フィルタ保存を使っていつでも担当別に絞込ができるように(ここはまた別途、記事書けたらいいな。)

今回使った GoogleAppScript

参考：【Google Apps Script入門】セルの取得・変更をする | UX MILK

● デバッグ

Logger.log(hoge);

→関数実行後、コマンド+Enterキー でログの中身を表示できる

● 設定したプロパティ値を取得

参考：Class UserProperties  |  Apps Script  |  Google Developers

使用例：スクリプトエディタのメニュー > プロジェクトのプロパティ > ユーザープロパティ > git_token をkeyにして github tokenを設置

var token = UserProperties.getProperty('git_token');

※注意：
UserProperties.getPropertyは廃止予定で
代替ぽい下記を試しましたが、うまく自分は取得できなかったので、一旦今も使えるものを利用、
使えなくなったら、config用シートを使うことを考えましたが、
複数の人数で見るものと想定するとapi tokenの保存場所は、別途要検討が必要そうです。

参考：Properties Service  |  Apps Script  |  Google Developers

var userProperties = PropertiesService.getUserProperties();
var units = userProperties.getProperty('DISPLAY_UNITS');

● シートを取得

var spreadSeet = SpreadsheetApp.getActive()
var sheet = spreadSeet.getSheetByName(sheetName);

● シートを作成

spreadSeet.insertSheet(sheetName);
sheet = spreadSeet.getSheetByName(sheetName);

● シートの最終行取得

var lastRow = sheet.getLastRow();

● セル範囲を指定

var area = sheet.getRange(row, column [, numrows [, numcolumns]])

● 指定範囲のセルの値を取得

var area = sheet.getRange(row, column);
var data = area.getValues();

● 行を削除

sheet.deleteRow(raw)

● 最終行に追加

sheet.appendRow(github_issue)

● メニューに関数実行リンクを追加

参考： GASでSpreadsheetを操作する自分的ベストプラクティス - Qiita

var items = [{name: 'sync_issues', functionName: 'updateIssues'}];
spreadSeet.addMenu('MyScripts', items);

● 指定列内のキーワードを含む行を取得

参照: Google Apps Scriptでスプレッドシート内を検索して行番号を返す関数（高速版）

function findRow(sheet, val, col){
  var sheet_data = sheet.getDataRange().getValues();

  for(var i=1; i < sheet_data.length; i++){
    if(sheet_data[i][col-1] === val){
      return i+1;
    }
  }
  return 0;
}

要件を満たす具体的なコード

参考：GoogleSpreadSheetにGitHub Issueの一覧を表示させる方法 - 連携の仕方 - Qiita

実行方法

1. Googleスプレッドシート > メニュー > ツール > スクリプトエディタ を開いて下記のようなコードをはりつける

2. 下記を使う場合、事前設定コメントメモ箇所部分済みであることが前提

// ※事前設定------------------------------------------
// ■1. 設定シート用意
// シート名：「config」
// A列： B列 ※ 定数名：value
//
// GITHUB_OWNER ： ownername
// REPOSITORY ： reponame
// GITHUB_ACCESS_TOKEN ： token ※1
// NEW_ISSUE_NUMBER ： 2848  ※2
//
// ※1 今回の場合、`UserProperties.getProperty` (※もうすぐ廃止予定なので注意)を使っているので、
// スクリプトエディタのメニュー > プロジェクトのプロパティ > ユーザープロパティ > `git_token ` を
//  keyにして github tokenを設置
//
// ※2 なければ、最新1ページ目のみ取得。
//     初回全ページ分取得したい時などはここを設定する。
//
// ■2.見出し行をリポジトリ名のシートに用意
// ここでは、「タイプ 作成日 タイトル リンク 担当 優先度」を利用
// -------------------------------------------------



function updateIssues() {

  var CONFIG_SHEET = getSeet('config');
  var GITHUB_OWNER = CONFIG_SHEET.getRange("B1").getValue();
  var REPOSITORY = CONFIG_SHEET.getRange("B2").getValue();
  // 注意：UserProperties.getPropertyは廃止後はシートから取得など別の方法が必要
  // var GITHUB_ACCESS_TOKEN = CONFIG_SHEET.getRange("B3").getValue();
  var GITHUB_ACCESS_TOKEN = UserProperties.getProperty('git_token');
  var NEW_ISSUE_NUMBER = CONFIG_SHEET.getRange("B4").getValue();
  var ISSUE_NUMBERS_COL = 2; //issue番号を記載する列番号


  // スプレッドシート取得
  function getSeet(sheetName){
    var spreadSeet = SpreadsheetApp.getActive()
    var sheet = spreadSeet.getSheetByName(sheetName);

    if(sheet == null) {
      spreadSeet.insertSheet(sheetName);
      sheet = spreadSeet.getSheetByName(sheetName);
    }
    return sheet;
  }


  // Github API issue情報取得
  function getGithubIssues(){
    //取得したいisuueページ数※3は適当。そのPJTのissue更新頻度に合わせて
    var page_count = NEW_ISSUE_NUMBER ? Math.ceil(NEW_ISSUE_NUMBER / 30) : 3;

    var git_token = UserProperties.getProperty('git_token')
    var data = [];

    for(i = 1; i <= page_count; i++) {
      var base = 'https://api.github.com/repos/' + GITHUB_OWNER + '/' + REPOSITORY + '/issues';
      var url = base + '?page=' + i +'&state=all&sort=created&direction=desc&access_token=' + git_token;

      var response = UrlFetchApp.fetch(url);
      var json = response.getContentText();
      Array.prototype.push.apply(data,JSON.parse(json));
    }
    return data;
  }


  // 既にシートに記載があるissue番号の配列を返す
  function getSeetIsuueNumbersArray(sheet, row, column){
    var numbers = []; 
    if (sheet){
      var lastRow = sheet.getLastRow();
      var numData = sheet.getRange(row, column, lastRow);
      numData = numData.getValues();
      numData.forEach(function(a){numbers.push(a[0])});
    }
    return numbers;
  }


  // ラベルから優先度の判断する文字のみ抽出
  function propertyFormatTxt(label_txt) {
    if (label_txt.match('急')){
      return 5;
    }else if (label_txt.match('高')){
      return 4;
    }else if (label_txt.match('普')){
      return 2;
    }else if (label_txt.match('低')){
      return 1;
    }else if (label_txt.match('要望')){
      return 3;
    }else if (label_txt.match('bug')){
      return 5;
    }
    return 0;
  };


  // issue情報を 行にあてはめるarrayに整形
  function issueRowFormatData(issue){

    var type = "";
    if(issue["html_url"]){
      type = issue["html_url"].match('issues');
      type = type != 'issues' ? 'pr' : 'issue';
    }
    if(type == 'pr'){return;}

    var assignee = "";
    if(issue["assignee"]){
      assignee = issue["assignee"]["login"];
    }

    var opend_at = "";
    if(issue["created_at"]){
      opend_at = issue["created_at"].substring(5, 10);
      opend_at = opend_at.replace(/-/g, '/');
    }

    var number_link = '=HYPERLINK("' + issue["html_url"] + '","' + issue["number"] + '")';

    var labels = "";
    if(issue["labels"]){
      labels = issue["labels"].map(function(label){
        return label["name"]
      }).join(",");
    }

    // タイプ 作成日 タイトル リンク 担当 優先度
    return [
      issue["title"],
      number_link,
      type,
      opend_at,
      assignee,
      propertyFormatTxt(labels)
    ]
  }


  // 検索対象の列を指定して該当値がある行番号を返す
  function findRow(sheet, val, col){
    var sheet_data = sheet.getDataRange().getValues();

    for(var i=1; i < sheet_data.length; i++){
      if(sheet_data[i][col-1] === val){
        return i+1;
      }
    }
    return 0;
  }


  // 最新を最終行に追加&closeを削除
  function updateSeet(){
    var sheet = getSeet(REPOSITORY);
    var sheet_issue_numbers = getSeetIsuueNumbersArray(sheet, 5, 2);

    var new_open_issues = getGithubIssues().map(function(github_issue){
      var issue_num = String(github_issue["number"]);
      var num_index = sheet_issue_numbers.indexOf(issue_num)
      
      // シートになかった時だけ追加用のdataを返す
      if(num_index == -1 && github_issue["state"] == "open"){
        return issueRowFormatData(github_issue);

      // closeしていたら該当行を消す
      }else if(github_issue["state"] == "closed"){
        var raw = findRow(sheet, issue_num, ISSUE_NUMBERS_COL);
        if(raw > 0){ sheet.deleteRow(raw); }
      }
    });

    // 追加データを最終行に追記
    new_open_issues.forEach(function(github_issue){
      if(github_issue){ sheet.appendRow(github_issue); }
    });
  }

  updateSeet(REPOSITORY);
}




// スプレッドシートのメニューに関数実行 を追加
function onOpen() {
  var spreadSeet = SpreadsheetApp.getActive();
  var items = [{name: 'sync_issues', functionName: 'updateIssues'}];
  spreadSeet.addMenu('MyScripts', items);
}

参考まとめ

• GoogleSpreadSheetにGitHub Issueの一覧を表示させる方法 - 連携の仕方 - Qiita

• GitHubのIssues縛りから抜け出す方法【禁断のマルパ○リ編】 - Qiita

• 【Google Apps Script入門】セルの取得・変更をする | UX MILK

• GASでSpreadsheetを操作する自分的ベストプラクティス - Qiita

• Google Apps Scriptでスプレッドシート内を検索して行番号を返す関数（高速版）

• Class UserProperties  |  Apps Script  |  Google Developers

• Properties Service  |  Apps Script  |  Google Developers

他js周りメモ

・注意：indexOf() 型をそろえないと 正しく判定できない

• Google Apps Script で 「スクリプトによるメソッド Range.getValue の使用頻度が高すぎます」って叱られたの巻 - Qiita

• JavaScriptにおける数値⇔文字列の型変換あれこれ - console.lealog();

コメント

概ね 「GoogleSpreadSheetにGitHub Issueの一覧を表示させる方法 - 連携の仕方 - Qiita」、こちらの記事を大変参考にさせて頂きました。yoshimikeisui さん ありがとうございますm(_ _)m

これが出来たおかげで、タスクが詰まってきた12月、残りの営業日で、あとどれくらいのissueに取り組めるかが、イメージしやすくなりました。ヾ(´∀`o)+。
リニューアルなど大きなまるっとした案件の時はあらかじめ、WBSをissueより先に作成運用していたりするのですが、通常改善フェーズだとissueが先に作成されて、そこからWBS連携管理は辛いなと思ってたのが解消できました！