/**
* Fetching data from githubarchive.org into BigQuery to see what is cool and hot on github
* Author: Ido Green
* Date: 2014-April-01
*
* A post on the subject: http://wp.me/pB1lQ-19i
* More on BQ: https://developers.google.com/bigquery/
*/
//
// Insert menu item
//
function onOpen() {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var menuEntries = [ {name: 'Find Hot PHP Repos (last Week)', functionName: 'runPhpQuery'} ];
sheet.addMenu('Hot On GitHub', menuEntries);
};
function runPhpQuery() {
var lastweek = getLastWeekString();
var sql = "SELECT repository_name, count(repository_name) as pushes, repository_description, repository_url \
FROM [githubarchive:github.timeline] \
WHERE type='PushEvent' \
AND repository_language='PHP' \
AND PARSE_UTC_USEC(created_at) >= PARSE_UTC_USEC('" + lastweek + " 00:00:00') \
GROUP BY repository_name, repository_description, repository_url \
ORDER BY pushes DESC LIMIT 100";
runQuery(sql);
}
//
// Build and run query - You can change it with a new 'sql' variable.
//
function runQuery(sql) {
// Replace this value with your Google Developer project number (It is really a number.
// Don't confuse it with an alphanumeric project id)
var projectNumber = 'todo-fill-it';
if (projectNumber.length < 1) {
var errMsg = "You forgot to set a project number. Please update it on the pervious line.";
Logger.log(errMsg);
Browser.msgBox(errMsg);
return;
}
var sheet = SpreadsheetApp.getActiveSheet();
var queryResults;
// Inserts a Query Job
try {
var queryRequest = BigQuery.newQueryRequest();
queryRequest.setQuery(sql).setTimeoutMs(100000);
queryResults = BigQuery.Jobs.query(queryRequest, projectNumber);
}
catch (err) {
Logger.log(err);
Browser.msgBox(err);
return;
}
// Check on status of the Query Job
while (queryResults.getJobComplete() == false) {
try {
queryResults = BigQuery.Jobs.getQueryResults(projectNumber, queryJob.getJobReference().getJobId());
}
catch (err) {
Logger.log(err);
Browser.msgBox(err);
return;
}
}
// Update the amount of results
var resultCount = queryResults.getTotalRows();
var resultSchema = queryResults.getSchema();
var resultValues = new Array(resultCount);
var tableRows = queryResults.getRows();
// Iterate through query results
for (var i = 0; i < tableRows.length; i++) {
var cols = tableRows[i].getF();
resultValues[i] = new Array(cols.length);
// For each column, add values to the result array
for (var j = 0; j < cols.length; j++) {
resultValues[i][j] = cols[j].getV();
}
}
// Update the Spreadsheet with data from the resultValues array, starting from cell A2
// We have our headers on the first line.
sheet.getRange(2, 1, resultCount, tableRows[0].getF().length).setValues(resultValues);
SpreadsheetApp.getActiveSpreadsheet().toast("We are done with updating the results");
}
function pad(number) {
if ( number < 10 ) {
return '0' + number;
}
return number;
}
// Simple util function to get last week date as a string
function getLastWeekString(){
var today = new Date();
var lastWeek = new Date(today.getFullYear(), today.getMonth(), today.getDate() - 7);
return "" + lastWeek.getFullYear() + "-" + pad(lastWeek.getMonth()+1) + "-" + pad(lastWeek.getDate());
}
Apps Script code to fetch us into google sheet the top repos on github per technology (e.g. PHP, JS) over the last week.
Be the first to comment
You can use [html][/html], [css][/css], [php][/php] and more to embed the code. Urls are automatically hyperlinked. Line breaks and paragraphs are automatically generated.