Google Apps Script to Extract Emails

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:

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:

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.

 

Related Posts

Using Node.js and OpenCV to Count Colonies on a Pe... If you're looking for a relatively simple way to automate routine tasks that involve extracting information from images, using computer vision librari...
Connecting Google Sheets With Quickbooks Google Sheets is a great tool to interactively work with an assortment of APIs to work as an intermediary database for experiments or small scale data...
Bitcoin Arbitrage Visualization with Google Sheets If you have access to international bitcoin exchanges, you might be able to take advantage of bitcoin arbitrage opportunities. Google Sheets has a gre...
 

David Chun

I'm a full stack developer that builds tools for marketing and managing ecommerce shops. I occasionally consult for startups. If you've got an interesting project, reach out.

 

12 thoughts on “Google Apps Script to Extract Emails

  1. 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? 🙂

  2. 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.

  3. 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.

  4. 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.

  5. 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.

  6. 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!

Leave a Reply

Your email address will not be published. Required fields are marked *