Search code examples
google-apps-scriptgoogle-sheetspagespeedpagespeed-insights

PageSpeed Insights on Google Sheets is running very slowly for multiple URLs

I currently run a daily export on Google Sheets using Google PageSpeed Insights. More URLs have been added recently and instead of taking around 30 seconds per URL (with just one), it is now taking 1 min which exceed the 6 minutes maximum from Google Scripts.

I modified a code to add a loop to include multiple URLs. Is it because I call too many times the function CALLPAGESPEED ? Do you have a simple optimization ?

Here's the code :

var scriptProperties = PropertiesService.getScriptProperties();
var pageSpeedApiKey = scriptProperties.getProperty('PSI_API_KEY');
var pageSpeedMonitorUrls = [
  'https://www.metro.ca/en/online-grocery',
  'https://voila.ca/',
  'https://www.loblaws.ca/',
  'https://www.walmart.ca/en',
  'https://www.amazon.ca/',
  'https://www.grocerygateway.com/store/groceryGateway/en/',
  'https://www.iga.net/en/online_grocery'
];

function monitor() {
  for (var i = 0; i < pageSpeedMonitorUrls.length; i++) {
  var url = pageSpeedMonitorUrls[i];
  var desktop = callPageSpeed(url,'desktop');
  var mobile = callPageSpeed(url,'mobile');
  displaydata(url,desktop,mobile);
  }
}

function callPageSpeed(url,strategy) {
  var pageSpeedUrl = 'https://www.googleapis.com/pagespeedonline/v5/runPagespeed?url=' + url + '&key=' + pageSpeedApiKey + '&strategy=' + strategy;
  var response = UrlFetchApp.fetch(pageSpeedUrl);
  var json = response.getContentText();
  return JSON.parse(json);
}


function displaydata(url,desktop,mobile){
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName('Sheet1');
  sheet.appendRow([
    Utilities.formatDate(new Date(), 'GMT-5', 'yyyy-MM-dd'),
    Utilities.formatDate(new Date(), 'GMT-5', 'HH'),      
    url,
    desktop['lighthouseResult']['categories']['performance']['score'] * 100,
    desktop['lighthouseResult']['audits']['metrics']['details']['items'][0]['firstContentfulPaint']/1000,
    desktop['lighthouseResult']['audits']['metrics']['details']['items'][0]['speedIndex']/1000,
    desktop['lighthouseResult']['audits']['metrics']['details']['items'][0]['interactive']/1000,
    mobile['lighthouseResult']['categories']['performance']['score'] * 100,
    mobile['lighthouseResult']['audits']['metrics']['details']['items'][0]['firstContentfulPaint']/1000,
    mobile['lighthouseResult']['audits']['metrics']['details']['items'][0]['speedIndex']/1000,
    mobile['lighthouseResult']['audits']['metrics']['details']['items'][0]['interactive']/1000
                  ]);
}

Thank you!

Solution

  • You can reduce the execution time of your script by changing the way that the data is added to the spreadsheet.

    The function displayData adds the data by using appendRow and it's included inside the for loop. This makes your script slow.

    One way to improve the performance is to change displayData to build an Array of rows (let call values), then, after the loop, add all the rows at once by using setValues(values)

    You might also try UrlFetchApp.fetchAll instead of UrlFechApp.fetch so with single call to the URL Fetch Service you can get the responses for all the URLs.

    Resources