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