Sync GitHub issues to spreadsheet

README.md

DataFire Dataflow: Sync GitHub issues to spreadsheet

Pulls all new issues from a GitHub repo into a spreadsheet

View on DataFire

About

This Dataflow will sync all issues in a particular GitHub repo to a Google Sheet.

The sync is one-way: if an issue changes in GH, the changes will be reflected in the Spreadsheet, but not vice-versa

This Dataflow is useful for extending GitHub issues with new fields, such as priority, severity, and timeEstimate. You can then calculate things like the number of hours of work to complete a particular milestone.

Workflow

The Dataflow will push all your GitHub issues to the first sheet in your spreadsheet. Any changes you make to the issues here will be overwritten in the next run.

To add new fields such as priority or timeEstimate, add a new column in the first sheet. These new columns will not be overwritten.

To work with your issues, we suggest creating a second sheet that copies everything over from the first sheet, e.g. by putting =Sheet1!A1 in row 1 col 1 of sheet 2. You can then sort the issues, hide closed issues, sum timeEstimates, etc.

Setup

Create the Spreadsheet

You'll need to create a Google Spreadsheet at https://docs.google.com/spreadsheets/

In the first row, add the following cells as column headers:

• number
• title
• labels
• assignee
• state
• milestone

You'll also need to add a dummy data row. Type "1" in cell A2.

It may also help to make this sheet publicly visible by clicking "Share" in the top right, then clicking "get shareable link".

Authorizations

Authorize both GitHub and Google Sheets on the Settings tab. Be sure to include any necessary scopes depending on whether the spreadsheet/repo are public or private.

Constants

repoId and ownerId can be pulled from the repository URL, github.com/{ownerId}/{repoId}

The spreadsheetID can be pulled from the Google Sheets URL, e.g. for

https://docs.google.com/spreadsheets/d/1FAH5MByiDtRcMxsI23PwPQf7RCOmVj_BhVf8dCtI9iU/edit#gid=0

The spreadsheetID is 1FAH5MByiDtRcMxsI23PwPQf7RCOmVj_BhVf8dCtI9iU

Contact

If you have any questions or issues, feel free to contact bobby@datafire.io

step0.js

// GET https://api.github.com/repos/{ownerId}/{repoId}/issues
function request(data) {
  var pages = [1,2,3,4,5,6,7,8];
  return pages.map(function(p) {
    return {ownerId: constants.ownerId, repoId: constants.repoId, page: p, state: 'all'}
  })
}

step1.js

// GET https://spreadsheets.google.com/feeds/list/{key}/{worksheetId}/{visibility}/{projection}
function request(data) {
  return {
    visibility: 'private',
    projection: 'full',
    key: constants.spreadsheetID,
    'GData-Version': '2.1',
    worksheetId: 'od6',
    alt: 'json'
  }
}

step2.js

// PUT https://spreadsheets.google.com/feeds/cells/{key}/{worksheetId}/{visibility}/{projection}/{cellId}
function request(data) {
  global.fields = ['number','title','milestone','state','body','labels','assignee','created','updated','closed','link'];

  var rows = data[1] ? (data[1].feed.entry || []) : [];
  if (!rows.length) throw new Error("Please add these headers to the first row of your sheet: " + global.fields.join(', ') + ' and add at least one placeholder row of data')
  if (rows.length) return [];
  
  // The below should add the header row automatically, but is not currently working.
  var sheetURL = 'https://spreadsheets.google.com/feeds/cells/'+ constants.spreadsheetID + '/od6/private/full';
  
  var cellXML = function(row, col, value) {
    return '<entry xmlns="http://www.w3.org/2005/Atom"' +
           '       xmlns:gs="http://schemas.google.com/spreadsheets/2006"'+
           '       xmlns:gd="http://schemas.google.com/g/2005" ' + 
           '        gd:etag="\'\'">' +
           '  <id>' + sheetURL + '/R' + row + 'C' + col + '</id>' +
           '  <link rel="edit" type="application/atom+xml"' +
           '        href="' + sheetURL + '/R' + row + 'C' + col + '"/>' +
           '  <gs:cell row="' + row + '" col="' + col + '" inputValue="' + value + '"/>' +
           '</entry>';
  }
  return global.fields.map(function(field, index) {
     return {
        'Content-Type': 'application/atom+xml',
        'GData-Version': '2.1',
        visibility: 'private',
        projection: 'full',
        key: constants.spreadsheetID,
        worksheetId: 'od6',
        alt: 'json',
        body: cellXML(1, index + 1, field),
        cellId: 'R1C' + (index + 1),
    }
  })
}

