0

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

7
  • Is there something logged in the script executions page ? Commented Aug 4, 2020 at 3:10
  • 2
    The user interface isn’t available when you open spreadsheets by Id. They just are opened on the server but no ui because there is no user. Try saving the results in a spreadsheet. Commented Aug 4, 2020 at 5:14
  • @Cooper this seems like a very complete answer already, could you post it so it can be upvoted and accepted? Commented Aug 4, 2020 at 9:26
  • 1
    This seems like an XY problem. Cooper is correct about the exact failure of trying to use toast, but the trigger is probably installed and running, just not changing the UI. Perhaps if you can explain the original problem you thought toast + trigger would solve you'll get some good answers. Commented Aug 4, 2020 at 13:02
  • Using toast would give me something to see. I understand the problem, lets give it a try1 Commented Aug 4, 2020 at 14:37

2 Answers 2

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

Sign up to request clarification or add additional context in comments.

1 Comment

This error was for the testClear function.. it must be finding the gUI from somewhere else in the script.. I'm going to keep digging.
0

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!

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.