Fetches themes from wordpress.org and feeds them into a Google Spreadsheet

query_themes.gs

/*
 * 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;
    };
  })();
}