How to run page speeds insights within Google Sheets
I recently wrote a blog post about fast websites and used a Google Sheet to check the page speed automatically.
You can get a copy of the sheet here
- Accessing Google Apps Script:In your new Google Sheets document, click on
Extensions -> Apps Script. This will open up the Google Apps Script editor where you can run the code. - Update the API Key:Replace the placeholder in the line
var key = 'AIzaSyDozgv1BOcIN2ihBjrITBEp4_Kcnb6VkaQ';with your actual PageSpeed Insights API key. If you don't have one, you can get it by following these instructions: https://developers.google.com/speed/docs/insights/v5/get-started - Run the Functions:Back in your Google Sheets, refresh the page. You should see a new menu option that matches the name of your script. Click on this and you should see options to run either
getMobilePageSpeedorgetDesktopPageSpeed. Running these will start fetching the PageSpeed data. - Authorizing the Script:The first time you run the script, you will be asked to authorize it. This is because the script requires permission to read your Google Sheet data and to make network requests to the PageSpeed API. Simply follow the instructions on screen to authorize it.If you see a screen that says “This app isn't verified”, don't worry. This just means that you (the developer) haven't submitted the script for review by Google so it can be used by others. Since you are the developer and user, you can click on
Advancedand thenGo to [Your Project Name] (unsafe)to proceed. - Understanding the Results:The scripts will write the mobile scores to column 2, and the desktop scores to column 3. If an error occurs during fetching data, it tries again for these URLs.
By following these steps, you should be able to use Google Apps Script to automatically fetch PageSpeed data for a list of URLs.
There is a more complex version of Google Sheets fetching page speed insights on Moz I like mine though as its simple. If you are doing page speeds in bulk, the paid version of screaming frog can do it too.
Ben has a BEng (Hons) in Computer Science and 20 years of experience in online marketing, specialising in SEO, lead generation and affiliate marketing. After spending over a decade as an igaming affiliate, he has decided to concentrate on GA4 training and SEO Audits.
