CODEWITHSUNDEEP
google-apps-script
TC76
TC76

Reputation: 860

getSheetByName seems to get ANY active sheet

I would like for the following function to only run on my "Estimate" tab, but it runs on all tabs. I thought calling the sheet by name would resolve this, but it doesn't. I'm very new to GAS and have minimal coding experience, so I'm obviously just doing something wrong.

Here is a link to my sheet

function onEdit(){

  //https://www.youtube.com/watch?v=8aOn0VMgG1w
  //var ss = SpreadsheetApp.getActiveSpreadsheet();

  //var estss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Estimate');
  //commented out to try the var below...
  //same result
  var estss = SpreadsheetApp.getActive().getSheetByName('Estimate');

  //var catss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Categories');
  //commented out to try the var below...
  //same result
  var catss = SpreadsheetApp.getActive().getSheetByName('Categories');

  var activeCell = estss.getActiveCell();

  if(activeCell.getColumn() == 1 && activeCell.getRow() > 1){

    activeCell.offset(0, 1).clearContent().clearDataValidations();

    var cat = catss.getRange(1,2,1,catss.getLastColumn()).getValues();
    var catIndex = cat[0].indexOf(activeCell.getValue()) + 2;

    if(catIndex != 0){

      var validationRange = catss.getRange(2, catIndex,catss.getLastRow());
      var validationRule = SpreadsheetApp.newDataValidation().requireValueInRange(validationRange).build();

      activeCell.offset(0, 1).setDataValidation(validationRule);

    }
  }
}

When selecting a category in column A on the "Estimate" tab, the following column (B) should set a data validation with all of the sub-categories (rows below the corresponding category), from the "Categories" tab. It basically works, but it also works on every other tab. I would like for it to work on the "Estimate" tab only.

Upvotes: 0

Views: 90

Answers (1)

Wicket
Wicket

Reputation: 38131

Instead of the active methods, use the Event object

If your event object is e -> function onEdit(e){}
e.range returns the edited range, then you could use

e.range.getSheet().getName() to get the name of the active sheet

then you could use an if statement like

if(e.range.getSheet().getName() !== 'Sheet 1') return;

to stop the execution of the script when the active sheet's name isn't Sheet 1

Upvotes: 2

Related Questions