standing-emailer.js

/*
 * This is an emailer appscript in google sheets to email students
 * Sheet requirements:
 * 1. First row should be headers (e.g. EXAM1, QUIZ, etc.)
 * 2. Second row should be the total (e.g. 80, 100, etc.)
*/

//-------- Change the values of the variables below to customize  --------

//The course
var subject = "CMSC 137";

//The section
var section = "AB";

//The semester
var semester = "Second Semester AY 2023-2024";

//The column where the name of the student is 
var nameCol = 'G';

//The column where the email of the student is
var emailCol = 'H';

//Column indicating that the email was sent already
var statusCol = 'AR';        //where to indicate that email has been sent

//The actual data row, row 3
var startRow = 3;           // First row of data to process

//The number of rows to process, NOTE!! this should include header and total rows
//actual data row in this example is only 2.
var numRows = 4;            // Number of rows to process

//The specific columns that will be included in the email
var colsToSend = ['N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AD','AF','AG','AJ','AK','AL','AM','AN','AO','AP']

//Sender
var sender = 'JACH';

// ------------- End of customization variables ----------


//--------- You don't need to touch the code blow if you are not into programming -----------------

//The actual function for sending the email
function sendEmails(){
  var sheet = SpreadsheetApp.getActiveSheet(); //get the active sheet
  var dataRange = sheet.getRange(1, 1, numRows, letterToColumn(statusCol));
  var data = dataRange.getValues();

  //get the header row and the total row
  var headerRow = data[0];
  var totalRow = data[1];
  
  //for each entry row
  for (var i = startRow-1; i < data.length; ++i) {
    var entryRow = data[i];


    var emailSubject = "Your current " + subject +" standing (Section "+section +" "+ semester+")";

    var emailAddress = entryRow[letterToColumn(emailCol)-1]
    var name = entryRow[letterToColumn(nameCol)-1]
 
    var emailMessage =  "Hello " + name + ",\n\nPlease check your scores and standing below: \n\n";
     for (var j=0; j< colsToSend.length; j++){
      //Logger.log(colsToSend[j])
      colIndex = letterToColumn(colsToSend[j])-1
      emailMessage += headerRow[colIndex] + ": " + entryRow[colIndex] + "/" + totalRow[colIndex]+ "\n";
    }

    emailMessage += "\n\nRegards,\n" + sender;
    emailMessage += "\n\n\nNOTE: This email was automatically generated from a script."
    emailMessage += "\nhttps://gist.github.com/jachermocilla/256d49a1d2b29dc7b52f1b05ccb14811"

    Logger.log(emailMessage)
  
    var emailSentStatus = entryRow[letterToColumn(statusCol)-1]; 
    //Check if the email was sent already to prevent duplicates
    if (emailSentStatus != "EMAIL_SENT") {

      MailApp.sendEmail(emailAddress, emailSubject, emailMessage);
      sheet.getRange(i+1, letterToColumn(statusCol)).setValue("EMAIL_SENT");

      // Make sure the cell is updated right away in case the script is interrupted
      SpreadsheetApp.flush();
    }

  }
}


//utility functions

function columnToLetter(column)
{
  var temp, letter = '';
  while (column > 0)
  {
    temp = (column - 1) % 26;
    letter = String.fromCharCode(temp + 65) + letter;
    column = (column - temp - 1) / 26;
  }
  return letter;
}

function letterToColumn(letter)
{
  var column = 0, length = letter.length;
  for (var i = 0; i < length; i++)
  {
    column += (letter.charCodeAt(i) - 64) * Math.pow(26, length - i - 1);
  }
  return column;
}


//GUI
function onOpen() {
  var ui = SpreadsheetApp.getUi();
   ui.createMenu('Email Options')
      .addItem('Email Standing', 'menuItem1')
      .addToUi();
}

function menuItem1() {
  sendEmails();
  SpreadsheetApp.getUi().alert('Emails sent!');
}