Extracting mails from a spreadsheet

Question

I have a Google Spreadsheet with two columns.

First column includes the name of a referrer and second column includes a free format text where some referred email addresses are mentioned. There might be multiple email addresses in one cell, or none.

Ex:

Referrer | Referral
--------------------------------------------------------------------------
Mister X | I would like to refer somebody@gmail.com and somebodyelse@outlook.com
Miss Y   | myfriend@mail.com
Mister Z | None!
etc      | ...

I would like to format the data such that for each referred address we have the referrer and the email address referred.

EX:

Referrer | Referral
--------------------------------------------------------------------------
Mister X | somebody@gmail.com
Mister X | somebodyelse@outlook.com
Miss Y   | myfriend@mail.com
etc      | ...

What is the best way of achieving this?

score 1 · Accepted Answer · edited May 23 '17 at 12:09

Here's your original data in a table.

Referrer    Referral
Mister X    I would like to refer somebody@gmail.com and somebodyelse@outlook.com
Miss Y  myfriend@mail.com
Mister Z    None!

Here's the same columns after they're over written.

Referrer    none
Mister X    somebody@gmail.com
Mister X    somebodyelse@outlook.com
Miss Y  myfriend@mail.com
Mister Z    none

And here's the code. Currently, you select the two columns as we were shown and I over write them in the format your requested. Although with such a limited dataset one can never be 100% sure. So further testing would be good. I included the menu and some of my display routines which help me debug the program. I suppose you may want to change the range. Go for it. Have fun. I enjoyed writing it.

function onOpen()
{
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('My Tools')
        .addItem('Extract Emails','emailFishing')
        .addToUi();
}


    function emailFishing()
{
  var rng = SpreadsheetApp.getActiveRange();
  var rngA = rng.getValues();
  var resultsA = [];
  //var s = '[';
  for(var i = 0;i < rngA.length; i++)
  {
    if(rngA[i][1])
    {
      matchA = extractEmails(rngA[i][1]);
      if(matchA)
      {
        for(var j = 0; j < matchA.length;j++)
        {
           resultsA.push([rngA[i][0], matchA[j]]);
           //s += '[' + rngA[i][0] + ', ' + matchA[j] + '], '
        }
      }
      else
      {
        resultsA.push([rngA[i][0],'none']);
        //s += '[' + rngA[i][0] + ', \'none\'],'
      }
    }
  }
  //s += ']';
  var orng = SpreadsheetApp.getActiveSheet().getRange(rng.getRow(), rng.getColumn(), resultsA.length, resultsA[0].length);
  orng.setValues(resultsA);
  //dispStatus('Results Array', s, 500, 400);
}


function extractEmails (text)
{
    return text.match(/([a-zA-Z0-9._-]+@[a-zA-Z0-9._-]+\.[a-zA-Z0-9._-]+)/gi);
}

function dispStatus(title,html,width,height)
{
// Display a modeless dialog box with custom HtmlService content.
  var title = typeof(title) !== 'undefined' ? title : 'No Title Provided';
  var width = typeof(width) !== 'undefined' ? width : 250;
  var height = typeof(height) !== 'undefined' ? height : 300;
  var html = typeof(html) !== 'undefined' ? html : '<p>No html provided.</p>';
  var htmlOutput = HtmlService
     .createHtmlOutput(html)
     .setWidth(width)
     .setHeight(height);
  SpreadsheetApp.getUi().showModelessDialog(htmlOutput, title);
}

The function extractEmail came from Leniel Macaferi. From this post Extract all email addresses from bulk text using jquery. Although I left out the JQuery part.

Thank you so much for your help! Noob questions: To execute it, I opened the script editor from the spreadsheet and executed onOpen, which added to the spreadsheet a tool called `Extract Mails` as expected. However, when I clicked on it it threw back an error saying that you cannot call the match function (from extractMails) over undefined. Am I executing it correctly? — Jsevillamol, Feb 19 '17 at 10:00
Are you using new data. If so can you add it to you code post and I'll stick it in my file and try the same thing and perhaps resolve the issue. Developing code that uses regular expressions can be tricky for me. I don't use them every day. But I'll be glad to fix it if you can provide me with some real data. Obviously, be careful about the content your posting on the web. A quick look makes me think that maybe some of your data has no content on the right had column so rngA[i][1] is null. I'll take a look at that. If you have time try to debug on your end too. Thanks — Cooper, Feb 19 '17 at 17:21
I went ahead and added this code if(rngA[i][1]) {} around the stuff inside the loop so that it doesn't try to match null strings. If I were smarter I would have caught that the first time through. — Cooper, Feb 19 '17 at 17:36
You welcome. All the questions I work on really help to learn a lot and I enjoy writing code. Actually, I don't enjoy writing it as much i do getting it to work. If you have any trouble adapting it to your situation please come back . — Cooper, Feb 20 '17 at 15:55

Extracting mails from a spreadsheet

1 Answers1