T212 Contract Note Email Importer

This script will import Contract Notes sent by T212 from your gmail inbox, to a google spread sheet.

  • create a new google sheet
  • click -> tools -> script editor
  • paste the script
  • change “c1” on line 3 here, to the sheet you want to update (or rename a sheet in the doc to c1)
  • change the label on line 5 to the actual label you use in Gmail.
  • save
  • close the script editor.
  • hard refresh the document
  • You should see a new menu item now like in the screenshot below
  • Click to import, first time you use it, google will ask you to authenticate (your docs to your email)

edit: made slight changes to download header once.

image

it’ll work as long as the format of the email is same, enjoy.

var currDocument=SpreadsheetApp.getActiveSpreadsheet();
// replace c1 with the sheet name you want to import into
var sheet1 = currDocument.getSheetByName('c1');

function getemails() {
  // this is the label you assigned to the contract notes, 
  // sub labels are separated by a forward slash
  var label = GmailApp.getUserLabelByName("someLabel/subLabel");
  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 message=messages[j];
      if(message.isUnread()) { // <- will only import unread messages, remove this if you want to import all
        process(message);
      }
    }
  }
}

function process(message) {
  var body = message.getPlainBody();
  var firstElement = "№"
  var lastElement = "Total cost"
  
  var rows = [];
  var cols = [];
  var lastCol = 0;
  var foundFirst = false
  var foundLast = false
  
  lines=body.split("\n").filter(function (el) {
    return el.trim() != "";
  });
  
  for( var i = 0; i < lines.length; i++) {
    var ct = lines[i].trim();
    if(ct == "* All transactions' execution times are GMT.") {
      break;
    }
    if( foundFirst && foundLast) {
      cols.push(ct);
      if(cols.length == lastCol) {
        rows.push(cols);
        cols = [];
      }
    } else {
      if( foundFirst == false && ct == firstElement) {
        foundFirst = true;
        cols.push("No")
      } else if(foundLast == false && ct == lastElement) {
        foundLast = true
        cols.push(ct)
        lastCol = cols.length;
        rows.push(cols);
        cols = [];
      } else if(foundFirst){
        cols.push(ct);
      }
    } 
  }
  for(var j = 0; j < rows.length; j++) {
    if(j==0 && sheet1.getLastRow()!==0){
      continue;
    } else {
      sheet1.appendRow(rows[j]);
    }
  }

  message.markRead();
}

function onOpen() {
  var entries = [{
    name : "T212 Contracts",
    functionName : "getemails"
  }];
  currDocument.addMenu("Import", entries);
};
13 Likes

You legend, thank you

Label? We are a progressive, inclusive community here :roll_eyes:

:+1: Thanks for sharing

I am a labeling addict :frowning:

image

1 Like

I was always like that with work email, never did it on my personal

I’m just gonna bump this. I feel it got buried and not many people saw this very helpful post.

Also I’ll add an example sheet of how I’ve formatted some of the values that are imported.

Using =GOOGLEFINANCE you can get a good update on the market value of your portfolio automatically. It will not reflect it exactly becuase GOOGLEFINANCE has up to 20mins delay.

1 Like

Thanks for this.
Very useful until T212 add in export functionality.