Skip to content

Instantly share code, notes, and snippets.

@csarigoz
Last active October 7, 2019 21:10
Show Gist options
  • Save csarigoz/76777a4743bfa8bed491 to your computer and use it in GitHub Desktop.
Save csarigoz/76777a4743bfa8bed491 to your computer and use it in GitHub Desktop.
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;
}
@SEAZ1
Copy link

SEAZ1 commented Jun 24, 2019

Great!

@lucam1234
Copy link

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