Parsing Gmail Email using Google Scripting and passing that to Google Sheets

Question

I have an email that is sent to me with data. I want to grab this data and put it in a Google Sheet. I have most of the code working the only problem is the regex area where I am not that proficient in.

Here is the HTML snipet of the email.

<table class="m_4348561758375603924m_886074067026015045MsoNormalTable" border="0" cellspacing="0" cellpadding="0" width="408" style="width:306.0pt;border-collapse:collapse">
<tbody>
    <tr style="height:31.5pt">
        <td width="196" nowrap style="width:147.0pt;border:solid windowtext 1.0pt;background:yellow;padding:0cm 5.4pt 0cm 5.4pt;height:31.5pt">
            <p class="MsoNormal" align="left" style="text-align:left"><b><span lang="EN-US" style="font-size:24.0pt;font-family:宋体;color:black">Date<u></u><u></u></span></b></p>
        </td>
        <td width="212" nowrap style="width:159.0pt;border:solid windowtext 1.0pt;border-left:none;background:yellow;padding:0cm 5.4pt 0cm 5.4pt;height:31.5pt">
            <p class="MsoNormal" align="left" style="text-align:left"><b><span lang="EN-US" style="font-size:24.0pt;font-family:宋体;color:black">Reset Code<u></u><u></u></span></b></p>
        </td>
    </tr>
    <tr style="height:31.5pt">
        <td width="196" nowrap style="width:147.0pt;border:solid windowtext 1.0pt;border-top:none;padding:0cm 5.4pt 0cm 5.4pt;height:31.5pt">
            <p class="MsoNormal" align="left" style="text-align:left"><strong><span lang="EN-US" style="font-size:24.0pt;font-family:宋体;color:black">2017.8.10<u></u><u></u></span></strong></p>
        </td>
        <td width="212" nowrap style="width:159.0pt;border-top:none;border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;padding:0cm 5.4pt 0cm 5.4pt;height:31.5pt">
            <p class="MsoNormal" align="left" style="text-align:left"><strong><span lang="EN-US" style="font-size:24.0pt;font-family:宋体;color:black">123456<u></u><u></u></span></strong></p>
        </td>
    </tr>
    <tr style="height:31.5pt">
        <td width="196" nowrap style="width:147.0pt;border:solid windowtext 1.0pt;border-top:none;padding:0cm 5.4pt 0cm 5.4pt;height:31.5pt">
            <p class="MsoNormal" align="left" style="text-align:left"><b><span lang="EN-US" style="font-size:24.0pt;font-family:宋体;color:black">2017.8.11<u></u><u></u></span></b></p>
        </td>
        <td width="212" nowrap style="width:159.0pt;border-top:none;border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;padding:0cm 5.4pt 0cm 5.4pt;height:31.5pt">
            <p class="MsoNormal" align="left" style="text-align:left"><strong><span lang="EN-US" style="font-size:24.0pt;font-family:宋体;color:black">123456<u></u><u></u></span></strong></p>
        </td>
    </tr>
</tbody>
</table>

The data that I need is the date which is 2017.8.10 and its code which is 123456. This is always 6 characters the dates are in pairs.

Here is my code for Google Script.

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Date Code')
      .addItem('Get Codes','DateCode')
      .addToUi();
}

function DateCode() {    
  var sheet = SpreadsheetApp.getActiveSheet();
  var label = GmailApp.getUserLabelByName("Inbox RC");
  var threads = label.getThreads();

  for (var i = 0; i < threads.length; i++) {      
    var tmp,
        message = threads[i].getMessages()[0],
        subject = message.getSubject(),
        content = message.getPlainBody();

    if (content) {    
      tmp = content.match(2017);
      var comment = (tmp && tmp[1]) ? tmp[1] : 'No Code Found!';

      sheet.appendRow([comment]);    
  }
}

Note* Inbox RC is the folder where this email is sent to by a filter on Gmail.

Where I need assistance is in the line tmp = content.match(2017); where 2017 would have the code that I need in order to get the code and date. I would extract two dates with their codes to two dif. fields. Which I can handle that later I just need to get the correct data after this script runs. Its picking up words and numbers but its not picking up what I want as it searches the entire folder Ibox RC.

score 0 · Answer 1 · answered Aug 10 '17 at 22:18

0

You just need a regex to match the date format that you know? How about something like this: http://regexr.com/3ghlh

answered Aug 10 '17 at 22:18

jas7457

1,712
13
21

What about the code? How do I go by getting the code? – Boba-Fett Aug 11 '17 at 12:05

score 0 · Answer 2 · answered Aug 11 '17 at 19:06

0

/>(\d{4}\.\d{1,2}\.\d{1,2})<|>(\d+)</g

https://regex101.com/r/qFHLGK/1

that's the regex - you'll have to split it into pairs after... How do you split an array into array pairs in JavaScript?

answered Aug 11 '17 at 19:06

jdubjdub

613
7
21

While going over your answer the Link helped a lot Bookmarked!. Now I know a little more about regex, enough to get my feet wet. Now to clean up my code and make sure I can capture the date and the code. Thank You! – Boba-Fett Aug 13 '17 at 02:47
I should say, Push the data into the correct columns. Google Makes it easy to code! – Boba-Fett Aug 13 '17 at 02:55
if this worked for you i'd appreciate you accepting the answer :) – jdubjdub Aug 14 '17 at 17:01

Parsing Gmail Email using Google Scripting and passing that to Google Sheets

2 Answers2