step3.js

// POST https://spreadsheets.google.com/feeds/list/{key}/{worksheetId}/{visibility}/{projection}
function request(data) {
  var rows = (data[1].feed.entry || []).map(function(row) {
    var ret = {};
    for (var key in row) {
      if (key.indexOf('gsx$') === 0) ret[key.substring(4)] = row[key].$t
    }
    return ret
  })
  var issueNumbersInSheet = rows.map(function(r) {return parseInt(r.number)})
  var headersAreInSheet = rows.length ? true : false;
  
  global.issueToRow = function(i) {
    console.log('c', i.created_at)
    return {
      title: i.title,
      milestone: i.milestone ? i.milestone.title : '',
      state: i.state,
      body: i.body,
      labels: i.labels ? i.labels.map(function(l) {return l.name}).join(',') : '',
      number: i.number,
      assignee: i.assignee ? i.assignee.login : '',
      link: 'https://github.com/' + constants.ownerId + '/' + constants.repoId + '/issues/' + i.number,
      created: i.created_at,
      updated: i.updated_at || '',
      closed: i.closed_at || '',
    }
  }
    
  var issues = [];
  data[0].forEach(function(page) {issues = issues.concat(page)});
  issues = issues.filter(function(i) {return i})
                 .map(global.issueToRow);
  var issueNumbersInGitHub = issues.map(function(i) {return i.number});
  var newIssues = issues
        .filter(function(i, index) {return issueNumbersInGitHub.lastIndexOf(i.number) === index})
        .filter(function(i) {return issueNumbersInSheet.indexOf(i.number) === -1})
  
  global.rowXML = function(row) {
    var ret =
      '<entry xmlns="http://www.w3.org/2005/Atom" ' +
          'xmlns:gd="http://schemas.google.com/g/2005" ' + 
          (row.etag ? ('gd:etag=\'' + row.etag + '\' ') : '') +
          'xmlns:gsx="http://schemas.google.com/spreadsheets/2006/extended">';
    for (var key in row) {
      var val = row[key];
      if (typeof val !== 'string') val = JSON.stringify(val);
      val = (val || '').replace(/</g, '﹤').replace(/>/g, '﹥').replace(/&/g, '﹠');
      ret += '<gsx:' + key + '>' + val + '</gsx:' + key + '>'
    }
    ret += '</entry>';
    return ret;
  }
  
  return newIssues.map(function(issue) {
    console.log(issue);
    return {
      'GData-Version': '2.1',
      visibility: 'private',
      projection: 'full',
      key: constants.spreadsheetID,
      worksheetId: 'od6',
      alt: 'json',
      rowId: 1,
      body: global.rowXML(issue),
      'Content-Type': 'application/atom+xml',
    }
  })
}

step4.js

// PUT https://spreadsheets.google.com/feeds/list/{key}/{worksheetId}/{visibility}/{projection}/{rowId}
function request(data) {
  var rows = (data[1].feed.entry || []).map(function(row) {
    var ret = {id: row.id.$t.substring(row.id.$t.lastIndexOf('/') + 1)};
    ret.etag = row.gd$etag;
    for (var key in row) {
      if (key.indexOf('gsx$') === 0) ret[key.substring(4)] = row[key].$t
    }
    return ret
  })
    
  var issues = [];
  data[0].forEach(function(page) {issues = issues.concat(page)});
  issues = issues.filter(function(i) {return i})
                 .map(global.issueToRow)
      
  issues.forEach(function(i) {
    var row = rows.filter(function(r) {return parseInt(r.number) === i.number})[0]
    if (!row) return;
    row.isChanged = false;
    global.fields.forEach(function(field) {
      if (row[field] !== i[field]) row.isChanged = true;
      row[field] = i[field];
    })
  })
  
  rows = rows.filter(function(r) {return r.isChanged})
  
  return rows.map(function(row) {
    console.log('r.created', row.created);
    return {
      'GData-Version': '2.1',
      visibility: 'private',
      projection: 'full',
      key: constants.spreadsheetID,
      worksheetId: 'od6',
      alt: 'json',
      rowId: row.id,
      body: global.rowXML(row),
      'Content-Type': 'application/atom+xml',
    }
  })
}