如何解决Google Sheets表格列下拉菜单选项无法获取的问题？

问题根源 #

Google Sheets里的结构化表格（即插入的带表头Table），下拉菜单的验证规则是绑定在表格列上，而非单个单元格。你原来的函数仅读取单个单元格的验证规则，所以表格内的单元格会返回null。

修复后的自定义函数 #

修改原函数，增加表格列验证规则的获取逻辑，同时兼容普通单元格和表格列两种场景：

/**
* @OnlyCurrentDoc
*/
function GET_DROPDOWN_OPTIONS(cell_reference_str) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  let sheetName, cellName;
  
  // 解析单元格引用，区分是否指定工作表
  if (cell_reference_str.includes('!')) {
    [sheetName, cellName] = cell_reference_str.split('!');
  } else {
    sheetName = ss.getActiveSheet().getName();
    cellName = cell_reference_str;
  }

  const actualSheet = ss.getSheetByName(sheetName);
  if (!actualSheet) throw new Error(`工作表不存在: ${sheetName}`);

  const cell = actualSheet.getRange(cellName);
  let dataValidation = cell.getDataValidation();

  // 如果单元格本身无验证规则，检查是否属于结构化表格
  if (!dataValidation) {
    const tables = actualSheet.getTables();
    for (const table of tables) {
      const tableRange = table.getRange();
      // 判断目标单元格是否在表格范围内
      const isInTable = cell.getRow() >= tableRange.getRow() && 
                        cell.getRow() <= tableRange.getLastRow() &&
                        cell.getColumn() >= tableRange.getColumn() && 
                        cell.getColumn() <= tableRange.getLastColumn();
      
      if (isInTable) {
        // 计算单元格在表格中的相对列索引（从0开始）
        const columnInTable = cell.getColumn() - tableRange.getColumn();
        // 获取表格列的验证规则
        const tableColumn = table.getColumn(columnInTable);
        dataValidation = tableColumn.getDataValidation();
        break;
      }
    }
  }

  // 处理验证规则并返回结果
  if (dataValidation) {
    const criteriaType = dataValidation.getCriteriaType();
    const criteriaValues = dataValidation.getCriteriaValues()[0];
    
    if (criteriaType === SpreadsheetApp.DataValidationCriteria.VALUE_IN_LIST) {
      return criteriaValues.map(option => [option]);
    } else if (criteriaType === SpreadsheetApp.DataValidationCriteria.VALUE_IN_RANGE) {
      return criteriaValues.getValues().flat().map(option => [option]);
    }
  }

  return [[""]];
}

核心修改点 #

• 新增表格检测逻辑：遍历当前工作表的所有表格，判断目标单元格是否属于某个表格范围
• 若单元格在表格内，直接获取对应表格列的验证规则，替代单个单元格的规则读取
• 保留原有的普通单元格验证逻辑，两种场景均能正常工作

使用说明 #

调用方式和原函数完全一致：

• 获取当前工作表A1单元格（无论是否在表格内）的下拉选项：=GET_DROPDOWN_OPTIONS("A1")
• 获取Sheet2中B3单元格的下拉选项：=GET_DROPDOWN_OPTIONS("Sheet2!B3")

内容的提问来源于stack exchange，提问作者debsim