rbren · July 1, 2021 22:09 · Jul 5, 2018 · Apr 18, 2016
diff --git a/README.md b/README.md
@@ -1,4 +1,7 @@
 # DataFire Dataflow: Sync GitHub issues to spreadsheet
+
+> **Deprecated** - DataFire is now out of beta. You can use the [new version of this project](https://app.datafire.io/projects?baseRepo=https:%2F%2Fgithub.com%2FDataFire-flows%2Fgithub-to-spreadsheet) on DataFire.io, or [view it on GitHub](https://github.com/datafire-repos/github-to-spreadsheet) 
+
 Pulls all new issues from a GitHub repo into a spreadsheet
 
 [View on DataFire](https://datafire.io/dataflow/565df42677f8630521dbbd31)

diff --git a/README.md b/README.md
@@ -0,0 +1,58 @@
+# DataFire Dataflow: Sync GitHub issues to spreadsheet
+Pulls all new issues from a GitHub repo into a spreadsheet
+
+[View on DataFire](https://datafire.io/dataflow/565df42677f8630521dbbd31)
+
+[metadata]: ./ '{"links":[{"connection":"563b9b84ea9ad5f345e97505","operation":{"method":"get","path":"/repos/{ownerId}/{repoId}/issues"}},{"connection":"563b9b85ea9ad5f345e97511","operation":{"method":"get","path":"/list/{key}/{worksheetId}/{visibility}/{projection}"}},{"connection":"563b9b85ea9ad5f345e97511","operation":{"method":"put","path":"/cells/{key}/{worksheetId}/{visibility}/{projection}/{cellId}"}},{"connection":"563b9b85ea9ad5f345e97511","operation":{"method":"post","path":"/list/{key}/{worksheetId}/{visibility}/{projection}"}},{"connection":"563b9b85ea9ad5f345e97511","operation":{"method":"put","path":"/list/{key}/{worksheetId}/{visibility}/{projection}/{rowId}"}}],"constants":[{"name":"spreadsheetID","description":"The ID of your spreadsheet from its URL: docs.google.com/spreadsheets/d/SPREADSHEET_ID","type":"string"},{"name":"ownerId","type":"string","default":"torvalds"},{"name":"repoId","type":"string","default":"linux"}]}'
+[comment]: ./ "End DataFire Preamble"
+
+## 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/](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](#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
diff --git a/step0.js b/step0.js
@@ -0,0 +1,7 @@
+// 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'}
+  })
+}
diff --git a/step1.js b/step1.js
@@ -0,0 +1,11 @@
+// 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'
+  }
+}
diff --git a/step2.js b/step2.js
@@ -0,0 +1,36 @@
+// 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),
+    }
+  })
+}
diff --git a/step3.js b/step3.js
@@ -0,0 +1,69 @@
+// 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',
+    }
+  })
+}
diff --git a/step4.js b/step4.js
@@ -0,0 +1,43 @@
+// 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',
+    }
+  })
+}