google-spreadsheet - sheet下拉式選單顏色 - 試算表格式




您如何在Google表格中進行動態/相關下拉? (4)

如何根據google工作表中主類別下拉列表中選擇的值來獲取子類別列以填充下拉列表?

我google了,找不到任何好的解決方案,因此我想分享自己的。 請參閱下面的答案。


注意

腳本有一個限制:它在一個下拉列表中處理多達500個值。

新腳本。 201801

該腳本於2018年1月發布。請參閱:

  1. 帶有說明和演示的主頁 ,您可以在其中提出問題。
  2. 帶有說明和源代碼的GitHub頁面

改進:

  1. 加速
  2. 處理1張表中的多個規則
  3. 將其他工作錶鍊接為源數據。
  4. 下拉列表的自定義列順序

舊腳本。 <201801

腳本的版本

  1. v.1
  2. 第2節。 2016-03 改進:適用於任何類別的重複項。 例如,如果我們有list1與汽車模型和list2與顏色。 顏色可以在任何模型中重複。
  3. V3。 2017-01 。 改進:輸入唯一值時無錯誤。
  4. 最新版本 :2018-02。 請參閱此處的文章

這個解決方案並不完美,但它帶來了一些好處:

  1. 讓你製作多個下拉列表
  2. 提供更多控制
  3. 源數據放在唯一的工作表上,因此編輯起來很簡單

首先,這是一個工作示例 ,因此您可以在進一步測試之前進行測試。

我的計劃:

  1. 準備數據
  2. 像往常一樣製作第一個列表: Data > Validation
  3. 添加腳本,設置一些變量
  4. 完成!

準備數據

數據看起來像一個表,其中包含所有可能的變體。 它必須位於單獨的工作表上,因此腳本可以使用它。 看看這個例子:

這裡我們有四個級別,每個值重複。 請注意,數據右側有2列保留,因此請勿鍵入/粘貼任何數據。

第一個簡單的數據驗證(DV)

準備一個唯一值列表。 在我們的示例中,它是行星列表。 使用數據查找工作表上的可用空間,並粘貼公式: =unique(A:A)在主菜單上選擇第一列,DV將從此處開始。 轉到數據>驗證,然後選擇具有唯一列表的範圍。

腳本

將此代碼粘貼到腳本編輯器中:

function SmartDataValidation(event) 
{
  //--------------------------------------------------------------------------------------
  // The event handler, adds data validation for the input parameters
  //--------------------------------------------------------------------------------------
  
  
  // Declare some variables:
  //--------------------------------------------------------------------------------------
  var TargetSheet = 'Main' // name of the sheet where you want to verify the data
  var LogSheet = 'Data' // name of the sheet with information
  var NumOfLevels = 4 // number of associated drop-down list levels
  var lcol = 2; // number of the leftmost column, in which the changes are checked; A = 1, B = 2, etc.
  var lrow = 2; // line number from which the rule will be valid
  //--------------------------------------------------------------------------------------
  
  //	===================================   key variables	 =================================
  //
  //		ss			sheet we change (TargetSheet)
  //			br				range to change
  //			scol			number of column to edit
  //			srow			number of row to edit	
  //			CurrentLevel	level of drop-down, which we change
  //			HeadLevel		main level
  //			r				current cell, which was changed by user
  //			X         		number of levels could be checked on the right
  //
  //		ls			Data sheet (LogSheet)
  //
  //    ======================================================================================
  
  
  // [ 01 ].Track sheet on which an event occurs
  var ts = event.source.getActiveSheet();
  var sname = ts.getName();
  
  if (sname == TargetSheet) 
  {
    
    // ss -- is the current book
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    
    // [ 02 ]. If the sheet name is the same, you do business...
    var ls = ss.getSheetByName(LogSheet); // data sheet
    
    // [ 03 ]. Determine the level
    
    //-------------- The changing sheet --------------------------------
    var br = event.source.getActiveRange();
    var scol = br.getColumn(); // the column number in which the change is made
    var srow = br.getRow() // line number in which the change is made
    // Test if column fits
    if (scol >= lcol) 
    {
      // Test if row fits
      if (srow >= lrow) 
      {  
        var CurrentLevel = scol-lcol+2;
        // adjust the level to size of
        // range that was changed
        var ColNum = br.getLastColumn() - scol + 1;
        CurrentLevel = CurrentLevel + ColNum - 1; 
        
        // also need to adjust the range 'br'
        if (ColNum > 1) 
        {
          br = br.offset(0,ColNum-1);
        } // wide range
        
        var HeadLevel = CurrentLevel - 1; // main level
        
        // split rows
        var RowNum = br.getLastRow() - srow + 1;
        
        var X = NumOfLevels - CurrentLevel + 1;

        
        // the current level should not exceed the number of levels, or 
        // we go beyond the desired range
        if (CurrentLevel <= NumOfLevels )	
        {
          // determine columns on the sheet "Data"
          var KudaCol = NumOfLevels + 2
          var KudaNado = ls.getRange(1, KudaCol);
          var lastRow = ls.getLastRow(); // get the address of the last cell
          var ChtoNado = ls.getRange(1, KudaCol, lastRow, KudaCol);

          // ============================================================================= > loop >				
          for (var j = 1; j <= RowNum; j++)
          {		
            for (var k = 1; k <= X; k++) 
            {
               
              HeadLevel = HeadLevel + k - 1; // adjust parent level
              CurrentLevel = CurrentLevel + k - 1; // adjust current level
              
              var r = br.getCell(j,1).offset(0,k-1,1);
              var SearchText = r.getValue(); // searched text

              // if anything is choosen!
              if (SearchText != '') 
              {
                
                //-------------------------------------------------------------------
                
                // [ 04 ]. define variables to costumize data
                // for future data validation
                //--------------- Sheet with data --------------------------           
                // combine formula 
                // repetitive parts
                var IndCodePart = 'INDIRECT("R1C' + HeadLevel + ':R' + lastRow + 'C';
                IndCodePart = IndCodePart + HeadLevel + '",0)';
                // the formula
                var code = '=UNIQUE(INDIRECT("R" & MATCH("';
                code = code + SearchText + '",';
                code = code + IndCodePart;
                code = code + ',0) & "C" & "' + CurrentLevel
                code = code + '" & ":" & "R" & COUNTIF(';
                code = code + IndCodePart;   
                code = code + ',"' + SearchText + '") + MATCH("';
                code = code + SearchText + '";';
                code = code + IndCodePart;
                code = code + ',0) - 1'; 
                code = code + '& "C" & "' ;   
                code = code + CurrentLevel + '",0))';
                // Got it! Now we have to paste formula
                
                KudaNado.setFormulaR1C1(code);   
                // get required array
                var values = [];
                for (var i = 1; i <= lastRow; i++) 
                {
                  var currentValue = ChtoNado.getCell(i,1).getValue();
                  if (currentValue != '') 
                  { 
                    values.push(currentValue);
                  } 
                  else 
                  {
                    var Variants = i-1; // number of possible values
                    i = lastRow; // exit loop
                  }       
                }
                //-------------------------------------------------------------------
                
                // [ 05 ]. Build daya validation rule
                var cell = r.offset(0,1);
                var rule = SpreadsheetApp
                .newDataValidation()
                .requireValueInList(values, true)
                .setAllowInvalid(false)
                .build();
                cell.setDataValidation(rule); 
                if (Variants == 1) 
                {
                  cell.setValue(KudaNado.getValue());		
                } // the only value
                else
                {
                  k = X+1;
                } // stop the loop through columns
                
                
              } // not blanc cell
              else
              {
                // kill extra data validation if there were 
                // columns on the right
                if (CurrentLevel <= NumOfLevels ) 
                {
                  for (var i = 1; i <= NumOfLevels; i++) 
                  {
                    var cell = r.offset(0,i);
                    // clean
                    cell.clear({contentsOnly: true});
                    // get rid of validation
                    cell.clear({validationsOnly: true});
                  }
                } // correct level
              } // empty row
            } // loop by cols
          } // loop by rows
          // ============================================================================= < loop <	
          
        } // wrong level
        
      } // rows
    } // columns... 
  } // main sheet
}

function onEdit(event) 
{
  
  SmartDataValidation(event);
  
}

以下是要更改的變量集,您可以在腳本中找到它們:

  var TargetSheet = 'Main' // name of the sheet where you want to verify the data
  var LogSheet = 'Data' // name of the sheet with information
  var NumOfLevels = 4 // number of associated drop-down list levels
  var lcol = 2; // leftmost column, in which the changes are checked; A = 1, B = 2, etc.
  var lrow = 2; // line number from which the rule will be valid

我建議每個熟悉腳本的人都會將您的編輯內容髮送給此代碼。 我想,有更簡單的方法來查找驗證列表並使腳本運行得更快。


在這裡,你有另一個基於@tarheel提供的解決方案

