I'm using the following referenced here: https://developers.google.com/apps-script/reference/xml-service to convert my HTML emails to XML, having trouble inserting it to google sheets.
// Create and log an XML representation of the threads in your Gmail inbox.
function createXml() {
var ss = SpreadsheetApp.openById('1_mUz_byvewuIN-pToQArGjanK9IZQISxM9l5EogIbxU');
var root = XmlService.createElement('threads');
var label = GmailApp.getUserLabelByName("Catering");
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 child = XmlService.createElement('thread')
.setAttribute('messageCount', threads[i].getMessageCount())
.setAttribute('isUnread', threads[i].isUnread())
.setText(threads[i].getFirstMessageSubject())
.setText(msg);
root.addContent(child);
}
var document = XmlService.createDocument(root);
var xml = XmlService.getPrettyFormat().format(document);
var parsed = XmlService.parse(xml);
ss.appendRow([parsed])
}
It throws the following:
[Document: No DOCTYPE declaration, Root is [Element: <threads/>]]
Looking for guidance on setting the DOCTYPE, I do not have access to change the body of the emails.
The following is the HTML email i'm trying to process:
--===============3318764831337248603==
MIME-Version: 1.0
Content-Type: text/html; charset="us-ascii"
Content-Transfer-Encoding: 7bit
<html>
<head>
</head>
<body style="font-family:arial, sans-serif; background-image:none" id="EmailC">
<div class="c">
<div class="cn-mailer">
<div class="middle-content admin-users order-list-details order-view">
<div class="middle-headline">
<h1 style="background-color:#efefef; background-image:none; border:1px solid #CCC; font-family:arial; padding:3px 10px" bgcolor="#efefef">
Order <span>#70306391</span>
</h1>
</div>
<div class="two-columns">
<div class="right-middle form">
<h2 style="background-color:#efefef; background-image:none; border:1px solid #CCC; font-family:arial; padding:3px 10px" bgcolor="#efefef">
Restaurant Details
</h2>
<div class="content">
<div class="left" style="float:left; font-family:arial; padding:3px 5px; width:184px" width="184">
<p>
<label style="font-weight:bold">Restaurant Name</label>
<br>
<span>Catering</span>
</p>
</div>
<div class="left" style="float:left; font-family:arial; padding:3px 5px; width:184px" width="184">
<p>
<label style="font-weight:bold">Restaurant Phone Number</label>
<br>
<span>236-2305</span>
</p>
</div>
<div class="clear" style="clear:both"></div>
<div class="left" style="float:left; font-family:arial; padding:3px 5px; width:184px" width="184">
<p>
<label style="font-weight:bold">Restaurant Address</label>
<br>
<span>
1120 S. Michigan Ave
<br>
Chicago,
IL
60605
</span>
</p>
</div>
<div class="clear" style="clear:both"></div>
</div>
</div>
</div>
<div class="two-columns">
<div class="right-middle form">
<h2 style="background-color:#efefef; background-image:none; border:1px solid #CCC; font-family:arial; padding:3px 10px" bgcolor="#efefef">
Customer Details
</h2>
<div class="content">
<div class="left" style="float:left; font-family:arial; padding:3px 5px; width:184px" width="184">
<p>
<label style="font-weight:bold">Time of Order</label>
<br>
<span>Wed, May 27 02:25 PM</span>
<br>
<span>May 27, 2020</span>
</p>
</div>
<div class="left" style="float:left; font-family:arial; padding:3px 5px; width:184px" width="184">
<p>
<label style="font-weight:bold">Requested Pickup Time</label><br>
<span>Sat, May 30 08:45 AM</span>
</p>
</div>
<div class="clear" style="clear:both"></div>
<div class="left" style="float:left; font-family:arial; padding:3px 5px; width:184px" width="184">
<p>
<label style="font-weight:bold">Customer Name</label><br>
<span>Gianluca Pesce</span>
</p>
</div>
<div class="left" style="float:left; font-family:arial; padding:3px 5px; width:184px" width="184">
<p>
<label style="font-weight:bold">Customer Phone Number</label><br>
<span>(313) 478-2671</span>
</p>
</div>
<div class="clear" style="clear:both"></div>
<div>
<p>
<span>
</span>
</p>
</div>
<div class="clear" style="clear:both"></div>
<div class="left" style="float:left; font-family:arial; padding:3px 5px; width:184px" width="184">
<p>
<label style="font-weight:bold">Order Type</label><br>
<span>
Pickup
(Order for later)
</span>
</p>
</div>
<div class="clear" style="clear:both"></div>
</div>
</div>
</div>
<div class="two-columns">
<div class="right-middle form top-margin details">
<h2 style="background-color:#efefef; background-image:none; border:1px solid #CCC; font-family:arial; padding:3px 10px" bgcolor="#efefef">
Order Details
</h2>
<div class="content">
<ul style="font-family:arial;background-image: none">
<div class="item">
<div class="customer">
<h4>Gianluca Pesce</h4>
</div>
<table max-width: cellpadding="5" cellspacing="0">
<tr class="order_item">
<td>
<strong id="ite_iq">3</strong>
</td>
<td style='"text-align:left;min-width:150px;"'>
<strong>*Disposable Items</strong>
</td>
<td></td>
<td></td>
</tr>
<tr>
<td></td>
<td style='"text-align:left;min-width:150px;"'>Set of 10</td>
<td style='"text-align:right;"'>$5.00</td>
<td>ea.</td>
</tr>
<tr>
<td></td>
<td style='"text-align:left;min-width:150px;"' colspan="2">
* <b><u>CUSTOMER REQUESTED: pickup on Sat, May 30 at 8:45 AM</u></b>
</td>
</tr>
<tr class="order_item">
<td>
<strong id="ite_iq">1</strong>
</td>
<td style='"text-align:left;min-width:150px;"'>
<strong>*Coffee Box</strong>
</td>
<td></td>
</tr>
<tr>
<td></td>
<td style='"text-align:left;min-width:150px;"'>Regular</td>
<td style='"text-align:right;"'>$20.00</td>
</tr>
<tr>
<td></td>
<td style='"text-align:left;min-width:150px;"'><b>Coffee Choice</b></td>
</tr>
<tr>
<td></td>
<td style='"text-align:left;min-width:150px;"'>Regular</td>
<td style='"text-align:right;"'> - </td>
</tr>
<tr>
<td></td>
<td style='"text-align:left;min-width:150px;"'><b>Coffee Goodies</b></td>
</tr>
<tr>
<td></td>
<td style='"text-align:left;min-width:150px;"'>10 Coffee 12 oz Cups/Stir Sticks, 30 Creamers, 20 Sugars, 10 Truvia</td>
<td style='"text-align:right;"'> - </td>
</tr>
<tr class="order_item">
<td>
<strong id="ite_iq">1</strong>
</td>
<td style='"text-align:left;min-width:150px;"'>
<strong>*Fresh Cut Fruit</strong>
</td>
<td></td>
</tr>
<tr>
<td></td>
<td style='"text-align:left;min-width:150px;"'>Regular</td>
<td style='"text-align:right;"'>$32.00</td>
</tr>
<tr>
<td></td>
<td style='"text-align:left;min-width:150px;"'><b>Tongs</b></td>
</tr>
<tr>
<td></td>
<td style='"text-align:left;min-width:150px;"'>No</td>
<td style='"text-align:right;"'> - </td>
</tr>
<tr class="order_item">
<td>
<strong id="ite_iq">1</strong>
</td>
<td style='"text-align:left;min-width:150px;"'>
<strong>*Cinnamon Roll French Toast</strong>
</td>
<td></td>
</tr>
<tr>
<td></td>
<td style='"text-align:left;min-width:150px;"'>Regular</td>
<td style='"text-align:right;"'>$44.00</td>
</tr>
<tr>
<td></td>
<td style='"text-align:left;min-width:150px;"'><b>Sweet Specialty Goodies</b></td>
</tr>
<tr>
<td></td>
<td style='"text-align:left;min-width:150px;"'>1 tong, 10 butter, 1 12oz bottle syrup</td>
<td style='"text-align:right;"'> - </td>
</tr>
<tr class="order_item">
<td>
<strong id="ite_iq">1</strong>
</td>
<td style='"text-align:left;min-width:150px;"'>
<strong>*Wraps</strong>
</td>
<td></td>
</tr>
<tr>
<td></td>
<td style='"text-align:left;min-width:150px;"'>Regular</td>
<td style='"text-align:right;"'>$45.00</td>
</tr>
<tr>
<td></td>
<td style='"text-align:left;min-width:150px;"'><b>CYO - Ingredients</b></td>
</tr>
<tr>
<td></td>
<td style='"text-align:left;min-width:150px;"'>Broccoli</td>
<td style='"text-align:right;"'> - </td>
</tr>
<tr>
<td></td>
<td style='"text-align:left;min-width:150px;"'>spinach</td>
<td style='"text-align:right;"'> - </td>
</tr>
<tr>
<td></td>
<td style='"text-align:left;min-width:150px;"'>Sausage</td>
<td style='"text-align:right;"'> - </td>
</tr>
<tr>
<td></td>
<td style='"text-align:left;min-width:150px;"'><b>CYO - Additional Ingredients</b></td>
</tr>
<tr>
<td></td>
<td style='"text-align:left;min-width:150px;"'>Caramelized onion</td>
<td style='"text-align:right;"'>$5.00</td>
</tr>
<tr>
<td></td>
<td style='"text-align:left;min-width:150px;"'>Swiss cheese</td>
<td style='"text-align:right;"'>$5.00</td>
</tr>
<tr>
<td></td>
<td style='"text-align:left;min-width:150px;"'><b>Wrap Condiments</b></td>
</tr>
<tr>
<td></td>
<td style='"text-align:left;min-width:150px;"'>Small cup of Salas, small cup of Sour Cream, 2 small spoons</td>
<td style='"text-align:right;"'> - </td>
</tr>
<tr>
<td></td>
<td style='"text-align:left;min-width:150px;"'><b>Tongs</b></td>
</tr>
<tr>
<td></td>
<td style='"text-align:left;min-width:150px;"'>Yes</td>
<td style='"text-align:right;"'> - </td>
</tr>
<tr class="order_item">
<td>
<strong id="ite_iq">1</strong>
</td>
<td style='"text-align:left;min-width:150px;"'>
<strong>*Buffalo Wrap</strong>
</td>
<td></td>
</tr>
<tr>
<td></td>
<td style='"text-align:left;min-width:150px;"'>Regular</td>
<td style='"text-align:right;"'>$60.00</td>
</tr>
<tr>
<td></td>
<td style='"text-align:left;min-width:150px;"'><b>Tongs</b></td>
</tr>
<tr>
<td></td>
<td style='"text-align:left;min-width:150px;"'>No</td>
<td style='"text-align:right;"'> - </td>
</tr>
<tr>
<td>--</td>
<td style='"text-align:left;min-width:150px;"'><strong>10% off orders over $0.50,
all day every day,
for a limited time only,
once per customer</strong></td>
<td>($22.60)</td>
</tr>
</table>
<div style="width:400px;border-bottom:1px solid #444;margin:10px;"></div>
<table cellpadding="5" cellspacing="0">
<tr>
<td style='"text-align:left;min-width:150px;"'>Item total:</td>
<td style='"text-align:right;"'>$226.00</td>
</tr>
<tr>
<td style='"text-align:left;min-width:150px;"'>Discounts:</td>
<td style='"text-align:right;"'>-$22.60</td>
</tr>
<tr>
<td style='"text-align:left;min-width:150px;"'>Sub-total:</td>
<td style='"text-align:right;"'>$203.40</td>
</tr>
<tr>
<td style='"text-align:left;min-width:150px;"'>Taxes/fees:</td>
<td style='"text-align:right;"'>$23.90</td>
</tr>
<tr>
<td style='"text-align:left;min-width:150px;"'>Tip/gratuity:</td>
<td style='"text-align:right;"'>CASH TIP</td>
</tr>
<tr class="grand_total">
<td style='"text-align:left;min-width:150px;"'><strong>Grand Total:</strong></td>
<td style='"text-align:right;"'><strong>$227.30</strong></td>
</tr>
</table>
</div>
</ul>
</div>
</div>
<div class="clear" style="clear:both"></div>
</div>
<div class="clear" style="clear:both"></div>
</div>
</div>
</div>
</body>
</html>
--===============3318764831337248603==--
Here is the Execution Transcript:
[20-05-29 10:16:19:810 CDT] Starting execution
[20-05-29 10:16:19:975 CDT] SpreadsheetApp.openById([1_mUz_byvewuIN-pToQArGjanK9IZQISxM9l5EogIbxU]) [0.157 seconds]
[20-05-29 10:16:20:283 CDT] GmailApp.getUserLabelByName([Catering]) [0.307 seconds]
[20-05-29 10:16:20:450 CDT] GmailApp.GmailLabel.getThreads() [0.166 seconds]
[20-05-29 10:16:20:608 CDT] GmailApp.GmailThread.getMessages() [0.158 seconds]
[20-05-29 10:16:20:770 CDT] GmailApp.GmailMessage.getBody() [0.161 seconds]
[20-05-29 10:16:20:770 CDT] GmailApp.GmailThread.getMessageCount() [0 seconds]
[20-05-29 10:16:20:771 CDT] GmailApp.GmailThread.isUnread() [0 seconds]
[20-05-29 10:16:20:771 CDT] GmailApp.GmailThread.getFirstMessageSubject() [0 seconds]
[20-05-29 10:16:20:931 CDT] GmailApp.GmailThread.getMessages() [0.159 seconds]
[20-05-29 10:16:21:083 CDT] GmailApp.GmailMessage.getBody() [0.151 seconds]
[20-05-29 10:16:21:083 CDT] GmailApp.GmailThread.getMessageCount() [0 seconds]
[20-05-29 10:16:21:084 CDT] GmailApp.GmailThread.isUnread() [0 seconds]
[20-05-29 10:16:21:084 CDT] GmailApp.GmailThread.getFirstMessageSubject() [0 seconds]
[20-05-29 10:16:21:253 CDT] SpreadsheetApp.Spreadsheet.appendRow([[[Document: No DOCTYPE declaration, Root is [Element: <threads/>]]]]) [0.149 seconds]
[20-05-29 10:16:21:398 CDT] Execution succeeded [1.438 seconds total runtime]
"Looking for guidance on setting the DOCTYPE, I do not have access to change the body of the emails."
[Document: No DOCTYPE declaration, Root is [Element: ]]
If you want to declare a DocType for the newly created Xml document you can do so by using the appropriate method(s) ( setDocType(), createDocType() )
Once you declare the DocType though, you'll still have to work on your parsing because all you will append with the current code is the same string, but now with a DocType declared ;)
function createXml() {
// Get sheet
var ss = SpreadsheetApp.getActiveSheet();
// Create Xml document with root element "threads"
var doc = XmlService.createDocument(XmlService.createElement('threads'));
// Declare DocType "whatever-you-like" for the Xml document
doc.setDocType(XmlService.createDocType('threads'));
// Get the root element
var root = doc.getRootElement();
// Get some threads from Gmail
var threads = GmailApp.getStarredThreads();
// For each thread
for (var i = 0; i < threads.length; i++) {
// Get messages
var messages = threads[i].getMessages();
// And for each message
for (var j=0; j<messages.length; j++){
// Get the plain html body
var msg = messages[j].getPlainBody();
// Create a child element "thread"
var child = XmlService.createElement('thread')
// Set "messageCount" attr
.setAttribute('messageCount', threads[i].getMessageCount())
// Set "isUnread" attr
.setAttribute('isUnread', threads[i].isUnread())
// Set text attr
.setText(threads[i].getFirstMessageSubject()+msg);
// Add the child element to root
root.addContent(child);
}
// Get prettyfied xml document
var xml = XmlService.getPrettyFormat().format(doc);
// Log the prettyfied xml doc
Logger.log(xml);
// Create list of parsed children to append to sheet row
var parsed = [];
// Add the parsed text from children elements of root
XmlService.parse(xml).getRootElement().getChildren().forEach((child) => {parsed.push(child.getText())});
// Append the row with parsed data
ss.appendRow(parsed)
}
}