Create Your Own Google Pagespeed & Mobile Usability Tracking Googlesheet 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-eavaluated 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 everyday 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 Googlesheet 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 Googlesheet with everyone.

You can start using the Googlesheet 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 Googlesheet 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 invoke access to the API. The API supports two types of credentials: OAuth & Public API Acess. The Googlesheet 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 on 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 Googlesheet

Having created your API key, the next step is to create your Googlesheet and access to your API through the Googlesheet.

  • Open the public Googlesheet 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 Googlesheet 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 Googlesheet

5. Get Your Data from the Googlesheet

Voila! Your copy of the Googlesheet 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

You can easily reuse the Googlesheet 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.