Active Sheet always being leftmost sheet on Google Sheets

Question

Continuation from the previous question: Detect Note changes with onChange

I know that this is a duplicate question, but the answers from the questions similar to it have not worked for me.

Google App Script getActiveSheet returns leftmost sheet, not active sheet

function createSpreadsheetOpenTrigger() {
  var ss = SpreadsheetApp.getActive();
  ScriptApp.newTrigger('onChange')
      .forSpreadsheet(ss)
      .onChange()
      .create();
}

function onChange(e) {
  var ss = SpreadsheetApp.getActive().getActiveSheet();
  if(e.changeType == 'OTHER') {
    if (ss.getName()=="Completed") {
      var celladdress ='G2';
      ss.getRange(celladdress).setValue(Utilities.formatDate(new Date(), "GMT-08:00", "MM/dd/yyyy HH:mm"));
    }
  }
}

Here is the sample sheet replicating the issue. https://docs.google.com/spreadsheets/d/1pVfIv6NRybK8JH-FgzTLYiTkzu73-8iD77HFZOGk3iA/edit#gid=2124234178

Editing or clearing a note in "Completed" sheet should write a date to the cell G2, but it does not. Additionally returning getRow() and getCol() of the active cell always yields in 1, presumably due to the fact that the cell does not exist in the active sheet (it's still stuck on leftmost sheet). Help would be appreciated.

EDIT: On further testing, the problem seems to lie with changeType OTHER. If I change the type to EDIT, the spreadsheet is the correct one. I presume it's a bug. I'd appreciate a workaround.

Boris Baublys · Answer 1 · 2021-01-22T20:55:39.033

2

Try this:

function createSpreadsheetOpenTrigger() {
  var ss = SpreadsheetApp.getActive();
  ScriptApp.newTrigger('onMyChange')
      .forSpreadsheet(ss)
      .onChange()
      .create();
}

function onMyChange(e) {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName('Completed');
  var celladdress ='G2';
  if(e.changeType == 'OTHER') {
    sheet.getRange(celladdress).setValue(Utilities.formatDate(new Date(), "GMT-08:00", "MM/dd/yyyy HH:mm"));
  }
}

You have an onChange trigger installed. Uninstall it and install the onMyChange trigger.

edited Jan 22 '21 at 20:55

answered Jan 22 '21 at 20:47

Boris Baublys

952
10
22

Thank you for your response. Is onMyChange a strictly better trigger compared to onChange? I have a worksheet flow using onChange, and am wondering if changing all that to onMyChange will cause any issues. – Brandon Park Jan 25 '21 at 20:19
"Is onMyChange a strictly better trigger compared to onChange?" To some extent, yes, because it works in your case :-) In addition onChange () is a reserved name as is onEdit (). There are cases of double triggering. – Boris Baublys Jan 25 '21 at 21:03
I have noticed that you have changed the code more than changing the trigger name. This isn't what I want; I want the function to check whether the sheet I am on is indeed called "Completed" instead of just grabbing the Completed sheet. `if (ss.getName()=="Completed")` is a necessary argument. – Brandon Park Jan 25 '21 at 22:49
In the post itself nothing was said about the need to preserve `If()` and I myself did not guess. Sorry I couldn't help you. – Boris Baublys Jan 26 '21 at 05:38

score 1 · Answer 2 · answered Jan 22 '21 at 21:19

function onMyChange(e) {
  const sh=e.source.getActiveSheet();//there is a source value in the event object which is the current spreadsheet
  //e.source.toast(sh.getName());//helpful when debugging
  if (e.changeType=='EDIT'&& sh.getName()=="Sheet3") {//change type to edit for debugging purpose and the sheet name is different
    sh.getRange('G2').setValue(Utilities.formatDate(new Date(), "GMT-08:00", "MM/dd/yyyy HH:mm"));
  }
  //Logger.log(JSON.stringify(e));//look at  event object
}

If I understand correctly the author of the question wants to catch an OTHER changeType, not an EDIT. It seems there is no e.source in onChange. — Boris Baublys, Jan 22 '21 at 21:31

Active Sheet always being leftmost sheet on Google Sheets

2 Answers2

Linked