function onEdit() {
    var sheetWithNestedSelectsName = "Sitemap";
    var columnWithNestedSelectsRoot = 1;
    var sheetWithOptionPossibleValuesSuffix = "TabSections";

    var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    var activeSheet = SpreadsheetApp.getActiveSheet();

    // If we're not in the sheet with nested selects, exit!
    if ( activeSheet.getName() != sheetWithNestedSelectsName ) {
        return;
    }

    var activeCell = SpreadsheetApp.getActiveRange();

    // If we're not in the root column or a content row, exit!
    if ( activeCell.getColumn() != columnWithNestedSelectsRoot || activeCell.getRow() < 2 ) {
        return;
    }

    var sheetWithActiveOptionPossibleValues = activeSpreadsheet.getSheetByName( activeCell.getValue() + sheetWithOptionPossibleValuesSuffix );

    // Get all possible values
    var activeOptionPossibleValues = sheetWithActiveOptionPossibleValues.getSheetValues( 1, 1, -1, 1 );

    var possibleValuesValidation = SpreadsheetApp.newDataValidation();
    possibleValuesValidation.setAllowInvalid( false );
    possibleValuesValidation.requireValueInList( activeOptionPossibleValues, true );

    activeSheet.getRange( activeCell.getRow(), activeCell.getColumn() + 1 ).setDataValidation( possibleValuesValidation.build() );
}

它比其他方法有一些好處:

  • 每次添加“根選項”時都不需要編輯腳本。 您只需使用此根選項的嵌套選項創建新工作表。
  • 我重構了腳本,為變量提供了更多的語義名稱,等等。 此外,我已經為變量提取了一些參數,以便更容易適應您的具體情況。 您只需設置前3個值。
  • 嵌套選項值沒有限制(我使用帶有-1值的getSheetValues方法)。

那麼,如何使用它:

  1. 創建您將擁有嵌套選擇器的工作表
  2. 轉到“工具”>“腳本編輯器...”,然後選擇“空白項目”選項
  3. 粘貼此答案附帶的代碼
  4. 修改腳本的前3個變量,設置值並保存
  5. 為“根選擇器”的每個可能值在同一文檔中創建一個工作表。 必須將它們命名為值+指定的後綴。

請享用!


編輯:下面的答案可能令人滿意,但它有一些缺點:

  1. 腳本的運行有明顯的暫停。 我的延遲時間為160毫秒,這足以令人煩惱。

  2. 它通過在每次編輯給定行時構建新範圍來工作。 這在某些時候給先前的條目提供了“無效內容”

我希望其他人可以稍微清理一下。

這是另一種方法,它可以節省大量的範圍命名:

工作表中的三個工作表:將它們稱為Main,List和DRange(對於動態範圍。)在主工作表上,第1列包含時間戳。 此時間戳在編輯時修改。

在列表中,您的類別和子類別將作為簡單列表排列。 我在我的樹林中使用它作為工廠庫存,所以我的列表如下所示:

Group   | Genus | Bot_Name
Conifer | Abies | Abies balsamea
Conifer | Abies | Abies concolor
Conifer | Abies | Abies lasiocarpa var bifolia
Conifer | Pinus | Pinus ponderosa
Conifer | Pinus | Pinus sylvestris
Conifer | Pinus | Pinus banksiana
Conifer | Pinus | Pinus cembra
Conifer | Picea | Picea pungens
Conifer | Picea | Picea glauca
Deciduous | Acer | Acer ginnala
Deciduous | Acer | Acer negundo
Deciduous | Salix | Salix discolor
Deciduous | Salix | Salix fragilis
...

哪裡| 表示分成列。
為方便起見,我還使用標題作為命名範圍的名稱。

DRrange A1具有公式

=Max(Main!A2:A1000)

這將返回最新的時間戳。

A2到A4有以下變化:

=vlookup($A$1,Inventory!$A$1:$E$1000,2,False) 

將每個單元格的2遞增到右邊。

在運行A2到A4時,將具有當前選定的組,屬和種類。

下面是每個這樣的過濾器命令,如下所示:

=唯一的(過濾器(Bot_Name,REGEXMATCH(Bot_Name,C1)))

這些過濾器將填充下面的塊,其中包含與頂部單元格內容匹配的條目。

可以修改過濾器以滿足您的需要以及列表的格式。

返回Main:使用DRange中的範圍完成Main中的數據驗證。

我使用的腳本:

