Grab data from Google Sheets and create emails in Gmail with recipients, subject and body based on i-CodePudding

I have a sheet with data that must serve as input parameters to emails in gmail.

So, the email must be copied to the people in Column A ("Emails"), it should have a defined subject, which is on B2 and a defined body text in C2.

Is there a way to create an Appscript that connects Google Sheets to Gmail and creates an email like this below? Could you suggest a script for that based in the

CodePudding user response：

To send email using App Script it only needs one line

MailApp.sendEmail(emailAddress, subject, message);

You have all these required information in your sheets that you can pull.

You can use the codes below. Just change the Spreadsheet URL. This works by looping through your rows starting from row 2 up to what row contains data to get the email address, subject, and message.

function sendMultipleEmails(){
  var ss = SpreadsheetApp.openByUrl('<your_SpreadSheetURL>');
  var sheet = ss.getSheetByName('Sheet1');

  var n = sheet.getLastRow();
  //loop through rows with data
  for (i = 2; i < n 1; i  ){
    var emailAddress = sheet.getRange(i, 1).getValue();
    var subject = sheet.getRange(i,2).getValue();
    var message = sheet.getRange(i,3).getValue();
    MailApp.sendEmail(emailAddress,subject,message);
  }
};

Just assign this function to your button and it should work.

Refer to this link for more information on sending Emails from SpreadSheet using App Script: https://productivityspot.com/automatically-send-emails-from-google-sheets/
On how you can add function to button: https://spreadsheet.dev/buttons-in-google-sheets

CodePudding user response：

Try

function sendEmail() {
  var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
  var emailAddress = sh.getRange('A2:A' sh.getLastRow()).getValues().join();
  var subject = sh.getRange('B2').getValue();
  var message = sh.getRange('C2').getValue();
  MailApp.sendEmail(emailAddress, subject, message);
}

instead of a button, you can add custom menu

function onOpen() {
  SpreadsheetApp.getUi().createMenu('⇩ M E N U ⇩')
    .addItem('