How can I turn my Google inbox into an excel document and process it?

November 11, 2013 11:06 AM

Over the last year, I've had a contact form on my website with a checkbox for "Notify me when your book is done." My book is done and I'd like to email them. Around 1200 people used my contact form, and ~800 people clicked that checkbox; how do I get all of their email addresses into an excel spreadsheet?

The contact form sent me an email with a fixed title ("New request received"). The body of each email has each person's info as follows (First name: Joe, Last name: Smith, Book Mailing List (on=yes): off, Email address: [e-mail], Hi sdis! I'm writing to blah blah blah). I'm on Gmail and I have a Mac with all of my emails downloaded locally, if that's handy infomation.

posted by sdis to Computers & Internet (15 answers total)

A few questions to clarify the problem:

1. Does this collection of emails have *just* the people you want to email, or does it mix together people who do and people who do not want the email?

2. Are you looking for just a list of email addresses, or do you want the spreadsheet to list all of the data collected by the form (First Name, Last name, etc)?

3. Does From field on these emails come from the user's email address, or is it from something like donotreply@[whateveryourwebsiteis].com?
posted by JDHarper at 11:13 AM on November 11, 2013

You can export your contacts in a CSV format. Click the upper left Gmail menu and select Contacts. Within contacts click More and then Export. Not sure what the final product will look like but Excel and other spreadsheets can import CSV files...
posted by jim in austin at 11:18 AM on November 11, 2013

Response by poster: @JDHarper

1. My inbox contains three relevant groups of people:
-Emails without the subject line "New request received" (30k or so)
-Emails with the subject line "New request received" who DO want the email (800 or so)
-Emails with the subject line "New request received" who DO NOT want the email (400 or so)

2. A spreadsheet with all the data collected by the form would be nice, but I'll accept addresses if I have to.

3. The "From" field is donotreply@[whateveryourwebsiteis].com and the "Reply to" field is the user's email address.

@jim in austin: I need some way to sort through the three groups of people listed above before exporting to csv.
posted by sdis at 11:21 AM on November 11, 2013

I need some way to sort through the three groups of people listed above before exporting to csv.

Give them a unique Group. Within Contacts click on the ones you want and assign them a new Group. You can then export that group only...
posted by jim in austin at 11:29 AM on November 11, 2013

2nding jim in austin. I needed to do something similar with about 500 of my 3500 contacts. I set up a group label, "Needed" then exported my contacts to csv. I then opened it with Excel and sorted on the column with labels. It was painless in that it only took me a few hours which for someone who knows what they are actually doing would likely take about 22 minutes.
posted by JohnnyGunn at 11:37 AM on November 11, 2013

Best answer: jim in Austin and JohnnyGunn: I don't think these contacts are in his contacts list; they are stored in actual emails.


Step one is to isolate just the emails you want to get info out of. You can do that with a search in gmail like this (you may have to tweak this search a bit):

"Book Mailing List (on=yes): on" Subject: New request received

Once you've done, that, click on the arrow next to the button with checkbox underneath the search box (screenshot), check All, and push the little notification at the top that says "Select all items that match this search."

Then click the icon of a tag, over on the right side of the toolbar, and choose Create New (screenshot). Name the label something descriptive like "BookMailingList"

