I have email body text in a column (populated using Google apps script getPlainBody() in my script), and I am trying to query it for a match. Because I will have several OR and AND statements using contains, I would rather use match. However, match does not work on the email body text, but if I copy the text from the formula box and then paste it into another cell, match works on the pasted cell. Match also works on the populated email subject, so it seems to just be an issue with the getPlainBody() text. The apps script code (get subject and body of two emails labeled test/test2) and then the queries are basically as below:
function emailTest() {
var ss=SpreadsheetApp.getActiveSpreadsheet();
var sh = ss.getSheetByName('test sheet').activate();
var destArray=new Array();
//My gmail has two emails labeled test/test2
var labelObject=GmailApp.getUserLabelByName('test/test2');
var threads = labelObject.getThreads(0, 30);
for (var n in threads) {
var msg = threads[n].getMessages();
var destArrayRow = new Array();
destArrayRow.push(msg[0].getSubject());
var entireThread=msg[msg.length-1].getPlainBody();
destArrayRow.push(entireThread);
destArray.push(destArrayRow);
}
range = sh.getRange(1, 1, 2,2).setValues(destArray);
}
Sample text and queries:
example also with
attachments
---------- Forwarded message ---------
example also with attachments ---------- Forwarded message ---------
=QUERY(A1:A2,"SELECT * WHERE lower(A) MATCHES '.*examp.*|.*forw.*'")
=QUERY(A1:A2,"SELECT * WHERE lower(A) CONTAINS 'examp' OR lower(A) CONTAINS 'forw'")
I would appreciate any help either changing the text through Google Sheets methods (I have already tried FORMULATEXT and TO_TEXT), or in Apps Script (perhaps the range must be formatted a certain way afterwards?).