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:

Creating a project on Google Developers Console
Creating a project on Google Developers Console

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.
Enabling Pagespeed Insights API
Enabling Pagespeed Insights 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.
Creating a Public Key for API Acess
Creating a Public Key for API Acess

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.


var key = "YOUR_API_KEY";

view raw

api_key_line_on_pagespeed_script.js

hosted with ❤ by GitHub

If you know how to use Google Apps Scripts, you can just take the script code here on GitHub Gist.

pagespeed and mobile usability googlesheet - script editor
Making Your Copy of the Google Sheet

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.

get data from pagespeed and mobile usability googlesheet
Get Data from the Google Sheet

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.

45 thoughts on “Create Your Own Google Pagespeed & Mobile Usability Tracking Google Sheet in 5 Steps”

  1. Hi Cagri,

    Could you please provide the updated script. Page speed data and sheet data is not match.

    Reply

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

    Reply

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

    Reply

    1. 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 :)

      Reply

      1. Looking forward to that update Çağrı :)

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

    Reply

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

      Reply

  8. The exact error is “Internal error executing the custom function.”

    Reply

  9. 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!

    Reply

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

      Reply

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

    Reply

    1. 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,

      Reply

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

    Reply

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

    Reply

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

      Reply

  13. This is fantastic! Thank you very much for the easy to follow instructions… this is what i’ve been looking for!

    Reply

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

    Reply

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

    Reply

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

      Reply

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

    Reply

      1. Hi Cagri,

        Have shared sheet access with you.

        Thanks for taking the time to look into this.
        :)

      3. 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"];

      4. 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,

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

    Reply

    1. 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 :)

      Reply

  19. I’m getting the following message ” Please enter your API key to the script” eventhough I’ve entered the key as guided.

    Reply

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

      Reply

  20. 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?

    Reply

      1. 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!

      2. 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 :)

Leave a Reply