Help us understand the problem. What is going on with this article?

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

More than 3 years have passed since last update.

GitHub API Issue x Google SpreadSheetの連携

Google_スプレッドシート.png

任意のレポジトリのissue一覧を表示してくれます。
レポジトリ名のシートがあればそこに、なければ追加してくれます。
スプレッドシート側の機能で条件付き書式で閉じたissueを暗くしたりとか、
GitHubのAPIで閉じたやつを表示しない、直近1カ月のだけ表示とか、
いろいろ試してみてください。

GitHub API
https://developer.github.com/v3/

入れ方

スプレッドシートをひらく

ツール

スクリプトエディタ

以下のコードを入れる

保存&実行、権限許可してください

トリガーも設定できます。

code

※access_tokenはここで発行してください
https://github.com/settings/tokens

function myFunction() {

  var GITHUB_OWNER = 'owner_name'; // レポジトリのオーナー名を入れてください
  var GITHUB_ACCESS_TOKEN = 'access_token'; // 上記で発行したaccess tokenを入れてください
  var REPOSITORIES = ['repository_name'] // 出したいレポジトリ名を入れてください

  // APIからissueのjsonを取得
  var getIssues = function(repository){
    var url = 'https://api.github.com/repos/' + GITHUB_OWNER + '/' + repository + '/issues?state=all&sort=created&direction=asc&access_token=' + GITHUB_ACCESS_TOKEN;
    var response = UrlFetchApp.fetch(url);

    var json = response.getContentText();
    return JSON.parse(json);
  }

  // issueのjsonから中身を取得
  var getAttributesOfIssue = function(issue){
    var milestone = "";
    if(issue["milestone"]){
      milestone = issue["milestone"]["title"];
    }

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

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

    var due_on = "";
    if(issue["milestone"] && issue["milestone"]["due_on"]){
      due_on = issue["milestone"]["due_on"].substring(0, 10);
    }

    var opend_at = "";
    if(issue["created_at"]){
      opend_at = issue["created_at"].substring(0, 10);
    }

    var closed_at = "";
    if(issue["closed_at"]){
      closed_at = issue["closed_at"].substring(0, 10);
    }

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

    return [
      milestone,
      issue["title"],
      assignee,
      issue["state"],
      labels,
      due_on,
      opend_at,
      closed_at,
      url
    ]
  }

  var sortByValueOfIndex = function(ary, index){
    return ary.sort(function(a,b){
      if( a[index] < b[index] ) return -1;
      if( a[index] > b[index] ) return 1;
      return 0;
    });
  }

  // スプレッドシートを取得
  var ss = SpreadsheetApp.getActive()

  // 指定したレポジトリでシートに反映していく
  REPOSITORIES.forEach(function(repository){
    var issues = getIssues(repository).map(function(issue){
      return getAttributesOfIssue(issue);
    });

    // due on でソートしています
    issues = sortByValueOfIndex(issues, 5);
    var titles = ["Milestone", "Title", "Assignee", "Status", "Labels", "due_on", "opened_at", "closed_at", "Issue URL"];
    issues.unshift(titles);

    var sheet = ss.getSheetByName(repository);
    if(sheet == null) {
      ss.insertSheet(repository);
      sheet = ss.getSheetByName(repository);
    }
    sheet.getRange("A1:I" + (issues.length).toString()).setValues(issues);
  });
}

すっごく気が向いたら、適当に済ませている部分とかを直します。
編集リクエストも大歓迎です。

yoshimikeisui
スタートアップでエンジニア修行をしていたら、楽しすぎて学校をやめていた FiNCアプリ限定招待中 https://bit.finc.com/675m29
https://www.wantedly.com/users/20936
finc
健康寿命を伸ばすアプリFiNCの開発・運営を行うモバイルヘルステクノロジーベンチャー
https://finc.com
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした