Google Sheet Script run when we edit on a particular sub sheet

user_14849557 0
Viewed 178

we have the following Google Sheet Script, which is working fine but we want to auto run it when only sub sheet "test" range A4 edit or change,

We make any change or edit on Sub Sheet 'test' Range A, it will run

function CopyPasteVal() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('test'), true);
  spreadsheet.getRange('B:B').activate();
  spreadsheet.getRange('C:C').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  spreadsheet.getActiveRangeList().setNumberFormat('0');
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('test'), true);
  spreadsheet.getRange('A4').activate();
}

but if we make any change in other sub sheet, above code will not run.

google-apps-script google-sheets
2 Answers

You need to put the logic in a simple trigger function, in this case, onEdit(e), and check if the active sheet is "test" and the edited range, e.range is exactly cell A4:

function onEdit(e) {
  var range = e.range;
  var spreadsheet = SpreadsheetApp.getActive();
  if (spreadsheet.getActiveSheet().getSheetName() == "test" && range.getA1Notation() == "A4") {
    spreadsheet.setActiveSheet(spreadsheet.getSheetByName('test'), true);
    spreadsheet.getRange('B:B').activate();
    spreadsheet.getRange('C:C').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
    spreadsheet.getActiveRangeList().setNumberFormat('0');
    spreadsheet.setActiveSheet(spreadsheet.getSheetByName('test'), true);
    spreadsheet.getRange('A4').activate();
  }
}

References:

Simple Triggers

user_146060490

Explanation:

Please note the following two things:

  • You don't need to activate ranges or sheets unless you explicitly want to see the changes in your sheet as the script is running.

  • You need an onEdit(e) trigger that will take full advantage of the event object. Then you can make sure that a block of code is only executed when the active sheet (the one you edit) is the test sheet and the edited cell is the cell A4.

Solution:

function onEdit(e) {
  const range = e.range;
  const spreadsheet = e.source;
  const as = spreadsheet.getActiveSheet();
  if(as.getName()=='test' && range.getA1Notation() == "A4"){
    const destRng = as.getRange('B:B');
    as.getRange('C:C').copyTo(destRng, SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
    destRng.setNumberFormat('0');
  }
}
user_112252910
Related