//Automagically send emails using Google Sheets - August 2018
//michel.g.arsenault@gmail.com

function sendAssignment() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Schedule");
  var startRow = 2;  // First row of data to process
  var numRows = sheet.getLastRow()-1;   // Number of rows to process
  var dataRange = sheet.getRange(startRow, 1, numRows, sheet.getLastColumn()); // Fetch the range of cells being used A2:LastUsed
  var data = dataRange.getValues(); // Fetch values for each row in the Range.
  
  var templateText = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Template").getRange(1,1).getValue(); //Get template text from first cell in Template sheet
  var EMAIL_SENT = 'EMAIL_SENT';

for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var date = new Date();
    var sheetDate = new Date(row[0]);

    //Make date formats the same for comparisson
    Sdate = Utilities.formatDate(date,'GMT-0300','EEE, MMM d, yyyy')
    SsheetDate = Utilities.formatDate(sheetDate,'GMT-0300','EEE, MMM d, yyyy')
    
   
    
    //Look through sheet for date in the first row that corresponds to today to build emails
    if (Sdate == SsheetDate){
      if (row[6] != EMAIL_SENT) { // Prevents sending duplicates
       var emailAddress = row[1]; 
       var subject = "Primary Assignment Reminder";
       
       var ChildName = row[2];
       var SundayDate = row[3];
       var Assignment = row[4]; 
       var Theme = row[5];
      
       var SundayDate = Utilities.formatDate(SundayDate,'GMT-0300','MMM d');  
   

    // Another option for the email text is to concatinate it directly rather than use a template with replacement
    //var emailText = "Hello, this is a reminder that this Sunday " + row[2]  + ", " + row[3]  + " has a " + row[4]+ " in Primary. This week's theme is " + row[5] +  " Thank you! The Primary Presidency";      


      //Replace the {STANDINS} in the template with the values assigned to variables from the Data Spreadsheet
      var emailText = templateText.replace("{SundayDate}", SundayDate).replace("{ChildName}",ChildName).replace("{Assignment}", Assignment).replace("{Theme}",Theme);           
      

      //Use the logger here to check that your template replacement has worked properly 
      //Logger.log(emailText);

      //Send the mail
      //MailApp.sendEmail(emailAddress, subject, emailText);

      //Add Email sent indication to end of row
      sheet.getRange(startRow+i,7).setValue("EMAIL_SENT");
      

      //Email yourself to let you know what email was sent
      //Logger.log('SENT :'+emailAddress+'  '+subject+'  '+emailText)
      //var body = Logger.getLog();
      
     }    
   }
//MailApp.sendEmail("YOUREMAIL@EMAIL","READ THIS!! - Sent Assignment" , body);
 }
}