How can I apply the dependent drop-down to two sheets

Question

I want to get a drop-down on both Q1 and Q2 sheets, but it's only possible on Q1. How can I create a drop-down for both sheets? The answer is doesn't seem to be operated on a spreadsheet. I don't know what to do.

first

function onEdit(){

  var tabLists = "Q1";
  var tabValidation = "DATA";
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var datass = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(tabLists);
  
  var activeCell = ss.getActiveCell();
  
  if(activeCell.getColumn() == 1 && activeCell.getRow() > 1 && ss.getSheetName() == tabValidation){
    
    activeCell.offset(0, 1).clearContent().clearDataValidations();
    
    var makes = datass.getRange(1, 1, 1, datass.getLastColumn()).getValues();
    
    var makeIndex = makes[0].indexOf(activeCell.getValue()) + 1;
    
    if(makeIndex != 0){
    
        var validationRange = datass.getRange(3, makeIndex, datass.getLastRow());
        var validationRule = SpreadsheetApp.newDataValidation().requireValueInRange(validationRange).build();
        activeCell.offset(0, 1).setDataValidation(validationRule);
  
     }  
      
  }
  
}

second

function onEdit(){
  var tabLists = "Q2";
  var tabValidation = "DATAS";
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var datass = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(tabLists);
  
  var activeCell = ss.getActiveCell();
  
  if(activeCell.getColumn() == 1 && activeCell.getRow() > 1 && ss.getSheetName() == tabValidation){
    
    activeCell.offset(0, 1).clearContent().clearDataValidations();
    
    var makes = datass.getRange(1, 1, 1, datass.getLastColumn()).getValues();
    
    var makeIndex = makes[0].indexOf(activeCell.getValue()) + 1;
    
    if(makeIndex != 0){
    
        var validationRange = datass.getRange(3, makeIndex, datass.getLastRow());
        var validationRule = SpreadsheetApp.newDataValidation().requireValueInRange(validationRange).build();
        activeCell.offset(0, 1).setDataValidation(validationRule);
  
     }  
      
  }

Rubén · Answer 1 · 2021-02-02T02:04:50.070

When a Google Apps Script project has two function declaration with the same name one of the functions will not work because function names should be unique.

A quick and dirty solution might be to rename your current on edit functions then call them from an onEdit function or by creating an installable trigger for each of them.

A better solution is rewrite the logic of your functions to integrate them on a single one, specially if you will be using a simple trigger and adding more operations to it as simple triggers have short maximum execution time (30 seconds).

score 0 · Answer 2 · answered Feb 01 '21 at 23:02

function onEdit(e) {
  const sh=e.range.getSheet();
  //Logger.log(JSON.stringify(e));//add this if you want to learn more about event object.
  const sheets=['Q1','Q2'];//Enter names of sheets that you want script to work on
  if(sheets.indexOf(sh.getName())>-1 && e.range.columnStart==1 && e.range.rowStart>1) {
    //run your code in here and it will only work for sheets whose names are in sheets

  }
}

How can I apply the dependent drop-down to two sheets

2 Answers2