1

I'm trying to modify a script to create a PageSpeed report that pulls in certain KPI's from Google Pagespeed API. Once the data is received, i want it to place those KPI's next to the URL and so forth.

I want it to look something like this:

+------------------+--------+----------------+
| URL              | KPI 1  |KPI2    |KPI 3  |
+------------------+--------+--------+-------+
| google.com       | 1      |    4   |  7    |
+------------------+--------+--------+-------+
| example.com      | 2      |    5   |  8    |
+------------------+--------+--------+-------+
| m.mail.com       | 6      |    6   |  9    |
+------------------+--------+--------+-------+

Currently it Appends a new row which is not what i want it to do. I want it in the next columns so the data can be overwritten the next time it runs.

This piece of code here seems to be where i cannot figure out how to not append rows. I've been searching around to find a way to pull in the data in the column but unable to get it to work.

function insertPageSpeedData() {
  var urls = getData();
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('InputData');  
  for(var i = 0; i < urls.length; i++){
    pageSpeedData = getPageSpeedData(urls[i]);
    sheet.appendRow([pageSpeedData.id, 
                     parseFloat(pageSpeedData.firstContentfulPaint, 10),
                     parseFloat(pageSpeedData.firstInputDelay, 10), 
                     pageSpeedData.performanceScore,
                     parseFloat(pageSpeedData.firstMeaningfulPaint, 10),
                     parseFloat(pageSpeedData.timetoInteractive, 10)
                    ]);
  }

The next step is trying to find a way to check the URL if it is a mobile site as well. Being able to pull the URLs on the sheets and have it distinguish it from Desktop and in return run API with strategy=mobile and if it's a desktop run strategy=desktop

function getPageSpeedData (url) {
  var url = url;
  var pageSpeedApi = "https://www.googleapis.com/pagespeedonline/v5/runPagespeed?url=" + url + "&strategy=desktop&key=AIzaSyC62fdejPrYL5CKmoVO3rL_ARWMk505&prettyPrint=true";
  var source = UrlFetchApp.fetch(pageSpeedApi, {muteHttpExceptions: true}).getContentText();
  var data = JSON.parse(source);
  var metrics = data.loadingExperience.metrics;
  var lighthouse = data.lighthouseResult.audits;
  var categories = data.lighthouseResult.categories;

Here is the code in its entirety for reference.

var sheetName = "PageSpeedResults";

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Page Speed')
      .addItem('Get Page Speed Data', 'menuItem1')
      .addToUi();
}

function menuItem1() {
  SpreadsheetApp.getUi()
     insertPageSpeedData();
}

function getData () {
  var data = [];
  var sheet = SpreadsheetApp.getActive().getSheetByName("InputData");
  var values = sheet.getDataRange().getValues();
  for(var i = 1; i < values.length; i++){
    data.push(values[i]);
  }

  return data;
}

function insertPageSpeedData() {
  var urls = getData();
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('InputData');  
  for(var i = 0; i < urls.length; i++){
    pageSpeedData = getPageSpeedData(urls[i]);
    sheet.appendRow([pageSpeedData.id, 
                     parseFloat(pageSpeedData.firstContentfulPaint, 10),
                     parseFloat(pageSpeedData.firstInputDelay, 10), 
                     pageSpeedData.performanceScore,
                     parseFloat(pageSpeedData.firstMeaningfulPaint, 10),
                     parseFloat(pageSpeedData.timetoInteractive, 10)
                    ]);
  }

 }

function getPageSpeedData (url) {
  var url = url;
  var pageSpeedApi = "https://www.googleapis.com/pagespeedonline/v5/runPagespeed?url=" + url + "&strategy=desktop&key=AIzaSyC62fdejPrYL5CKmoVO3rL_ARWMk505&prettyPrint=true";
  var source = UrlFetchApp.fetch(pageSpeedApi, {muteHttpExceptions: true}).getContentText();
  var data = JSON.parse(source);
  var metrics = data.loadingExperience.metrics;
  var lighthouse = data.lighthouseResult.audits;
  var categories = data.lighthouseResult.categories;


  return {
    id: data.id,
    firstContentfulPaint: (lighthouse['first-contentful-paint'].displayValue != undefined ? lighthouse['first-contentful-paint'].displayValue : ' '),
    firstInputDelay: (lighthouse['max-potential-fid'].displayValue != undefined ? lighthouse['max-potential-fid'].displayValue : ' '),
    performanceScore: (categories['performance'].score != undefined ? categories['performance'].score : ' '),
    firstMeaningfulPaint: (lighthouse['first-meaningful-paint'].displayValue != undefined ? lighthouse['first-meaningful-paint'].displayValue : ' '),
    timetoInteractive: (lighthouse['interactive'].displayValue != undefined ? lighthouse['interactive'].displayValue : ' '),


  }
}
  • Welcome. The question is too broad as it describes what is goal but not how the script works and were do you get stuck. Please add a brief description of your search/research effort as is suggested on How to Ask. If you didn't do it yet, please read developers.google.com/apps-script/guides/sheets – Rubén Aug 1 at 22:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.