I am not a programmer but I’ve been digging through the weeds to figure something out on my own and I’m stuck. I have a google spreadsheet with multiple sheets that I need to populate with content from an inline html table sent via email. I can get the spreadsheet duplicated and renamed. But I cannot get the data to populate. The log says it’s successful, but when I look at the sheet cells – it’s blank.

Here is a sample inline table sent to email (it would, of course, display as a table – not 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">[email protected]</td>
    </tr>
    <tr>
      <td>Consent</td>
      <td id="consent">Yes</td>
    </tr>
  </tbody>
</table>

Here is my current Apps Script for paring the email (as I understand it):

// 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);
        }
      });
    }
  });
}

I’ve rewritten the code, recreated my test tables, and run the script 20+ times… no go.