Automate Google PageSpeed Insights and Core Web Vitals Logging with Apps Script

Hello Robert,

This is just what I've been looking for. A way to automate the collection/export of the Google Analytics PageSpeed report.

I'm fairly new to programming - okay, very new - and I'm running into a problem. Hoping you might be able to assist me.

I've created a Google API key from the console as you instructed. I then launched the Script Editor from within my Google Sheet and I pasted your above script into the editor, replacing everything that shows up in the Script Editor by default.

After adding my API key and my URL to monitor, I ran the script and I receive the following error:

"Request failed for https ://www.googleapis.com/pagespeedonline/v1/runPagespeed?"

I looked up that error message and I found the page (

Pagespeedapi: runpagespeed) - which references v4 instead of v1, but I still get the error if I use v4.

If you have any suggestions on how to resolve this, I would be incredibly grateful.

Best,

Mike

Hi Mike, I think that version 1 of the API was deprecated. It was still working for me but probably because I've had the API key for long enough. I've just updated the script to use v5. The two changes are the v5 in the PageSpeed API URL and the score has moved to lighthouseResult.categories.performance.score and is from 0-1 rather than 0-100. I multiply by 100 above to make it consistent with the old score. Let me know if you're still having any problems after making these changes.

Hi, Robert.

I´m trying to add more features to this script. I tried adding

desktop.lighthouseResult.audits.network-rtt.id,

but it returns

TypeError: Cannot read property "items" from undefined. (line 9, file "Code")Dismiss

could you help me?

Go to PageSpeed Insights, click Docs and then API Reference. There is a Try this API pane where you can make a call to the API and browse the JSON. This is really helpful for figuring out the response structure.

Thank you Robert!

Are you available for freelance? I have a monitoring project.

Terry, sorry, I'm not. Happy to help with any questions I can answer in comments.

Hi Robert, thank you for this! you mentioned it would be easy to monitor for more urls. I'm wondering if you could perhaps show how that is done. I have tried multiple approaches, but the script only gives me results on one url at a time, no matter what I try. Any ideas how to solve this?

Wessel - the callPageSpeed() runs the test for a URL and device type. If you call this multiple times you can pass in different urls and get results back from each. Depending on the number of URLs you'll likely want to refactor how the results are written out as well.

Hi Robert,

I keep getting the error below. Do you know what causes it or how to fix?

This app is blocked

This app tried to access sensitive info in your Google Account. To keep your account safe, Google blocked this access.

Hi Rod - do you use Google Workspace? If so, you could try going to API access in the admin console, find this app and then mark it as trusted (or ask your Workspace Admin to do this if it isn't you). If not then my best guess would be to go to your personal Google Account security settings, revoke any access for the script and then reauthorize.

You're right. A personal account won't do. A Workspace account did.

The API appears to be more lighthouse based data not field data (CRUX), if you compare the two you get very different results. Even the API docs are not clear on what this data represents. But it appears to be the non field data.

Hi Rob,

Absolutely fantastic work btw! This is the clearest and simplest script ive seen written for accomplishing this task.

Extremely helpful. I do have one question however.

I notice that the data being extracted is referring to the originLoadingexperience. Am I right in assuming this is using the value from the origin dataset? Is there anyway to use the lighthouse data set for This URL, and not Origin?

I have checked the data on the PageSpeed Insights page by toggling between them, and it seems that the data thats pulled is the Origin set.

I've tried using just Loadingexperience instead, but it doesnt pull anything across.

Ahh so ive just realised the issue I had was that I was using Loadingexperience and not loadingexperience lol.

FYI, in order to access the live metrics of the API you would need to use the below format:

desktop.lighthouseResult.audits["speed-index"].numericValue

I hope that helps someone as it near enough drove me insane finding that out today.