Grab html table from gmail and put in google sheets using google apps script

Question

I am newbie to google apps script. I am trying to write a google apps script that will take a table from gmail and put the data into google sheets.

The email body is somewhat like the following

CONFIRMATION CODE        GUEST'S NAME    GUEST'S EMAIL    GUEST'S PHONE
      A1                   Name          someone@gmail      012377777

So far I have tried the following the code to get the data in the email and push it to spreadsheet.

function myFunction() {

  var ss = SpreadsheetApp.getActiveSheet();

  var label = GmailApp.getUserLabelByName("[Gmail]/test");
  var threads = label.getThreads();

  for (var i=0; i<threads.length; i++)
  {
    var messages = threads[i].getMessages();

    for (var j=0; j<messages.length; j++)
    {
      var msg = messages[j].getBody();
      var sub = messages[j].getSubject();
      var dat = messages[j].getDate();

      //ss.appendRow([msg, sub, dat])
    }
     // threads[i].removeLabel(label);
  }
  
  }

messages[j].getBody() gives me the following html

<u></u>


    

<div>
<table cellpadding="3" style="font-size:.9em">
    <thead>
    
    <tr>
        
            <th>CONFIRMATION CODE</th>
        
            <th>GUEST&#39;S NAME</th>
        
            <th>GUEST&#39;S EMAIL</th>
        
            <th>GUEST&#39;S PHONE</th>
        
    </tr>
    </thead>
    <tbody>
    
    <tr>
        
        <td>
            
            A1
            
        </td>
        
        <td>
            
            Name
            
        </td>
        
        <td>
            
            someone@gmail 
            
        </td>
        
        <td>
            
            012377777
            
        </td>
        
    </tr>
    
    </tbody>
</table>
</div>

Next I am trying to parse this using the following

  var msg = threads[0].getMessages();
       
var rows = Xml.parse(msg[0].getBody(),true).getElement()
          .getElement("div")
          .getElement("table")
          .getElement("tbody")
          .getElements("tr");
  

But this throwing me the error "TypeError: Cannot call method "getElement" of null. " I do not have much experience with XML so please can you tell me what I am doing wrong here. Thanks :)

Answer 1

It looks like you're calling .getElement() without specifying the element you want it to get. Looking at the docs, XmlService seems to be the current implementation of the api so you might want to use the functions provided for it rather than the deprecated versions in the XML object. https://developers.google.com/apps-script/reference/xml-service/

It looks like you could do something along the lines of:

 var document = XmlService.parse(xml);
 var content = document.getAllContent();

which will return an array of elements.

Answer 2

You could use what Anders has suggested, but since the content of the email does not have root elements which is something XMLService mandates as part of parsing.

You will have to append root elements on both ends of the email content.

I would recommend doing always writing line 1 before sending it to the parse method

var xml = "<root>" + messageBody + "</root>"     
var document = XmlService.parse(xml);
var content = document.getAllContent();