Your Pagespeed can heavily affect your SEO. PageSpeed Insights is a valuable tool for monitoring the performance of your website. With the help of Google Apps Script, you can automate the process of fetching PageSpeed data and populating a Google Sheet with the information. This guide will walk you through the steps to set up your Google Sheet and implement the provided script.

Track your PageSpeed and optimise your SEO:

Prerequisites

• A Google account.
• Basic knowledge of Google Sheets and Google Apps Script.
• Access to the Google PageSpeed Insights API.

Set Up Your Google Sheet

1. Open Google Sheets and create a new spreadsheet or use an existing one.
2. Rename the default sheet to “home” (or any name you prefer).

Enable Google Apps Script

1. In your Google Sheet, go to Extensions > Apps Script. This will open the Google Apps Script editor in a new tab.

Paste the Provided Script

1. In the Google Apps Script editor, remove any existing code and paste the provided script into the editor.

var pageSpeedApiKey = '{ YOUR-API-KEY }'; //Paste your own API Key here
var pageSpeedMonitorUrl = 'https://example.com/'; //Replace with the domain you want to monitor

function monitorHome() {
  var desktop = callPageSpeed('desktop', pageSpeedMonitorUrl);
  var mobile = callPageSpeed('mobile', pageSpeedMonitorUrl);

  var desktopVitals = getVitals(desktop);
  var mobileVitals = getVitals(mobile);

  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName('home');

  sheet.appendRow([
                   Utilities.formatDate(new Date(), 'GMT+2', 'yyyy-MM-dd hh:mm:ss'),
                   desktop.lighthouseResult.categories.performance.score * 100,
                   desktopVitals.lcp == -1 ? ' ' : desktopVitals.lcp,
                   desktopVitals.fid == -1 ? ' ' : desktopVitals.fid,
                   desktopVitals.cls == -1 ? ' ' : desktopVitals.cls,
                   mobile.lighthouseResult.categories.performance.score * 100,
                   mobileVitals.lcp == -1 ? ' ' : mobileVitals.lcp,
                   mobileVitals.fid == -1 ? ' ' : mobileVitals.fid,
                   mobileVitals.cls == -1 ? ' ' : mobileVitals.cls
                  ]);
    
    // much more available, i.e. desktop.pageStats.numberResources
}

function callPageSpeed(strategy, url) {
  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 getVitals(result) {
  var lcp;
  var fid;
  var cls;

  try {
    lcp = result.originLoadingExperience.metrics.LARGEST_CONTENTFUL_PAINT_MS.percentile / 1000;
  } catch {
    lcp = -1;
  }

  try {
    fid = result.originLoadingExperience.metrics.FIRST_INPUT_DELAY_MS.percentile / 1000;
  } catch {
    fid = -1;
  }

  try {
    cls = result.originLoadingExperience.metrics.CUMULATIVE_LAYOUT_SHIFT_SCORE.percentile;
  } catch {
    cls = -1;
  }

  return { "cls":cls, "fid":fid, "lcp":lcp  };
}

Set Your PageSpeed Insights API Key

1. Replace 'YOUR_API_KEY' in the pageSpeedApiKey variable with your actual PageSpeed Insights API key. You can obtain this key from the Google Developer Console.

Customize Page and Metrics

1. You can adjust the pageSpeedMonitorUrl variable to specify the URL of the website you want to monitor. Change it to your website’s URL.
2. Customize the metrics you want to collect by modifying the data appended to the row in the sheet.appendRow function. You can add or remove metrics as needed.

Save the Script

1. Click the floppy disk icon or press Ctrl + S (Windows) or Command + S (Mac) to save your script.

Run the Script Manually

1. In the Apps Script editor, click the play button (▶️) to run the script manually for the first time. This will populate your Google Sheet with the initial data.

Set Up Trigger for Automatic Updates (Optional)

1. If you want to automate the data collection process, you can set up a trigger to run the script at specified intervals.
2. Click the clock icon ⏰ in the toolbar to open the triggers page.
3. Click on “Add Trigger” in the bottom right corner.
4. Configure the trigger settings according to your preference (e.g., time-driven trigger).
5. Save the trigger.

Step 9: Monitor Your PageSpeed Data

1. Your Google Sheet will now be automatically populated with the PageSpeed Insights data at the specified intervals (if you set up a trigger).
2. You can analyze and visualize the data as needed using Google Sheets’ built-in features.

Congratulations! You have successfully set up a Google Sheet to automatically populate PageSpeed Insights data using Google Apps Script. This will help you monitor and track the performance of your website over time.