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!
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?
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.
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
Hi Thomas, did you add the value scoreAccess to the array.push? Alongside, timetofirstbyte, speedindex etc.
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
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.
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?
Hi Daniel, try a different, working URL. It seems that https://www.googleapis.com is returning the erroneous response.
Oh, after some digging I found out that it was just the “/” at the end of the URL. It was breaking the collection. Thanks!
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!
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!
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!
Hey Ben,
Unfortunately, this is not a verified application. Are you able to bypass this message?
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?
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.
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.
Unfortunately no way to bypass it for me – sorry about that!
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!
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!
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.
Any plans to support Lighthouse V6 metrics and score?
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).
Hello James
How to get the data for FDI from the script?
Been trying to retrieve this data.
Hi Jason,
I’m happy to tell you that we have updated the template to include First Input Delay: https://docs.google.com/spreadsheets/d/1MQjM9Blm87c5DicqgF5UR20Eww-03nhkXDJL-jWSml4/copy
Thanks!
Hi there – it seems that the sheet no longer functions from my testing. Is it working for others? Or does it need updating or changes of some sort so it can function again?
Hi Matthew, I tested this morning and the document works. Could you share some more details?
Hello James!
I love this document, it is really useful, now I am trying to use it for about 50 urls and I have detected 2 details that could improve.
For sites that have a low performance, an error appears and then continues to the next line. Although if I delete this line later and only leave those spaces that marked error, it shows them without problem.
At the end, the log function does not copy the complete data to the other tab, and I have not really discovered the pattern, but I am always missing the odd url. This affects if I want to have an automated history.
I don’t know if I’m asking too much besides how wonderful this document is. But do you think this has a solution?
Hi Diana, very odd! We are always working to improve this document. We hope to have an update on this soon. Thanks!
Is this tool still working guys? I can’t get it to work, so maybe I do something wrong here, or there’s something else going on.
I get this error:
“Exception: Request failed for https://www.googleapis.com returned code 403. Truncated server response: { “error”: { “code”: 403, “message”: “PageSpeed Insights API has not been used in project 958014537578 before or it is disabled. Enable i… ”
Checked the credentials, API is enabled, Disabled restrictions, but still no luck. Anyone who has an idea what’s the problem?
Hm. It seems that the API is not enabled specifically for the project 958014537578. I think enabling it for that project will allow it to function for you. Alternatively you could create a new project and enable/generate an API key for that.