I'm currently exploring different integrations that can be done between Google App Script and Git. This is part of a bigger attempt to integrate Git data into my project management system. I'll post more on that later. GitHub supports a number of very nice sub-systems. One of them is the GitHub Issues ticket tracking system. It's fairly robust and comes with GitHub - which means it's highly integrated out-of-the-box. Integration with Google's App Script is done via the fetchURL command and JSON object manipulation. After a good bit of experimentation, I've settled on querying GitHub Issues with an "on open spreadsheet event" to avoid issues with Google's quota system. Essentially, Google prevents you from calling the fetchURL command more than X times (see the quota limits ) per day. That's not great if you have people actively working and changing data in a spreadsheet. Some of my other App Script routines are running thousands of times per d
I'm currently exploring different integrations that can be done between Google App Script and Git. This is part of a bigger attempt to integrate Git data into my project management system. I'll post more on that later.
GitHub supports a number of very nice sub-systems. One of them is the GitHub Issues ticket tracking system. It's fairly robust and comes with GitHub - which means it's highly integrated out-of-the-box. Integration with Google's App Script is done via the fetchURL command and JSON object manipulation.
After a good bit of experimentation, I've settled on querying GitHub Issues with an "on open spreadsheet event" to avoid issues with Google's quota system. Essentially, Google prevents you from calling the fetchURL command more than X times (see the quota limits) per day. That's not great if you have people actively working and changing data in a spreadsheet. Some of my other App Script routines are running thousands of times per day and we're just getting started.
To make the event system really work requires a caching sheet in Google Sheets... but it's relatively simple to grab statistics from the cached values and push them to the rest of the system using VLOOKUP or other custom functions.
NOTE: You'll need to create a GitHub access token (instructions here). Change the OWNER and REPO to your particular values.
My integration code is:
var ghToken = "MY TOKEN";
var api_str = "https://api.github.com";
var apiRepo_str = api_str + "/repos/OWNER/REPO/";
function fxGitIssues_List(sheetName = "Git", state = "all") {
var options = {
"headers": {
"authorization": "token " + ghToken
},
"muteHttpExceptions": true
};
//connect to GitHub API
var response = UrlFetchApp.fetch(apiRepo_str + "issues?state=" + state, options);
//get the context from the response
var context = response.getContentText();
//convert to a JSON object
var jObj = JSON.parse(context);
//array to hold the counts for each label
var aCounts = [];
//for each element in the JSON object
jObj.forEach(function(data) {
//for each element in the label array
data.labels.forEach(function(lbl) {
//see if we can find an existing element with the same label name
let found = aCounts.find(o => o.name === lbl.name);
//if we did not get undefined, the aCounts array has this object so we just increment the counters
if (typeof found !== 'undefined') {
if (data.state == "open") found.open++
else found.closed++
}
//if we did get undefined, the aCounts array does not have this object yet so we have to create it
else {
if (data.state == "open") aCounts.push({
name: lbl.name,
open: 1,
closed: 0
});
else aCounts.push({
name: lbl.name,
open: 0,
closed: 1
});
}
})
})
//sort the array so it's nice and neat
aCounts.sort(function(a, b) {
if (a.name.toUpperCase < b.name.toUpperCase) {
return -1;
} else if (b.name.toUpperCase > a.name.toUpperCase) {
return 1;
} else return 0;
})
//now copy the array into a Sheets range so that we can use standard VLOOKUP routines to determine the open/closed count per item
//NOTE: This assumes the Sheet that's being used is strictly for Git integration
var sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
//get the range for the first three columns, rows 1 - length+1 (we want to have a header so +1)
var range = sheet.getRange("A1:C" + aCounts.length + 1);
//get a 2-dimensional array to fill the values (note: it's easier than just declaring a js "jagged" array plus it includes the current cell contents if needed)
var vals = range.getValues();
//set the headers
vals[0][0] = "Label";
vals[0][1] = "Open";
vals[0][2] = "Closed";
//for each element in the aCounts array, fill the val 2-dim array
for (var row = 0; row < aCounts.length; row++) {
vals[row + 1][0] = aCounts[row].name;
vals[row + 1][1] = aCounts[row].open;
vals[row + 1][2] = aCounts[row].closed;
}
//set the values into the spreadsheet
range.setValues(vals);
}
and produces, in a Git sheet in my active workbook (make sure the sheet exists before using the code):
You can just write a wrapper function in your "on open" trigger (directions here) to call this every time someone opens the spreadsheet.
I can now use the Git cached data in my other sheets via a VLOOKUP and return, for instance, the number of open bug tickets. My next step is to dive deeper into the integration and get better ecosystem integration going. In the meantime, I hope this helps someone.
Comments