Time trigger not calling function

Question

In my Google sheet there a function called onOpen which will check Column C, If the value in Column C is Closed, then that row should move to next sheet.

This function is working fine, when I'm editing sheet directly. The problem is function will not work If I update sheet with AppSheet, Also, Time trigger will not run in Google sheet.

function onOpen() {

  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);
  }

}

I believe that is because the onOpen event will only occur when you open the sheet in Google Sheets. Maybe look at creating a trigger that will fire when an edit is made to the spreadsheet instead? Also, you mention your time trigger function, but it doesn't look like you posted your time trigger function — Adjit
@Adjit I tried onEdit, this will run. But it will only run when I edit sheet directly,NOT when I edit sheet with third party like AppSheet — Rajiv Goswami
And your time driven trigger doesn't work? Because I know you can set it to fire every hour/every minute — Adjit
There is also an onChange trigger, which I have not used, but have you tried that as well? — Adjit
@Adjit Right now Im using onChange, same thing It will work if I make changes directly, not with time, — Rajiv Goswami

Adjit Adjit · Accepted Answer · 2015-10-20T18:48:09

It's hard for me to tell how exactly you are setting up your events to fire.

Here is how I would set up a simple test to make sure that everything is working as you expected.

First, in your script attached to your spreadsheet set up your test function:

function timeDrivenTestFunction() {
  var emailName = "Time Driven Admin";
  var replyTo = "[email protected]";
  var email = "[email protected]";
  var body = "Timestamp: " + Date.now();
  var subject = "Timestamp Test";

  var advancedArgs = {
    name: emailName,
    replyTo: replyTo,
    to: email,
    subject: subject,
    htmlBody: body,
    noReply: true
  };

  var autoReplyErr = "Error in sending email message. No reply was sent";

  try {
    MailApp.sendEmail(advancedArgs);
  } catch (e) {
    MailApp.sendEmail(errorEmail, autoReplyErr);
  }      
}

Now that you have that set up you can add triggers to your project.

Step 1 - Go to Current project's triggers

Step 2 - Add a new trigger

Step 3 - Set your test function to run as a time driven event every minute. Then click on notifications to bring you to Step 4

Step 4 - Set up error notifications, so that if the function fails to execute it will send your gmail an email message telling you that the function failed to execute and will also state the reason why it didn't execute (ie. it will give you some sort of error message)

Step 5 - Click OK, and Save then wait a few minutes for it to kick in. It should work well, you may just need to set up permissions in your spreadsheet in order to allow Google Docs to access your gmail and send emails.

Time trigger not calling function

1 Answers