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

The easiest way to get started with the Google Calendar API is through API Connector’s built-in integration.

  1. Select Google Pagespeed from the drop-down list of applications
    googlepagespeed-application
  2. Under Authorization, click Connect to Google Pagespeed
    googlepagespeed-authorization
  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.

Part 2: Pull Data from Google PageSpeed Insights to Sheets

Now that we’re connected, let’s pull some data into Sheets.

  1. Select the /runPagespeed?fields=lighthouseResult/categories/*/score endpoint, which allows us to retrieve a summary of the audit scores reported by Lighthouse
    googlepagespeed-endpoints
  2. In the url parameter, enter your URL of interest. This can be any URL as all data processed by Lighthouse is public.
    googlepagespeed-parameters
  3. Optionally populate other request parameters to customize your request. For this example, we'll select multiple categories.
    googlepagespeed-parameters-categories
  4. Choose a destination sheet, name your request, and hit Run. This API is pretty slow, so just hang on for a minute or two. When it finishes running, you'll see the response data in your sheet.
    googlepagespeed-response

Part 3: Create a Custom Request

Alternatively, you can run your own custom requests instead of using API Connector’s pre-built integration, using any of the endpoints and parameters shown in the API documentation. Here's a sample request configuration:

  • ApplicationCustom
  • MethodGET
  • Request URLhttps://www.googleapis.com/pagespeedonline/v5/runPagespeed?fields=lighthouseResult/categories/*/score&strategy=mobile&category=performance&category=pwa&category=best-practices&category=accessibility&category=seo&url=https://www.mixedanalytics.com
  • OAuthGoogle PageSpeedgooglepagespeed-custom

Part 4: API Documentation

Official API documentation: https://developers.google.com/speed/docs/insights/rest/v5/pagespeedapi/runpagespeed

Expression builder
The 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 (Extensions > API Connector > Import / Export > Import cURL). Just remove the Authorization header as we handle authorization through the OAuth2 connection manager.

2 thoughts on “Import Google PageSpeed Insights Data to Google Sheets”

  1. if i have a list of 100 website urls, and I want to automatically run the site speed test every month, whats the best way to accomplish this? Do i have to create a new request for every url or can I use cell values to pull each url?

    Reply
    • Sure, you can reference a list of cells. That syntax currently only works for custom requests, so you'd enter a custom request URL like https://www.googleapis.com/pagespeedonline/v5/runPagespeed?fields=lighthouseResult/categories/*/score&strategy=mobile&category=performance&category=pwa&category=best-practices&category=accessibility&category=seo&url=+++Sheet1!A1:A20+++
      This API is really slow to respond, so I suggest splitting it up into several different requests of, say, 20 URLs at a time, to ensure each chain of requests completes within Google Sheets' 6-mins request processing limit. You can set each block of requests to run at a different hour, e.g. 1am, 2am, and so on.

      Reply

Leave a Comment

Jump To...