Search code examples
google-apps-scriptgoogle-sheetsgmail

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

I have a sheet with data that must serve as input parameters to emails in gmail. Here is an example sheet, but basically this is it:

enter image description here

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 example sheet?

enter image description here

Solution

  • I have an example code that might work for you this would take the rows of the emails and send different emails:

    function sendEmail() {

var ss = SpreadsheetApp.getActiveSpreadsheet()
var sheet1=ss.getSheetByName('Sheet4');
var n=sheet1.getLastRow();
for (var i = 2; i < n+1 ; i++ ) {
var emailAddress = sheet1.getRange(i,1).getValue();
var subject = sheet1.getRange(i,2).getValue();
var message = sheet1.getRange(i,3).getValue();
MailApp.sendEmail(emailAddress, subject, message);
}

}

    By inserting a Google Drawing:

    You add a button via the Insert > Drawing menu.

    You edit the drawing and after finishing it, you click on the three dots and assign a script, you just need to write the function name, in my case it is the

    sendEmail

    You can take a look at the Sheet and how it looks, very similar to yours.

    Also if you are looking only to send a template email to all the emails from column A, you can use this one:

    function sendEmail() {

var ss = SpreadsheetApp.getActiveSpreadsheet()
var sheet1=ss.getSheetByName('Sheet4');
var sheet2=ss.getSheetByName('somesheet');
var subject = sheet2.getRange(2,1).getValue();;
var message = sheet2.getRange(2,2).getValue();
var n=sheet1.getLastRow();
for (var i = 2; i < n+1 ; i++ ) {
var emailAddress = sheet1.getRange(i,1).getValue();
MailApp.sendEmail(emailAddress, subject, message);
}

}

    Note:

    -In order to use the template example, I used two workbooks or worksheets.