Extracting all Email Addresses from a GMail Label

As with any other product, when working with GMail / Google Apps you sometimes get to a point where you want something the product simply does not provide. When that happens, a scripting interface can be a lifesaver. Let’s see how we can put Google Apps Script to use.

List all Email Addresses from a Label’s Messages

GMail’s most useful feature is the labelling system. The simple fact that you can tag a message with more than one label makes it far superior to message filing systems based on folders.

Let’s say you tag all personal communications with your friends and family with the label friends. If you are throwing a big party you might want to send invitation emails to all of them. If there only was a function to extract a list of all email addresses from all the emails tagged with the label friends.

When I needed this functionality recently I quickly stumbled upon a solution on labnol.org. However, that turned out to be slow and did not even work reliably. So I created my own.

Features

The script extracts email addresses from all messages in all threads tagged with a given label. It processes even large numbers of emails quickly. In addition to the sender’s email address it lists the sender’s name and the date of first communication.

Usage

The email extraction script is part of a Google Sheet to provide an easy user interface for data input and output. To use the script copy the sheet to your Google Drive by clicking this link.

Side note: this nifty copy feature is triggered by appending /copy to the document’s URL.

You will be asked if you want to make the copy:

01-Copy-document

Next, your copy of the spreadsheet opens in Google Sheets. Fill in the label from which you want to extract email addresses. In this example I am using the label Spam. then click Extract email addresses in the HK Scripts menu:

02-Spreadsheet

You will be asked for authorization:

03-Authorization

04-Request-for-permission

Now the script runs. It should not take long. Its output, the extracted email addresses, are written to a new sheet:

05-Script-finished

As you can see above, it not only lists the email addresses, but also the person’s name and the time of first communication.

The Script

You do not need this in order to use the email extraction script, but for the those interested here is the source code. You can also view it directly in the sheet by navigating to Tools > Script editor.

//
// Searches one label for the first time someone sent you an email
// Returns name, e-mail address (extracted from the "From" field) and message time
//
function GetAddresses ()
{
  // Get the active spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();  
 
  // Label to search
  var userInputSheet = ss.getSheets()[0];
 
  var labelName = userInputSheet.getRange("B2").getValue();
 
  // Create / empty the target sheet
  var sheetName = "Label: " + labelName;
  var sheet = ss.getSheetByName (sheetName) || ss.insertSheet (sheetName, ss.getSheets().length);
  sheet.clear();
 
  // Get all messages in a nested array (threads -> messages)
  var addressesOnly = [];
  var messageData = [];
 
  var startIndex = 0;
  var pageSize = 100;
  while (1)
  {
    // Search in pages of 100
    var threads = GmailApp.search ("label:" + labelName, startIndex, pageSize);
    if (threads.length == 0)
      break;
    else
      startIndex += pageSize;
 
    // Get all messages for the current batch of threads
    var messages = GmailApp.getMessagesForThreads (threads);
 
    // Loop over all messages
    for (var i = 0; i < messages.length ; i++)
    {
      // Loop over all messages in this thread
      for (var j = 0; j < messages[i].length; j++)
      {
        var mailFrom = messages[i][j].getFrom ();
        var mailDate = messages[i][j].getDate ();
 
        // mailFrom format may be either one of these:
        // name@domain.com
        // any text <name@domain.com>
        // "any text" <name@domain.com>
 
        var name = "";
        var email = "";
        var matches = mailFrom.match (/\s*"?([^"]*)"?\s+<(.+)>/);
        if (matches)
        {
          name = matches[1];
          email = matches[2];
        }
        else
        {
          email = mailFrom;
        }
        // Check if (and where) we have this already
        var index = addressesOnly.indexOf (mailFrom);
        if (index > -1)
        {
          // We already have this address -> remove it (so that the result is ordered by data from new to old)
          addressesOnly.splice(index, 1);
          messageData.splice(index, 1);
        }
 
        // Add the data
        addressesOnly.push (mailFrom);
        messageData.push ([name, email, mailDate]);
      }
    }
  }
 
  // Add data to corresponding sheet
  sheet.getRange (1, 1, messageData.length, 3).setValues (messageData);
}
 
 
//
// Adds a menu to easily call the script
//
function onOpen ()
{
  var sheet = SpreadsheetApp.getActiveSpreadsheet ();
 
  var menu = [ 
    {name: "Extract email addresses",functionName: "GetAddresses"}
  ];  
 
  sheet.addMenu ("HK Scripts", menu);    
}

, , ,

