如何为4个多个从属下拉菜单创建脚本? [英] How can I create a script for 4 multiple dependent drop down menu?

问题描述

我想为此

以下是其工作原理的视频:(不是当前版本)

I would like to create a script for this Sheets. In Data tab I have all the levels.

With that script I wanna create 4 dependent drop down lists for entire columns with multiple levels in Index tab.

Thanks to this tutorial I could create one script that read one table. But I would prefer avoid create one table with one conditions per row, it would be a table with so many rows.

Expenses include so many sublevels, this is the principal problem.

This is the code:

var mainWsName = "Index";
var leyendaWsName = "Data";
var firstLevelColumn = 2;
var secondLevelColumn = 3;
var thirdLevelColumn = 4;

var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(mainWsName);
var wsData = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(dataWsName);
var data = wsData.getRange(2, 1,wsData.getLastRow(),3).getValues();


function onEdit(e){
  var activeCell = e.range;
  var val = activeCell.getValue();
  var r = activeCell.getRow();
  var c = activeCell.getColumn();
  var wsName = activeCell.getSheet().getName();
  if(wsName === mainWsName && c === firstLevelColumn && r > 1){
    applyFirstLevelValidation(val,r);
  }  else if(wsName === mainWsName && c === secondLevelColumn && r > 1){
    applySecondLevelValidation(val,r);    
  }
  
}  

function applyFirstLevelValidation(val,r){
    if(val === ""){
      ws.getRange(r, secondLevelColumn).clearContent();
      ws.getRange(r, secondLevelColumn).clearDataValidations();
      ws.getRange(r, thirdLevelColumn).clearContent();
      ws.getRange(r, thirdLevelColumn).clearDataValidations(); 
      
    }  else{
      ws.getRange(r, secondLevelColumn).clearContent();
      ws.getRange(r, secondLevelColumn).clearDataValidations();      
      ws.getRange(r, thirdLevelColumn).clearContent();      
      ws.getRange(r, thirdLevelColumn).clearDataValidations(); 
      var filteredData = data.filter(function(l){ return l[0] === val});
      var listToApply = filteredData.map(function(l){ return l[1]}) 
      var cell = ws.getRange(r, secondLevelColumn);
      applyValidationToCell (listToApply,cell);
    }  

} // end onEdit

function applySecondLevelValidation(val,r){
    if(val === ""){
      ws.getRange(r, thirdLevelColumn).clearContent();
      ws.getRange(r, thirdLevelColumn).clearDataValidations();
    }  else{
      ws.getRange(r, thirdLevelColumn).clearContent();
      var firstLevelColValue = ws.getRange(r, firstLevelColumn).getValue();
      var filteredLeyenda = data.filter(function(l){ return l[0] === firstLevelColValue && l[1] === val});
      var listToApply = filteredData.map(function(l){ return l[2]}) 
      var cell = ws.getRange(r, thirdLevelColumn);
      applyValidationToCell (listToApply,cell);
    }  

} // end onEdit

function applyValidationToCell (list,cell){

  var rule = SpreadsheetApp.
  newDataValidation()
  .requireValueInList(list)
  .setAllowInvalid(false)
  .build();
  
  cell.setDataValidation(rule);
  
  
}

Thanks!

解决方案

Multilevel Dependent Drop Down Script

This function uses named ranges to hold the various levels. One could easily extend it by using more named ranges. The possibilities are large.

The command buttons are checkboxes that trigger functions via an onEdit() function. There's one button to start another search and there's another button that allows you to select a range to clear the content and the data validations. The clear functionality could probably be made to be a bit more automated after I play with it some more.

You can comment out the e.source.toast() lines in the myOnEdit() function I just left them in there for debugging purposes.

gs:

function onOpen() {
  var ui=SpreadsheetApp.getUi();
  ui.createMenu('My Tools')
  .addItem('Create Named Range', 'createNamedRange')            
  .addToUi();
}

function searchInit() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Search');
  var drg=ss.getRangeByName('Departments');
  var rule=SpreadsheetApp.newDataValidation().requireValueInRange(drg).build();
  var h=getColumnHeight(3,sh,ss);
  var rg=sh.getRange(h+1,3);
  rg.setDataValidation(rule);
}

function getDropDown(pObj) {
  Logger.log(JSON.stringify(pObj));
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Search');
  var prg=ss.getRangeByName(pObj.value);
  if(!prg){
    ss.toast(Utilities.formatString('No additional data for current selection: %s', pObj.value), 'Status', 5);
    return;
  }
  var rule=SpreadsheetApp.newDataValidation().requireValueInRange(prg).build();
  var h=getColumnHeight(pObj.col,sh,ss);
  var rg=sh.getRange(pObj.row,pObj.col);
  rg.setDataValidation(rule);
}

function clearRange() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Search');
  var rg=sh.getRange(sh.getActiveRange().getRow(),3,1,sh.getLastColumn()-2);
  rg.clearContent();
  rg.setDataValidation(null);
  rg.deleteCells(SpreadsheetApp.Dimension.ROWS);
}

