spring-symposium-emails.js

function generalSymEmail() {
  const recipient = 'sym@middlebury.edu';//sym@middlebury.edu 
  const subject = 'Spring Symposium Application'
  const body = betterAllData();
  MailApp.sendEmail({
    to: recipient,
    replyTo: 'sym@middlebury.edu',
    name: 'Spring Student Symposium',
    subject: subject,
    htmlBody: body,
  });
}



function sponsorEmail() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const rows = sheet.getDataRange();
  const lastRow = rows.getLastRow();

  const recipient = sheet.getRange('T'+lastRow).getValue();
  const facultyName = sheet.getRange('Q'+lastRow).getValue() + ' ' + sheet.getRange('R'+lastRow).getValue();
  const studentName = sheet.getRange('H'+lastRow).getValue() + ' ' + sheet.getRange('I'+lastRow).getValue()

  const subject = 'Spring Symposium Application - Sponsor Notification';
  const submissionDetails = betterAllData();
  const body =facultyEmailBody(studentName, facultyName, submissionDetails);
  MailApp.sendEmail({
    to: recipient,
    replyTo: 'sym@middlebury.edu',
    name: 'Spring Student Symposium',
    subject: subject,
    htmlBody: body,
  });
}



function getDataElement(column){
   const sheet = SpreadsheetApp.getActiveSheet();
   const rows = sheet.getDataRange();
   const lastRow = rows.getLastRow();
   const qTitle = sheet.getRange(column+1).getValue();
   const qAnswer = sheet.getRange(column+lastRow).getValue();
   return qTitle + ' -- ' + qAnswer;
}

function betterAllData(){
  const sheet = SpreadsheetApp.getActiveSheet();
  const rows = sheet.getDataRange();
  const lastRow = rows.getLastRow();
  const newEntry = sheet.getRange('A'+lastRow+':V'+lastRow).getValues();//Set range you want to get data from
  let body = '';
  let titleCount = 1;//account for difference between all rows and range selected so it needs to change based on the difference from the range and the total sheet columns
  for (var row in newEntry) {
    for (var col in newEntry[row]) {
    let question = sheet.getRange(1,titleCount+parseInt([col])).getValue();
    let answer = newEntry[row][col];
    if(answer != ''){
      body = body +`<strong>${question}</strong><br><p>${answer}</p></br>`;
    } else {
      body = body +`<strong>${question}</strong><br><p>Not Submitted</p></br>`;
    }
  }
}
return body;
}



function facultyEmailBody(studentName, advisorName, submissionDetails){
  let emailBody = `Dear ${advisorName},</br></br>
 
${studentName} has submitted the attached proposal to present at the Spring Student Symposium on April 22, 2022 and indicated you as the sponsor. Please review the information and be aware that the final abstract is due Wednesday, March 30, 2022.  The schedule and materials will be online in a private event this year but titles or other information could potentially appear publicly. Please let us know if you have any restrictions and/or advise your student to generalize the title adequately.</br></br>
 
We appreciate your willingness to advise this student as they prepare their presentation for the symposium. For students presenting works in progress, please note that we welcome their presentation even if the project is not completed by the symposium date. We do remind students of this, but wanted to make you aware as well. If you do have any concerns or questions, please do let us know at sym@middlebury.edu.</br></br>
 
Thank you for sponsoring this student and participating in the Spring Student Symposium!</br></br>
  
The 2022 Spring Student Symposium Planning Committee</br></br></br></br></br>


*****Submission Details******</br>
${submissionDetails}
`

return emailBody;

}