Getting e-mail adresses using a filter in Google Sheets /w scripts

Posted on

Getting e-mail adresses using a filter in Google Sheets /w scripts – Whether you’re just starting out with Google Sheets or are a seasoned pro, sooner or later one of your formulas will give you a formula or script parse error message rather than the result you want. However, every Formula user, irrespective of whether it’s a beginner or expert, has invariably come across a formula parse error in Google Sheets at least once in their life (and if you haven’t. you soon will). It can be frustrating, especially if it’s a longer formula where the formula parse error may not be obvious. In this article, we will talk about some common script or formula parse errors in Google Sheets, like Getting e-mail adresses using a filter in Google Sheets /w scripts, and how to fix it.

I am trying to extra email replies from gmail. I already found some code that is supposed to work.

My question is: how can one expand it to support a filter as a variable? (in this case so a filter is passed and working named z_lbl)

I am calling the function like so: =listemails('test')

But my error is sometimes: ‘Formula parse error’; or I have also seen ‘not allowed to use GmailApp.search’

IN the end I would like to make a function that I can call in my sheets. It takes 1 variable that I program either as a label finder (becomes label:searchme). Or even better: as a complete filter that is added as a search filter (becomes ‘label:searchme has:attachment’ etc).

Help is appreciated

function listEmails(z_lbl) { 
  // http://stackoverflow.com/a/12029701/1536038  
  // get all messages      
  if (z_lbl == null) {
    throw 'Please add a label variable to the function to search for';
  }
  var eMails = GmailApp.getMessagesForThreads(
    GmailApp.search('label:'+z_lbl))
      .reduce(function(a, b) {return a.concat(b);})
      .map(function(eMails) {
    return eMails.getFrom() 
  });

  // sort and filter for unique entries  
  var aEmails = eMails.sort().filter(function(el,j,a)
    {if(j==a.indexOf(el))return 1;return 0});  

  // create 2D-array
  var aUnique = new Array();  
  for(var k in aEmails) {
    aUnique.push([aEmails[k]]);
  }

  // add data to sheet
  SpreadsheetApp.getActiveSheet().getRange(1, 1, aUnique.length, 1)
    .setValues(aUnique);
} 

Answer :

I created a sheet that uses filters that allow all kinds of exports.

Works great – hope it helps others

Shared here: https://docs.google.com/spreadsheets/d/14nOGCyu2nOLDQ_cJNQfVsDiBxy-Te3W8eh9du3C4sVQ/copy

Leave a Reply

Your email address will not be published.