function launchClearDialog() {
  var html='<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script><link rel="stylesheet" href="//code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css"><script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>';
  html+='<style>input{margin:3px 2px 3px 0;}#clrrng{font-size:8px;width:120px;}</style>';
  html+='<input type="text" placeholder="Select Row on the Spreadsheet" id="clrrng" /><label for="clearrang">Range To Clear</label><br />';
  html+='<input type="button" value="Select Row" onClick="getSelectedRange();" /><br />';
  html+='<input type="button" value="Clear Selected Row" onClick="clearSelectedRange();" /><br />';
  html+='<input type="button" value="Exit" onClick="google.script.host.close();" /><br />';
  html+='<script>';
  html+='function clearSelectedRange(){var rg=$("#clrrng").val();google.script.run.withSuccessHandler(function(){google.script.host.close();}).clearRange(rg);}';
  html+='function getSelectedRange(){google.script.run.withSuccessHandler(function(rs){$("#clrrng").val(rs);}).getSelectedRange();}';
  html+='</script>';
  var ui=HtmlService.createHtmlOutput(html).setWidth(275).setHeight(150);
  SpreadsheetApp.getUi().showModelessDialog(ui, "Clear Selected Row");
}

function getSelectedRange() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('search');
  var rg=sh.getRange(sh.getActiveRange().getRow(),3,1,sh.getLastColumn()-2);
  rg.activate();
  return rg.getA1Notation();
}

function clearAll() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Search');
  var rg=sh.getRange(2,3,sh.getLastRow()-1,sh.getLastColumn()-2);
  rg.clear();
  rg.setDataValidation(null);
}

function clearLastRow() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Search');
  var rg=sh.getRange(getColumnHeight(3,sh,ss),3,1,sh.getLastColumn()-2);
  rg.clear();
  rg.setDataValidation(null);
}

This is an installable onEdit() function. Be sure to setup the trigger for it.

function onMyEdit(e) {
  e.source.toast('Entry');
  var sh=e.range.getSheet();
  if(sh.getName()!="Search")return;
  e.source.toast('Past Return');
  if(e.range.columnStart==1 && e.range.rowStart==2 && e.value=="TRUE") {
    e.source.toast('searchInit');
    e.range.setValue("FALSE");
    searchInit();
  }
  if(e.range.columnStart==1 && e.range.rowStart==3 && e.value=="TRUE") {
    e.source.toast('Row Clear');
    e.range.setValue("FALSE");
    launchClearDialog();
  }
  if(e.range.columnStart==1 && e.range.rowStart==4 && e.value=="TRUE") {
    e.source.toast('Clear All');
    e.range.setValue("FALSE");
    clearAll();
  }if(e.range.columnStart==1 && e.range.rowStart==5 && e.value=="TRUE") {
    e.source.toast('Clear Last Row');
    e.range.setValue("FALSE");
    clearLastRow();
  }
  if(e.range.columnStart==3 && e.range.rowStart>1 && e.value.length>0) {
    e.source.toast('Category');
    getDropDown({value:e.value,row:e.range.rowStart,col:e.range.columnStart+1});
  }
  if(e.range.columnStart==4 && e.range.rowStart>1 && e.value.length>0) {
    e.source.toast('Products');
    getDropDown({value:e.value,row:e.range.rowStart,col:e.range.columnStart+1});
  }
  if(e.range.columnStart==5 && e.range.rowStart>1 && e.value.length>0) {
    e.source.toast('Types');
    getDropDown({value:e.value,row:e.range.rowStart,col:e.range.columnStart+1});
  }
  if(e.range.columnStart==6 && e.range.rowStart>1 && e.value.length>0) {
    e.source.toast('Quantity');
    getDropDown({value:e.value,row:e.range.rowStart,col:e.range.columnStart+1});
  }
}

function getColumnHeight(col,sh,ss){
  var ss=ss || SpreadsheetApp.getActive();
  var sh=sh || ss.getActiveSheet();
  var col=col || sh.getActiveCell().getColumn();
  var rg=sh.getRange(1,col,sh.getLastRow(),1);
  var vA=rg.getValues();
  var dvA=rg.getDataValidations();
  Logger.log(vA);
  Logger.log(dvA);
  while(vA[vA.length-1][0].length==0 && !dvA[dvA.length-1][0]){
    vA.splice(vA.length-1,1);
    dvA.splice(dvA.length-1,1);
  }
  return vA.length;
}

This function is handy for creating named ranges. You can put the name of the range in one cell and then put all the range values in the cells immediately below it and then select all of them and run the function.

function createNamedRange() {
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var sht=ss.getActiveSheet();
  var rng=ss.getActiveRange();
  if(rng.getWidth()==1 && rng.getHeight()>1) {
    var rngA = rng.getValues();
    var name = rngA[0][0];
    var ro = rng.getRow() + 1;
    var co = rng.getColumn();
    var h = rng.getHeight()-1;
    var w = 1;
    var rg = sht.getRange(ro, co, h, 1);
    ss.setNamedRange(name, rg); 
    ss.toast(rg.getA1Notation(),name);
  }
}

Here's a screen shot of the Spreadsheet:

Here's a video of how it works:(not the current version)

这篇关于如何为4个多个从属下拉菜单创建脚本?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！