getSheetValuesSendEmail.js

function myFunction() {
 
  function calcPercent(totalPts, scoredPts, percentValue) {
    
    return (scoredPts/totalPts)*percentValue;  
  }
  
 var ss = SpreadsheetApp.getActiveSheet(); 
 var dataRange = ss.getDataRange();
 var dataValues = dataRange.getValues();  
  
  for (var i = 1; i < dataValues.length; i++) {
  
   var studentName = dataValues[i][1]; 
   var studentEmail = dataValues[i][2];
   var assessmentType = dataValues[i][3];
   var semester = dataValues[i][5];
   var assessors = dataValues[i][6];
  
   //Skill one  
   var skill1_1 = dataValues[i][7];
   var skill1_2 = dataValues[i][8];
   var skill1_3 = dataValues[i][9];
   var skill1_total = skill1_1 + skill1_2 + skill1_3; 
   var skill1_pct = calcPercent(30, skill1_total, 15);  
   var commentsSkill1 = dataValues[i][10]; 
    
   //Skill two 
   var skill2_1 = dataValues[i][11];
   var skill2_2 = dataValues[i][12]; 
   var skill2_3 = dataValues[i][13];
   var skill2_total = skill2_1 + skill2_2 + skill2_3; 
   var skill2_pct = calcPercent(30, skill2_total, 20);
   var commentsSkill2 = dataValues[i][14]; 


   //Skill three 
   var skill3_1 = dataValues[i][15];
   var skill3_2 = dataValues[i][16];  
   var skill3_total = skill3_1 + skill3_2; 
   var skill3_pct = calcPercent(20, skill3_total, 20); 
   var commentsSkill3 = dataValues[i][17];
    
   //Skill four 
   var skill4_1 = dataValues[i][18];
   var skill4_2 = dataValues[i][19];
   var skill4_total = skill4_1 + skill4_2; 
   var skill4_pct = calcPercent(20, skill4_total, 15);  
   var commentsSkill4 = dataValues[i][20]; 
    
   //Skill five 
   var skill5_1 = dataValues[i][21];
   var skill5_2 = dataValues[i][22];
   var skill5_total = skill5_1 + skill5_2; 
   var skill5_pct = calcPercent(20, skill5_total, 10);  
   var commentsSkill5 = dataValues[i][23];
    
   //Skill six 
   var skill6_1 = dataValues[i][24]; 
   var skill6_total = skill6_1; 
   var skill6_pct = calcPercent(10, skill6_total, 10); 
   var commentsSkill6 = dataValues[i][25]; 
    
   //Skill seven 
   var skill7_1 = dataValues[i][26]; 
   var skill7_total = skill7_1; 
   var skill7_pct = calcPercent(10, skill7_total, 10);  
   var commentsSkill7 = dataValues[i][28]; 
    
   //Reassessment 
   var reassessment = dataValues[i][30];
   var reassessmentDate = dataValues[i][31];
   var reassessmentComments = dataValues[i][32];  
   var passFail = dataValues[i][33];
   var overallComments = dataValues[i][34]; 
    
   
   var reportContent = "Art Assessment Results-" 
   + studentName +" | " + studentEmail 
   + "\n\n" + "Assessment Type: " + assessmentType 
   + "\n\n" + "Assessment Semester: " + semester 
   + "\n\n" + "Assessors: " + assessors
   + "\n\n" + "Skill 1: Communication (15%): "  
   + "\n"   + "Skill 1.1 Clarity/Quality of Writing: " + skill1_1 + "/10"  
   + "\n"   + "Skill 1.2 Clarity/Quality of Speaking: " + skill1_2 + "/10"  
   + "\n"   + "Skill 1.3 Correlation of Statement of Work: " + skill1_3 + "/10" 
   + "\n"   + "Skill 1 Total Percent: " + skill1_pct + "% out of 15%" 
   + "\n"   + "Skill 1 Comments: " + commentsSkill1   
   + "\n\n" + "Skill 2: Formal/Technical (20%): "
   + "\n"   + "Skill 2.1 Craft: " + skill2_1 + "/10"  
   + "\n"   + "Skill 2.2 Formal Properties: " + skill2_2 + "/10"  
   + "\n"   + "Skill 2.3 Exploration of Materials: " + skill2_3 + "/10"
   + "\n"   + "Skill 2 Total Percent: " + skill2_pct + "% out of 20%" 
   + "\n"   + "Skill 2 Comments: " + commentsSkill2
   + "\n\n" + "Skill 3: Conceptual (20%): "
   + "\n"   + "Skill 3.1 Quality of Concept: " + skill3_1 + "/10"  
   + "\n"   + "Skill 3.2 Concept Editing and Development: " + skill3_2 + "/10" 
   + "\n"   + "Skill 3 Total Percent: " + skill3_pct + "% out of 20%" 
   + "\n"   + "Skill 3 Comments: " + commentsSkill3
   + "\n\n" + "Skill 4: Critical Thinking/Research (15%): "
   + "\n"   + "Skill 4.1 Research: " + skill4_1 + "/10"  
   + "\n"   + "Skill 4.2 Originality: " + skill4_2 + "/10"
   + "\n"   + "Skill 4 Total Percent: " + skill4_pct + "% out of 15%" 
   + "\n"   + "Skill 4 Comments: " + commentsSkill4
   + "\n\n" + "Skill 5: Historic and Contemporary Relevance (10%): "
   + "\n"   + "Skill 5.1 Knowledge of Artist: " + skill5_1 + "/10"  
   + "\n"   + "Skill 5.2 Knowledge of Thinking: " + skill5_2 + "/10"
   + "\n"   + "Skill 5 Total Percent: " + skill5_pct + "% out of 10%"
   + "\n"   + "Skill 5 Comments: " + commentsSkill5
   + "\n\n" + "Skill 6: Thematic Body of Work (10%): "
   + "\n"   + "Skill 6.1 Quality of Body of Work: " + skill6_1 + "/10" 
   + "\n"   + "Skill 6 Total Percent: " + skill6_pct + "% out of 10%"   
   + "\n"   + "Skill 6 Comments: " + commentsSkill6
   + "\n\n" + "Skill 7: Professional Skills (10%): "
   + "\n"   + "Skill 7.1 Quality of Resume: " + skill7_1 + "/10" 
   + "\n"   + "Skill 7 Total Percent: " + skill7_pct + "% out of 10%"    
   + "\n"   + "Skill 7 Comments: " + commentsSkill7
   + "\n\n" + "Pass: " + passFail
   + "\n"   + "Overall Comments: " + overallComments
   + "\n\n" + "Need for Reassessment: " + reassessment
   + "\n"   + "Date of Reassessment: " + reassessmentDate
   + "\n"   + "Reassessment Comments: " + reassessmentComments ; 
   
  
    
    
  var doc = DocumentApp.create("Art Assessment Results-" + studentName); 
   var body = doc.getBody(); 
   body.appendParagraph(reportContent); 
   doc.saveAndClose();  
   var newReport = doc.getAs("application/pdf"); 
   
   
  
   MailApp.sendEmail(studentEmail, "Art Assessment Results", "Please find attached a PDF copy or your art assessment results. If you are a first, second, or third-year student, you may see some zeros since we did not assess that skill.***Please note that this is not a grade calcuated using a hundred point scale. Refer to the Pass section on the last page.", {
    name: "Art Assessment Results", 
    attachments: newReport
  });
    
  MailApp.sendEmail("nelsonkellym5@gmail.com","Art Assessment Results", "Please find attached a PDF copy or your art assessment results. If you are a first, second, or third-year student, you may see some zeros since we did not assess that skill.***Please note that this is not a grade calcuated using a hundred point scale. Refer to the Pass section on the last page.", {
    name: "Art Assessment Results", 
    attachments: newReport
  });  
    
    
    
    Logger.log(dataValues[i]); 
  
  }//End for loop 
 
}//End function