Time Trigger not running

Question

My Time Trigger is not letting the onEdit to run. If I remove trigger and Edit Google sheet onEdit will work. However, when I want onEdit to run every minute, it won't run. Here is my sheet and script.

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

function onOpen() {
    SpreadsheetApp.getUi().createMenu('My Menu')
        .addItem('MOVE', 'onEdit')
        .addToUi()
}




function onEdit() {
  // assumes source data in sheet named Needed
  // target sheet of move to named Acquired
  // test column with yes/no is col 4 or D
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getActiveSheet();
  var r = s.getActiveRange();



  if(s.getName() == "Load Board" && r.getColumn() == 3 && r.getValue() == "Closed") {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Closed");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    s.deleteRow(row);
  }

}

Well, I don't see a 'MOVE' function for one thing - that's just the name you gave onEdit in the menu. Second, why can't you just create this using the triggers button? Seems like you're only creating it once. Third, I think you may run into issues using a time trigger to run a function that uses get ActiveSheet and getActiveRange since there won't be an active sheet or range when it runs... — Jens Astrup
I tried adding from Trigger Button, the problem is: Since onEdit is designed to run when you edit sheet directly(which is running fine). In my case, AppSheet(third party software) will edit the spread sheet, and onEdit is not running with that. I thought if I add trigger in script that may help. — Rajiv Goswami

David Tew David Tew · Accepted Answer · 2015-10-19T21:49:09

Based upon my interpretation of your comments to previous answers, I give you this code (edited my own previous answer). (I don't know AppSheet though, so you could be asking for more/different).

The code below will 'move' all the rows fulfilling the condition (that column 3 == 'Closed'). With the condition met, 'move' can be initiated in all three ways: editing the sheet manually; clicking on the sheet's My Menu; or via a trigger timer (which trigger can itself can be set by the code or manually in the script interface).

Note that the onEdit function could simply call function move() rather than its own code performing the move (in which case most of the code in onEdit is not needed). I have left a comment in the code to this effect.(I don't know which is the better method.)

function setTrigger() { // Note: 1 this could be set manually; 2) There needs to be a 'move' function which I have created; this trigger could equally be set to trigger onEdit every minute instead and, due to the function call in the onEdit funtion, would effect the same result, as can be seen by the onOpen menu item calling onEdit directly.
  ScriptApp.newTrigger("move")
  .timeBased()
  .everyMinutes(1)
  .create();
}

function move(){ 
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getSheetByName('Load Board');
  var values = s.getDataRange().getValues();
  var counter = 0; // not part of process, simply here for counting what's happening for inspection if required
  var targetSheet = ss.getSheetByName("Closed");
  for (var i = values.length -1 ; i>=0  ; i--){
    Logger.log('loop number: ' + i + ' gives value in column 3 = ' + values[i][2] );
    if(values[i][2] == "Closed"){
      targetSheet.appendRow(values[i]);
      s.deleteRow(i+1)
      counter++
    }
  }
  if(counter > 0)
    PropertiesService.getScriptProperties().setProperty('MOVE_INDICATOR', counter +' rows moved at ' + new Date()); // merely gives you a log of the last update
}

function onOpen() {
  SpreadsheetApp.getUi().createMenu('My Menu')
  .addItem('MOVE', 'onEdit')
  .addToUi()
}

function onEdit(e){
  if(e === undefined){ // note, just for testing, if you change this line to if(e){   then this remainder of this function is redundant since function move() is called and does a similar job.
    move();
    return
  }
  var ss = e.source;
  Logger.log(ss.getName() ) // You can inspect the logger to see what is happening.
  var s = e.source.getActiveSheet() // active tab in spreadsheet
  Logger.log(s.getName() )
  var r = e.range; // active range}

  if(s.getName() == "Load Board" && r.getColumn() == 3 && r.getValue() == "Closed")
  {
    var row = r.getRow();
    var numColumns = ss.getLastColumn();
    var targetSheet = ss.getSheetByName("Closed");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    s.deleteRow(row);}
}

function deleteProps(){ // here just to clear down the PropertiesService
  PropertiesService.getScriptProperties().deleteAllProperties();
}

Time Trigger not running

1 Answers