Email multiple addresses contained in one cell

40 views
Skip to first unread message

chocho...@gmail.com

unread,
Jun 20, 2022, 10:14:34 AMJun 20
to Google Apps Script Community
Hello, I am trying to use app script to email multiple addresses in one cell of a spreadsheet.  Some rows of my spreadsheet contain only one address in the cell, but others need to go to multiple directors.  The below snippet works when there is only one email address in the director cell, but not when they are two, which I've separated with a comma:


Here's the snippet:

for (var i = 0; i < data.length-1; ++i) {
   var row = data[i];
   var link = row[28];
   var emailAddress = row[29];
   var director = row[33];
   Logger.log(director);
   var manager = row[34];
   Logger.log(manager);
   var htmlBody2 = "Dear Provider,";
   var emailSent = row[30];
  if (emailSent === '' && emailAddress !== '')
     {GmailApp.sendEmail(emailAddress, subject, '', { htmlBody: htmlBody2, cc: 'em...@thisaddress.org'+','+ manager + ','+ director});

Can anyone help with how to set up either the script or the cell to have it send to both addresses in one cell?

Thank you!
     

chocho...@gmail.com

unread,
Jun 22, 2022, 9:32:52 AMJun 22
to Google Apps Script Community
Just bumping this post to see if anyone can help?

Clark Lind

unread,
Jun 23, 2022, 10:04:09 AMJun 23
to Google Apps Script Community
See if this makes a difference. I usually build my 'To:' and 'Cc:' fields outside the email object which makes it easier to read and understand 2-3 years from now.. your future self will appreciate it! lol

for (var i = 0; i < data.length-1; ++i) {  //open for

   var row = data[i];
   var link = row[28];
   var emailAddress = row[29];
   var director = row[33];
   Logger.log(director);
   var manager = row[34];
   Logger.log(manager);
   var htmlBody2 = "Dear Provider,";
   var emailSent = row[30];
   var cc = `em...@thisaddress.org, ${manager}, ${director}`;   //or even:     var cc = `em...@thisaddress.org, ${ row[34] }, ${  row[33]  }`;
  if (emailSent === '' && emailAddress !== '') {            //open if
    GmailApp.sendEmail(emailAddress, subject, '', {  //open object  //I assume the subject is defined above somewhere
    htmlBody: htmlBody2,
    cc: cc
       }) //close object
      }   //close if
  } //close for

Clark Lind

unread,
Jun 23, 2022, 10:11:39 AMJun 23
to Google Apps Script Community
You may need to add quotes around the emails if the above doesn't work:

var cc = ` "em...@thisaddress.org", "${manager}", "${director}" `;   //or even:     var cc = `"em...@thisaddress.org", "${ row[34] }", "${  row[33] }" `;

p

unread,
Jun 23, 2022, 11:06:54 AMJun 23
to google-apps-sc...@googlegroups.com
Thanks, Clark, for your response. 

This still doesn't work for what I am trying to do.


The script works fine with just one director, thisdi...@domain.org, but stops working when I add another director in the spreadsheet cell.  

Does that make better sense?  I imagine I could try putting the other director in a different row, but wanted to know if there was some one to have the script email both directors in one cell and how to do that.

Thanks for any help you can provide!

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/46e7c4ad-ff04-43d9-80f5-53d659788cb4n%40googlegroups.com.

Jon Couch

unread,
Jun 23, 2022, 11:25:44 AMJun 23
to google-apps-sc...@googlegroups.com
I don't know if this helps but in my applications the addresses are passed to bcc as "thisdi...@domain.org, thisothe...@domain.org". I think I discovered that the entire list of addresses need to be within a single set of quotes. 

I also have a routine that concatenates two addresses as 
var combinedEmail = emailOne + "," + emailTwo; 

I don't know if either of these things help. I haven't been following the thread so you may have already tried these.

Jon


Clark Lind

unread,
Jun 23, 2022, 12:52:50 PMJun 23
to Google Apps Script Community
Can you paste the sanitized results of a console.log(row[33], row[34])  for single and multiple email addresses in row[33]?  Jon is right; you only want to pass on a single string like maybe:
var cc =  "em...@thisaddress.org"
cc = cc.concat(", ", row[33]);
cc = cc.concat(", ", row[34]);

p

unread,
Jun 23, 2022, 4:41:11 PMJun 23
to google-apps-sc...@googlegroups.com
Thanks, Joh, but I am pulling the two email addresses from one cell in the spreadsheet.  The script works fine with only one email address.  I'm just not sure how to pull cells that contain two addresses into the script and have the script not throw an error (read them as two addresses).

Phillip Bainbridge

unread,
Jun 24, 2022, 6:05:42 AMJun 24
to Google Apps Script Community

Hi

I'm not seeing anywhere in your code that you need to account for splitting the contents of the cells containing multiple email addresses - you will need to do this first before you can use them. Example: https://www.pbainbridge.co.uk/2019/09/split-single-cell-contents-with-new-line.html

Kind regards
Phil
Reply all
Reply to author
Forward
0 new messages