49 Responses to Extracting all Email Addresses from a GMail Label

  1. Rahul March 18, 2015 at 14:29 #

    Good script.
    But doesn’t work for labels with large number of emails. Execution fails :(

  2. TJ May 9, 2015 at 20:31 #

    Thank you for your script!

    It works well with from field, but I would need to collect addresses from reply-to field. Can this script be modified to get that done?

    • Rajesh March 18, 2016 at 16:07 #

      use var mailReplyTo = messages[i][j].getReplyTo ();
      and then modify the code accordingly.

      • Rik April 11, 2017 at 20:51 #

        Hello Rajesh, is there a way you can write where do I need to add the VAR on the code. Im not an expert. Im trying to get this done as Im jus reading and understanding how to implement it.

      • prashant October 13, 2017 at 07:21 #

        Rajesh, can you please help to write reply to code , i don’t have any idea how its work this code work perfectly for inbox mail i need reply to address please help me

  3. josh June 6, 2015 at 01:47 #

    great little script! thanks.

  4. Siva June 10, 2015 at 06:52 #

    Very handy. Thanks for the efforts!

  5. Alex Seidanis June 25, 2015 at 14:59 #

    This script works perfectly for me, but it only extracts senders. In my case, the email addresses are scattered within the body of all messages in a certain label. Could this script possibly be updated to scan message bodies too? That’d be a great helping hand.

  6. Alex July 5, 2015 at 16:59 #

    Is it possible to extracts email addresses found in the message body? Thanks

  7. Anuraag August 29, 2015 at 12:27 #

    Awesome! Worked like a charm, and very simple. Thank you so very much!

  8. Christopher Sahnwaldt September 8, 2015 at 20:53 #

    Thanks a lot for the script! It was a great help.

    I needed it because a spammer had sent 30 emails in my name (see [1] for details) and I wanted to send a warning/apology to all the recipients, so I moved all the spam mails to a new label, changed ‘getFrom’ in the script to ‘getTo’, and seconds later, I had a list of all affected addresses. Thanks!

    [1] http://www.digitaltrends.com/social-media/if-you-get-an-email-from-myzamana-trash-it/

  9. Mr Beam September 9, 2015 at 08:11 #

    Instead of the script from labnol.org this one worked fine and has more features.
    Thanks for sharing it!

  10. G.Lemmens December 2, 2015 at 20:46 #

    Amazing easy to use google sheet. Exactly where is was looking for. Thank you so much!

  11. Alex December 15, 2015 at 12:48 #

    Hi there!

    I am trying to use the tool and get
    “The coordinates or dimensions of the range are invalid”
    msg on the top

    it creates a new tab, but nothing inside.

    Any ideas what is wrong?

    Thaks

    • Luis December 16, 2016 at 16:56 #

      Just avoid teh use of spaces ” ” or special characters in the label… it worked for me as a fix for the same error

    • Niklas February 4, 2017 at 12:15 #

      It is because your label is empty in gmail

  12. Cat December 16, 2015 at 19:47 #

    OMG! That was incredible – you’re a magician :) Thank you so much!

  13. TurkPipoTutunu December 25, 2015 at 07:56 #

    Great stuff.

    Any way to simply collect number of email for each address ?

  14. Ramu January 14, 2016 at 08:11 #

    Very useful

  15. Henrik January 29, 2016 at 11:57 #

    Thanks so much, saved me a bundle of time!

  16. ade tolu February 18, 2016 at 12:45 #

    Works superbly
    Please can this script be updated to
    1) scan through the body of the message for email addresses
    2) scan through for contact numbers and possibly addresses?
    Thanks

  17. Josh Padilla February 28, 2016 at 17:30 #

    Thanks Helge,

    I’ve been reading through this GApps Script and it looks great. I’m going to use it as a starting point for new functionality. I’ve created a new GitHub repo named gmail-linker (https://github.com/joshpadilla/gmail-linker) which will host the code for the new script.

    I often perform research throughout the week on my phone, tablet, and various computers (mostly on DevOps, Programming and Cloud Infrastructure topics). So daily, I am emailing myself links with URLs in the body of the messages. I have a specific GMail label “Links” which these messages eventually get moved into. I’ve used services like Evernote, Delicious and others but none really matched up to just keeping all the links (URL lists) nicely categorized in a spreadsheet with a category and notes column (nice and simple).

    As you can imagine, as the days and weeks pass on, eventually I fall behind, with a couple hundred emails piling up in the “Links” Gmail label which have never made it into the Google Sheet designed to list all these great links. So finally I am ready to automate the process of extracting the URLs from the messages in the “Links” label and copying them into a Google Sheet. Once they are in a Google Sheet I can add a category and sort as needed.

    This should save me a good few hours of work every month and I figured your script was as good a starting point as any, since it does something similar but with email addresses instead of URLs. Thanks again!

  18. Joan Garcia March 3, 2016 at 02:30 #

    Hi Helge,

    Thanks so much for this script. Extracting all Email Addresses from a GMail Label worked for me. It recovered 666 rows of email addresses. Then I was able to use 195 of those email addresses.

    I prefer your method because I don’t save Google contacts to avoid my Gmail account from being hacked. This was great! Take care.

  19. Matt M March 14, 2016 at 04:37 #

    This is exactly what I’m looking for…but it failed! It seems that I was looking for too much :( The operation timed out and didn’t list anything. Is there a way to limit the script by time frame?

  20. Matt Maher March 14, 2016 at 17:22 #

    I fixed a problem I had with this script and then it worked wonderfully for me.

    I ran this script initially on one of my larger labels, and the script timed out because it was too massive. (I posted about it here but I guess that post was deleted)

    I made the next logical move and simply searched for everything in that label, and added “older:2012/01/01” (you could choose any date) and added a sub label. I then switched “older” to “newer” and gave those a second sub label. I then ran the script twice, one on each sub label, and go two sheets that I then combined.

    The one downside is that you end up with a ton of duplicates, but it only took a few minutes to sort the list by address, scan it, and delete the dupes.

    I don’t blame the script for timing out. I think my label had at least 14K emails in it. What I do think needs some adjusting is the duplicates it makes. It seems that the script will return the same email address multiple times if the associated name changes, or if the email address has any differentiation in capitalization. I was getting 2, 3, and 4 dupes for some addresses.

    • MiKE April 12, 2016 at 18:46 #

      Matt I tried to run this script and keep getting an error what line did you add the date and what was causing the error?

      thanks.

    • coco May 27, 2016 at 19:01 #

      Thaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaanks

      Resume: Just write in Label

      LabelName older:2016/1/1

  21. Ben Thomas April 11, 2016 at 21:41 #

    Thank you so much for your generosity to share this! Worked well for me.

  22. Cristianne Bastos May 7, 2016 at 22:48 #

    Thank you very much for your amazing script! It works perfectly.

    I was wondering if was possible to also get the email subject…

    Can you help me?

    Regards,

    Cris Bastos

  23. Henri June 21, 2016 at 18:05 #

    Thanks a lot. This is very useful.!!

    I had to use the trick described by Matt Maher as my Label was too big and the script was timing out.

    The easiest way to do it is to NOT do anything in Gmail but as coco wrote : directly in the spreadsheet, replace the name of the Label by:
    [LabelName] older:2016/01/01
    Run the script, a new workesheet will be created
    Then modify the Label to:
    [LabelName] newer:2016/01/01
    Run the script, a new workesheet will be created

    Concatenate both lists and remove the duplicates.

  24. ANDRES WATSON July 21, 2016 at 06:04 #

    Excellent! changed some lines to achieve my need getting from To, but it was an excellent base…

  25. Ali August 8, 2016 at 21:16 #

    This just saved me a tonne of work. Thank you!!

  26. philip October 7, 2016 at 16:52 #

    this was great. worked perfectly, just what I needed. many thanks.

  27. Thom October 20, 2016 at 07:05 #

    I get the error message Exceeded maximum execution time

  28. Daniel October 27, 2016 at 06:45 #

    Thank you, it worked ok! Made minor changes, saved me a lot of work.

  29. Luis Doporto Alejandre November 9, 2016 at 16:41 #

    I am having hard time to extract large number of emails. Any solution? Thanks

  30. Jose Monte November 17, 2016 at 06:33 #

    Just wanted to know how I can modify script to extract the email from inside the message body.

  31. Lester January 18, 2017 at 11:20 #

    Hi,

    How do I edit the script that I can extract all the emails from my gmail?

    meaning every email that has sent me an email or me sending them an email?

    Thanks!

  32. sterch January 21, 2017 at 14:47 #

    Thank you for this. Is there an option to extract only emails from yesterday? (younger than the day before)?
    thank you

  33. Andrei February 22, 2017 at 07:28 #

    This is what I get:

    The coordinates or dimensions of the range are invalid.DetailsDismiss

    Any solution?

    • Kelley October 31, 2017 at 21:24 #

      I’m getting the same thing. :-( Did you find an answer?

  34. mitakeet March 6, 2017 at 13:38 #

    Awesome! First Google hit and a perfect solution! Thanks so much!

  35. dragonbtv March 8, 2017 at 03:52 #

    Thank you for your contribution!
    But the label under label (subfolder) cannot be extracted.
    Will you upgrade the script recently?
    Thank you!

  36. THQureshi March 13, 2017 at 18:04 #

    any help with Exceeded maximum execution time??

  37. Ulises April 9, 2017 at 10:01 #

    Its weird, for me it extracts a lot of emails that are not labeled and other definitely are. Thank you anyways.

  38. Imran May 18, 2017 at 08:23 #

    Hello Everyone,
    This script is not working for me at all with Google business account. Any idea why?

  39. Charlie May 24, 2017 at 06:57 #

    This works like a charm and solved a huge problem for me. THANK YOU!!!!!!

Leave a Reply