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.
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);
};
Thanks for sharing
