Google Scripts: onEdit of non active sheet?

Question

On sheet1 there is a submit button which runs a script and moves data into sheet2.

I'm 'timestamping' the move (under certain conditions to prevent overwriting, and pointless timestamps), however with the current script—

Issue (1): onEdit only takes place if I manually make an edit on sheet2 (=sheet2 is active).

RESOLVED! (See comments) Issue (2): onEdit won't restrict to sheet2, and will happen as well on sheet1.

function onEdit() {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('sheet2');
    if( sheet.getName() == "sheet2" ) {
        var activecell = sheet.getActiveCell();
        if( activecell.getColumn() == 2 ) {
            var pastecell = activecell.offset(0, -1);
            if( pastecell.getValue() === '' & activecell.getValue() != '' )
                pastecell.setValue(new Date()).setNumberFormat('YY-MM-DD HH:mm:ss');
        }
    }
}

Anyone know how two fix this?

Your help and insight is much appreciated!

ISSUE (2) RESOLVED! By simply changing— var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('sheet2'); to: var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet — saadya
It is absolutely intentional that edit triggers do not activate for programmatic edits. If you're editing programmatically, you can just call the desired function from your program already. — tehhowch
@tehhowch Yup! Just realized that🤦‍♀️ Going to timestamp straight in the move function. Thanks. — saadya

saadya saadya · Accepted Answer · 2019-03-21T19:10:35

So after reading up🤦‍♀️ and @tehhowch insight of the following—

https://developers.google.com/apps-script/guides/triggers/

Script executions and API requests do not cause triggers to run. For example, calling Range.setValue() to edit a cell does not cause the spreadsheet's onEdit trigger to run.

Resolved by simply running the timestamp in the original move function, like so—

function move() {

  var source = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('sheet1');
  var source_range = source.getRange(3, 15, 1, 6).getValues();

  var target = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('sheet2');
  var target_range = target.getRange(target.getLastRow() + 1, 2, 1, 6);

  target_range.setValues(source_range);

  var datecell = target_range.offset(0, -1, 1, 1);

  datecell.setValue(new Date()).setNumberFormat('YY-MM-DD HH:mm:ss');

  var clearsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('sheet1');
  clearsheet.getRange("J3:K4").clearContent();
  clearsheet.getRange("B8:B25").clearContent();
  clearsheet.getRange("H8:H9").clearContent();
  clearsheet.getRange("H11").clearContent();
  clearsheet.getRange("O7:P16").clearContent();

}

Sometimes you got to just think out loud. Hope this helps someone else.

Google Scripts: onEdit of non active sheet?

1 Answers