Search API Connector Documentation

Print

Import Google PageSpeed Insights Data to Google Sheets

premium

In this guide, we’ll walk through how to pull Google PageSpeed Insights data data directly into Google Sheets, using the API Connector add-on for Sheets.

PageSpeed Insights is Google’s tool to measure and analyze the performance of a webpage. You may have seen data from this API when running a Lighthouse audit of your site, which retrieves PageSpeed Insights data and presents it in your browser console like this:

With this API, you can see this data in Sheets instead of (or in addition to) viewing the Lighthouse report in your browser.

Contents

Before You Begin

Click here to install the API Connector add-on from the Google Marketplace.

Part 1: Connect to the Google PageSpeed Insights API

If you haven’t connected API Connector to Google’s PageSpeed Insights API before, you’ll first need to initiate the connection as follows:

  1. Open up Google Sheets and click Add-ons > API Connector > Manage Connections.
  2. In the list of available connections, find “Google PageSpeed” and click Connect.
    google-pagespeed-img1
  3. You will be directed to google.com and asked to sign in.
  4. You’ll then be returned to your Google Sheet, and can verify that your Google PageSpeed Insights connection is active in the Connections screen.

Part 2: Create a Google PageSpeed Insights API Request

We’ll first follow Google’s PageSpeed Insights API documentation to pull in a summary of all the audit scores reported by Lighthouse.

  • API root: https://www.googleapis.com
  • Endpoint: /pagespeedonline/v5/runPagespeed
  • Query strings: ?url=https://mixedanalytics.com&fields=lighthouseResult/categories/*/score&strategy=mobile&category=performance&category=pwa&category=best-practices&category=accessibility&category=seo

Note the different query strings you can modify: in ‘url’ you can substitute in your own URL, under ‘strategy’ you can choose mobile or desktop, and under ‘fields’ you can choose the fields you want to return.

Putting it together, we get the full API Request URL:

https://www.googleapis.com/pagespeedonline/v5/runPagespeed?url=https://mixedanalytics.com&fields=lighthouseResult/categories/*/score&strategy=mobile&category=performance&category=pwa&category=best-practices&category=accessibility&category=seo

Part 3: Pull Google PageSpeed Insights API Data into Sheets

We can now start pulling in data.

  1. In API Connector, click Create, choose GET from the dropdown menu, and paste in the Request URL we created above.
    google-pagespeed-img3
  2. Choose ‘Google PageSpeed’ from the authentication dropdown.
  3. Under headers, enter Key = Accept, Value = application/json.
    google-pagespeed-img4
  4. Create a new tab and click ‘Set current’ to use that tab as your data destination.
  5. Name your request and click Run. A moment later you’ll see your summary scores populate your sheet. Note that they may not exactly match what you see in your Lighthouse audit, because the scores vary between runs.
    google-pagespeed-img6

Part 4: More Example PageSpeed API Requests

Loading time details

https://www.googleapis.com/pagespeedonline/v5/runPagespeed?url=https://mixedanalytics.com&fields=id,loadingExperience&strategy=mobile

Expression builder

Google’s PageSpeed Insights documentation also contains a handy expression builder that you can use to create and test queries. You can then take the resulting cURL snippet and copy/paste it into API Connector (Add-ons > API Connector > Import / Export > Import cURL). Just make sure to remove the Authorization header as we handle authorization through the OAuth2 connection manager.

Previous Import Google Cloud Vision API Data to Google Sheets
Next Import Google Search Console Data to Sheets

Leave a Comment

Table of Contents