{"id":4038,"date":"2021-06-21T07:00:00","date_gmt":"2021-06-21T06:00:00","guid":{"rendered":"https:\/\/www.diogonunes.com\/blog\/?p=4038"},"modified":"2021-03-13T15:57:02","modified_gmt":"2021-03-13T15:57:02","slug":"monitor-psi-pagespeed-google-sheets","status":"publish","type":"post","link":"https:\/\/www.diogonunes.com\/blog\/monitor-psi-pagespeed-google-sheets\/","title":{"rendered":"Monitor PageSpeed (v5) with Google Sheets"},"content":{"rendered":"

\"\"<\/a><\/p>\n

The goal is to create a Google Sheet that can track the historical PageSpeed Insights (PSI) perfomance data of one or more pages\/URLs. You will use JavaScript code to call the PageSpeed Insights API and store the result in your sheet.<\/p>\n

<\/p>\n

Run the test + Store the data<\/h3>\n

For each target URL, the script will run the PSI test for both desktop and mobile and record the overall test score (configurable). Since the PSI scores tend to slightly vary between executions, the script runs the test three times (configurable) and store the average.<\/p>\n

    \n
  1. Generate a PageSpeed Insights<\/strong> (PSI) key:\n
      \n
    1. Open the PSI docs<\/a> and click the Get a Key<\/strong> button<\/li>\n
    2. Follow the wizard and copy the generated key<\/li>\n<\/ol>\n<\/li>\n
    3. Create a new Google Sheet<\/a><\/li>\n
    4. Navigate to Tools > Script Editor<\/strong><\/li>\n
    5. From the Google Apps Script<\/strong> editor, give your script a name (e.g. PSI Monitoring<\/code>)<\/li>\n
    6. Navigate to File > Project properties<\/strong>, create a new Script property<\/strong> named PSI_API_KEY<\/code> and paste in your API key<\/li>\n
    7. In Code.gs<\/code>, paste the script below\n
        \n
      1. Don’t forget to find the \/\/ TODO<\/code> comments and configure them as you desire<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n
        \/\/ Adapted from Rick Viscomi (@rick_viscomi)\n\/\/ Originally adapted from https:\/\/ithoughthecamewithyou.com\/post\/automate-google-pagespeed-insights-with-apps-script by Robert Ellison\n\nvar scriptProperties = PropertiesService.getScriptProperties();\nvar pageSpeedApiKey = scriptProperties.getProperty(\"PSI_API_KEY\");\nvar pageSpeedMonitorTitles = [\n    \/\/ TODO: CHANGE THESE\n  \"Website name\",\n  \"More descriptive than long urls\",\n];\nvar pageSpeedMonitorUrls = [\n  \/\/ TODO: CHANGE THESE\n  \"https:\/\/yourdomain.com\",\n  \"https:\/\/another.website\",\n];\n\nfunction monitor() {\n  for (var i = 0; i < pageSpeedMonitorUrls.length; i++) {\n    var url = pageSpeedMonitorUrls[i];\n    var title = pageSpeedMonitorTitles[i];\n    \/\/ Scores tend to fluctuate, specially on STG, so we average them\n    var desktopScore = getAverageScore(url, \"desktop\", 3);\n    var mobileScore = getAverageScore(url, \"mobile\", 3);\n    addRow(\"Sheet1\", title, desktopScore, mobileScore);\n  }\n}\n\nfunction callPageSpeed(url, strategy) {\n  var pageSpeedUrl =\n    \"https:\/\/pagespeedonline.googleapis.com\/pagespeedonline\/v5\/runPagespeed?url=\" + url + \"&key=\" + pageSpeedApiKey + \"&strategy=\" + strategy;\n  var response = UrlFetchApp.fetch(pageSpeedUrl);\n  var json = response.getContentText();\n  return JSON.parse(json);\n}\n\nfunction addRow(sheetName, desc, desktopScore, mobileScore) {\n  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();\n  var sheet = spreadsheet.getSheetByName(sheetName);\n  sheet.appendRow([\n    Utilities.formatDate(new Date(), \"GMT\", \"yyyy-MM-dd\"),\n    desc,\n    desktopScore,\n    mobileScore\n  ]);\n}\n\nfunction extractOverallScore(data) {\n  return data.lighthouseResult.categories.performance.score * 100;\n}\n\nfunction getAverageScore(url, strategy, attempts) {\n  var scores = [];\n  for (var i = 0; i < attempts; i++) {\n    var auditData = callPageSpeed(url, strategy);\n    scores.push(extractOverallScore(auditData));\n  }\n  var totalScore = 0;\n  for (const score of scores) {\n    totalScore += score;\n  }\n  return totalScore \/ scores.length; \/\/ average\n}\n<\/code><\/pre>\n
        The script will store the results in a sheet named “Sheet1” (configurable). If you rename it, make sure you update the script as well.<\/p>\n
        You can test the script by opening the Select function<\/strong> menu, selecting monitor<\/strong>, and clicking the Run<\/strong> button. For your first run, you’ll need to authorize the script to run the API. If all goes well, you can open up your sheet to see the results.<\/p>\n
        Run it on a schedule<\/h3>\n
        Right now, you can run the PSI test with the click of a button. But ideally the test would run every day automatically. Luckily, we can configure triggers to run our script:<\/p>\n
          \n
        1. Return to the Google Apps Script<\/strong> editor<\/li>\n
        2. Navigate to Edit > Current project’s triggers<\/strong><\/li>\n
        3. Configure your trigger \u2013 if you want to run it daily, use the following config:\n