Important Update (AUGUST 2020)
This post is pretty old, and unfortunately doesn’t work. I recently found a new Google Sheet for tracking the pagespeed performance scores and logging them in a separate sheet, so I suggest you to check this one:
https://www.upbuild.io/blog/automated-pagespeed-insights-update/
Important Update (September 2018)
The Google Apps Script that I’ve written on the Google Sheet that this blog post is about is using Google Pagespeed API v2. The current API version is v4, and v2 was depreciated on June 30th, 2018 (thanks Laurean for the heads up).
I updated the code with API v4 on my own copy of the spreadsheet. You can make your own copy from here. You can see the new version of the script here. I updated the links on the post with the new ones as well.
I also wanted to add the mobile-friendly test results but it turned out that Google Search Console’s API quota restrictions were too tight, returning error almost all the time. So I commented out that part of the code for the time being.
I didn’t have the time to update this blog post yet, I wanted to add this note until I get the post updated–hopefully soon.
Inbound marketers & SEO professionals all know that Google, as a search engine, is paying the most attention to mobile search experience in 2015. Google announced its mobile-friendly update 2 months before the rollout, and updated its guide to mobile-friendly sites into a fancy looking responsive one.
Google says every single page’s mobile-friendliness is re-evaluated on each crawl.
Quoting from the FAQ post about the mobile-friendly update on the Webmaster Central Blog:
We determine whether a page is mobile-friendly every time it’s crawled and indexed — you don’t have to wait for another update.
Google offers Mobile Usability Report on the Search Console (new name of Google Webmaster Tools) for checking mobile usability for webmasters and Mobile-Friendly Test for anyone who wants to test a few pages. These tools are pretty useful for tracking mobile usability issues, but tracking multiple pages every day can waste lots of time. If your team is constantly pushing updates on your mobile website, it becomes even more critical to track your mobile website’s mobile-friendliness.
Besides being mobile-friendly on mobile usability perspective, having a fast loading site has always been a critical factor for SEO. Tracking both mobile usability and pagespeed data for your important pages (landing pages, pages that drive traffic to your landing pages: like blog posts, side projects, etc.)
I created a Google Sheet for tracking Mobile Pagespeed, Mobile Usability, and Desktop Pagespeed values for a list of pages by accessing Pagespeed Insights API. And I wanted to share this simple yet handy Google Sheet with everyone.
You can start using the Google Sheet by following the 5 easy steps below. If you know how to create a server key for public access to Pagespeed Insights API, you can skip the steps and use the Google Sheet that I named Google Pagespeed & Mobile Usability Checker right away. Just follow the steps on the first sheet, it’s self-explanatory.
1. Create a Project on Google Developers Console
It’s quite easy to create a project on Google Developers Console:
Sign in to Google Developers Console with your Google account.Create a Project with a cool name.
2. Enable Pagespeed Insights API
Having our project created, the next step is to enable Pagespeed Insights on API:
In the sidebar on the left, expand APIs & auth. Next, click APIs.Select Pagespeed Insights API from the list of APIs, then select the Enable API button for the API.
3. Create a Public Key to Access API
Having Pagespeed Insights API enabled, the next step is to invoke access to the API. The API supports two types of credentials: OAuth & Public API Acess. The Google Sheet I created needs to have Public API Access.
In the sidebar on the left, expand APIs & auth. Next, click Credentials.Create a public server key for API access, and copy the generated API key for using in the next step.Note: Do not share your API key with anyone. Sharing your API key gives access to anyone who has it, meaning that you are sharing your free quota limit for the API.
4. Create Your Google Sheet
Having created your API key, the next step is to create your Google Sheet and access to your API through the Google Sheet.
Open the public Google Sheet that I named Google Pagespeed & Mobile Usability Checker, and make a copy of it via “File > Make a Copy”. Give it a name you like.Open your copy of the Google Sheet and open Script Editor via “Tools > Script Editor”.On the Script Editor, change your API key only in the line below with “YOUR_API_KEY” in the script. Save the script, close the Script Editor.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| var key = "YOUR_API_KEY"; |
If you know how to use Google Apps Scripts, you can just take the script code here on GitHub Gist.
5. Get Your Data from the Google Sheet
Voila! Your copy of the Google Sheet is ready for use. You can use checkAll function on the sheet named Checker for getting real-time Mobile Pagespeed, Mobile Usability, and Desktop Pagespeed data for your URLs.
You can easily reuse the Google Sheet by just changing the URLs on column A and applying the checkAll function to the URLs. As I mentioned earlier, Pagespeed Insights API has a quota limit for access. The API responds well up to about 50 URLs. If you have more than 50 URLs to check simultaneously, you may want to apply the checkAll function to first 50 URLs, get the results to another sheet (by copy-pasting as values only), and repeat for the next 50 URLs.
If you experience any difficulties or have questions, please don’t hesitate to ask.
Hi Cagri,
Could you please provide the updated script. Page speed data and sheet data is not match.
https://pagespeedplus.com/ will scan your entire site using the sitemap so new urls are automatically tested.
Hello, can you short tell my why i get no Data in Rows: Mobile Speed: FCP (seconds) Mobile Speed: DCL (seconds) Desktop Speed: FCP (seconds) Desktop Speed: DCL (seconds)
thank you
Hi Cagri,
Thanks so much, real time saver. Though unfortunately I’m also having the same issue that Stacie and Ondřej highlighted. Getting very different numbers than using the page directly, which kind of makes it unusable at the moment.
I’d be very interested in hearing about coming updates, this one is a life saver.
Hi Cagri,
I have the same problem like Stacie. The numbers are absolutely different.
Hi Cagri,
I’m just now implementing this script and it appears to be working well! One question I have is where are these numbers coming from? I’m comparing results from https://developers.google.com/speed/pagespeed/insights/?hl=en&utm_source=wmx&utm_campaign=wmx_otherlinks and the numbers aren’t the same.
Thanks!
Hi Stacie,
Glad to hear that it worked for you. I guess that’s because of the new reporting features of Pagespeed Insights. I’m planning to upgrade my script and the Google Sheet to the newest version of Pagespeed API, so that -hopefully- the numbers will match.
Let me take a look and keep you updated over here :)
Looking forward to that update Çağrı :)
Hi Cagri
Thank you very much for this. I am getting “Please enter your API key to the script” in the google doc cells.
Added Speed Page insight Api key in both var key = and if (key == .
Hi Novak,
You should only replace it on the line with “var key=”. I updated the blog post stating that explicitly.
Hope this helps. If you’d have further questions, please don’t hesitate to ask.
The exact error is “Internal error executing the custom function.”
Really cool Cagri. Thanks a lot for sharing. I’m retrieving the results for about 20 domains in my sheet. Only 10 of them work. 10 of them always get “ERROR” because of the 30 seconds limit in Google Apps Script for Custom Functions.
Do you have any smart trick to circumvent this? Thanks again!
Hi there,
Maybe you can try to overcome this problem by increasing the sleep time at the end of the script to a bigger value like
Utilities.sleep(5000);or even more.I hope this helps.
Hi Cagri,
Great script, thanks!
Is it possible to make the script run automatically each day in google spreadsheets withouth actually opening the sheet yourself? I want the results to be imported in our daily main dashboard.
Best,
Mark
Hi Mark,
I’m glad you liked it!
I guess maybe you can add the current date as a parameter at the end of each URL, e.g. ‘?date=today()’ so that the script gets triggered on each new day.
I’m not sure if it works but maybe you can try something like this.
Cheers,
Thank you sooo much! You don’t know how much time you are saving me!! I appreciate your generosity in sharing this valuable tool. This is the best one out there like this so far.
Wow, thank you so much! It’s been years I’ve written this blog post and I’m very happy it still helps to solve problems!
Hi Cagri it seems some error on the script. Can you help me?
Error details :Request failed for https://www.googleapis.com/pagespeedonline/v2/runPagespeed?
Message details of above error
Request failed for https://www.googleapis.com/pagespeedonline/v2/runPagespeed?url=undefined&strategy=mobile&key=My API key is listed here returned code 400. Truncated server response: { “error”: { “errors”: [ { “domain”: “global”, “reason”: “invalidParameter”, “message”: “Invalid value ‘undefined’. Values must m… (use muteHttpExceptions option to examine full response) (line 18, file “Code”)
I have replaced my API key on the above message. Hope you can help me.
Hi there,
This is probably because of the fact that Google deprecated API v2, as I stated out at the beginning of the post, with title “Important Update (June 2018)”. You can see the details there.
I actually have an updated version of the Google Sheet, but I didn’t have the time to update this post. You can copy your own version of the new Google Sheet from the links I provided on the update note.
Hope this helps.
This is fantastic! Thank you very much for the easy to follow instructions… this is what i’ve been looking for!
Thanks Laurean, it’s good to hear that the tool helped you solve your problems :)
Thank you so much for the reply,
I’ve already solved my problem; the url was not correctly taking the value of the cell so the request had no value in that parameter.
Regards
Great to hear you figured it out!
Cheers,
Hi,
I’m having an error while checking different sites.
When I check the first site all went good, but when I open a second sheet and try to check a different site (different domain) the script just give an error, it returns error 400.
I don’t know if the api key is limited to 1 domain.
Hi there,
I tried your case with checking pages from different domains, and my Google Sheet worked without any problems. You can find the screenshot from my own copy of the Google Sheet here.
I might take a look at your Google Sheet to better understand your problem, if you can give access to my email: cagri.sarigoz@gmail.com
Hi,
Hi RenKai,
I guess your comment was longer but right now all I can see is “Hi,” 🤔
Hi Cagri, great script, works a dream.
I have modified the script slightly – so that it can return more information from the page speed insights API – for example:
var numberResources = contentMobile[“pageStats”][“numberResources”];
and then pushing this into the same array that is already there by:
array.push([speedScoreMobile, usabilityScoreMobile, speedScoreDesktop, numberResources])
This data is then added to an additional column in the sheet that I am using.
However, when I try to extract a certain value (see screenshot) – SizeTapTargetsAppropriately.urlBlocks.urls.result.args.value (first occurence), nothing is returned into the sheet.
Would you be able to help?
Thanks,
https://uploads.disquscdn.com/images/e1be409874ed16edb36c3f5b8ae2225d38a4cb4212fba4f485bb9341045ee9ca.png
Hi Tom,
I’m glad that you made use of the script for yourself :)
I can take a look at your Google Sheet if you can give access to my email: cagri.sarigoz@gmail.com
Cheers,
Hi Cagri,
Have shared sheet access with you.
Thanks for taking the time to look into this.
:)
Hi Tom,
Thanks for sharing. I will take a look and get back to you -hopefully- with a solution 🤞
Hi Tom,
I guess that’s because there are more than one “url” value in “urlBlocks”.
Maybe you can run a for loop in “urlBlocks” and get the necessary values from each “url” and add them together with new lines.
By the way, currently your code tries to get “url” as the value, not result.args.value under “url”:
var taptargeturls = contentMobile["formattedResults"]["ruleResults"]["SizeTapTargetsAppropriately"]["urlBlocks"]["url"];Hi Cagri,
Thanks for this – when I tried to return the result.args.value previously, the result was the same (null). Would you possibly have a small piece of code that would execute that loop? I’m fairly new to Google Scripts :)
Really appreciate the help,
Cagri, this is a great solution, thanks!
I was wondering if there’s any way of doing a time stamp and automating the check every 7 days, for example, to track the progress of page speed across time. I would like to track some pages of my website against the competitor’s pages periodically and keep each historic result so I can make a time progress chart.
Hi Victoria, I’m happy to hear that you found it useful.
I had a similar request on Twitter, which I think makes total sense. I haven’t got the time to come up with a solution, but I think it can be done by using something like a Zapier integration with Google Sheets.
Let’s keep each other updated on that :)
I’m getting the following message ” Please enter your API key to the script” eventhough I’ve entered the key as guided.
Hi Sameer,
Have you saved the script after entering your API key?
If you saved your script but still got the error, you may try to delete the cell that contains the checkAll function and rewrite the function in that cell to make sure that the function is called after you changed the script.
If you’d still receive an error after doing these, I can take a look at your Google Sheet if you can give access to my email: cagri.sarigoz@gmail.com
Hmm… I get an error when I try to check our own URL with your script. Any other URL works fine, but the URL for our own service fails. I get #ERROR in the cell. Any idea what might cause this or how I can check what can go wrong?
Hi Frank,
When you hover your cursor to the cell with #ERROR in it, what error message do you get in the info box?
I can help further if you can give acces for your Google Sheet to my email: cagri.sarigoz@gmail.com
Hi Cagri, I get the error “Internal error executing the custom function.”. Pretty descriptive huh ;-) I just gave you access so you can take a look!
Hi Frank,
I checked your spreadsheet, I think the error was happening because of HTTP -> HTTPS redirection of your URL.
When I changed your site’s URL with the HTTPS version in the spreadsheet cell, the results came up :)
Nice work, we developed a CMS which optimizes all pages automaticly and we get pagespeed 100/100 for Desktop and Mobile. Have a look if you like: https://www.bluecreator-cms.de/
Google Pagespeed Insight Test of our page https://t.co/TixSAlafBJ
Hi André, thanks for your appreciation of my work. I wish you success with your venture.
By the way, if you’re trying to get traction with comment marketing, I suggest you to watch / read this Whiteboard Friday episode about the best practices for building a sustainable comment marketing strategy: https://moz.com/blog/comment-marketing-how-to-earn-benefits-from-community-participation-whiteboard-friday
Thanks, your from works fine!!
Hi Marco,
I’m glad that it worked for you :)