I have a script that is executed in google sheet to automate the extraction of landing performance data with pageSpeed, the problem I have is that it throws me an error:
This would be the function to execute the function below and to send the corresponding url
function runTool() {
var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Results");
var rows = activeSheet.getLastRow();
for (var i = 6; i <= rows; i++) {
var workingCell = activeSheet.getRange(i, 2).getValue();
var stuff = "=runCheck"
if (workingCell != "") {
activeSheet.getRange(i, 3).setFormulaR1C1(stuff + "(R[0]C[-1])");
}
}
}
Key would be the google key
strategy would be the device, either desktop or mobile
this is the script
function runCheck(Url) {
var settingsSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Settings");
var key = settingsSheet.getRange("C7:C7").getValue();
var strategy = settingsSheet.getRange("C19").getValue();
var serviceUrl = "https://www.googleapis.com/pagespeedonline/v5/runPagespeed?url=" + Url + "&key=" + key + "&strategy=" + strategy + "";
var array = [];
if (key == "YOUR_API_KEY")
return "Please enter your API key to the script";
var response = UrlFetchApp.fetch(serviceUrl);
if (response.getResponseCode() == 200) {
var content = JSON.parse(response.getContentText());
if ((content != null) && (content["lighthouseResult"] != null)) {
if (content["captchaResult"]) {
var score = content["lighthouseResult"]["categories"]["performance"]["score"];
var timetointeractive = content["lighthouseResult"]["audits"]["interactive"]["displayValue"].slice(0, -2);
var largestcontentfulpaint = content["lighthouseResult"]["audits"]["largest-contentful-paint"]["displayValue"].slice(0, -2);
var firstcontentfulpaint = content["lighthouseResult"]["audits"]["first-contentful-paint"]["displayValue"].slice(0, -2);
var serverresponsetime = content["lighthouseResult"]["audits"]["server-response-time"]["displayValue"].slice(19, -3);
var speedindex = content["lighthouseResult"]["audits"]["speed-index"]["displayValue"].slice(0, -2);
var device = strategy;
} else {
var score = "An error occured";
var timetointeractive = "An error occured";
var largestcontentfulpaint = "An error occured";
var firstcontentfulpaint = "An error occured";
var serverresponsetime = "An error occured";
var speedindex = "An error occured";
var device = "An error occured";
}
}
var currentDate = new Date().toJSON().slice(0, 10).replace(/-/g, '/');
array.push([score, timetointeractive, largestcontentfulpaint, firstcontentfulpaint, serverresponsetime, speedindex, currentDate, "complete", device]);
Utilities.sleep(1000);
return array;
}
}