Create Your Own Google Pagespeed & Mobile Usability Tracking Google Sheet in 5 Steps

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 with “YOUR_API_KEY” in the script. Save the script, close the Script Editor.

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.

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

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

    Like

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

        Like

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

    Like

    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

      Like

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

    Like

    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 🙂

      Like

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

    Like

      1. Hi Cagri,

        Have shared sheet access with you.

        Thanks for taking the time to look into this.
        🙂

        Like

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

        Like

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

        Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s