Skip to content

Instantly share code, notes, and snippets.

@hakilebara
Created April 29, 2019 07:10
Show Gist options
  • Save hakilebara/5ad14c2b7f0e2c32b44ed416cc3e42a0 to your computer and use it in GitHub Desktop.
Save hakilebara/5ad14c2b7f0e2c32b44ed416cc3e42a0 to your computer and use it in GitHub Desktop.
Google spreadsheet script that lists open pull requests on specified repos https://docs.google.com/spreadsheets/d/1sPyN9z9wZMpTNwqCfa6R9QSPZkIW4iQd-H4gZC7ILLk/edit?usp=sharing
// This is the main script function
function myFunction() {
var ss = SpreadsheetApp.getActiveSpreadsheet()
// target repositories
var openedPullRequestsSpreadsheet = ss.getSheetByName('OPEN_PULL_REQUESTS');
// spreadsheet that contains a list of Github repositories
var coreRepositoriesSpreadsheet = ss.getSheetByName('CORE_REPOSITORIES');
// spreadsheet that contains a list of ember-learn Github repositories
var learningRepositoriesSpreadsheet = ss.getSheetByName('LEARNING_TEAM_REPOSITORIES');
// get list of Github repositories from spreadsheet
var learningRepositoriesRange = learningRepositoriesSpreadsheet.getDataRange().getValues();
var learningRepositories = learningRepositoriesRange.reduce(function(acc, repo) {
return acc.concat(repo);
}, []);
var coreRepositoriesRange = coreRepositoriesSpreadsheet.getDataRange().getValues();
var coreRepositories = coreRepositoriesRange.reduce(function(acc, repo) {
return acc.concat(repo);
}, []);
// clear the target spreadsheet
openedPullRequestsSpreadsheet.clear({ contentsOnly: true });
coreRepositories.forEach(function(repo){
var url = 'https://api.github.com/repos/'+repo+'/pulls';
var response = JSON.parse(UrlFetchApp.fetch(url).getContentText());
response.forEach(function(pr){
if (/wip/i.test(pr.title)) { return; }
if (!/doc/i.test(pr.title)) { return; }
appendPullRequest(repo, pr, openedPullRequestsSpreadsheet)
});
});
learningRepositories.forEach(function(repo){
var url = 'https://api.github.com/repos/'+repo+'/pulls';
var response = JSON.parse(UrlFetchApp.fetch(url).getContentText());
response.forEach(function(pr){
if (/dependabot/.test(pr.user.login)) { return; }
if (/wip/i.test(pr.title)) { return; }
appendPullRequest(repo, pr, openedPullRequestsSpreadsheet)
});
});
}
function appendPullRequest(repo, pr, spreadsheet) {
var row = [];
var commentsUrl = pr.comments_url;
// fetch the Pull Request comments
var commentsData = JSON.parse(UrlFetchApp.fetch(commentsUrl).getContentText());
// find date of latest activity on the Pull Request
var lastUpdate = new Date(pr.updated_at);
if (commentsData.length > 0) {
var timeSortedCommentsData = commentsData.sort(function(a, b){
return new Date(b.updated_at) - new Date(a.updated_at);
});
lastUpdate = new Date(timeSortedCommentsData[0].updated_at);
}
row.push(repo);
row.push(pr.title);
row.push('=HYPERLINK("'+ pr.user.html_url +'","'+ pr.user.login +'")');
row.push(lastUpdate);
row.push(pr.html_url);
spreadsheet.appendRow(row);
}
// This updates the graph data
function trackInactivePullRequests() {
var ss = SpreadsheetApp.getActiveSpreadsheet()
var inactivePullRequestsSpreadsheet = ss.getSheetByName('OPENED PULL REQUESTS INACTIVE >= 1 WEEK');
var range = inactivePullRequestsSpreadsheet.getRange("A3:A3");
var cell = range.getCell(1, 1);
var row = [];
row.push(new Date());
row.push(cell.getValue());
inactivePullRequestsSpreadsheet.appendRow(row);
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment