Skip to content

Instantly share code, notes, and snippets.

@jancbeck jancbeck/query_themes.gs
Last active Jun 4, 2016

Embed
What would you like to do?
Fetches themes from wordpress.org and feeds them into a Google Spreadsheet
/*
* Step 1: Execute `buildThemeList` to build the basic list of all themes.
* Step 2: Enter your Page Speed Insights API Key
* Step 3: Create minutely trigger for `getPageSpeeds`.
*/
var apiKey = 'YOUR API KEY';
var deleteOldSheets = true;
/*
* Inserts a new worksheet, sets first row as frozen, deletes all other existing sheets and builds the theme list.
* Building the list of all themes first helps avoid hitting the maximum execution time.
*/
function buildThemeList(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.insertSheet( new Date().toISOString(), 0).setFrozenRows(1);
if (deleteOldSheets){
// delete all old sheets
ss.getSheets().forEach(function(sheet, i){
if(i !== 0 ){ // skip the current one
ss.deleteSheet(sheet);
}
});
}
var themes = _getWpThemes();
_insertThemeRows(themes, sheet);
}
/*
* Pulls the list of all themes from the wordpress.org API
*/
function _getWpThemes() {
var queryString = '?action=query_themes'
+ '&request[per_page]=99999'
+ '&request[browse]=updated'
+ '&request[fields][description]=0'
+ '&request[fields][screenshot_url]=0'
+ '&request[fields][homepage]=0'
+ '&request[fields][active_installs]=1'
+ '&request[fields][template]=0'
+ '&request[fields][last_updated]=1';
var result = _fetch('https://api.wordpress.org/themes/info/1.1/' + queryString);
if (result){
return result.themes;
}
}
/*
* Helper function to get JSON data from REST APIs
*/
function _fetch(url){
var result = UrlFetchApp.fetch(url, {muteHttpExceptions: true});
if( 200 != result.getResponseCode()){
return false;
}
return JSON.parse(result.getContentText());
}
/*
* Inserts given list of themes into worksheet
*/
function _insertThemeRows(themes, sheet){
var header = Object.keys(themes[0]);
// setValues expects a 2-dimensional array
themes = themes.map(function(theme){
var values = Object.keys(theme).map(function(key){return theme[key]});
return values;
});
// add header to beginning of theme list
themes.unshift( header );
var range = sheet.getRange(1,1,themes.length,header.length)
.setNumberFormat("@") // disable autoformatting
.setValues(themes);
}
/*
* Gets page speed scores for each theme in list
*/
function getPageSpeeds(){
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var range = sheet.getDataRange();
var values = range.getValues();
var limit = 30;
var requests = 0;
for (var i = 0; i < values.length; i++) {
// stop execution if we are hitting the API limit
if( requests >= limit ) {
return;
}
// skip header line
if( i == 0 ) {
continue;
}
// Don't process existing results
if( ! values[i][9] ){
var scores = _getPageSpeedScores(values[i][3]); // URL stored in column 4
if( scores ){
_insertScores(sheet, scores, i)
}
requests++;
}
}
}
/*
* Gets score for URL and returns flattened object
*/
function _getPageSpeedScores(url) {
var mobileResults = _getPageSpeedResultsForURL(url, 'mobile');
var desktopResults = _getPageSpeedResultsForURL(url, 'desktop');
if( mobileResults && desktopResults){
var flattenResults = _flatten({ mobile: mobileResults, desktop: desktopResults }, {});
return Object.assign(_getSpeedResultKeys(), flattenResults);
}
return false
}
/*
* Builds query string for Page Speed API
*/
function _getPageSpeedResultsForURL(url, strategy){
var queryString = '?url='+ encodeURIComponent(url)
+ '&filter_third_party_resources=true'
+ '&screenshot=false'
+ '&strategy='+ strategy
+ '&fields=pageStats%2CruleGroups'
+ '&key=' + apiKey;
var result = _fetch('https://www.googleapis.com/pagespeedonline/v2/runPagespeed' + queryString);
if (result){
return result;
}
return {};
}
/*
* Inserts data into sheet row
*/
function _insertScores(sheet, scores, row){
var headerRange = sheet.getRange(1, 10, 1, Object.keys(scores).length);
_insertPageSpeedHeader(headerRange, scores);
var valueRange = sheet.getRange(row+1, 10, 1, Object.keys(scores).length);
_insertPageSpeedValues(valueRange, scores);
}
/*
* Inserts values in correct formatting
*/
function _insertPageSpeedValues(valueRange, scores){
var scoreValues = Object.keys(scores).map(function(key){return scores[key]});
valueRange.setNumberFormat("@").setValues([scoreValues])
}
/*
* Uses the scores object to determine header fields
*/
function _insertPageSpeedHeader(headerRange, scores){
if ( headerRange.isBlank() ){
headerRange.setValues( [ Object.keys(scores) ] );
}
}
/*
* Pads results object with "N/A" value
*/
function _getSpeedResultKeys(){
var speedResultKeys = ['mobile.ruleGroups.SPEED.score','mobile.ruleGroups.USABILITY.score','mobile.pageStats.numberResources','mobile.pageStats.numberHosts','mobile.pageStats.totalRequestBytes','mobile.pageStats.numberStaticResources','mobile.pageStats.htmlResponseBytes','mobile.pageStats.cssResponseBytes','mobile.pageStats.imageResponseBytes','mobile.pageStats.javascriptResponseBytes','mobile.pageStats.otherResponseBytes','mobile.pageStats.numberJsResources','mobile.pageStats.numberCssResources','desktop.ruleGroups.SPEED.score','desktop.pageStats.numberResources','desktop.pageStats.numberHosts','desktop.pageStats.totalRequestBytes','desktop.pageStats.numberStaticResources','desktop.pageStats.htmlResponseBytes','desktop.pageStats.cssResponseBytes','desktop.pageStats.imageResponseBytes','desktop.pageStats.javascriptResponseBytes','desktop.pageStats.otherResponseBytes','desktop.pageStats.numberJsResources','desktop.pageStats.numberCssResources']
return speedResultKeys.reduce(function(o, v, i) {
o[v] = 'N/A';
return o;
}, {});
}
/*
* Flattens given object
*/
function _flatten(x, result, prefix) {
if(typeof x === 'object') {
for (var k in x) {
_flatten(x[k], result, prefix ? prefix + '.' + k : k)
}
} else {
result[prefix] = x
}
return result
}
/*
* Object.assign Polyfill
*/
if (typeof Object.assign != 'function') {
(function () {
Object.assign = function (target) {
'use strict';
if (target === undefined || target === null) {
throw new TypeError('Cannot convert undefined or null to object');
}
var output = Object(target);
for (var index = 1; index < arguments.length; index++) {
var source = arguments[index];
if (source !== undefined && source !== null) {
for (var nextKey in source) {
if (source.hasOwnProperty(nextKey)) {
output[nextKey] = source[nextKey];
}
}
}
}
return output;
};
})();
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.