Needed: Serious GMail Search-Fu

July 13, 2014 7:02 PM

Is there a way to query gmail to give me a list of every unique "TO:" address that exists in my All Mail folder?

I have a domain where my mail has been forwarded from, and for years I've used unique addresses when signing up for services (i.e. [e-mail], [e-mail]) Recently, my ISP turned off the ability to have all emails from @mydomain.com accept email so now each one needs to be whitelisted. Problem is, I can't remember them all. Since every email gets forwarded to a single gmail account, what I really need is a way to query gmail to give me a list of every unique "TO:" address that exists in my All Mail folder. Possible?

posted by coffee and minarets to Computers & Internet (9 answers total)

I'm assuming someone will chime in with a great answer to your question, but as a last resort you could switch email providers from your ISP to another provider that explicitly offers catch-all email service. Since you have your own domain, you have the power to set your MX records to point to any custom email provider.
posted by reeddavid at 7:16 PM on July 13, 2014

I can't think of an easy way to do what you're asking. I don't think that Gmail's search interface offers any way to do a "SELECT UNIQUE"-type query.

If someone put a gun to my head and told me to figure out a way to do it, what I would do would be to connect the Gmail account to a desktop mail program (Apple Mail or Thunderbird or similar) and download the entire contents of the Gmail account to the desktop program. Then I'd parse and analyze the saved messages on disk for whatever you want. Most desktop email programs build up index files (I think Thunderbird uses SQLite under the hood; Apple mail does some proprietary stuff) that you could peek at rather than chewing through all the messages themselves, too.

But the right solution is probably to get a mailhost that offers wildcard / catchall service.
posted by Kadin2048 at 7:22 PM on July 13, 2014

I use Google Apps for my domain and have it set to catch all. Many of those I forward to my gmail account. When I search my gmail account for to:*@mydomain.com, all of those forwarded to gmail from that domain come up. This will at least isolate them, but if there are a lot, it could be a tedious task. Every time you find one, in the search bar add -[name before the @ sign] and that will cut down on the duplicates as you go through them.

Another thought is through the use of a series of filters, although I am not sure how to narrow them down beyond putting a label on all the ones from your forwarded domain.

Also, maybe if you export all your emails or the label that has all from your domain, you could import it into some program that could sort them all.
posted by 724A at 7:38 PM on July 13, 2014

If you want an only semi-automated method, here's what I would do:

1. Take your main address and search for to:[e-mail]
2. Select all. Then scroll down and click the thing that says you really do mean all, not just the ones on the current screen.
3. Add the label "done" to these.
4. search for -is:done (note the minus sign)

This should give you all the other messages.

5. Click the first. Get the address. We'll call this [e-mail] Add that address to your list.
6. Search for the to:[e-mail]
7. Select them all. Scroll down to make sure you really selected all.
8. Add the label "done".

Rinse and Repeat until done.
posted by If only I had a penguin... at 9:18 PM on July 13, 2014

If you can export to mbox format (basically a text file of messages one after another) you can do this with a relatively simple Unix command line (Unix shell utilities are already native on your system if you have a Mac, can be easily installed via a package like Cygwin if you are on a PC)

Something like "grep '^To: ' mbox | sort -u" (though probably you'd want to allow for: (a) being listed on the Cc: header rather than To:, and (b) more than one recipient listed in that header.

It still should be fairly easy to pick it apart once you have it in text format. And I'm sure somebody makes a tool to download your gmail in mbox format..
posted by Nerd of the North at 10:20 PM on July 13, 2014

If you have access to Outlook, you can configure it to access your account (using IMAP not POP3) and then do an export.

In there you can select CSV format and limit it to only the "to" field. After that use Excel to filter out all the duplicates.

I think that moving your domain to another company is probably going to be the best thing in the long run.
posted by mr_silver at 1:04 AM on July 14, 2014

If you simply sort by the "to" address and then filter out all that are your main email address you'll be left with just the wildcard addresses you've used over the years. Then it shouldn't be too much of a pain to simply manually skim through the list. I'm assuming you haven't used 500 wild card addresses here.
posted by COD at 4:58 AM on July 14, 2014

Best answer: Google provides a pretty powerful scripting utility that can perform all sorts of operations on Gmails.

Here's an example of a third-party script that should give you the info you need:

http://www.labnol.org/internet/extract-gmail-addresses/28037/

Good luck!
posted by woodman at 5:09 AM on July 14, 2014

Response by poster: I couldn't get the above linked script to work, but it lead me to Google Script, which actually has more methods than the Gmail API. I ended up writing a function that scoops up all TO: email addresses and keeps track of the unique instances in a Google Docs Spreadsheet (along with the number of hits per email address.) Here's the ugly code that got the job done (I had to run the script several times upping the paging amount because Google Script times out after 5 minutes or so:

function getAllToNames() {
  var page = 14*(20*50),
      totalPages = 20,
      itemsPerPage = 100,
      uniqueNames = [],
      results,
      sheet = SpreadsheetApp.openById("[Google Docs Spreadsheet ID]").getActiveSheet(),
      sheetData = sheet.getDataRange().getValues();
      
      for (var i in sheetData) {
        uniqueNames[ sheetData[i][0] ] = sheetData[i][1];
      }
      
   for ( var i=0;i
      results = GmailApp.search("label:all to:@yourdomain.com",page,itemsPerPage);
      for ( var items = 0; items < results.length; items++) {
        var messages = results[items].getMessages(),
            toName = messages[0].getTo(),
            name;
            
            if ( toName.indexOf("<") == -1 ) {
                name = toName;
              } else {
                name = toName.replace(/.*\<(.*)\>/, "$1");
              }
            
        if ( !uniqueNames[name] ) {
          uniqueNames[name] = 1;
          sheet.appendRow([ name, 1]);
          sheetData = sheet.getDataRange().getValues();
        } else {
          uniqueNames[name]++;
          var count = 1;
          for (var i in sheetData) {
            if ( sheetData[i][0] == name ) {
              var cell = sheet.getRange(count,2);
              cell.setValue(uniqueNames[name]);
            }
            count++;
          }
        }
      }
      //use first time:
      //page = (i+1) * itemsPerPage;
      //this this:
      page += itemsPerPage;
   }
}

posted by coffee and minarets at 9:42 PM on July 14, 2014