December 14, 2019

How To: Save your Google PageSpeed Insights metrics in Google Sheets

Calculating page speed performance metrics of individual sites can help you to understand how efficiently your site is running and how your visitor is experiencing the speed.

Given that Google uses the speed of a site (frequently measured by and referred to as Google PageSpeed Insights) as one of its rank affecting signals, it's important to have that insight so you can see where your site needs improvement.

In this article, we'll show you how to create a script that periodically performs a Google PageSpeed test on your website, and automatically saves the results in a Google Sheet. This gives you the ability to monitor the performance of your website over time, and see if traffic or server load affects your site's speed and user experience.

Part 1: Collected data

Before we dive into building the script, let me first go over the things that it will store in your Sheet.

Obviously, we'll add a date & timestamp to the sheet. But apart from that, we'll be storing the following data, both for desktop and mobile:

  • The overall PageSpeed score
  • First Contentful Paint (FCP): First Contentful Paint marks the time at which the first text or image is painted.
  • Speed Index: Speed Index shows how quickly the contents of a page are visibly populated.
  • Time to Interactive (TTI): Time to interactive is the amount of time it takes for the page to become fully interactive, which is when a user is able to interact with your website.
  • First Meaningful Paint (FMP): First Meaningful Paint measures when the primary content of a page is visible.
  • First CPU Idle: First CPU Idle marks the first time at which the page's main thread is quiet enough to handle input.
  • Max Potential First Input Delay: The maximum potential First Input Delay that your users could experience is the duration, in milliseconds, of the longest task.
  • Time to First Byte (TTFB): identifies the time at which your server sends a response.

It's important to not stare yourself blind on Google's PageSpeed Insights score alone, as you can optimize your website to death (and reach a perfect score), but destroy the user's experience.

That's why I find it very important to also take the other metrics into account (like FCP, TTI, and TTFB). And this script will gather all this data on a regular (fully automated) schedule.

Part 2: adding the script to your sheet

Prepare your Sheet

