CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
M Coder
M Coder

Reputation: 49

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

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.

Upvotes: 1

Views: 277

Answers (2)

Marios
Marios

Reputation: 27350

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

Upvotes: 2

CMB
CMB

Reputation: 5163

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

Upvotes: 1

Related Questions