Google Sheets GitHub Issue Integration

GitHubIntegration.gs

var paramsSheetName = "GitHub Params";

function createGitHubParams() {
  var sheets = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = sheets.insertSheet(paramsSheetName);
    sheet.appendRow(["API Token", "TODO"]);
    sheet.appendRow(["Owner", "TODO"]);
    sheet.appendRow(["Repository", "TODO"]);
}

function getGitHubParams() {
  var sheets = SpreadsheetApp.getActiveSpreadsheet();
  var parametersSheet = sheets.getSheetByName(paramsSheetName);
  if (parametersSheet == null) {
    throw new Error("Expected 'GitHub Params' sheet. Initialize one from the menu.");
  }
  return {
    token: parametersSheet.getRange(1, 2).getValue(),
    owner: parametersSheet.getRange(2, 2).getValue(),
    repo: parametersSheet.getRange(3, 2).getValue()
  };
}

function queryGitHub(token, graphQuery) {
  var githubUrl = "https://api.github.com/graphql";
  var options = {
    method: "post",
    payload: graphQuery,
    headers: {
      "Authorization": "bearer " + token
    }
  };
  var response = UrlFetchApp.fetch(githubUrl, options);
  var body = response.getContentText();
  return JSON.parse(body);
}

function extractTomlParameters(input) {
  var openingTag = "```toml";
  var closingTag = "```";
  var paramsStartIndex = input.indexOf(openingTag);
  if (paramsStartIndex < 0) {
    return {};
  }
  var paramsEndIndex = input.indexOf(closingTag, paramsStartIndex + openingTag.length);
  var paramsSection = input.substring(paramsStartIndex + openingTag.length, paramsEndIndex);
  var assignments = paramsSection.split("\n");
  var params = {};
  assignments.forEach(function (assignment) {
    var opIndex = assignment.indexOf("=");
    if (opIndex > 0) {
      var property = assignment.substr(0, opIndex);
      var value = JSON.parse(assignment.substr(opIndex + 1));
      params[property] = value;
    }
  });
  return params;
}

function extractIssues(results) {
  return results.edges.map(function (edge) {
    var node = edge.node;
    return {
      number: node.number,
      title: node.title,
      url: node.url,
      params: extractTomlParameters(node.body)
    };
  });
}

function extractMilestones(results) {
  return results.edges.map(function (edge) {
    var node = edge.node;
    return {
      title: node.title,
      issues: extractIssues(node.issues)
    };
  });
}

function getMilestones() {
  var params = getGitHubParams();
  var query = JSON.stringify({
    query: "{ \
      repository(owner: \"" + params.owner + "\", name: \"" + params.repo + "\") { \
        milestones(first: 100) { \
          edges { \
            node { \
              title \
              issues(first: 100, states: OPEN) { \
                edges { node { number title url body } } \
              } \
            } \
          } \
        } \
      } \
    }"
  });
  var results = queryGitHub(params.token, query);
  return extractMilestones(results.data.repository.milestones);
}

function buildMilestoneSheets() {
  var sheets = SpreadsheetApp.getActiveSpreadsheet();
  getMilestones().forEach(function (milestone) {
    var milestoneSheetName = "Milestone: " + milestone.title;
    var sheet = sheets.getSheetByName(milestoneSheetName);
    if (!sheet) {
      sheet = sheets.insertSheet(milestoneSheetName);
    }
    sheet.clearContents();
    var columns = [
      ["Issue #", function(issue) { return issue.number; }],
      ["Title", function(issue) { return issue.title; }],
      ["URL", function(issue) { return issue.url; }]
    ];
    milestone.issues.forEach(function (issue) {
      Object.keys(issue.params).forEach(function (key) {
        var columnExists = columns.some(function (col) { return col[0] === key });
        if (!columnExists) {
          columns.push([key, function (i) {
            var value = i.params[key];
            return value === undefined ? "" : value;
          }]);
        }
      });
    });
    sheet.appendRow(columns.map(function (col) { return col[0]; }));
    milestone.issues.forEach(function (issue) {
      var values = columns.map(function (col) {
        var getValue = col[1];
        return getValue(issue);
      });
      sheet.appendRow(values);
    });
    sheet.autoResizeColumn(1);
    sheet.autoResizeColumn(2);
  });
}

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu("GitHub")
    .addItem("Create Params Sheet", "createGitHubParams")
    .addItem("Update Milestone Sheets", "buildMilestoneSheets")
    .addToUi();
}