https://www.pbainbridge.co.uk/2019/01/student-feedback-system-send-emails.html

Student Feedback system - send emails.gs

function onOpen() {
  
  SpreadsheetApp.getUi()
  .createMenu('Feedback')
  .addItem('Send Student Email', 'sendEmailsConfirm')
  .addToUi();
  
}


function sendEmailsConfirm() {
  
  var ui = SpreadsheetApp.getUi();

  var result = ui.alert(
     'Send Student 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".
    sendEmails() //run this Function to send emails
  } else {
    // User clicked "No" or X in the title bar so do nothing further
  }
  
}


function sendEmails() {
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  ss.toast('Starting to send emails');
  var fileName = ss.getName();
  
  var groupSheet = ss.getSheetByName('Group Feedback');
  var groupSheetData = groupSheet.getDataRange().getValues();
  var numRows = groupSheet.getLastRow();
  
  // used later to determine if new emails have been sent to then update Master Google Sheet
  var newEmails = 0;
  
  
  // loop through each Student email address to get relevant row data ****************************************
  for (var i=2; i<numRows; i++) {
    
    var emailAddress = groupSheetData[i][0];
    var emailSent = groupSheetData[i][10];
    
    // check email address not blank and no value in 'Email Sent?' Column
    if (emailAddress && emailSent == '') {
      
      var studentName = groupSheetData[i][1]
      
      var attendance1 = groupSheetData[i][2]
      var contribution1 = groupSheetData[i][3]
      var attitude1 = groupSheetData[i][4]
      var comments1 = groupSheetData[i][5]
      
      var attendance2 = groupSheetData[i][6]
      var contribution2 = groupSheetData[i][7]
      var attitude2 = groupSheetData[i][8]
      var comments2 = groupSheetData[i][9] 
      
      // ************ START OF EDIT EMAIL SECTION ************
      
      // edit text within single quotes below to change email subject
      var subject = fileName;
      
      // edit text within single quotes below to change email body
      var body = 'Dear ' + studentName + " \n\n";
      
      body+= 'Here is your feedback for your contributions in PBL sessions this term. You will see that it has descriptors of your performance ' +  
      'which are related to the criteria for assessment of contributions and also that there is constructive feedback to help you to identify your ' +
      'contributions next term. Please note that we are not including a mark at this point because we hope that you will develop further next term. ' +
      'Instead we hope that you will use this feedback to gain a sense of how well you are performing in general terms and to ' +
      'identify how you might improve that performance next term.' + " \n\n";
      body+= '------------------------------------------- \n';
      body+= groupSheetData[0][2] + " \n";
      body+= 'Attendance: ' + attendance1 + " \n";
      body+= 'Contribution to Learning: ' + contribution1 + " \n";
      body+= 'Attitude and Behaviours: ' + attitude1 + " \n";
      body+= 'Comments: ' + " \n";
      body+= comments1 + " \n";
      body+= '------------------------------------------- \n\n';
      
      body+= '------------------------------------------- \n';
      body+= groupSheetData[0][6] + " \n";
      body+= 'Attendance: ' + attendance2 + " \n";
      body+= 'Contribution to Learning: ' + contribution2 + " \n";
      body+= 'Attitude and Behaviours: ' + attitude2 + " \n";
      body+= 'Comments: ' + " \n";
      body+= comments2 + " \n";
      body+= '------------------------------------------- \n';
      Logger.log(body);
      
      // edit any options for email below
      var options = {noReply:true};
      
      // try/catch to prevent script error if invalid email address
      try {
        // send email
        MailApp.sendEmail(emailAddress, subject, body, options);
        var invalidEmail = false; // used to determine if timestamps created
      }
      catch(e) {
        var invalidEmail = true; // used to determine if timestamps created
        Logger.log('Error with email: ' + emailAddress + ' ' + e);
      }
      
      // ************ END OF EDIT EMAIL SECTION **************
      
      
      // only continue 'if' not invalid email
      if (invalidEmail == false) {
        
        // write timestamp to confirm email been sent
        var date = new Date;
        var timestamp = Utilities.formatDate(date, SpreadsheetApp.getActive().getSpreadsheetTimeZone(), "dd MMM yyyy");
        groupSheet.getRange(i+1, 11).setValue(timestamp);
        
        // update variable as emails have been sent, so know to update Master Google Sheet
        var newEmails = 1;
        
      }
      else {
        Logger.log('Invalid email address was found');
      }
      
    }// end of check 'if' email address blank
    else {Logger.log('Email address is blank or email already sent, row: ' + i)}
    
  }// end of loop through each Student email address to get relevant row data *******************************
  
  
  // check variable to see 'if' any new emails have been sent, to then update Master Google Sheet timestamp
  if (newEmails == 1) {
    
    // get Group ID from filename so this can be looked up in the Master Google Sheet
    var groupID = fileName.split(': ');
    Logger.log('Group ID from filename is: ' + groupID[0]);
    
    // get Master Google Sheet to lookup Group ID
    var masterFileID = ss.getSheetByName('Config').getRange(4, 2).getValue();
    var masterFile = SpreadsheetApp.openById(masterFileID).getSheetByName('Groups');
    var masterNumRows = masterFile.getLastRow();
    Logger.log('masterNumRows is: ' + masterNumRows);
    var masterGroupID = masterFile.getRange(2, 1, masterNumRows).getValues(); // 2-D array
    Logger.log(masterGroupID[0][0]);
    
    // loop through Group IDs looking for match ***************************************************************
    for (var j=0; j<masterNumRows; j++) { 
      if (groupID[0] == masterGroupID[j][0]) {
        Logger.log(groupID[0] + ' matches ' + masterGroupID[j][0]);
        masterFile.getRange(j+2, 5).setValue(timestamp);
      }
    }// end of loop through Group IDs looking for match ******************************************************
    
  }// end of check 'if' any new emails have been sent
  else {Logger.log('No new emails have been sent')}
  
  ss.toast('Sending emails complete');
  
}