If you have a generic email that is being sent to you regularly and you happen to be a gmail user, you can easily extract that data into a spreadsheet where you can further parse the data into a database if that particular email follows a template.
Zapier recently introduced a free email parser but it was too much of a hassle to actually use (although it seemed really cool and easy at first blush). Plus the integration with a database was so cumbsersome that I just gave up and found my own solution.
I receive notifications of my app sales from multiple vendors and I create some formulas in a basic spreadsheet to exract the data I need.
Below is a script that extracts the body of an email that has been pre labled “your custom label”. When you enter into Google Docs, create a spreadsheet, click the tools tab and select script editor. Once in the editor, add the following code into the window:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
var sheet = SpreadsheetApp.getActiveSheet(); var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); function getEmails() { var label = GmailApp.getUserLabelByName("your custom label"); var threads = label.getThreads(); var row = 2; for (var i = 0; i < threads.length; i++) { var messages=threads[i].getMessages(); for (var m=0; m < messages.length; m++) { sheet.getRange(row,1).setValue(messages[m].getPlainBody()); row++; } } } function onOpen() { var menuEntries = [ {name: "Load Emails", functionName: "getEmails"} ]; spreadsheet.addMenu("Email", menuEntries); } |
Save the file and reload your empty spreadsheet. You will notice that there is now a new tab in your main menu that is labeled “Email”. Select “Load Email” and the script will fetch the body of your emails and add them to the second row.
If you want to add subject lines and sender info you can use the getSubject() or the getFrom() methods and append it to you setValue method. A list of Gmail class methods can be found in the Google Apps Documentation
Also, if you happen to be planning on extracting more than a hundred or so emails, you may run into a limit restriction that Google sets on fetching emails.
It’s recommended that you add the following to your loops:
|
1 |
Utilities.sleep(1000); |
It didn’t work for me, so I split up my function into separate parts and that got the job done.
We’ll there you have it, now you can start making sense of the mountain of emails you deal with throughout your workweek.
Thanks for this great starter. What I’m trying to elaborate now is a script that appends extracted values in the same spreadsheet if run regularly. Right now it fetches values to the spreadsheet again and again. I tried to duct tape it myself with a little help from Google Developers documentation but I won’t get it running. Would you help me out? 🙂
A bit late but if you still need help, pm me
Hey Dchun I am actually looking for a bit of help with something similar, are you available to help at all?
Cheers
J
Hi Jay, send over details about what you’re trying to accomplish and your budget here: https://boopis.com/contact and I will get back to you.
hello sir.
when I run this code in spreadsheet.the email link appears but after that when I click on load email it shows cannot call method “getThreads” of null. how can I remove this error.
hello sir.
after load email. it show TypeError: Cannot call method “getThreads” of null.
That means that you don’t have any emails that have been labeled by the string you set to the label variable in the line before calling the getThread function.
I’m trying to copy just the table that my customer send me on the email, is not an attachment is a table on the message. I created a macro in excel that separates the data on the table as I need it to create different report but I have to copy from email to excel and than run the macro, I’m trying to get all the tables they send me in one file so I can copy it from there or try to do a macro in google sheets to do the report for me.
Whoops, dunno if that one worked, but I am looking for assistance with something very similar to this. Interested?
Hello,
var threads = GmailApp.getInboxThreads(401, 600);
when i fetch emails it gives error.(Argument max cannot exceed 500).
Please help me how can i fetch emails above 500 limit.
Thanks for this, it turned out top be just what I needed.
For those, like me, who haven’t written script before the ‘append it to you setValue method’ looks like this:
for (var m=0; m < messages.length; m++) {
sheet.getRange(row,1).setValue(messages[m].getSubject());
sheet.getRange(row,2).setValue(messages[m].getDate());
row++;
(I'm used to adding delimited parameters in the setValue but learned I needed to set the column in the sheet.)
If anyone figured out how to use nested labels or labels with special characters feel free to let us know.
Hello!
I am looking for something similar, but for some reason I can’t even make the example script above work. I will be grateful if you have some time to respond. Thank you!