Google Apps Script Tutorial to Schedule & Send HTML5 Template Emails inside Google Sheets Automatically

Welcome folks today in this blog post we will be scheduling html5 template email in google apps script to send all row data of sheet as email after specified amount of time. All the full source code of the application is shown below.

Get Started

In order to get started you need to make a new google sheet and inside it you need to add some data as shown below

After that we need to create the named range inside that we will be including all the data of rows that we need to sent inside the email as shown below

Writing the App Script Code

Now guys we will be writing the app script code for this application. Just go to the App Script section and inside it we need to copy paste the code in code.gs file

code.gs

function sendEmail() {
  var stockData = getData();
  var body = getEmailText(stockData);
  
  MailApp.sendEmail({
    to: "harshadchopra1997@gmail.com",
    subject: "Player update",
    body: body
  });
}

function getEmailText(playerData) {
  var text = "";
  playerData.forEach(function(player) {
    text = text + player.name + "\n" + player.age + "\n" + player.country + "\n-----------------------\n\n";
  });
  return text;
}

/**
 * @OnlyCurrentDoc
 */
function getData() {
  var values = SpreadsheetApp.getActive().getSheetByName("Sheet1").getRange("playerInfo").getValues();
  values.shift(); //remove headers
  var players = [];
  values.forEach(function(value) {
    var player = {};
    player.name = value[0];
    player.age = value[1];
    player.country = value[2];
    players.push(player);
  })
  //Logger.log(JSON.stringify(stocks));
  return players;
}

function sendEmail() {

var stockData = getData();

var body = getEmailText(stockData);

MailApp.sendEmail({

to: "harshadchopra1997@gmail.com",

subject: "Player update",

body: body

});

}

function getEmailText(playerData) {

var text = "";

playerData.forEach(function(player) {

text = text + player.name + "\n" + player.age + "\n" + player.country + "\n-----------------------\n\n";

});

return text;

}

/**

* @OnlyCurrentDoc

function getData() {

var values = SpreadsheetApp.getActive().getSheetByName("Sheet1").getRange("playerInfo").getValues();

values.shift(); //remove headers

var players = [];

values.forEach(function(value) {

var player = {};

player.name = value[0];

player.age = value[1];

player.country = value[2];

players.push(player);

})

//Logger.log(JSON.stringify(stocks));

return players;

}

As you can see in the above code we are first of all grabbing all the rows from the google sheet and then we are converting the row data to an array of objects using the getData() method and then we are using the MailApp library to send the email and then we are providing the email address and the body of the email.

Adding Triggers & Scheduling

Now we need to add the triggers and scheduling part to make sure only the script code runs after a specified amount of time. For this we need to go to triggers section as shown below

Now we will be running the script only at the specified date and time. We need to select the function that we will be executing as shown below

Now you can see that automatically when the time reaches then automatically script executes and the email is sent successfully to the target users as shown below

As you can see above we have received the html5 template email and we have got all the data there inside the named ranges that we have created in the google sheets.