Active Sheet always being leftmost sheet on Google Sheets

1
votes

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.

2
google-apps-scriptgoogle-sheets

2 Answers

2
votes

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.

1
votes
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
}