Issue running an Installed Trigger in Google App Script

Question

Fairly new to app script so bare with me. Wrote this massive script, then went to set it up on a times trigger and it just refuses to run. I've gone ahead an back tracked as much as I could, to get at least something to work, yet I can't even get a basic toast to appear on a minute interval.

This is the script I've built, which I'm running directly to enable the trigger:

function createTriggers() {
  
  ScriptApp.newTrigger('testTime')
      .timeBased()
      .everyMinutes(1)
      .create();
};

The function it's calling is super simple, I've used it a lot and change it a lot too:

var gSS = SpreadsheetApp.openById("this_1s/the.1d")

function testTime() {
  var d = new Date()
  var Start = d.getTime();
  
  gSS.toast(Start, "Testing", 30)
};

So how it should work, and it does if I just call the 'testTime' function directly, is a little toast pop-up appears on the spreadsheet in question, and stays visible for 30s. When I run the trigger function 'createTriggers', nothing happens..

Please help! All the code I wrote is for nothing if I can't get it to run on its own.. :(

***** EDIT - 08/04/20 - based on comments *****

It's possible this was an XY example, I tried to run a small segment of the original code which works when I run it directly, and its not working here either.. this snippit does not have any UI facing functions in it, so it shouldn't be the issue.. All i did was take the above trigger function and change the name to 'testClear', which calls to the following functions:

function testClear(){
  sheetVars(1)
  clearSheetData(sheetSPChange)
};

function sheetVars(numSprints) {
  // returns the global vars for this script
  
  try {
    sheetNameSprints = "Name of Sprint Sheet"
    sheetNameSPChange = "Name of Story Point Change Sheet"
    sheetSprints = gSS.getSheetByName(sheetNameSprints)
    sheetSPChange = gSS.getSheetByName(sheetNameSPChange)
    arraySprints = iterateColumn(sheetSprints,"sprintIDSorted", 1, numSprints)
  }
  catch(err) {
    Logger.log(err)
  };
};

function iterateColumn(sheet, header, columnNum, numRows) {
  // Create an array of first column values to iterate through
  // numRows is an int, except for the string "all"
  
  var gData = sheet.getDataRange();
  var gVals = gData.getValues();
  var gLastR = ""
  var gArray = []
  
  
  // check how many rows to iterate
  
  if (numRows == "all") {
    gLastR = gData.getLastRow();
  }
  else {
    gLastR = numRows
  };
  
  // Iterate through each row of columnNum column
  
  for (i = 1; i < gLastR; i++){
    // iterate through
    
    if(gVals[i][columnNum] !== "" && gVals[i][columnNum] !== header){
      // push to array
      
      gArray.push(gVals[i][columnNum]);
    }
    else if (gVals[i][columnNum] == "") {
      break
    };
  };
  
  return gArray
};

function clearSheetData(sheet) {
  // Delete all rows with data in them in a sheet
    
  try {
    if (!sheet.getRange(sheet.getLastRow(),1).isBlank()){
      sheet.getRange(2, 1, sheet.getLastRow()-1, sheet.getLastColumn()-1).clearContent()
      Logger.log("Sheet cleared from old data.")
    }
    else {
      sheet.deleteRows(2, sheet.getLastRow()-1)
      Logger.log("Sheet rows deleted from old data.")
    };
  }
  catch(err){
    Logger.log(err)
    emailLogs()
  };
};

The 'emailLogs' function is a basic MailApp so i get notified of an issue with the script:

function emailLogs() {
  // Email Nikita the loggs of the script on error
  
  var email = "my work email@jobbie"
  var subject = "Error in Sheet: " + gSS.getName()
  var message = Logger.getLog()
  MailApp.sendEmail(email, subject, message)
};

Thanks to a comment I've now discovered the executions page!! :D This was the error for the edited script.

Aug 4, 2020, 10:48:18 AM Error Exception: Cannot call SpreadsheetApp.getUi() from this context. at unknown function

Answer 1

Based on all the comments, and new things I'd learned from that..:

I'd been calling to a global variable for my onOpen function:

var gUI = SpreadsheetApp.getUi();

even though I wasn't using it for the trigger, since it was global it tried to run and then failed.

I moved the actual definition of gUI into the onOpen function, and tried again and it worked. Thank you all for the support!

Answer 2

To show a toast every certain "time" (every n iterations) add this to the for loop

if (!((i+1) % n)) spreadsheet.toast("Working...")

---

From the question

Aug 4, 2020, 10:48:18 AM Error Exception: Cannot call SpreadsheetApp.getUi() from this context. at unknown function

The above error means that your time-drive script is calling a method that can only be executed when a user has opened the spreadsheet in the web browser.

In other words, toast can't be used in a time-driven trigger. The solution is to use client-side code to show that message every minute. To do this you could use a sidebar and a recursive function that executes a setTimeout

References

• https://developer.mozilla.org/en-US/docs/Learn/JavaScript/Asynchronous/Timeouts_and_intervals