Automatically Plot Data from Emails

My wife and I are buying a house with an unusually long delay before the closing date. This was actually beneficial because we could track the interest rates over time and lock in that rate when we felt we had the best value. Unfortunately, our bank’s arcane way of notifying us of the current interest rate was with a daily email. This post will explain how to use Google Sheets to automatically parse specific emails in Gmail and plot the results.

Example of an interest rate email from our bank

Let’s start out by creating a new spreadsheet on Google Sheets. Under the menu bar item Tools, select Script editor. We will add our code to the code.gs file that opens in Google Scripts. The first function we will add creates a new menu item in the spreadsheet that will allow us to manually run our scripts. This functionality is not strictly necessary for the end product, but is useful for debugging. The below code creates a menu bar item called Macros and adds an item called Parse email that calls the function interest (which we’re about to write).

function menu(e) {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Macros')
      .addItem('Parse email', 'interest')
      .addToUi();
}

The new Parse email item in the menu bar

Next we’ll create the interest() function that was just mentioned. This function starts by assigning the current sheet as the sheet that will be modified. Next, we search for all emails in our Gmail with the subject “Dollar Bank rate watch notification”. The asterisk at the end is a wildcard so it could match if the emails have extra data such as a date like “Interest rate 9/26/18”. Next up is the for loop which is used to view every message with this subject and push them to the array a the plain text body and date of the message. parseMail() is a custom function and will be explained next. The remaining lines explain where to put the parsed data in the spreadsheet. My spreadsheet retains the headings on the first two rows and places the data starting on the following row.

function interest() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getActiveSheet();
  var threads = GmailApp.search('subject: "Dollar Bank rate watch notification*"');
  var a=[];
  for (var i = 0; i < threads.length; i++) {
    var messages = GmailApp.getMessagesForThread(threads[i]);
    for (var j = 0; j < messages.length; j++) {
      a.push(parseMail(messages[j].getPlainBody(), messages[j].getDate()));
    }
  }
  // start at row 3, overwrite old data
  var nextRow=3;
  var numRows=a.length;
  var numCols=a[0].length;
  s.getRange(nextRow,1,numRows,numCols).setValues(a);
}

Finally we have the parseMail() function. This is the part will change the most since it depends on the format of the particular email. Google Scripts allows breakpoints by clicking on the line number and I used this feature a lot to look at the raw data and transform it accordingly. An example of the email I based my script on was shown above. In my script, I split the email into lines, and later divided each line into words by splitting on spaces. I store the important data (dates, rates, values) into an array called rates which is returned from this function. I start by adding the date which is actually the date on which the email is delivered. Then I loop through the three lines with the interest rate (starting at line 7). The 4th word is the percent, the 6th word is the discount points, and the 10th word is rebate amount.

function parseMail(body, date) {
  // split based on newlines
  var lines = body.split("\n");
  
  // hold lines split by spaces
  var lines_split = [];
  
  // hold important data
  var rates = [];
  
  rates.push(date);
  
  for (var i = 0; i < 3; i++)
  {
    lines_split[i] = lines[i+7].split(" ");
    rates.push(lines_split[i][4]);
    rates.push(lines_split[i][6]);
    rates.push(lines_split[i][10]);
  }
  
  return rates;
}

The full version of this code is located at this gist: https://gist.github.com/dwaq/03b556940e56a069bb555bdaf1ab0b75

Now that all the code is written, you’ll need to authorize access to your Gmail account. You can select the interest function in the toolbar and hit the Run icon. A window will pop up where you have to approve access to your account. A scary-looking screen will appear saying that the app isn’t verified, but if you hit the Advanced link at the bottom, you can proceed to authorize the project.

If everything was correct, you can go back to the Sheet and see that the data has been imported into the spreadsheet. The next thing that we’ll do is add triggers so that the data is automatically imported each time the spreadsheet is opened. In the Google Script, click Edit, then Current project’s triggers. Click the link to add one. I use the two triggers shown below to automatically run the interest function to populate the data and the menu function to add the Parse email button to the menu bar.

Triggers for the Script so everything runs automatically

As a final touch, you can plot the data on the Sheet, which also updates automatically with the freshest data. Insert a Chart and use the Chart editor to select the data. I have the date set for the X-axis and the three interest rates set as different series. By selecting a range to the 1000th row, your chart will pull in up to a thousand data points.

If you’ve found this story useful, please like it. If you use my code as a basis of your own Script please reply with some details so others have more examples to draw from. Thanks for reading!