SheetsEmailTable

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Send Email')
      .addItem('Send Points', 'sendEmailToStudent')
      .addToUi();
}

function sendEmailToStudent()
{

  //capture the sheet with this identifier
  var sheet = SpreadsheetApp.getActive().getSheetByName("ChCh");

  //get the email address of the student
  var email = sheet.getRange(1, 2).getValue(); //loads the current email address into variable "email". Notice columns are referred to as numbers.

  //get student data for this kid
  var dataGrid = sheet.getRange(2, 1, sheet.getLastRow(), 3).getValues(); //loads the data in this range at the time
  var message = ""; //container to hold final email content

  //set up the HTML to be rendered by sendEmail eventually.
  //adding a table for the column headings and content inside it.

  message = "  <table>";
  message += "<tr>"
  message += "<td><b>Challenge</td>"
  message += "<td><b>Points</td>"
  message += "<td><b>Challenge Name</td>"
  message += "</b> </tr>"

  //Loop through the content in data grid and set them up in each column with new row for each.
  for(var i=1;i<dataGrid.length; i++)
  {
    message+= "<tr>"
    message+= "<td>" + dataGrid[i][0] + "</td>"
    message+= "<td>" + dataGrid[i][1] + "</td>"
    message+= "<td>" + dataGrid[i][2] + "</td>"
    message+= "</tr>"
  }
  //end of table tag
  message +="</table>"


  //send email. Done!

MailApp.sendEmail(email, "Your Points Update", "", {htmlBody:message});
  Browser.msgBox("Email sent!");
  }