Is there any function in APPS Script that will allow me to email staff based on emails in a cell or-CodePudding

I'm trying to send an email from Google Sheets to update staff if they have filled a particular form out. The emails I need to be sent are sitting in the google sheet with the finish the form.

If there are 5 emails in the sheet, I need a draught email sent to them with a link attached.

I know you can send an email based on a value in a cell but I need an email sent to addresses that appear in the sheet.

Example

[email protected]

SEND LINK

The Apps Script needs to pick up those 3 emails and send the link I need them to receive.

CodePudding user response：

Since you know how to send emails to a cell, you could jam all the addresses into a cell with textjoin, and separate them with ; That would setup your addresses to send to...

So say in cell C1 you could put =TEXTJOIN(";",true,A:A) (assuming column a has addresses).

Then you would have your addresses just as a single cell with...

var addressesToSend = SpreadsheetApp.getActiveSheet().getRange('C1').getValue();

The alternative method would be to use app scripts to create an array and then join them, but if you've never done that before, I'd go with the textjoin approach.

CodePudding user response：

A simple Example

function sendEmIfTheyreThere() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const vs = sh.getDataRange().getValues();
  vs.forEach((r,i)=> {//for loop using arrow notation
    if(r[0] && r[1] && r[2] && !r[3]) {//look for truthiness of column1,column2 and column3 and the falsiness of column4
      GmailApp.sendEmail(r[0],r[1],r[3]);//send email
      sh.getRange(i   1,4).setValue("SENT")//put sent into column 4 to prevent emails for this row being sent again
    }
  });
}

Sheet0:

TO	FROM	MESSAGE	SENT
comma separated emails	single email	Text

CodePudding user response：

This is available with the free Mail Merge here:

https://developers.google.com/apps-script/samples/automations/mail-merge

that's basically what it does, you add the email addresses you want in the 'Recipients' column, the idea here is to modify the next columns in the example to match exactly what you would like to send them.

If you are looking for a different kind of functionality just let me know and I can look it up