0

I have a function that I want to run on a time trigger every hour (in the test I have put it to minutes). However, it is not running unless I manually trigger it. Can anyone help me?

function setTrigger() {
  ScriptApp.newTrigger("LiDCOcheck")
    .timeBased()
    .everyMinutes(1)
    .create();
}

function LiDCOcheck() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ui = SpreadsheetApp.getUi();
  var buttons = ui.ButtonSet.YES_NO;
  var bed = ss.getRange("C2").getValue();
  var due = new Date(ss.getRange("D2").getValue());
  var time = new Date(ss.getRange("A2").getValue());

  if (+due.getHours() == +time.getHours()) {
    var lidco = ui.alert("It hsa been 24 hours since the LiDCO in Bed " + bed + " was calibrated.  Is it still required?", buttons);
    if (lidco == ui.Button.YES) {
      ss.getRange("D2").setBackground('#ff9900');}
    else {
      ss.getRange("D2").setValue('Not Required')}
  }
}

Execution Transcript Test page

tehhowch
  • 9,645
  • 4
  • 24
  • 42
Aidan Wilson
  • 51
  • 1
  • 10
  • 1
    Instead of `SpreadsheetApp.getActiveSpreadsheet()`, try explicitly opening the spreadsheet with `SpreadsheetApp.openById()`. – Diego Jul 24 '18 at 10:47
  • @Diego, I have tried that but the timer trigger still doesn't work. – Aidan Wilson Jul 24 '18 at 11:42
  • When you say it isn't running, do you actually mean it doesn't execute, or it doesn't do anything when it does execute? If your if test is false, there are no changes made. – tehhowch Jul 24 '18 at 12:56
  • @tehhowch Sorry I mean it doesn't execute at all when it is on a time trigger. It will work correctly when I manually trigger it. – Aidan Wilson Jul 24 '18 at 13:23
  • @tehhowch I noticed the syntax error and corrected it. I have tried both manual and programmatically. When the Google app trigger didn't work I tried to program it. Neither are working correctly. – Aidan Wilson Jul 24 '18 at 13:29
  • 1
    @AidanWilson post your execution transcript. Odds are you cannot bind to the UI class from a time based trigger, because there is no UI in the instance that runs the function. – tehhowch Jul 24 '18 at 13:40
  • @tehhowch I have added a picture of the execution transcript. As a novice to this i don't actually understand what it means so i have also added a link to the sheet. – Aidan Wilson Jul 24 '18 at 13:49
  • A script can only interact with the UI for the current instance of an open editor, and only if the script is container-bound to the editor. https://developers.google.com/apps-script/guides/bound#creating_a_bound_script My hunch is your script is not container-bound. Worth a look at least. – Jeremy Kahan Jul 24 '18 at 14:31
  • @JeremyKahan It is container-bound, thanks for the suggestion though. – Aidan Wilson Jul 24 '18 at 14:46
  • Okay, I hope someone gives you a good answer – Jeremy Kahan Jul 24 '18 at 14:49
  • 1
    Possible duplicate of ["SpreadsheetApp.getUi() cannot be called from this context"](https://stackoverflow.com/questions/36117119/spreadsheetapp-getui-cannot-be-called-from-this-context) – tehhowch Jul 24 '18 at 18:55

0 Answers0