google-spreadsheet google試算表下拉式選單 - 您如何在Google表格中進行動態/相關下拉?




google表單多重下拉式選單 試算表格式 (5)

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

/**
 * "on edit" event handler
 *
 * Based on JavierCane's answer in 
 * 
 *   http://stackoverflow.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()

正如哈維爾所說:

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

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

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

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


在這裡,你有另一個基於@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. 為“根選擇器”的每個可能值在同一文檔中創建一個工作表。 必須將它們命名為值+指定的後綴。

請享用!


您可以從設置了主頁的Google工作表開始,然後下拉源頁面,如下所示。

您可以通過常規數據>驗證菜單提示設置第一列下拉菜單。

主頁

下拉源頁面

之後,您需要設置名為 onEdit的腳本。 (如果不使用該名稱,則getActiveRange()將不執行任何操作,只返回單元格A1)

並使用此處提供的代碼:

function onEdit() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSheet();
  var myRange = SpreadsheetApp.getActiveRange();
  var dvSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Categories");
  var option = new Array();
  var startCol = 0;

  if(sheet.getName() == "Front Page" && myRange.getColumn() == 1 && myRange.getRow() > 1){
    if(myRange.getValue() == "Category 1"){
      startCol = 1;
    } else if(myRange.getValue() == "Category 2"){
      startCol = 2;
    } else if(myRange.getValue() == "Category 3"){
      startCol = 3;
    } else if(myRange.getValue() == "Category 4"){
      startCol = 4;
    } else {
      startCol = 10
    }

  if(startCol > 0 && startCol < 10){
    option = dvSheet.getSheetValues(3,startCol,10,1);
    var dv = SpreadsheetApp.newDataValidation();
    dv.setAllowInvalid(false);  
    //dv.setHelpText("Some help text here");
    dv.requireValueInList(option, true);
    sheet.getRange(myRange.getRow(),myRange.getColumn() + 1).setDataValidation(dv.build());
   }

  if(startCol == 10){
    sheet.getRange(myRange.getRow(),myRange.getColumn() + 1).clearDataValidations();
  } 
  }
}

之後,通過轉到“編輯”>“當前項目觸發器”,在腳本編輯器屏幕中設置觸發器。 這將打開一個窗口,讓您選擇各種下拉菜單,最終結果如下:

你應該好好去追尋!


注意

腳本有一個限制:它在一個下拉列表中處理多達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

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


這與兩天前提出的問題非常相似,該問題涉及將事件電子表格與日曆同步。 聽起來你想要將電子表格視為它發起的事件的主人,這將大大簡化問題。 本答案涵蓋了您需要做的基本知識。 如果您只是修改現有代碼,我在下面有一個實現。

我有來自此博客的代碼的修改版本,它將修改預先存在的日曆條目以匹配電子表格中的信息。 我的電子表格安排不同,這反映在代碼中。

日期| 標題| 開始時間| 結束時間| 位置| 說明| 事件ID

創建新事件時,腳本會填充事件ID列,然後在以後的調用中使用該列從日曆中檢索事件,從而避免重複。

腳本

/**
 * Adds a custom menu to the active spreadsheet, containing a single menu item
 * for invoking the exportEvents() function.
 * The onOpen() function, when defined, is automatically invoked whenever the
 * spreadsheet is opened.
 * For more information on using the Spreadsheet API, see
 * https://developers.google.com/apps-script/service_spreadsheet
 */
function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "Export Events",
    functionName : "exportEvents"
  }];
  sheet.addMenu("Calendar Actions", entries);
};

/**
 * Export events from spreadsheet to calendar
 */
function exportEvents() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var headerRows = 1;  // Number of rows of header info (to skip)
  var range = sheet.getDataRange();
  var data = range.getValues();
  var calId = "YOUR_CALENDAR_ID";
  var cal = CalendarApp.getCalendarById(calId);
  for (i=0; i<data.length; i++) {
    if (i < headerRows) continue; // Skip header row(s)
    var row = data[i];
    var date = new Date(row[0]);  // First column
    var title = row[1];           // Second column
    var tstart = new Date(row[2]);
    tstart.setDate(date.getDate());
    tstart.setMonth(date.getMonth());
    tstart.setYear(date.getYear());
    var tstop = new Date(row[3]);
    tstop.setDate(date.getDate());
    tstop.setMonth(date.getMonth());
    tstop.setYear(date.getYear());
    var loc = row[4];
    var desc = row[5];
    var id = row[6];              // Sixth column == eventId
    // Check if event already exists, update it if it does
    try {
      var event = cal.getEventSeriesById(id);
    }
    catch (e) {
      // do nothing - we just want to avoid the exception when event doesn't exist
    }
    if (!event) {
      //cal.createEvent(title, new Date("March 3, 2010 08:00:00"), new Date("March 3, 2010 09:00:00"), {description:desc,location:loc});
      var newEvent = cal.createEvent(title, tstart, tstop, {description:desc,location:loc}).getId();
      row[6] = newEvent;  // Update the data array with event ID
    }
    else {
      event.setTitle(title);
      event.setDescription(desc);
      event.setLocation(loc);
      // event.setTime(tstart, tstop); // cannot setTime on eventSeries.
      // ... but we CAN set recurrence!
      var recurrence = CalendarApp.newRecurrence().addDailyRule().times(1);
      event.setRecurrence(recurrence, tstart, tstop);
    }
    debugger;
  }
  // Record all event IDs to spreadsheet
  range.setValues(data);
}

刪除/重新創建

在此替代方案中,eventID用於查找和刪除先前存在的事件。 之後,將使用電子表格中的數據創建新事件。 這樣做的好處是可以更新事件的所有值,包括開始和停止時間(請參閱下面的註釋)。 另一方面,對原始事件所做的任何更改都將丟失 - 例如,如果其他人被邀請參加活動,或者添加了自定義提醒。

要使用此替代方法,只需將匹配的代碼替換為:

// Check if event already exists, delete it if it does
try {
  var event = cal.getEventSeriesById(id);
  event.deleteEventSeries();
  row[6] = '';  // Remove event ID    
}
catch (e) {
  // do nothing - we just want to avoid the exception when event doesn't exist
}
//cal.createEvent(title, new Date("March 3, 2010 08:00:00"), new Date("March 3, 2010 09:00:00"), {description:desc,location:loc});
var newEvent = cal.createEvent(title, tstart, tstop, {description:desc,location:loc}).getId();
row[6] = newEvent;  // Update the data array with event ID
debugger;

筆記

  • getEventSeriesById的文檔錯誤地指出它在沒有找到匹配事件時返回null ,而是拋出異常。 (討厭!)所以我把它放在一個try / catch塊中,只是為了繼續游泳。
  • 不幸的是,當getEventSeriesById用於檢索事件時,它returns一個EventSeries對象,該對像不支持setTime()方法。 如果您不希望更改事件的時間,這沒關係。 否則,您可以通過設置重複規則和時間將Event更改為EventSeries ,或刪除舊事件並創建新事件,如刪除/重新創建中所示。 問題1154
  • 電子表格總是勝利。 通過Google日曆記錄的任何事件更改(在相關字段中)都將被腳本覆蓋。




google-spreadsheet validation