How To: Save your Google PageSpeed metrics in Google Sheets

5 min readDec 23, 2019

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 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 PaageSpeed 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.

Get an API key

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

Go to the credentials page: https://console.developers.google.com/apis/credentials
Click on “Create credentials” and select “API key” in the dropdown.
Copy the API key and save it temporarily in Notepad, we’ll use it later.

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.

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.

Scheduling the script

If desired, you can quite easily set the script to run every few hours or days. If you want to find out how you can check out the original post over at wpoperations.com