-2

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?).

TheMaster
  • 45,448
  • 6
  • 62
  • 85
dims
  • 9
  • 1
  • code and formulas are added now! – dims Jul 21 '20 at 18:13
  • If you add the sample text as in your cell to your question, it'll be easy to reproduce. Also sharing your spreadsheet exposes your email. Any way, prima facie, your text probably contains new lines(`\n`). To factor this into regex, you need to add [single line mode flag](https://github.com/google/re2/wiki/Syntax): `s`. `=QUERY(A1:A2,"SELECT * WHERE lower(A) MATCHES '(?s).*examp.*|.*forw.*'")`. If that works, let me know, I'll add a answer. – TheMaster Jul 21 '20 at 19:25
  • My text does contain new lines, but the flag does not work. I pasted the sample text, but there might be something more going on than just the text, hence the sheet link as well – dims Jul 21 '20 at 21:03
  • Does my alternative solution work? – TheMaster Jul 22 '20 at 06:43
  • Your sample text is in a code block, its hard to understand it that way; and the link to the sample sheet is missing too. Can you update the question? – Aerials Jul 23 '20 at 14:55
  • old edits of the question are like that/include link. seems different people want the question formatted different ways... – dims Jul 24 '20 at 22:27

1 Answers1

0

The text contains new lines(\n). To factor this into regex, you need to add single line mode flag: s, which makes . to include \n.

=QUERY(A1:A2,"SELECT * WHERE lower(A) MATCHES '(?s).*examp.*|.*forw.*'")

If that doesn't work, use REGEXREPLACE to remove \n before hand:

=ARRAYFORMULA(QUERY(REGEXREPLACE(A1:A2,"\n",),"SELECT * WHERE lower(Col1) MATCHES '.*examp.*|.*forw.*'"))
TheMaster
  • 45,448
  • 6
  • 62
  • 85
  • flag didn't work for me, and removing \n before hand is not preferred in my case for readability. I ended up using a filter with regexmatch instead of a query, which has its own limitations too – dims Jul 24 '20 at 22:29
  • @dims Fun fact: Query "match" is kindof local/ done in client side. Mobile apps will support flags. Once result is retrieved, they're uploaded to server and can be retrieved in desktop without rerunning the function. – TheMaster Jul 25 '20 at 05:20
  • @dims if you found a solution, please post it. – Aerials Jul 29 '20 at 15:06
  • no, no solution to my posted question about query match (I ended up not using query in my project) – dims Jul 30 '20 at 22:38