• Skip to main content
  • Skip to primary sidebar
  • Skip to footer

UpBuild

Values-Driven Technical Marketing

  • About
    • Mission & Values
    • Meet the Team
    • Be a Builder & Work at UpBuild
    • Offices & Location
  • Expertise & Services
    • SEO
    • Analytics
    • CRO
    • Custom Training & Education
    • Technical Marketing
  • Working With Us
    • Pricing
    • Non-Negotiables
    • Case Study: Zoomdata
    • Case Study: Indow
    • Case Study: Twistlock
  • Blog
  • Contact

Automating Pagespeed Insights Using Google Sheets [Updated Template]

October 8, 2019 by James McNulty 22 Comments

In September, Moz published UpBuild’s guide on “How to Automate Pagespeed Insights For Multiple URLs using Google Sheets”. We got a lot of really excellent questions and feedback on the post, and have continued making tweaks and improvements to the tool, which we thought we’d share here today. I recommend reading the Moz blog post first to give you a good understanding of what to expect from this tool. Then once you’re done, read on below to hear more about the additions we have made based on the feedback we received.

Performance Score and Mobile Results

A large majority of the community requested some additional functionality that was not part of the original Google Sheet, namely the PageSpeed Insights Performance score generated for each page and the ability to run the tool for mobile results (the tool pulls insights for desktop URLs by default).

We’re always happy to hear about ways to make the things we build even better and so we are happy to announce that we have added these great suggestions to an updated version of the template.

Download the Updated Google Sheet

You can grab an updated copy of the Google Sheet that now includes the Performance Score and the ability to run your URLs through PageSpeed Insights for mobile or desktop results.

Desktop and Mobile Insights

On this updated template, the Settings tab now has the option to choose between Desktop or Mobile. You will want to make sure that this is defined before running the tool. At this time you can only choose one strategy type, but if you’d like to have insights for both, simply make two copies of the new Google Sheet template.

Keep Us Updated

Let us know what else you’d like to see added to this tool, or if you find yourself digging into the Google App Script and modifying it yourself, let us know in the comments below – we want to hear all about it!

Related

Filed Under: SEO

About James McNulty

James McNulty was born in Sidcup, Kent England in 1985. James now lives in North Richland Hills, Texas with his wife Megan, and two dogs Colin and Davey. James has been building websites since 1999 and is currently a Senior Marketing Strategist at UpBuild.

Keep up with James on Twitter

Stay in the Know

Reader Interactions

