Google Script To notify with an email to multiple recipients with cell values in tabular formate

What this script does:
This script will get the recent changes from the google sheet and will send to multiple recipients given with the data that is entered in the sheet. Simply the last row is targeted and fetched and sent to recipients with the headers in a table formate.

Anyone can use this script and change the script names and manipulate the code to run on their google sheet.

Instructions:

1. Open the sheet where you want to trigger this code.

2. Click on Tools - > Set notification rules -> shown in below image

3

3.Then save.

4. Again click on Tools -> select "script edtitor" and paste this whole script there.

5. Set the sheet name as your sheet name, and your email ids where you want the mail to be sent.

6. set the trigger when to fire this script select on resources  - > current project Trigger and set it as shown in below image. If you want this should be fire on onEdit. you can set trigger as you want. They are self-explanatory.

save and click on run select testMail function and check your mail.

function testMail(){

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var responses = ss.getSheetByName("Sheet Name"); // Selecting Sheet

  var lastRow = responses.getLastRow(); // Getting the last row value

  var values = responses.getRange("A"+(lastRow)+":G"+(lastRow)).getValues(); // range of columns to select 

  var headers = responses.getRange("A1:AG1").getValues(); // range of headers

  var message = composeMessage(headers,values); 

  var messageHTML = composeHtmlMsg(headers,values);

  var emails = ["emailid","emailid"]; // email recipents array

  var i=0;

  Logger.log(messageHTML);

  for(var chk=0; chk < values[0].length;++chk){ // checking if all cell values are filled or not

    if(values[0][chk] != ""){

    i++;

    }

  }

  

  if(i == values[0].length){

  for (var i = 0; i < emails.length; i++){

  MailApp.sendEmail(emails[i],'Leave Response', message,{'htmlBody':messageHTML}); // sending mails to all recipents

  }

  }

  }

// below code is for creating structured tables 

function composeMessage(headers,values){

  var message = 'Here are the data of Leave Response :\n'

  for(var c = 0;c < values[0].length;++c){

    message+='\n'+headers[0][c]+' : '+values[0][c]

  }

  return message;

}

function composeHtmlMsg(headers,values){

  var message = 'Here are the data of new Leave Response :<br><br><table style="background-color:lightblue;border-collapse:collapse;" border = 1 cellpadding = 5><th>Header</th><th>Values</th><tr>'

  for(var c = 0;c < values[0].length;++c){

    message+='<tr><td>'+headers[0][c]+'</td><td>'+values[0][c]+'</td></tr>'

  }

  return message+'</table>';

}

reference : http://stackoverflow.com/questions/17658409/how-to-format-email-from-spreadsheet-data-using-arrays