First off, you'll need to create a new sheet. While you're at it, rename the default sheet name ('sheet1') to 'results' (or anything else that's meaningful enough for you).

Add the following titles to your first row:

  • Date
  • Time
  • Score
  • First Contentful Paint
  • Speed Index
  • Time to Interactive
  • First Meaningful Paint
  • First CPU Idle
  • Max Potential First Input Delay
  • Time To First Byte
  • Score
  • First Contentful Paint
  • Speed Index
  • Time to Interactive
  • First Meaningful Paint
  • First CPU Idle
  • Max Potential First Input Delay
  • Time To First Byte

The first set of Score -> TTFB will be filled up with Desktop data, the second set will be the Mobile data. So you can add another row on top, or add some coloring to highlight the difference.

Your top rows should look something like this when you're done.

Get an API key

Secondly, we'll need to go and grab an API key to use in our script:

  • Copy the API key and save it temporarily in Notepad, we'll use it later.
Create an API key in the dropdown menu
Open the dropdown to create an API key

On a side note, if you want to add more security to your API key, you can restrict it to HTTP traffic and only allow it to be used with the PageSpeed API.

For reference, here's the official Google Guide on how to acquire an API key.

Add the script

Next, head back to your Sheet and select "Tools"->"Script Editor" from the menu. This will open a new tab, paste the following code in there:

Adapt the first 2 rows and insert your API key and website URL. Also, doublecheck if your sheet name is 'results'. If not, change the 'results' on the 7th row to the name of your sheet.

var pageSpeedApiKey = 'insert you api key here';
var pageSpeedMonitorUrl = 'insert your website's URL here'; 
function monitor() {
  var desktop = callPageSpeed('desktop');
  var mobile = callPageSpeed('mobile');
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName('results'); //doublecheck your sheet's name!
  sheet.appendRow([
                   Utilities.formatDate(new Date(), 'GMT+1', 'yyyy-MM-dd'),
                   Utilities.formatDate(new Date(), 'GMT+1', 'hh:mm:ss'),
                   desktop['lighthouseResult']['categories']['performance']['score'] * 100,
                   desktop['lighthouseResult']['audits']['metrics']['details']['items'][0]['firstContentfulPaint']/1000 +" s",
                   desktop['lighthouseResult']['audits']['metrics']['details']['items'][0]['speedIndex']/1000 +" s",
                   desktop['lighthouseResult']['audits']['metrics']['details']['items'][0]['interactive']/1000 +" s",
                   desktop['lighthouseResult']['audits']['metrics']['details']['items'][0]['firstMeaningfulPaint']/1000 +" s",
                   desktop['lighthouseResult']['audits']['metrics']['details']['items'][0]['firstCPUIdle']/1000 +" s",
                   desktop['lighthouseResult']['audits']['metrics']['details']['items'][0]['estimatedInputLatency'] +  " ms",
                   desktop['lighthouseResult']['audits']['time-to-first-byte']['numericValue'].toFixed(0) + " ms",
                   mobile['lighthouseResult']['categories']['performance']['score'] * 100,
                   mobile['lighthouseResult']['audits']['metrics']['details']['items'][0]['firstContentfulPaint']/1000 +" s",
                   mobile['lighthouseResult']['audits']['metrics']['details']['items'][0]['speedIndex']/1000 +" s",
                   mobile['lighthouseResult']['audits']['metrics']['details']['items'][0]['interactive']/1000 +" s",
                   mobile['lighthouseResult']['audits']['metrics']['details']['items'][0]['firstMeaningfulPaint']/1000 +" s",
                   mobile['lighthouseResult']['audits']['metrics']['details']['items'][0]['firstCPUIdle']/1000 +" s",
                   mobile['lighthouseResult']['audits']['metrics']['details']['items'][0]['estimatedInputLatency'] +  " ms",
                   mobile['lighthouseResult']['audits']['time-to-first-byte']['numericValue'].toFixed(0) + " ms"
                  ]);
}

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

Once you have entered and adapted the code, click on the play icon on top. And make sure the dropdown has "monitor" selected.

Click the play icon to run the script.
Make sure "monitor" is the selected function to run.

If this is the first time you're running the script, Google will show you a pop-up to allow the script to run on your account. This is all good and you should give access to the script.

Once access has been given, the script will run for a few seconds and push the data in your Google Sheet.

The data should populate on the next available row in your Google Sheet.
If all goes well, it should look something like this!

Part 3: scheduling the script to run every 4 hours

Now obviously, if you're a bit like me, you're crazy about automation ( let's face it, sometimes it's a good thing to be a tad lazy). Luckily, Google allows you to schedule the execution of scripts on their Sheets.

To schedule a script, click on the clock icon in your script editor. Alternatively, you can go to "Edit" -> "Current project triggers"

Click the clock icon on top of the Code Editor to schedule the trigger.
Click the clock icon to add a project trigger

This will open another tab in your browser. Click on the "+ Add trigger" button in the bottom right-hand corner. This should give you another popup.

Here, we're going to select the following options:

  • Select even source: set this to "Time-driven"
  • Set the type of time-based trigger to "Hour timer". You can also pick a day, week, month... whatever meets your requirements.
  • Select an hour interval at the bottom. Mine is set to "every 4 hours", but you can select the interval that works best for you.
Select the triggers to use with the PageSpeed API.
Schedule the trigger using time-based intervals

Click on "Save" and the trigger should show up in the oversight:

When succesfully added, you'll get an overview of the applied triggers.

That's it!

Now that you've set up the script, we'll be retrieving data from PageSpeed's API every few hours.

What do you do with Google Sheets to automate your life? 

Google Sheets is a formidable tool, what do you use it for?

Leave a Reply

Your email address will not be published. Required fields are marked *

Your Operations Team

Here at WP Operations, we know the ins and out of WordPress and we keep evolving. Our primary focus is helping you put out the fires on your website, and ensure that you can sleep at night. 

Let us manage your daily WP Operations! 
CONTACT US
linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram