1

I'm trying to get an HTML table from the body of an email in Gmail into a Google Sheet and I'm getting an error on XmlService.parse method. I have read some questions here and the problem seems to be that the email is in Html and XmlService is trying to parse it as Xml.

Here's the script:

function myFunction() {
  var ss = SpreadsheetApp.openById('idhere');
  var updateSheet = ss.getSheetByName('Affiliate bookings');
  var threads = GmailApp.search("Daily report (affiliate bookings)");
  var message = threads[0].getMessages()[0];
  var body = message.getBody();
  
      console.log(body);

  var xml = XmlService.parse(body);

  updateSheet.getRange(1, 1, xml.length, xml[0].length).setValues(xml);
}

and here's the console.log(body)

<!-- sales_list -->
<table style="border:0 none;border-spacing:0;border-collapse: collapse;word-break:normal;">
    <tr style="background-color: #d1d1d1">
        <th style="padding:8px">ID</th>
        <th style="padding:8px">Commission</th>
        <th style="padding:8px">Total Cost</th>
        <th style="padding:8px">Order ID</th>
        <th style="padding:8px">Product ID</th>
        <th style="padding:8px">Created</th>
        <th style="padding:8px">Campaign name</th>
        <th style="padding:8px">Type</th>        
        <th style="padding:8px">Status</th>
        <th style="padding:8px">Paid</th>
        <th style="padding:8px">Affiliate</th>
        <th style="padding:8px">Channel</th>
    </tr>
            <tr>
            <td style="padding:8px">ny9kq352</td> 
            <td style="padding:8px">$ &lrm;30.00</td>
            <td style="padding:8px">$ &lrm;500.00</td>
            <td style="padding:8px">554683</td>
            <td style="padding:8px">Thursday, April 08, 2021 :: Half Day Trip (PM) @Size Matters Charters</td>
            <td style="padding:8px">04/07/2021</td>
            <td style="padding:8px">Direct Links for Approved Affiliates</td>
            <td style="padding:8px">Sales</td>            
            <td style="padding:8px">declined</td>
            <td style="padding:8px">Unpaid</td>
            <td style="padding:8px">Cathy  Sheehan</td>
            <td style="padding:8px"></td>                                 
        </tr>
            <tr>
            <td style="padding:8px">h4tdux7d</td> 
            <td style="padding:8px">$ &lrm;24.00</td>
            <td style="padding:8px">$ &lrm;400.00</td>
            <td style="padding:8px">553921</td>
            <td style="padding:8px">Friday, April 09, 2021 :: 4 Hour Trip (AM) @R&R Fishing Charters</td>
            <td style="padding:8px">04/07/2021</td>
            <td style="padding:8px">Direct Links for Approved Affiliates</td>
            <td style="padding:8px">Sales</td>            
            <td style="padding:8px">declined</td>
            <td style="padding:8px">Unpaid</td>
            <td style="padding:8px">Joanne Bergstrom</td>
            <td style="padding:8px"></td>                                 
        </tr>
            <tr>
            <td style="padding:8px">qj9cfp0g</td> 
            <td style="padding:8px">$ &lrm;24.00</td>
            <td style="padding:8px">$ &lrm;400.00</td>
            <td style="padding:8px">553921</td>
            <td style="padding:8px">Friday, April 09, 2021 :: 4 Hour Trip (AM) @R&R Fishing Charters</td>
            <td style="padding:8px">04/07/2021</td>
            <td style="padding:8px">Direct Links for Approved Affiliates</td>
            <td style="padding:8px">Sales</td>            
            <td style="padding:8px">Pending</td>
            <td style="padding:8px">Unpaid</td>
            <td style="padding:8px">Joanne Bergstrom</td>
            <td style="padding:8px"></td>                                 
        </tr>
            <tr>
            <td style="padding:8px">wujm6buw</td> 
            <td style="padding:8px">$ &lrm;39.00</td>
            <td style="padding:8px">$ &lrm;650.00</td>
            <td style="padding:8px">554032</td>
            <td style="padding:8px">Tuesday, July 27, 2021 :: Half Day Trip (PM) @All Hanns On Deck</td>
            <td style="padding:8px">04/06/2021</td>
            <td style="padding:8px">Direct Links for Approved Affiliates</td>
            <td style="padding:8px">Sales</td>            
            <td style="padding:8px">Pending</td>
            <td style="padding:8px">Unpaid</td>
            <td style="padding:8px">eric matechak</td>
            <td style="padding:8px"></td>                                 
        </tr>
            <tr>
            <td style="padding:8px">ixonoun4</td> 
            <td style="padding:8px">$ &lrm;28.50</td>
            <td style="padding:8px">$ &lrm;475.00</td>
            <td style="padding:8px">554003</td>
            <td style="padding:8px">Saturday, May 29, 2021 :: 4 Hour Trip (AM) @Fins Up Adventure Charters</td>
            <td style="padding:8px">04/06/2021</td>
            <td style="padding:8px">Direct Links for Approved Affiliates</td>
            <td style="padding:8px">Sales</td>            
            <td style="padding:8px">Pending</td>
            <td style="padding:8px">Unpaid</td>
            <td style="padding:8px">eric matechak</td>
            <td style="padding:8px"></td>                                 
        </tr>
    </table>

The error I'm getting is:

Exception: Error on line 19: The entity "lrm" was referenced, but not declared.

Tanaike
  • 181,128
  • 11
  • 97
  • 165
mare011
  • 117
  • 7
  • Why not just remove them? They have nothing to do with content. – Cooper Apr 08 '21 at 20:29
  • You might want to refer here: [How to parse an HTML string in Google Apps Script without using XmlService?](https://stackoverflow.com/questions/33893143/how-to-parse-an-html-string-in-google-apps-script-without-using-xmlservice). Please take note that XML,parse() was already deprecated. I tried this [answer](https://stackoverflow.com/a/35142822/14606046) but it gave me a string return which is not ideal in your goal. One option I could think of is to parse your html string manually – Ron M Apr 08 '21 at 22:44
  • @RonM how would you do it manually? Sorry, I'm new at apps script – mare011 Apr 08 '21 at 23:28
  • Did my answer show you the result what you want? Would you please tell me about it? That is also useful for me to study. If this works, other people who have the same issue with you can also base your question as a question which can be solved. If you have issues for my answer yet, I apologize. At that time, can I ask you about your current situation? I would like to study to solve your issues. – Tanaike Apr 13 '21 at 03:18
  • @Tanaike this solved my issue, now when I run the script the table is pasted into the sheet I chose. Thank you! – mare011 Apr 13 '21 at 12:25

1 Answers1

2

I believe your current situation and your goal as follows.

  • The value of body of var body = message.getBody() is the value shown at and here's the console.log(body) section.
    • The HTML table of var body = message.getBody() is surely the value from <!-- sales_list --> to </table>.
  • You want to put the HTML table to the Spreadsheet.

In this case, I would like to propose to use Sheets API. When Sheets API is used, the HTML table can be automatically parsed and put it to Google Spreadsheet. When this is reflected to your script, it becomes as follows.

Modified script:

Before you use this script, please enable Sheets API at Advanced Google services.

function myFunction() {
  var spreadsheetId = "###"; // Please set the Spreadsheet ID.
  var sheetName = "###"; // Please set the sheet name.

  var ss = SpreadsheetApp.openById(spreadsheetId);
  var updateSheet = ss.getSheetByName(sheetName);
  var body = `<!-- sales_list -->
<table style="border:0 none;border-spacing:0;border-collapse: collapse;word-break:normal;">
    <tr style="background-color: #d1d1d1">
        <th style="padding:8px">ID</th>
        <th style="padding:8px">Commission</th>
        <th style="padding:8px">Total Cost</th>
        <th style="padding:8px">Order ID</th>
        <th style="padding:8px">Product ID</th>
        <th style="padding:8px">Created</th>
        <th style="padding:8px">Campaign name</th>
        <th style="padding:8px">Type</th>        
        <th style="padding:8px">Status</th>
        <th style="padding:8px">Paid</th>
        <th style="padding:8px">Affiliate</th>
        <th style="padding:8px">Channel</th>
    </tr>
            <tr>
            <td style="padding:8px">ny9kq352</td> 
            <td style="padding:8px">$ &lrm;30.00</td>
            <td style="padding:8px">$ &lrm;500.00</td>
            <td style="padding:8px">554683</td>
            <td style="padding:8px">Thursday, April 08, 2021 :: Half Day Trip (PM) @Size Matters Charters</td>
            <td style="padding:8px">04/07/2021</td>
            <td style="padding:8px">Direct Links for Approved Affiliates</td>
            <td style="padding:8px">Sales</td>            
            <td style="padding:8px">declined</td>
            <td style="padding:8px">Unpaid</td>
            <td style="padding:8px">Cathy  Sheehan</td>
            <td style="padding:8px"></td>                                 
        </tr>
            <tr>
            <td style="padding:8px">h4tdux7d</td> 
            <td style="padding:8px">$ &lrm;24.00</td>
            <td style="padding:8px">$ &lrm;400.00</td>
            <td style="padding:8px">553921</td>
            <td style="padding:8px">Friday, April 09, 2021 :: 4 Hour Trip (AM) @R&R Fishing Charters</td>
            <td style="padding:8px">04/07/2021</td>
            <td style="padding:8px">Direct Links for Approved Affiliates</td>
            <td style="padding:8px">Sales</td>            
            <td style="padding:8px">declined</td>
            <td style="padding:8px">Unpaid</td>
            <td style="padding:8px">Joanne Bergstrom</td>
            <td style="padding:8px"></td>                                 
        </tr>
            <tr>
            <td style="padding:8px">qj9cfp0g</td> 
            <td style="padding:8px">$ &lrm;24.00</td>
            <td style="padding:8px">$ &lrm;400.00</td>
            <td style="padding:8px">553921</td>
            <td style="padding:8px">Friday, April 09, 2021 :: 4 Hour Trip (AM) @R&R Fishing Charters</td>
            <td style="padding:8px">04/07/2021</td>
            <td style="padding:8px">Direct Links for Approved Affiliates</td>
            <td style="padding:8px">Sales</td>            
            <td style="padding:8px">Pending</td>
            <td style="padding:8px">Unpaid</td>
            <td style="padding:8px">Joanne Bergstrom</td>
            <td style="padding:8px"></td>                                 
        </tr>
            <tr>
            <td style="padding:8px">wujm6buw</td> 
            <td style="padding:8px">$ &lrm;39.00</td>
            <td style="padding:8px">$ &lrm;650.00</td>
            <td style="padding:8px">554032</td>
            <td style="padding:8px">Tuesday, July 27, 2021 :: Half Day Trip (PM) @All Hanns On Deck</td>
            <td style="padding:8px">04/06/2021</td>
            <td style="padding:8px">Direct Links for Approved Affiliates</td>
            <td style="padding:8px">Sales</td>            
            <td style="padding:8px">Pending</td>
            <td style="padding:8px">Unpaid</td>
            <td style="padding:8px">eric matechak</td>
            <td style="padding:8px"></td>                                 
        </tr>
            <tr>
            <td style="padding:8px">ixonoun4</td> 
            <td style="padding:8px">$ &lrm;28.50</td>
            <td style="padding:8px">$ &lrm;475.00</td>
            <td style="padding:8px">554003</td>
            <td style="padding:8px">Saturday, May 29, 2021 :: 4 Hour Trip (AM) @Fins Up Adventure Charters</td>
            <td style="padding:8px">04/06/2021</td>
            <td style="padding:8px">Direct Links for Approved Affiliates</td>
            <td style="padding:8px">Sales</td>            
            <td style="padding:8px">Pending</td>
            <td style="padding:8px">Unpaid</td>
            <td style="padding:8px">eric matechak</td>
            <td style="padding:8px"></td>                                 
        </tr>
    </table>`;
  console.log(body);
  Sheets.Spreadsheets.batchUpdate({ requests: { pasteData: { html: true, data: body, coordinate: { sheetId: updateSheet.getSheetId(), rowIndex: 0, columnIndex: 0 } } } }, spreadsheetId);
}

Note:

  • At above modified script, the value of body shown in your question is directly used. When you want to use var body = message.getBody(), please use the following script. If the following script doesn't work, your sample value might be different from the actual body in the following script. At that time, can you provide the sample value of body for replicating the issue? By this, I would like to confirm it.

      function myFunction() {
    var spreadsheetId = "###"; // Please set the Spreadsheet ID.
    var sheetName = "###"; // Please set the sheet name.

    var ss = SpreadsheetApp.openById(spreadsheetId);
    var updateSheet = ss.getSheetByName(sheetName);
    var threads = GmailApp.search("Daily report (affiliate bookings)");
    var message = threads[0].getMessages()[0];
    var body = message.getBody();
    console.log(body);
    Sheets.Spreadsheets.batchUpdate({ requests: { pasteData: { html: true, data: body, coordinate: { sheetId: updateSheet.getSheetId(), rowIndex: 0, columnIndex: 0 } } } }, spreadsheetId);
  }

References:

Tanaike
  • 181,128
  • 11
  • 97
  • 165