Now, if your Mac is set up to sync with your gmail over IMAP it should create a new folder with the same name as that tag, with all the 800 some-odd emails in it. (If you're downloading via POP, you may have to do something similar to the above in the Mac Mail application instead).

Step two: Now that you have a folder of just the emails you want to deal with in your Mac Mail program, we have to figure out a way to get the information out of those emails. I think you could do something like what you're trying to accomplish using AppleScript but I don't have access to my Mac right now to be of much further assistance on that point at this point in the day. If no one else has come up with a good answer by the time I get home I'll see what I can figure out.
posted by JDHarper at 11:41 AM on November 11, 2013

Response by poster: Ok, I have all of group #1 in a mailbox in Mail.app. Next goal is to convert that mailbox into CSV or something, which is currently eluding me.
posted by sdis at 12:06 PM on November 11, 2013

So, the emails downloaded to your Mac should be stored in a folder like:
/Users/[username]/Library/Mail/V2/IMAP-[you]@[e-mail]/[folder-name].mbox/51C47B00-FA83-F6EF-9B24-789544D957F7/Data/1/6/3/Messages/

This folder will be full of files with names like 281916.emlx. If you're running a recent version of Mac OS X, your Library folder is probably hidden, but you can open it by going to the Finder's Go menu, selecting "Go to folder…" and typing in ~/Library. Once you've found the correct folder containing all the messages, open /Applications/Utilities/Terminal.app, type cd followed by a space, then drag the folder into the terminal window to insert it's path. Hit enter, and you will have changed directories into the one containing all the emails.

Next, paste the following line of code into the terminal and hit enter:
for f in *; do if grep --quiet "Subject: New request received" "$f" && grep --quiet "Book Mailing List (on=yes)" "$f"; then sed -n 's/^Reply-To: \(.*\)$/\1/p' "$f"; fi; done

This command should go through each file containing an email, check if it contains "Subject: New request received" and "Book Mailing List (on=yes)", and for those that do, print out the contents of the Reply-To header.
posted by JiBB at 12:13 PM on November 11, 2013

Oh, if you already have only the emails from which you want the Reply-To header in a folder, you can just use the command:
for f in *; do sed -n 's/^Reply-To: \(.*\)$/\1/p' "$f"; done

(No need to test the files, just search for and print the contents of the Reply-To header.)
posted by JiBB at 12:15 PM on November 11, 2013

Response by poster: JiBB: It's clearly doing something (the window header in Terminal is changing from bash to sed), but it's not printing out anything. (I tried both lines of code - with and without the test)
posted by sdis at 12:22 PM on November 11, 2013

Hmm… do any of the files in the folder have lines beginning "Reply-To: "?

Also, I've found two problems with my previous command: it fails on some oddly-encoded messages, and I'd just been looking at a mailbox with only a small number of messages in it—larger mailboxes have multiple numeric subdirectories. However, if you run this command, replacing the path "/Users/[username]/Library/Mail/V2/IMAP-[you]@[e-mail]/[mailbox-name].mbox" with the path to your .mbox folder, it should print the Reply-To addresses:

LANG=C find /Users/[username]/Library/Mail/V2/IMAP-[you]@[e-mail]/[mailbox-name].mbox -type f -name "*.emlx" -exec sed -n 's/^Reply-To: \(.*\)$/\1/p' {} \;
posted by JiBB at 12:40 PM on November 11, 2013

sdis:
Everything in quotes like "this" in JiBB's script has to match the email *exactly*.
Same case, formatting, etc.

Like is it:
Book Mailing List (on=yes)
Book Mailing List: on
sbis's contact survey: yo!
or something else?

Could you copy and paste one or three of the emails, and fudge the details, so we can be sure the script is formatted correctly?
posted by Elysum at 12:45 PM on November 11, 2013

Response by poster: Got it! It's "Reply-to: ", not "Reply-To: "
posted by sdis at 5:29 PM on November 11, 2013

Ah, good! In case someone else finds it useful later, here's the way I was just able to get it working in Applescript:

1. Open the Applescript Editor (under the Utilities folder in Applications)
2. Paste in this (replacing FOLDERNAME with the correct folder name and GMAIL with the account name as listed in Mail.app)

set emaillist to {}
tell application "Mail"
	set theMessages to the messages of mailbox "FOLDERNAME" of account "GMAIL"
	repeat with aMessage in theMessages
		set emaillist to emaillist & reply to of aMessage
	end repeat
end tell
emaillist

3. Click Run. Once it's finished, it will list all the reply-to addresses in the Results pane (screenshot)

You could tweak this to have it pull the content of the message (use "content" instead of "reply to" in the most indented line above) and then do some parsing on that if you wanted, but it sounds like you've got what you need.
posted by JDHarper at 5:56 PM on November 11, 2013

Incidentally, this is not just a technical matter of sending out the emails. I would not be shocked at all if some link in the chain started rejecting your emails as a suspected spammer at some point. For instance, if a large number of them are going to GMail, that would set off red flags. Getting mass emails to end up in people's inbox is not a particularly easy problem.
posted by smackfu at 9:26 AM on November 13, 2013