[試算表] 多層下拉式選單根據前一個選擇動態產生下一個資料驗證-指令編輯器 App Script-Google Sheets

下拉式選單就是利用資料驗證所設定出來的一種功能,根據上一次介紹的 [試算表] 資料驗證幫助輸入資料節省時間並統一輸入的內容 下拉式選單 日期選擇都不是問題,可以得知資料驗證是一個常使用試算表或是 Excel 的人一定要學起來的功能之一。而當試算表設計的架構越來越複雜時,就會發現簡單的資料驗證已經不符合需求,這時這篇文章可以幫助你一步一步的設計出更貼近需求的試算表。

之前所教的單一內容資料驗證是沒有任何條件,直接顯示當時所設定好的內容。這一次所要教的是可以依照當前選擇的內容,來動態決定下一層要顯示哪一個資料驗證內容。由於 Google Sheets 不像 Excel 可以在設定資料驗證的時候就使用 INDIRECT 來設定內容。所以 Google Sheets 的實做方式只能依賴 App Script 來實現功能,但千萬不要看到程式碼就退縮,其實一點都不難,一步一步做一定可以成功!

功能展示

在開始教學前來用個 GIF 展示一下成功後的效果,看一下多層下拉式選單的功能是怎麼樣。
可以看到 “尺寸” 會因為 “商品類型” 所選擇的內容而動態變資料驗證的內容。

設定命名範圍

下面的內容是這一次示範的每個項目的內容。

第一步,需要設定每個資料驗證的內容範圍,選擇要你設定內容的範圍後點擊右鍵定義已命名範圍

將你選擇的範圍命名

命名完成後的樣子,共命名四個項目

設定資料驗證

選擇你要設定資料驗證的儲存格

內容就直接寫剛剛所定義已命名範圍的名字

就會自動將你剛剛設定範圍的內容都帶進來

設定程式碼

接著來到重點,由於功能比較複雜就需要使用到指令程式碼來實做功能

下面是程式碼的主體

function depDrop_(range, sourceRange){
  var rule = SpreadsheetApp.newDataValidation().requireValueInRange(sourceRange, true).build();
  range.setDataValidation(rule);
}

function onEdit (){
  var aCell = SpreadsheetApp.getActiveSheet().getActiveCell();
  var aColumn = aCell.getColumn();
  
  // 只作用在某個資料表
  if(SpreadsheetApp.getActiveSheet().getName() == '銷售表'){
    if ((aColumn == 1 || aColumn == 2) && SpreadsheetApp.getActiveSheet()){
      var range = SpreadsheetApp.getActiveSheet().getRange(aCell.getRow(), aColumn + 1);
      var sourceRange = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(aCell.getValue());
      depDrop_(range, sourceRange);
    }
  }
}

舉幾個比較重要的程式碼來講解一下。

這行主要是限制動態下拉式選單只作用於哪一個工作表中,將銷售表更改為你要作用的工作表名稱即可。

if(SpreadsheetApp.getActiveSheet().getName() == '銷售表')

數字 1 代表作用於哪一欄,只要設定就自動監控一整欄。以我上面的範例來說,我要作用於 A、B 兩欄,所以程式碼裡面就是設定 1 跟 2

if ((aColumn == 1 || aColumn == 2) && SpreadsheetApp.getActiveSheet())

程式碼貼上後儲存功能就完成囉!

第三層的下拉式選單?

當我的下拉式選單需要到第三層的話該怎麼做呢?下面是衣服尺寸的第三層資料

只需要加第三欄上去就可以囉!

if ((aColumn == 1 || aColumn == 2 || aColumn == 3) && SpreadsheetApp.getActiveSheet())
function depDrop_(range, sourceRange){
  var rule = SpreadsheetApp.newDataValidation().requireValueInRange(sourceRange, true).build();
  range.setDataValidation(rule);
}

function onEdit (){
  var aCell = SpreadsheetApp.getActiveSheet().getActiveCell();
  var aColumn = aCell.getColumn();
  
  // 只作用在某個資料表
  if(SpreadsheetApp.getActiveSheet().getName() == '銷售表'){
    if ((aColumn == 1 || aColumn == 2 || aColumn == 3) && SpreadsheetApp.getActiveSheet()){
      var range = SpreadsheetApp.getActiveSheet().getRange(aCell.getRow(), aColumn + 1);
      var sourceRange = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(aCell.getValue());
      depDrop_(range, sourceRange);
    }
  }
}

整個試算表都作用

根據上面程式碼,有寫一些 if 來限制下拉式選單的作用範圍,一方面避免效能問題,一方面避免資料衝突。但當你想要全部的資料表都作用動態下拉式選單的話可以將 if 整個拿掉,或是視情況加入條件以符合自己的需求

function depDrop_(range, sourceRange){
  var rule = SpreadsheetApp.newDataValidation().requireValueInRange(sourceRange, true).build();
  range.setDataValidation(rule);
}

function onEdit (){
  var aCell = SpreadsheetApp.getActiveSheet().getActiveCell();
  var aColumn = aCell.getColumn();

  if (SpreadsheetApp.getActiveSheet()){
    var range = SpreadsheetApp.getActiveSheet().getRange(aCell.getRow(), aColumn + 1);
    var sourceRange = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(aCell.getValue());
    depDrop_(range, sourceRange);
  }
}

結語

多層下拉式選單比起單層來說效能會因為程式碼的關係有些延遲以及會需要使用到程式碼,所以在設定上對於新手會不太好上手。但對於複雜的試算表來說,這項功能可以讓整體事半功倍,我覺得非常值得學習。

在〈[試算表] 多層下拉式選單根據前一個選擇動態產生下一個資料驗證-指令編輯器 App Script-Google Sheets〉中有 2 則留言

  1. 您好,如果我希望除了資料參照表以外的其他分頁資料表都可以使用這個多層參照設定,請問該怎麼做?這個程式碼好像是設定只在某一個資料表使用

    回覆
    • 如果你想要全部都作用的話就把這行 if(SpreadsheetApp.getActiveSheet().getName() == '銷售表') 拿掉
      那如果你想要作用多個資料表的話,可以改成 if(SpreadsheetApp.getActiveSheet().getName() == '銷售表' || SpreadsheetApp.getActiveSheet().getName() == '產品分類') 這樣子。

      回覆

有話大聲說!