How to extract email addresses tagged with a specific Gmail label

Extract email from Gmail

Everyone uses email in different ways. For me, email is at the very centre of my business. Everything flows through my Gmail account.

Being able to categorize all work enquiries or questions with a specific label, for example Queries (which I nest under my Work label) is one of the most useful features of Gmail.

Recently I needed to extract all of the email addresses for the hundreds of messages under this label. Super tedious to do manually, but thankfully there’s a much quicker way using Apps Script.

In this post, we’ll see how to setup a Google Sheet to extract all the email address for a specific Gmail label. I’ll share the code and walk through it with you.

How to use the Gmail Service with Apps Script and Google Sheets

Step 1: Set up the Google Sheet

The Google Sheet for this example is super simple for a change.

Cell B1 is where we type the label that we want to extract emails from, and then on row 3 are the two column headings, Name and Email.

The sheet looks like this:

Setup sheet for gmail app

Really important note

In this example I have a nested label, where I want email addresses from the label “queries” which belongs to the parent label “work”. Hence I need to write the label with a dash to show this relationship, with the parent label listed first, hence: “work-queries”.

If you are just looking at a single label with no relationship with other labels, then you can just type that label, e.g. “work”.

Step 2: Add the code

Go to Tools > Script editor...

Rename the project and clear out the boiler plate code so your editor window is blank:

Blank script editor window

Then paste in the following code (this same code is here on GitHub):

This script uses the Gmail Service to retrieve all the messages with the chosen label, and from there extract the names and email addresses.

Lines 2 – 7 add the custom menu to our spreadsheet. Specifically, line 5 adds the selector item to the menu, Extract emails..., which, when clicked, runs the function called extractEmails.

This extractEmails function begins on line 10.

In lines 13 to 16 we set up some variables to hold the active sheet and the label we’ve entered in cell B1:

Line 18 searches my Gmail with this label and gets all email threads tagged with that label.

Line 21 extracts all the messages from those threads and puts them into an array.

Lines 26 to 30 extract all of the To and From email addresses from all of these emails (so it doesn’t matter whether I sent or received, we’ll still grab the email addresses).

Lines 33 to 35 de-duplicate the email array, by filtering out any items in the array that occur more than once. Only the first instance will satisfy the filter condition (that the item index equals its position) and thus be included in our new array, uniqueEmailArray.

Lines 37 to 53 (up to the period) parse the email addresses to separate out the name and the email address and throw away the quotation marks and angled brackets. In other words, they turn this:

"Ben Collins" <benlcollins@gmail.com>

into this:

Ben Collins   ,   benlcollins@gmail.com

The most difficult line to understand in this section of code is probably the regular expression on line 41 (which also messes up the code colors in this wordpress plugin!):

Let’s break it down, piece by piece:

  •    /   indicates the start of the regular expression
  •    \s*   means zero or more whitespace
  •    "?   means zero or one quotation mark "
  •    [^"]*   matches any character that is not a quotation, zero or more times
  •    ([^"]*)   is a numbered capturing group, which groups the regex between the parentheses
  •    "?   means zero or one quotation mark "
  •    \s+   means one or more white spaces
  •    & l t ;   means a left-angled bracket < (because this the format of our email addresses we receive from the Gmail Service
  •    (.+)   means one or more of any character (this is the email address)
  •    & g t ;   means a right-angled bracket > (because this the format of our email addresses we receive from the Gmail Service
  •    /   indicates the end of the regular expression

Click here for the full list of re2 regular expression syntax.

The rest of line 53 down to line 61 simply filters out some email addresses I’m not interested in. You’ll find there will still be some tidy up to do with the outputted list, but this step helps reduce that a little.

Line 64 clears out any old data that’s already in our sheet. It clears columns A and B from row 4 down to wherever the last bit of data is in columns A or B.

Finally, line 67 pastes in the array of names and email addresses that we’ve created, into columns A and B, starting from row 4, underneath the headings.

Right, that’s the script dealt with, let’s put this thing to work for us!

Step 3: Grant permission to Apps Script to access the necessary resources

Select the onOpen function in the menu and hit run:

run onOpen function

The script will send a message to tell you it’s preparing to execute:

Preparing script message

Next up, a message to alert you the script is running:

Script running message

However, it won’t run all the way through yet.

The authorization dialog popups will appear, to confirm it’s ok to access our spreadsheets and our Gmail.

Click Review Permissions to continue:

authorization required

The next authorization popup will show you details of what resources the script wants to access, in this case the spreadsheet service and Gmail.

Click Allow to continue:

authorization script resources

Finally you’ll see the yellow popup messages again at the top of your browser to say the script is preparing to run and then running.

When they disappear, head back to your spreadsheet and you should now see a new, custom menu called Extract Emails:

Custom email menu

Step 4: Extract the email addresses

Now it’s simply a case of running your script from that custom menu we created above.

Select Extract Emails... and click it to run your email extractor function.

Assuming it all works ok, then you should see names and emails populate your Sheet, as shown in the animated image at the top of this screen.

Leave a Reply

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