0

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:

enter image description here

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;
}
}
user289418
  • 45
  • 4

0 Answers0