如何用Google Apps Script将XHTML格式.xls转为标准GSheet并提取数据？

核心思路 #

跳过手动转XLSX的步骤，直接处理Google Drive转换后的GSheet中A列的XHTML文本：先定位目标数据的起止区间，再解析每行的<TR><TD>结构，最终转换成标准行数组[[cell1, cell2,...],...]并写入目标工作表。

完整代码实现 #

function extractTargetTableData() {
  // 配置参数
  const sourceSheetId = "你的转换后GSheet文件ID";
  const sourceSheetName = "转换后的工作表名称（通常是Sheet1）";
  const targetSheetId = "目标工作表文件ID";
  const targetSheetName = "目标工作表名称";
  const startMarker = '<NOBR>Some header</NOBR>';
  const endMarkerPattern = /TOTAL \(Qtys: \d+\)/; // 匹配TOTAL (Qtys: 数字)格式的结束标识

  // 获取源工作表数据
  const sourceSheet = SpreadsheetApp.openById(sourceSheetId).getSheetByName(sourceSheetName);
  const columnAData = sourceSheet.getRange(1, 1, sourceSheet.getLastRow()).getValues().flat(); // 转成一维数组

  // 定位起始和结束行索引
  let startIndex = -1;
  let endIndex = -1;
  for (let i = 0; i < columnAData.length; i++) {
    const cellText = columnAData[i];
    if (cellText.includes(startMarker) && startIndex === -1) {
      startIndex = i + 1; // 下一行开始是数据行
    }
    if (endMarkerPattern.test(cellText) && endIndex === -1) {
      endIndex = i; // 当前行是结束行，不包含
      break;
    }
  }

  if (startIndex === -1 || endIndex === -1) {
    throw new Error("未找到目标数据的起始或结束标识");
  }

  // 提取目标区间的XHTML行并解析
  const targetRows = [];
  for (let i = startIndex; i < endIndex; i++) {
    const rowHtml = columnAData[i];
    // 解析TR中的TD内容，用正则提取所有TD标签内的文本
    const tdMatches = rowHtml.match(/<TD[^>]*>(.*?)<\/TD>/g);
    if (!tdMatches) continue; // 跳过空行或无效行
    const rowCells = tdMatches.map(td => {
      // 去除TD标签，同时清理可能的HTML标签（如NOBR）
      return td.replace(/<[^>]+>/g, "").trim();
    });
    targetRows.push(rowCells);
  }

  // 将解析后的行数组写入目标工作表
  const targetSheet = SpreadsheetApp.openById(targetSheetId).getSheetByName(targetSheetName);
  // 清空目标工作表原有数据（可选）
  targetSheet.clearContents();
  // 写入数据
  if (targetRows.length > 0) {
    targetSheet.getRange(1, 1, targetRows.length, targetRows[0].length).setValues(targetRows);
  }

  Logger.log(`成功提取${targetRows.length}行数据`);
}

关键细节说明 #

• 标识定位：用循环遍历A列数据，精准匹配起始标识和正则匹配动态的结束标识（处理TOTAL (Qtys: xxx)中数字变化的情况）
• XHTML解析：通过正则表达式提取<TD>标签内的文本，同时去除嵌套的HTML标签（如<NOBR>），确保单元格内容为纯文本
• 数据写入：直接将解析后的行数组通过setValues()批量写入，保证处理效率

替代方案：直接解析原始XLS文件（跳过Drive转换） #

如果Drive转换后的XHTML格式不稳定，也可以直接读取原始XLS文件的二进制内容，用第三方库解析：

// 示例：直接解析原始XLS文件（需先将文件上传到Drive并获取可访问链接）
function parseRawXLS() {
  const xlsFileUrl = "原始XLS文件的可下载链接";
  const response = UrlFetchApp.fetch(xlsFileUrl);
  const blob = response.getBlob();
  
  // 加载xlsx库（需先在脚本编辑器中添加库：项目ID 1ReeQ6WO8kKNxoaA_O0XEQ589cIrRvEBA9qcWpNqdOP17i47u6N9M5Xh0）
  const workbook = XLSX.read(blob.getBytes(), {type: 'array'});
  const secondSheetName = workbook.SheetNames[1]; // 获取第二个表格
  const sheetData = XLSX.utils.sheet_to_json(workbook.Sheets[secondSheetName], {header: 1}); // 转成行数组
  
  // 后续处理sheetData即可
  Logger.log(sheetData);
}

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