CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetstriggers
Lolo
Lolo

Reputation: 25

This script does not populate sheet after parsing retrieved data

I hope this is well explained. First of all, sorry because my coding background is zero and I am just trying to "fix" a previously written script.

Problem The script does not populate sheet after parsing retrieved data if the function is triggered by timer and the sheet is not open in my browser .

The script works OK if run it manually while sheet is open.

Problem details: When I open the sheet the cells are stuck showing "Loading" and after a short time, data is written.

Expected behavior is to get the data written no matter if I don't open the sheet.

Additional info: This is how I manually run the function

function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [
    {name: "Manual Push Report", functionName: "runTool"}
     ];
  sheet.addMenu("PageSpeed Menu", entries);
}

Additional info: I set the triggers with Google Apps Script GUI See the trigger

Before posting the script code, you can see how the cells look in the sheet:
Cells in the sheet

Script code

function runTool() {
var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Results");
var rows = activeSheet.getLastRow();


  for(var i=3; 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])");

     }
     }    
}


// URL check //

function runCheck(Url) {

  var key = "XXXX Google PageSpeed API Key";
  var strategy = "desktop"

    var serviceUrl = "https://www.googleapis.com/pagespeedonline/v5/runPagespeed?url=" + Url + "&key=" + key + "&strategy=" + strategy +"";

    var array = [];

    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"];

            } else {
                      var score = "An error occured";

            }
        }


    array.push([score,"complete"]);
    Utilities.sleep(1000);
    return array;
}
}

You can try the code using the sheet below with a valid Pagespeed API key.

You only need to add a Trigger and wait for it's execution while the sheet is not open in your browser

https://docs.google.com/spreadsheets/d/1ED2u3bKpS0vaJdlCwsLOrZTp5U0_T8nZkmFHVluNvKY/copy

Upvotes: 0

Views: 141

Answers (1)

Wicket
Wicket

Reputation: 38435

I suggest you to change your algorithm. Instead of using a custom function to call UrlFetchApp, do that call in the function called by a time-driven trigger.

You could keep your runCheck as is, just replace 

activeSheet.getRange(i, 3).setFormulaR1C1(stuff + "(R[0]C[-1])");

by

 activeSheet.getRange(i, 3, 1, 2).setValues(runCheck(url));

NOTE

Custom functions are calculated when the spreadsheet is opened and when its arguments changes while the spreadsheet is open.

Related

Upvotes: 1

Related Questions