从邮件HTML表格填充Google Sheets失败求助

我不是程序员，自己研究把邮件里的内嵌HTML表格数据填充到多工作表的Google Spreadsheet时卡壳了。现在能复制并重命名表格，但数据填不进去——日志显示执行成功，但单元格还是空的。

邮件内嵌HTML表格示例 #

<table width="50%" border="0" cellspacing="0" cellpadding="10">
  <tbody>
    <tr>
      <td>Name</td>
      <td id="name">Testy McTesterson</td>
    </tr>
    <tr>
      <td>Date of Birth</td>
      <td id="dob">5-21-81</td>
    </tr>
    <tr>
      <td>Email</td>
      <td id="email">testy@testing.com</td>
    </tr>
    <tr>
      <td>Consent</td>
      <td id="consent">Yes</td>
    </tr>
  </tbody>
</table>

当前使用的Apps Script代码 #

// Define your mapping for email body table cell IDs to target spreadsheet sheets and cells
const MAPPING = {
  'Intake': {
    'B2': 'name', // Cell A1 in Sheet1 maps to the HTML table cell with id "cell1"
    'C2': 'dob', // Cell B1 in Sheet1 maps to the HTML table cell with id "cell2"
    'D2': 'email', // Cell C1 in Sheet1 maps to the HTML table cell with id "cell3"
  },
  'Consent': {
    'A2': 'consent', // Cell A1 in Sheet2 maps to the HTML table cell with id "cell4"
  }
};

   
    // Parse the email body and extract data
    const extractedData = parseEmailBody(emailBody);

    // Populate the new spreadsheet with the extracted data
    populateSpreadsheet(newSpreadsheet, extractedData);

   
// Parse the email body to extract data based on cell IDs
function parseEmailBody(emailBody) {
  const data = {};

  // Extract data from the HTML table
  const doc = XmlService.parse(emailBody);
  const rootElement = doc.getRootElement();
  const tableElements = rootElement.getDescendants(XmlService.getElementNameFilter('table'));

  tableElements.forEach(table => {
    const rows = table.getChildren('tr');
    rows.forEach(row => {
      const cells = row.getChildren('td');
      cells.forEach(cell => {
        const cellId = cell.getAttribute('id') ? cell.getAttribute('id').getValue() : '';
        const cellContent = cell.getValue().trim();
        if (cellId) {
          Object.keys(MAPPING).forEach(sheetName => {
            const sheetMapping = MAPPING[sheetName];
            Object.keys(sheetMapping).forEach(targetCell => {
              const targetId = sheetMapping[targetCell];
              if (cellId === targetId) {
                data[targetCell] = cellContent;
              }
            });
          });
        }
      });
    });
  });

  return data;
}

// Populate the new spreadsheet based on the extracted data
function populateSpreadsheet(spreadsheet, data) {
  Object.keys(MAPPING).forEach(sheetName => {
    const sheet = spreadsheet.getSheetByName(sheetName);
    if (sheet) {
      const mapping = MAPPING[sheetName];
      Object.keys(mapping).forEach(cellId => {
        const targetCell = mapping[cellId];
        const value = data[cellId];
        if (value !== undefined) {
          sheet.getRange(targetCell).setValue(value);
        }
      });
    }
  });
}

问题原因及修复方案 #

1. XmlService解析HTML的缺陷 #

XmlService要求严格的XML格式，但邮件中的HTML可能存在不规范（比如标签大小写、未闭合标签），而且原代码没有处理<tbody>标签——表格的<tr>是嵌套在<tbody>里的，直接调用table.getChildren('tr')会找不到行。

2. 填充函数的映射逻辑错误 #

在populateSpreadsheet里，遍历mapping时把键值搞反了：mapping的结构是{单元格位置: HTML单元格ID}，但代码里错误地把mapping[cellId]当成了目标单元格，实际上cellId本身就是目标单元格位置，应该直接用sheet.getRange(cellId)。

修复后的完整代码 #

const MAPPING = {
  'Intake': {
    'B2': 'name',
    'C2': 'dob',
    'D2': 'email',
  },
  'Consent': {
    'A2': 'consent',
  }
};

// 主执行逻辑（确保emailBody和newSpreadsheet已正确传入）
function processEmailToSheet(emailBody, newSpreadsheet) {
  const extractedData = parseEmailBody(emailBody);
  populateSpreadsheet(newSpreadsheet, extractedData);
}

function parseEmailBody(emailBody) {
  const data = {};
  // 先处理HTML，转为XmlService可解析的格式（处理tbody和标签大小写）
  const cleanedHtml = emailBody.replace(/<tbody>/gi, '<tbody>').replace(/<tr>/gi, '<tr>').replace(/<td>/gi, '<td>');
  
  try {
    const doc = XmlService.parse(cleanedHtml);
    const root = doc.getRootElement();
    
    // 找到所有table元素
    root.getDescendants().forEach(node => {
      if (node.getElement() && node.getElement().getName() === 'table') {
        const table = node.getElement();
        // 先找tbody，再找里面的tr
        const tbodies = table.getChildren('tbody');
        tbodies.forEach(tbody => {
          const rows = tbody.getChildren('tr');
          rows.forEach(row => {
            const cells = row.getChildren('td');
            cells.forEach(cell => {
              const cellIdAttr = cell.getAttribute('id');
              if (!cellIdAttr) return;
              
              const cellId = cellIdAttr.getValue().trim();
              const cellContent = cell.getValue()?.trim() || '';
              
              // 匹配MAPPING中的ID，记录对应的单元格位置
              Object.values(MAPPING).forEach(sheetMapping => {
                Object.entries(sheetMapping).forEach(([targetCell, targetId]) => {
                  if (cellId === targetId) {
                    data[targetCell] = cellContent;
                  }
                });
              });
            });
          });
        });
      }
    });
  } catch (e) {
    console.log('HTML解析错误:', e.message);
  }
  
  return data;
}

function populateSpreadsheet(spreadsheet, data) {
  Object.entries(MAPPING).forEach(([sheetName, sheetMapping]) => {
    const sheet = spreadsheet.getSheetByName(sheetName);
    if (!sheet) return;
    
    Object.keys(sheetMapping).forEach(targetCell => {
      const value = data[targetCell];
      if (value !== undefined) {
        sheet.getRange(targetCell).setValue(value);
      }
    });
  });
}

额外注意事项 #

• 确保emailBody是完整的HTML内容（如果邮件有其他内容，可能需要先提取出目标表格的HTML片段）
• 测试时可以在parseEmailBody里加console.log(data)，查看是否正确提取到数据
• 检查Google Spreadsheet中工作表名称（如Intake、Consent）是否和MAPPING中的完全一致（大小写敏感）

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