Comments

  1. Dan says

    November 22, 2019 at 1:37 am

    Hi James,

    This is amazing, thanks so much. The only thing I would like to ask is whether it’s possible to change throttling settings? I’m assuming this is default set to no throttling?

    Reply
    • James McNulty says

      December 5, 2019 at 7:01 am

      Hi Dan, I’m not quite sure about the throttling settings, that hasn’t been accounted for in this script. This would be using the default throttle settings for the API. I couldn’t find anything specific in their documentation, but do let me know what you find out.

      Reply
  2. Thomas Charles says

    December 4, 2019 at 4:08 pm

    Hi James,

    Thanks for sharing this! I’m trying to add a new metric to this list in the script, the accessibility score.
    I added
    var scoreAccess = content[“lighthouseResult”][“categories”][“accessibility”][“score”];

    But I’m getting the following error
    “TypeError: Cannot read property “score” from undefined. (line 604).”

    Do you have any idea why?

    Thanks,
    Thomas

    Reply
    • James McNulty says

      December 5, 2019 at 6:57 am

      Hi Thomas, did you add the value scoreAccess to the array.push? Alongside, timetofirstbyte, speedindex etc.

      Reply
      • Thomas Charles says

        December 5, 2019 at 7:19 am

        hi James,

        Thanks for you answer. Yes I added scoreAccess to the array.push at the end of the script, please see the full code:

        if (content[“captchaResult”]) {
        var score = content[“lighthouseResult”][“categories”][“performance”][“score”];
        var scoreAcess = content[“lighthouseResult”][“categories”][“accessibility”][“score”];
        var timetointeractive = content[“lighthouseResult”][“audits”][“interactive”][“displayValue”].slice(0, -2);
        var firstcontentfulpaint = content[“lighthouseResult”][“audits”][“first-contentful-paint”][“displayValue”].slice(0, -2);
        var firstmeaningfulpaint = content[“lighthouseResult”][“audits”][“first-meaningful-paint”][“displayValue”].slice(0, -2);
        var timetofirstbyte = content[“lighthouseResult”][“audits”][“time-to-first-byte”][“displayValue”].slice(19, -3);
        var speedindex = content[“lighthouseResult”][“audits”][“speed-index”][“displayValue”].slice(0, -2);
        } else {
        var score = “An error occured”;
        var scoreAcess = “An error occured”;
        var timetointeractive = “An error occured”;
        var firstcontentfulpaint = “An error occured”;
        var firstmeaningfulpaint = “An error occured”;
        var timetofirstbyte = “An error occured”;
        var speedindex = “An error occured”;
        }
        }

        var currentDate = new Date().toJSON().slice(0, 10).replace(/-/g, ‘/’);

        array.push([score, scoreAcess, timetointeractive, firstcontentfulpaint, firstmeaningfulpaint, timetofirstbyte, speedindex, currentDate, “complete”]);
        Utilities.sleep(1000);
        return array;
        }
        }

        I’m not sure if I’m missing anything here.

        Thanks,
        Thomas

        Reply
        • James McNulty says

          December 5, 2019 at 8:10 am

          Try this:

          var scoreAcess = content[“lighthouseResult”][“categories”][“accessibility”][“auditRefs”][“score”];

          I think there may be another level in the array after [“accessibility”] that isn’t accounted for.

          Reply
  3. Daniel says

    January 17, 2020 at 5:56 am

    Hi! Thank you so much for this tool. But as a non-coder, can you help me with this? I’m getting this error message:

    Request failed for https://www.googleapis.com returned code 403. Truncated server response: { “error”: { “code”: 403, “message”: “Requests from referer \u003cempty\u003e are blocked.”, “errors”: [ { “message”: “… (use muteHttpExceptions option to examine full response) (line 595).

    Even after the proper credential configs. No app restrictions and Pagespeed Insights API restriction set, right?

    Reply
    • James McNulty says

      January 17, 2020 at 7:01 am

      Hi Daniel, try a different, working URL. It seems that https://www.googleapis.com is returning the erroneous response.

      Reply
      • Daniel says

        January 17, 2020 at 7:44 am

        Oh, after some digging I found out that it was just the “/” at the end of the URL. It was breaking the collection. Thanks!

        Reply
  4. Lisa says

    February 14, 2020 at 8:54 am

    Hi James – I just copied the sheet two more times and it looks like the Settings tab is missing the Mobile/ Desktop selector. This is the 5th time I’ve made a copy (big fan) and I know I’ve seen it before.

    Is this a known issue? Is there something I can add to the script to add the selector back?

    Thank you!

    Reply
    • James McNulty says

      February 14, 2020 at 9:03 am

      Hey Lisa!

      The link above reading “updated copy of the Google Sheet” should work. I just tried it myself and I can see the Desktop/Mobile setting on the first sheet. Here’s the direct link: https://docs.google.com/spreadsheets/d/1iMwxNoxHWjW2hc2k9hIQwFVth5qBM2zG8hY2yliBjRk/copy

      Thanks!

      Reply
  5. Ben Rutt says

    February 19, 2020 at 5:16 am

    James – this looks like an awesome tool! I’m excited about using it. I’m getting the following error. Can you assist possibly?

    “Sign in with Google temporarily disabled for this app
    This app has not been verified yet by Google in order to use Google Sign In.”

    Thanks!

    Reply
    • James McNulty says

      February 19, 2020 at 7:40 am

      Hey Ben,

      Unfortunately, this is not a verified application. Are you able to bypass this message?

      Reply
      • Jimbo says

        February 20, 2020 at 1:26 pm

        Hey James, thanks so much for putting this tool together!

        I’m having the same issue as Ben Rutt and am seeing “Sign in with Google temporarily disabled for this app. This app has not been verified yet by Google in order to use Google Sign In.”

        Would you be aware of any workaround? How have all the other users been able to successfully use the tool?

        Reply
        • James McNulty says

          February 25, 2020 at 7:04 am

          Hey Jimbo,

          Unfortunately, this warning message has seemingly only been occurring in the last few weeks. I don’t seem to have any additional control over the authorization/verification of the script being used in the sheet. Others were able to access and use the tool for quite some time when it was published. I would suggest perhaps ensuring that the browser/profile is logged into a Gmail address.

          Reply
          • Thijs says

            June 9, 2020 at 5:03 am

            I had the same problem. Then I tried with my company Google account (paid G Suite account) and then it worked. While my private gmail account didn’t work, it returned the authorization/verification error.

  6. Ben Rutt says

    February 19, 2020 at 11:42 am

    Unfortunately no way to bypass it for me – sorry about that!

    Reply
  7. Lisa says

    June 1, 2020 at 10:49 am

    Hi James,
    Over the last week or so, I’ve been getting errors in the report. It’s line 607, can’t ready “displayValue” of undefined. Is this related to the updated Page Speed Insights moving to Core Web Metrics, and is there a fix to this?
    Thank you!

    Reply
    • James says

      June 1, 2020 at 11:24 am

      Hey Lisa,

      Good catch! It was the “Time to First Byte” metric causing the issue. The PageSpeed API service had renamed it to Server Response Time. I’ve updated the sheet to account for this change. I also updated the sheet to report on Cumulative Layout Shift and Largest Contentful Paint too. Here is the updated sheet: https://docs.google.com/spreadsheets/d/1iMwxNoxHWjW2hc2k9hIQwFVth5qBM2zG8hY2yliBjRk/copy).

      Thanks!

      Reply
  8. Lei says

    June 5, 2020 at 2:46 pm

    Hi! Thanks for your sheet and updated.

    Just to clarify, this is not actually working:
    “That is why this setting will run the report again three more times in the consecutive hours, picking up exactly where it left off” (Moz’s post)

    The script does not resume “exactly where it left off” after trigger’s “Exceeded maximum execution time”. It begins from scratch.

    At least with “standard” Google account.

    Reply
  9. Gilberto says

    June 23, 2020 at 11:34 am

    Any plans to support Lighthouse V6 metrics and score?

    Reply
    • James McNulty says

      June 23, 2020 at 1:13 pm

      Yes – we do plan to! We were notified it had stopped working entirely at the beginning of June. This was due to the PageSpeed API service renaming “Time to First Byte” to “Server Response Time”. We’ve updated the sheet to account for this change, and also added Cumulative Layout Shift and Largest Contentful Paint metrics too. Here is the updated sheet: https://docs.google.com/spreadsheets/d/1iMwxNoxHWjW2hc2k9hIQwFVth5qBM2zG8hY2yliBjRk/copy).

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar

