// create menu item across top of spreadsheet
function onOpen() {

  SpreadsheetApp.getUi()
  .createMenu('Email')
  .addItem('Send Email', 'sendEmailsConfirm') // label for menu item, name of function to run.
  .addToUi();

}


// create popup box for user confirmation
function sendEmailsConfirm() {

  var ui = SpreadsheetApp.getUi();

  var result = ui.alert(
    'Send Emails?',
    'Are you sure you want to continue?',
    ui.ButtonSet.YES_NO);

  // Process the user's response.
  if (result == ui.Button.YES) {
    // User clicked "Yes".
    sendEmail() // run this Function to send emails
  }
  else {
    // User clicked "No" or X in the title bar so do nothing further
  }

}


function sendEmail() {

  // get active spreadsheet
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  // get active sheet from spreadsheet
  var ss = spreadsheet.getActiveSheet();

  // get last row of data from sheet for later loop
  var lastRow = ss.getLastRow();
  Logger.log('Last row of data is: ' + lastRow);


  // loop through each row in turn to get specific data, send email and write timestamp ***********
  for (var i=2; i<lastRow+1; i++) {

    // get specific spreadsheet data ************************************
    var emailAddress = ss.getRange(i, 2).getValue();
    Logger.log('Email Address is: ' + emailAddress);

    var firstName = ss.getRange(i, 3).getValue();
    Logger.log('First Name is: ' + firstName);

    var lastName = ss.getRange(i, 4).getValue();
    Logger.log('Last Name is: ' + lastName);

    var awardStatus = ss.getRange(i, 5).getValue();
    Logger.log('Award Status is: ' + awardStatus);

    // get the 'Email Sent' cell and value separately for later use
    var emailSent = ss.getRange(i, 1);
    var emailSentValue = emailSent.getValue();
    // end of get specific spreadsheet data *****************************



    /*
    check 'Email Sent' column IS blank and Email Address column IS NOT blank before proceeding,
    to avoid duplicate email sending and skip where no email address given
    */
    if ( (emailSentValue == '') && (emailAddress != '') ) {

      // compose email *****************************************************
      var subject = 'Congratulations here is your result';

      var body = 'Dear ' + firstName + ' ' + lastName + '\n\n';
      body+= 'You have been awarded a: ' + awardStatus + '\n\n';
      body+= 'Thank you for participating.'
      Logger.log(body);

      var options = {replyTo:'reply@example.com'};
      // end of compose email **********************************************



      // use try/catch to send email to prevent error if problem with email address ****************
      try {
        // send email
        MailApp.sendEmail(emailAddress, subject, body, options);

        // set flag to true as send was successful
        var sendSuccessful = true;
      }

      catch(e) {
        Logger.log('Failed to send email: ' + e);

        // set flag to false as send was unsuccessful
        var sendSuccessful = false;
      }
      // end of use try/catch to send email to prevent error if problem with email address *********



      // check flag status before writing the relevant text back to the 'Email Sent' column *********
      // if flag is true - meaning email has been sent:
      if (sendSuccessful) {

        // get current date/time, timeZone and format nicely
        var date = new Date();
        var timeZone = spreadsheet.getSpreadsheetTimeZone();
        var timestamp = Utilities.formatDate(date, timeZone, "dd/MM/yy @ HH:mm:ss");

        // insert the timestamp value
        emailSent.setValue(timestamp);

      }
      // else if flag is not true - meaning email has not successfully sent:
      else {

        // insert error message
        emailSent.setValue('Error sending email');

      }
      // end of check flag status before writing the relevant text back to the 'Email Sent' column **


    }
    else {
      /*
      do nothing as either 'Email Sent' column is not blank or Email Address column is blank, so no
      attempts at sending email should be made
      */
    }



  }
  // end of loop through each row in turn to get specific data, send email and write timestamp ****

}