function onEdit(event) {

  //SETTINGS
  var dynamicSheet='DRange'; //sheet where the dynamic range lives
  var tsheet = 'Main'; //the sheet you are monitoring for edits
  var lcol = 2; //left-most column number you are monitoring; A=1, B=2 etc
  var rcol = 5; //right-most column number you are monitoring
  var tcol = 1; //column number in which you wish to populate the timestamp
  //

  var s = event.source.getActiveSheet();
  var sname = s.getName();
  if (sname == tsheet) {
    var r = event.source.getActiveRange();
    var scol = r.getColumn();  //scol is the column number of the edited cell
    if (scol >= lcol && scol <= rcol) {
      s.getRange(r.getRow(), tcol).setValue(new Date());
      for(var looper=scol+1; looper<=rcol; looper++) {
         s.getRange(r.getRow(),looper).setValue(""); //After edit clear the entries to the right
      }
    }
  }
}

原始的Youtube演示文稿給了我大部分onEdit時間戳組件: https://www.youtube.com/watch?v=RDK8rjdE85Yhttps://www.youtube.com/watch?v=RDK8rjdE85Y RDK8rjdE85Y


繼續這個解決方案的發展我通過添加對多個根選擇和更深層嵌套選擇的支持來提高賭注。 這是JavierCane解決方案的進一步發展(反過來建立在tarheel的基礎上)。

/**
 * "on edit" event handler
 *
 * Based on JavierCane's answer in 
 * 
 *   http://.com/questions/21744547/how-do-you-do-dynamic-dependent-drop-downs-in-google-sheets
 *
 * Each set of options has it own sheet named after the option. The 
 * values in this sheet are used to populate the drop-down.
 *
 * The top row is assumed to be a header.
 *
 * The sub-category column is assumed to be the next column to the right.
 *
 * If there are no sub-categories the next column along is cleared in 
 * case the previous selection did have options.
 */

function onEdit() {

  var NESTED_SELECTS_SHEET_NAME = "Sitemap"
  var NESTED_SELECTS_ROOT_COLUMN = 1
  var SUB_CATEGORY_COLUMN = NESTED_SELECTS_ROOT_COLUMN + 1
  var NUMBER_OF_ROOT_OPTION_CELLS = 3
  var OPTION_POSSIBLE_VALUES_SHEET_SUFFIX = ""
  
  var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  var activeSheet = SpreadsheetApp.getActiveSheet()
  
  if (activeSheet.getName() !== NESTED_SELECTS_SHEET_NAME) {
  
    // Not in the sheet with nested selects, exit!
    return
  }
  
  var activeCell = SpreadsheetApp.getActiveRange()
  
  // Top row is the header
  if (activeCell.getColumn() > SUB_CATEGORY_COLUMN || 
      activeCell.getRow() === 1 ||
      activeCell.getRow() > NUMBER_OF_ROOT_OPTION_CELLS + 1) {

    // Out of selection range, exit!
    return
  }
  
  var sheetWithActiveOptionPossibleValues = activeSpreadsheet
    .getSheetByName(activeCell.getValue() + OPTION_POSSIBLE_VALUES_SHEET_SUFFIX)
  
  if (sheetWithActiveOptionPossibleValues === null) {
  
    // There are no further options for this value, so clear out any old
    // values
    activeSheet
      .getRange(activeCell.getRow(), activeCell.getColumn() + 1)
      .clearDataValidations()
      .clearContent()
      
    return
  }
  
  // Get all possible values
  var activeOptionPossibleValues = sheetWithActiveOptionPossibleValues
    .getSheetValues(1, 1, -1, 1)
  
  var possibleValuesValidation = SpreadsheetApp.newDataValidation()
  possibleValuesValidation.setAllowInvalid(false)
  possibleValuesValidation.requireValueInList(activeOptionPossibleValues, true)
  
  activeSheet
    .getRange(activeCell.getRow(), activeCell.getColumn() + 1)
    .setDataValidation(possibleValuesValidation.build())
    
} // onEdit()

正如哈維爾所說:

  • 創建您將擁有嵌套選擇器的工作表
  • 轉到“工具”>“腳本編輯器...”,然後選擇“空白項目”選項
  • 粘貼此答案附帶的代碼
  • 修改腳本頂部的常量設置值並保存
  • 為“根選擇器”的每個可能值在同一文檔中創建一個工作表。 必須將它們命名為值+指定的後綴。

如果你想看到它的實際應用,我已經創建了一個演示表 ,你可以看到代碼,如果你拿一份副本。