为多个选项卡和多行创建一个依赖下拉列表 [英] Create a dependent dropdown for multiple tabs and multiple rows

问题描述

//CREATE PRIMARY DROPDOWN LIST
function createPrimaryDrpdwon() {
 /* SET FOLLOWING VARIABLES */

   var dataSS           = "Lookup";         //Name of the sheet that contain data for dropdown lists
   var dropSS           = "Jun 15";         //Name of the sheet which dropdown list to be created
   var primaryDataRange = "A4:A14";       //Data range for primary dropdown
   var primaryDropRange = "H3:H200";       //Range which primary dropdown set

  var primaryDropList  = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(dataSS).getRange(primaryDataRange).getValues();

  var primaryDropRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(dropSS).getRange(primaryDropRange);

  var validationRule   = SpreadsheetApp.newDataValidation().requireValueInList(primaryDropList).build();

   primaryDropRange.setDataValidation(validationRule);
}

 
//CREATE SECONDARY DROPDOWN LIST
function onEdit(){
 /* SET FOLLOWING VARIABLES */
 var dataSS       = "Lookup";         //Name of the sheet that contain data for dropdown lists
 var dropSS       = "Jun 15";         //Name of the sheet which dropdown list to be created
 var allDataRange = "B5:C183";       //Data range for dropdown list (both primary and dependent)
 var primaryDDCol = 8;               //Column number of the primary drop down

  var dropSS_      = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(dropSS);
  var dropDData    = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(dataSS).getRange(allDataRange).getValues();

  var activeCell   = dropSS_.getActiveCell();

  var activeColumn = activeCell.getColumn();

  var activeRow    = activeCell.getRow();

  

  if(activeColumn==primaryDDCol){

    var dep_Col         = primaryDDCol+1;

    var dep_Row         = activeRow;

    var depCell         = dropSS_.getRange(dep_Row, dep_Col);

    var primarySelected = activeCell.getValue();

    var validationRule  = SpreadsheetApp.newDataValidation().requireValueInList(getDependentList(dropDData,primarySelected)).build();

    

    depCell.setDataValidation(validationRule);

  }

}

 

function getDependentList(dropDData,primarySelected){

  var dependenList = [];

  var j = 0;

  if(dropDData != null){

    for(i=0; i<dropDData.length; i++){

      if(dropDData[i][0]==primarySelected){

        dependenList[j] = dropDData[i][1];

        j++;

      }

    }

  }

  return dependenList;

}

我正在尝试编写一个脚本，该脚本将允许在工作簿中的多个工作表上进行依赖下拉菜单.我已经创建了一个脚本，可以在一张纸上工作，并且效果很好，但是例如，如果每个选项卡是一个月的一周中的"5-4-20"，"5-11"，则必须更改每个纸的名称.-20"，"5-18-20"等，但是在该周选项卡中收集了相同的信息，我如何为相同的依存下拉列表运行脚本，以使每个选项卡以相同的方式工作?

I am trying to write a script that will allow for dependent dropdowns on multiple sheets within the workbook. I have already created a script to work on one sheet and it works good but I have to change the name of the sheet for each for instance if each tab was a week of the month "5-4-20", "5-11-20", "5-18-20" etc. but the same information is collected in that week tab how can I run a script for the same dependent drop down to work the same way for every tab?

推荐答案

替换 var dropSS

的静态定义

第一种可能性:除 Lookup 外，在所有工作表上运行脚本 SpreadsheetApp.getActiveSpreadsheet().getSheets(); ，您可以检索所有可用的工作表并在所有工作表上运行脚本.

Replace the static definition of var dropSS

First possibility: run the script on all sheets apart from Lookup SpreadsheetApp.getActiveSpreadsheet().getSheets(); allows you to retrieve all available sheets and run the script on all of them.

示例:

function iterateThroughAllSheets(){
  var dataSS           = "Lookup";             
  var primaryDataRange = "A4:A14";       
  var primaryDropRange = "H3:H200";
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  for (var i = 0; i < sheets.length; i++){
    var dropSS = sheets[i].getName();
    if (dropSS != dataSS){
      // continue the rest of your code as before
      var primaryDropList  = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(dataSS).getRange(primaryDataRange).getValues();     
      var primaryDropRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(dropSS).getRange(primaryDropRange);      
      var validationRule   = SpreadsheetApp.newDataValidation().requireValueInList(primaryDropList).build();      
      primaryDropRange.setDataValidation(validationRule);
    }
  }
}

第二种可能性:仅在最新的表格上运行脚本

假设您的工作表将始终附加在右侧，则最新的工作表将具有最高的索引-对应于 sheets.length .

Assuming that your sheets will always be appended to the right, the newest sheet will have the highest index - corresponding to sheets.length.

示例:

function runOntheLastSheet(){
  var dataSS           = "Lookup";             
  var primaryDataRange = "A4:A14";       
  var primaryDropRange = "H3:H200";
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  var sheetNumber = sheets.length;
  var dropSS = sheets[sheetNumber-1].getName();  
  // continue the rest of your code as before
  var primaryDropList  = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(dataSS).getRange(primaryDataRange).getValues();     
  var primaryDropRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(dropSS).getRange(primaryDropRange);      
  var validationRule   = SpreadsheetApp.newDataValidation().requireValueInList(primaryDropList).build();      
  primaryDropRange.setDataValidation(validationRule); 
}

第三种可能性:在活动工作表上运行脚本

Third possibility: Run the script on the active sheet

定义 var dropSS = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName()并在您选择的工作表处于活动状态时运行脚本.

Define var dropSS = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName() and run the script when the sheet of your choice is active.

对于所有三种可能性，请修改 function onEdit()

这篇关于为多个选项卡和多行创建一个依赖下拉列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！