Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Googlesheet Script that returns Mobile Speed & Optimization and Desktop Speed & Optimization values in six adjacent columns
/**
* Returns Mobile Speed & Optimization and Desktop Speed & Optimization values in six adjacent columns
* by Cagri Sarigoz
*/
function checkAll(Url) {
//CHANGE YOUR API KEY WITH YOUR_API_KEY BELOW
var key = "YOUR_API_KEY";
var serviceUrlMobile = "https://www.googleapis.com/pagespeedonline/v4/runPagespeed?url=" + Url + "&strategy=mobile&key=" + key;
var serviceUrlDesktop = "https://www.googleapis.com/pagespeedonline/v4/runPagespeed?url=" + Url + "&strategy=desktop&key=" + key;
// var serviceUrlMobileFriendliness = "https://searchconsole.googleapis.com/v1/urlTestingTools/mobileFriendlyTest:run?fields=mobileFriendliness&key="+key;
// var formDataMobileFriendliness = {'url':Url};
// var optionsMobileFriendliness = {
// 'method' : 'post',
// 'payload' : formDataMobileFriendliness
// };
var array = [];
if (key == "YOUR_API_KEY")
return "Please enter your API key to the script";
var responseMobile = UrlFetchApp.fetch(serviceUrlMobile);
if (responseMobile.getResponseCode() == 200) {
var contentMobile = JSON.parse(responseMobile.getContentText());
if ((contentMobile != null) && (contentMobile["ruleGroups"] != null)) {
if (contentMobile["responseCode"] == 200) {
var optimizationScoreMobile = contentMobile["ruleGroups"]["SPEED"]["score"];
if ("metrics" in contentMobile["loadingExperience"]) {
var avgFCPMobile = ((contentMobile["loadingExperience"]["metrics"]["FIRST_CONTENTFUL_PAINT_MS"]["median"]) / 1000).toFixed(1).toString() + " (" + contentMobile["loadingExperience"]["metrics"]["FIRST_CONTENTFUL_PAINT_MS"]["category"] + ")";
var avgDCLMobile = ((contentMobile["loadingExperience"]["metrics"]["DOM_CONTENT_LOADED_EVENT_FIRED_MS"]["median"]) / 1000).toFixed(1).toString() + " (" + contentMobile["loadingExperience"]["metrics"]["DOM_CONTENT_LOADED_EVENT_FIRED_MS"]["category"] + ")";
} else {
var avgFCPMobile = "Unavailable";
var avgDCLMobile = "Unavailable";
}
} else {
var optimizationScoreMobile = "An error occured";
var avgFCPMobile = "An error occured";
var avgDCLMobile = "An error occured";
}
}
}
var responseDesktop = UrlFetchApp.fetch(serviceUrlDesktop);
if (responseDesktop.getResponseCode() == 200) {
var contentDesktop = JSON.parse(responseDesktop.getContentText());
if ((contentDesktop != null) && (contentDesktop["ruleGroups"] != null)) {
if (contentDesktop["responseCode"] == 200) {
var optimizationScoreDesktop = contentDesktop["ruleGroups"]["SPEED"]["score"];
if ("metrics" in contentDesktop["loadingExperience"]) {
var avgFCPDesktop = ((contentDesktop["loadingExperience"]["metrics"]["FIRST_CONTENTFUL_PAINT_MS"]["median"]) / 1000).toFixed(1).toString() + " (" + contentDesktop["loadingExperience"]["metrics"]["FIRST_CONTENTFUL_PAINT_MS"]["category"] + ")";
var avgDCLDesktop = ((contentDesktop["loadingExperience"]["metrics"]["DOM_CONTENT_LOADED_EVENT_FIRED_MS"]["median"]) / 1000).toFixed(1).toString() + " (" + contentDesktop["loadingExperience"]["metrics"]["DOM_CONTENT_LOADED_EVENT_FIRED_MS"]["category"] + ")";
} else {
var avgFCPDesktop = "Unavailable";
var avgDCLDesktop = "Unavailable";
}
} else {
var optimizationScoreDesktop = "An error occured";
var avgFCPDesktop = "An error occured";
var avgDCLDesktop = "An error occured";
}
}
}
// var responseMobileFriendliness = UrlFetchApp.fetch(serviceUrlMobileFriendliness, optionsMobileFriendliness);
// var contentMobileFriendliness = JSON.parse(responseMobileFriendliness.getContentText());
//
// if ( (contentMobileFriendliness != null) && (contentMobileFriendliness["mobileFriendliness"] != null) )
// {
// var mobileFriendliness = contentMobileFriendliness["mobileFriendliness"];
// }
// else
// {
// var mobileFriendliness = "An error occured";
// }
var currentDate = new Date().toJSON().slice(0, 10).replace(/-/g, '/');
array.push([avgFCPMobile, avgDCLMobile, optimizationScoreMobile, avgFCPDesktop, avgDCLDesktop, optimizationScoreDesktop, currentDate]);
Utilities.sleep(1000);
return array;
}
@MarcusJTWhich

This comment has been minimized.

Copy link

@MarcusJTWhich MarcusJTWhich commented Dec 17, 2018

Do you have any plans to update this to use the v5 API (released in June 2018)? See https://developers.google.com/speed/docs/insights/v5/reference/

@csarigoz

This comment has been minimized.

Copy link
Owner Author

@csarigoz csarigoz commented Dec 20, 2018

Do you have any plans to update this to use the v5 API (released in June 2018)? See https://developers.google.com/speed/docs/insights/v5/reference/

Hello there,
It seems that the v4 will be deprecated about April-May 2019 (details here). I will try to update the script before that.
Thanks for the heads up.

@SEAZ1

This comment has been minimized.

Copy link

@SEAZ1 SEAZ1 commented Jun 24, 2019

Great!

@lucam1234

This comment has been minimized.

Copy link

@lucam1234 lucam1234 commented Jun 28, 2019

Hi @csarigoz,
Are you planning to update the script with the v5 of the API by any chance? That would be super helpful!
Thanks in advance.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment