0

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.

1

1 Answer 1

0

The code has several problems.

  1. emailBody variable has not been declared.
    • It's unclear how the HTML shown in the question is got and passed to the emailBody variable.
  2. populateSpreadsheet is called in the top level / global scope using a parameter thas has not been declared and another having problems. This might cause problems when calling this function from other places. As this function has parameters that are required, besides fixing the problems already mentioned you need to declare a function that initializes the parameters to be used to call this function.

Considering the above, you should take several steps back and learn the basics of JavaScript and Google Apps Script. Please start by studying the official docs: https://developers.google.com/apps-script. The Samples section has several types of samples, including pointers to videos.

Sign up to request clarification or add additional context in comments.

1 Comment

Thank you. I appreciate your input. But - I'm merely a designer LOL. I guess I'll hire someone to fix this up.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.