Google Apps Script无法读取Google Docs表格下拉框的解决方案咨询

原脚本使用cell.getText()只能提取普通文本内容，而Google Docs中的下拉框属于**列表项（ListItem）**元素，需要单独处理才能获取选中值。下面是修改后的完整脚本，核心是在读取单元格内容时增加对下拉框的判断逻辑：

function combineTablesFromDocuments() {
  var folderId = "1KW8OPJr9D9qkJI2C5Zea5WbX6iPAwYOz"; // 目标文件夹ID
  var sheetName = "Sheet1"; // 汇总表格名称

  var folder = DriveApp.getFolderById(folderId);
  var files = folder.getFilesByType(MimeType.GOOGLE_DOCS);
  var combinedData = [];

  while (files.hasNext()) {
    var file = files.next();
    var document = DocumentApp.openById(file.getId());
    var tables = document.getBody().getTables();
    var fileName = file.getName();

    for (var i = 0; i < tables.length; i++) {
      var table = tables[i];
      var tableData = tableTo2DArray(table, fileName);
      combinedData = combinedData.concat(tableData);
    }

    document.saveAndClose();
  }

  combinedData = combinedData.filter(row => row.length === 4 && row.some(cell => cell !== ''));

  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var combinedSheet = spreadsheet.getSheetByName(sheetName);

  if (!combinedSheet) {
    combinedSheet = spreadsheet.insertSheet(sheetName);
  } else {
    combinedSheet.clear();
  }

  var headerRow = ["File Name", "Date", "Type", "Note"];
  combinedData.unshift(headerRow);

  var range = combinedSheet.getRange(1, 1, combinedData.length, 4);
  range.setValues(combinedData);
}

function tableTo2DArray(table, fileName) {
  var tableData = [];

  for (var i = 1; i < table.getNumRows(); i++) {
    var rowData = [fileName];
    var row = table.getRow(i);
    var isEmptyRow = true;

    for (var j = 0; j < table.getRow(i).getNumCells(); j++) {
      var cell = row.getCell(j);
      var cellContent = "";

      // 检查单元格是否包含下拉列表项
      var listItems = cell.getListItems();
      if (listItems.length > 0) {
        // 遍历列表项，找到选中的那一个
        for (var k = 0; k < listItems.length; k++) {
          var item = listItems[k];
          if (item.isSelected()) {
            cellContent = item.getText();
            break;
          }
        }
      } else {
        // 无下拉框时，读取普通文本
        cellContent = cell.getText();
      }

      rowData.push(cellContent);

      if (cellContent !== '') {
        isEmptyRow = false;
      }
    }

    if (!isEmptyRow) {
      tableData.push(rowData);
    }
  }

  return tableData;
}

关键修改说明： #

1. 下拉框检测：通过cell.getListItems()获取单元格内的所有下拉选项，若返回数组长度大于0，说明是下拉框单元格
2. 选中值提取：遍历下拉选项，通过item.isSelected()判断是否为选中状态，获取其文本内容
3. 兼容原有逻辑：非下拉框单元格仍保留原有的cell.getText()读取方式，不影响普通文本内容的提取

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