Get Our Weekly Newsletter

Technical marketing news made simple, from UpBuild's expert team straight to your inbox.

* indicates required

Browse previous newsletters. It's good stuff.

Recent Thoughts

How To Find & Fix Layout Shifts with Chrome DevTools

February 25, 2021 By Danielle Rohe

Web Accessibility Lessons from Whitehouse.gov

February 18, 2021 By Michelle Polk

Web Accessibility: Rated ‘E’ for Everyone

February 11, 2021 By Tiffany Moore

The Rich Results Get Richer

February 3, 2021 By Nick Bradman

SEO As Part of Your Go-to-Market Strategy

January 26, 2021 By Ruth Burr Reedy

In the Spotlight

Engagements

You don't have time to worry about technical marketing and SEO. We do, and what's more, we love doing it. If … [keep reading] about Engagements

Stay Connected

  • Facebook
  • LinkedIn
  • Twitter

Footer

Who We Are

We're geeks. Unashamed and idealistic. We're here to do innovative work that gets results for clients we're proud to partner with.

Learn more about us

Featured Blog Posts

Performance tab of DevTools showing a layout shift summary

How To Find & Fix Layout Shifts with Chrome DevTools

Image of the White House Lawn

Web Accessibility Lessons from Whitehouse.gov

Access written on return key of white keyboard.

Web Accessibility: Rated ‘E’ for Everyone

Newsletter

Sign up for our updates.

  • Home
  • About
  • Contact
  • Offices & Location
  • Certifications & Associations

© Copyright 2021 UpBuild, LLC · All Rights Reserved · Proudly powered by WordPress