Generating reports from a G Sheet into a Doc

gistfile1.txt

/**
 * FOLDER_NAME
 * The path, relative to the Sheet the script is running from, where new reports are created
 */
var FOLDER_NAME = "Schedules";
/**
 * SPREADSHEET_MAPPING
 * Contains Template variables and the columns
var SPREADSHEET_MAPPING = {
  HAS_FORM: 6,
  FIRSTNAME_CELL: 1,
  LASTNAME_CELL: 0,
  
  TEACHER_NAME:4,
  HOMEROOM:3,
  GRADE:2,
  
  SESSION_1_NAME:7,
  SESSION_1_TEACHER:9,
  SESSION_1_ROOM:8,
  
  SESSION_2_NAME:10,
  SESSION_2_TEACHER:12,
  SESSION_2_ROOM:11,
  
  SESSION_3_NAME:13,
  SESSION_3_TEACHER:15,
  SESSION_3_ROOM:14,
  
  SESSION_4_NAME:16,
  SESSION_4_TEACHER:18,
  SESSION_4_ROOM:17
};

function onOpen(){
  const menuEntries = [ { 
    name: "Generate Student Schedule", 
    functionName: "generateStudentSchedule" 
  }, {
    name: "Generate Course Rosters",
    functionName: "generateCourseRosters"
  }, {
    name: "Generate Teacher Rosters",
    functionName: "generateTeacherRosters"
  }];
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  ss.addMenu("POE Forms", menuEntries);
}

function appendElementToDoc(doc, object) {
  var type = object.getType(); // need to handle different types para, table etc differently
  var element = object;
  element = object.copy();
  
  Logger.log('Content ' + element.getText());
  if (type == "PARAGRAPH") {
    doc.appendParagraph(element);
  } else if (type == "TABLE") {
    doc.appendTable(element);
  } else {
    Logger.log('erm ' + type);
  }
}

function appendToDoc(src, dst) {
  // iterate across the elements in the source adding to the destination
  const length = src.getNumChildren();
  Logger.log('Number of Children '+length);
  for (var i = 0; i < length; i++) {
    var child = src.getChild(i);
    Logger.log('Appending index:' + i +' and content '+ child.getText());
    appendElementToDoc(dst, child);
  }
}

function getSpreadsheetData() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  const data = sheet.getRange(2, 1, sheet.getLastRow()-1, sheet.getLastColumn()).getValues();
  return data;
}

function createDocument(name) {
  const username = Session.getActiveUser().getEmail();
  var newDoc = DocumentApp.create(name);
  newDoc.addEditor(username);
  return newDoc;
}

function addDocumentToFolder(document, folderName) {
  const newDocId = document.getId();
  const file = DriveApp.getFileById(newDocId);
  const folder = DriveApp.getFoldersByName(folderName).next(); // lazily get the first
  folder.addFile(file);
  return folder;
}

function generateStudentSchedule() {
  Logger.log('>>>');
  const templateid = "1hiopIDgPiQaBQi13QPbPjka5RgE9dtAK-CBumzSi03Q";
  const GENERATED_DATE = Utilities.formatDate(new Date(), "GMT", "MM/dd/yyyy");  
  const SESSION_1_DATE ='Thursday, February 1st';
  const SESSION_2_DATE ='Wednesday, February 7th';
  const SESSION_3_DATE ='Tuesday, February 13th';
  const SESSION_4_DATE ='Thursday, February 22nd';
  
  const data = getSpreadsheetData();
  const newDoc = createDocument("Student Schedules - " + Utilities.formatDate(new Date(), "GMT", "HH:mm dd/MM/yyyy"));
  addDocumentToFolder(newDoc,FOLDER_NAME);
  var content = '';
  
  var docid = DriveApp.getFileById(templateid).makeCopy().getId();
  var doc = DocumentApp.openById(docid);
  var docbody = doc.getActiveSection();
  for (var i in data){
    var row = data[i];
    // do nothing if no data or no lastname
    if(!row || !row[0]) { continue; }
    
    // Ignore students that don't have any signups
    if(row[SPREADSHEET_MAPPING.HAS_FORM] !== 'Yes') { continue; }
    
    // clone the template for injection
    var body = docbody.copy();
    body.replaceText("##STUDENT_NAME##", row[SPREADSHEET_MAPPING.FIRSTNAME_CELL] +' '+ row[SPREADSHEET_MAPPING.LASTNAME_CELL]);
    body.replaceText("##GENERATED_DATE##", GENERATED_DATE);
    body.replaceText("##SESSION_1_DATE##", SESSION_1_DATE);
    body.replaceText("##SESSION_2_DATE##", SESSION_2_DATE);
    body.replaceText("##SESSION_3_DATE##", SESSION_3_DATE);
    body.replaceText("##SESSION_4_DATE##", SESSION_4_DATE);
    
    // quickly loop through and update all mapped variables
    Object.keys(SPREADSHEET_MAPPING).forEach(function(key, index) {
      var value = row[SPREADSHEET_MAPPING[key]];
      if(!value || value.length < 1 || value === "#N/A" || value === "None") {
        value = '-';
      }
      body.replaceText('##'+key+'##',  value); 
    });
    
    Logger.log("Appending to the main document "+body.getText());
    appendToDoc(body, newDoc); // add the filled in template to the students
    newDoc.appendPageBreak();
    // batch output
    if((i % 100) === 0) {
      newDoc.saveAndClose();
      newDoc = DocumentApp.openById(newDocId);
    }
  }
  doc.saveAndClose();
  DriveApp.getFileById(docid).setTrashed(true); // delete temporary template file
  
  Logger.log('<<<');
  SpreadsheetApp.getActiveSpreadsheet().toast("Reports have been complied");
  
}

function generateCourseRosters() {
  const courseRoster = "1nP-O976VJp_5_LdmcSDxwIy55_GSKoBvYSmF9jCq3b0";
  const studentInformation = "1QvzR2v6YvvqmskJWgVkszBZ2XzAwVBjNcXiFTkvyfIE";
  
  // Generate a blank document
  var newDoc = createDocument("Course Roster - " + Utilities.formatDate(new Date(), "GMT", "HH:mm dd/MM/yyyy"));
  addDocumentToFolder(newDoc,FOLDER_NAME);

  const courses = {};
  const studentsInCourses = [];

  // Loop through all rows and group the students into each class per session
  const data = getSpreadsheetData();
  data.forEach(function(row, index) {
    // Do nothing if not signed up.
    // do nothing if no data or no lastname
    if(!row || !row[0]) { return; }
    
    // Ignore students that don't have any signups
    if(row[SPREADSHEET_MAPPING.HAS_FORM] !== 'Yes') { return; }

    // Loop each session
    for(var i = 1, j = 4; i<=j; i++) {
      // If the class doesn't exist, create it
      var session = i;
      var className = row[SPREADSHEET_MAPPING['SESSION_'+ session +'_NAME']];
      Logger.log("Class Name " + className);

      if(!className || className === 'None' || className === "#N/A") {
        // Skip, as no data!
        continue;
      }
      var id = "" + className + i;
      // Student has Class Data
      Logger.log("Location of Class in Array using id " + id + ": " + courses[id]);
      var course = null;
      if(typeof courses[id] == 'undefined') {
        Logger.log("Adding the course");
        courses[id] = studentsInCourses.length; // inline indexing
        course = { 'courseName': className, 'students': [], 'session': session, 'teacher': row[SPREADSHEET_MAPPING["SESSION_" + session + "_TEACHER"]] };
        studentsInCourses.push(course);
        Logger.log("Course Added " + course);
        Logger.log("New Course value " + courses[id]);
      }
      // Add student to the class with basic data
      var student = { firstName: row[SPREADSHEET_MAPPING.FIRSTNAME_CELL], lastName: row[SPREADSHEET_MAPPING.LASTNAME_CELL], teacher: row[SPREADSHEET_MAPPING.TEACHER_NAME], homeRoom: row[SPREADSHEET_MAPPING.HOMEROOM], grade: row[SPREADSHEET_MAPPING.GRADE] };
      Logger.log("Attempt to add the student " + JSON.stringify(student) + ": " + studentsInCourses[courses[id]]);
      studentsInCourses[courses[id]].students.push(student);
    } 
  });
  
  var courseTemplateId = DriveApp.getFileById(courseRoster).makeCopy().getId();
  var courseDocOpen = DocumentApp.openById(courseTemplateId);
  var courseDocBody = courseDocOpen.getActiveSection();
  
  var studentTemplateId = DriveApp.getFileById(studentInformation).makeCopy().getId();
  var studentDocOpen = DocumentApp.openById(studentTemplateId);
  var studentDocBody = studentDocOpen.getActiveSection();

  studentsInCourses.forEach(function(course, index){
    var courseTemplate = courseDocBody.copy();
    courseTemplate.replaceText("##COURSE_NAME##", course.courseName);
    courseTemplate.replaceText("##TEACHER_NAME##", course.teacher);
    courseTemplate.replaceText("##SESSION##", course.session);
    courseTemplate.replaceText("##TOTAL_STUDENTS##", course.students.length);
    appendToDoc(courseTemplate,newDoc);
    course.students.forEach(function(student) {
      var studentTemplate = studentDocBody.copy();
      studentTemplate.replaceText('##STUDENT_NAME##', student.firstName + " " + student.lastName);
      studentTemplate.replaceText('##STUDENT_TEACHER##', student.teacher);
      studentTemplate.replaceText('##STUDENT_ROOM##', student.homeRoom);
      studentTemplate.replaceText('##STUDENT_GRADE##', student.grade);
      appendToDoc(studentTemplate,newDoc);
    });
    newDoc.appendPageBreak();
    if( index % 5) {
      newDoc.saveAndClose();
      newDoc = DocumentApp.openById(newDoc.getId());
    }
  });
  // Loop though each group, and create a new document
  // Loop through each student in that group and append to that document
  // Append this document to the main document with a newline.
  
  newDoc.saveAndClose();
  DriveApp.getFileById(courseTemplateId).setTrashed(true); // delete temporary template file
  DriveApp.getFileById(studentTemplateId).setTrashed(true); // delete temporary template file
}

function generateTeacherRosters() {
  const teacherRoster = "1vQAyrZOemDfu33PeT8QzLe5DGFlInaCwbD3BMo3GawA";
  const studentInformation = "1KuXOlY_-AKLtOz5cZOY8Tv3wPvwWLeADmElMVH8cIdQ";
  
  // Generate a blank document
  var newDoc = createDocument("Teacher Roster - " + Utilities.formatDate(new Date(), "GMT", "HH:mm dd/MM/yyyy"));
  addDocumentToFolder(newDoc,FOLDER_NAME);

  const teachers = {};
  const studentsOfTeacher = [];

  // Loop through all rows and group the students into each class per session
  const data = getSpreadsheetData();
  data.forEach(function(row, index) {
    // Do nothing if not signed up.
    // do nothing if no data or no lastname
    if(!row || !row[0]) { return; }
    
    // Ignore students that don't have any signups
    if(row[SPREADSHEET_MAPPING.HAS_FORM] !== 'Yes') { return; }
    // Loop each session
    for(var i = 1, j = 4; i<=j; i++) {
      // If the class doesn't exist, create it
      var session = i;
      var className = row[SPREADSHEET_MAPPING['SESSION_'+ session +'_NAME']];

      if(!className || className === "None") {
        continue; // Skip this session/student
      }
      
      var id = row[SPREADSHEET_MAPPING.TEACHER_NAME] + session;
      var teacher = null;
      // Create teacher if not existing
      if(typeof teachers[id] == 'undefined') {
        Logger.log("Adding the teacher");
        teachers[id] = studentsOfTeacher.length; // inline indexing
        teacher = { 'session': session, 'students': [], 'teacher': row[SPREADSHEET_MAPPING.TEACHER_NAME] };
        studentsOfTeacher.push(teacher);
      }

      // Add student to the class with basic data
      var student = { 
        firstName: row[SPREADSHEET_MAPPING.FIRSTNAME_CELL], 
        lastName: row[SPREADSHEET_MAPPING.LASTNAME_CELL], 
        courseTeacher: row[SPREADSHEET_MAPPING['SESSION_'+session+'_TEACHER']],
        courseName: row[SPREADSHEET_MAPPING['SESSION_'+session+'_NAME']],
        courseRoom: row[SPREADSHEET_MAPPING['SESSION_'+session+'_ROOM']],
        grade: row[SPREADSHEET_MAPPING.GRADE]
      };
      Logger.log("Attempt to add the student " + JSON.stringify(student) + ": " + studentsOfTeacher[teachers[id]]);
      studentsOfTeacher[teachers[id]].students.push(student);
    }

  });

  var courseTemplateId = DriveApp.getFileById(teacherRoster).makeCopy().getId();
  var courseDocOpen = DocumentApp.openById(courseTemplateId);
  var courseDocBody = courseDocOpen.getActiveSection();
  
  var studentTemplateId = DriveApp.getFileById(studentInformation).makeCopy().getId();
  var studentDocOpen = DocumentApp.openById(studentTemplateId);
  var studentDocBody = studentDocOpen.getActiveSection();

  studentsOfTeacher.forEach(function(course, index){
    var courseTemplate = courseDocBody.copy();
    courseTemplate.replaceText("##TEACHER_NAME##", course.teacher);
    courseTemplate.replaceText("##SESSION##", course.session);
    courseTemplate.replaceText("##TOTAL_STUDENTS##", course.students.length);
    appendToDoc(courseTemplate,newDoc);
    course.students.forEach(function(student) {
      var studentTemplate = studentDocBody.copy();
      studentTemplate.replaceText('##STUDENT_NAME##', student.firstName + " " + student.lastName);
      studentTemplate.replaceText('##COURSE_TEACHER##', student.courseTeacher);
      studentTemplate.replaceText('##COURSE_NAME##', student.courseName);
      studentTemplate.replaceText('##COURSE_ROOM##', student.courseRoom);
      studentTemplate.replaceText('##STUDENT_GRADE##', student.grade);
      appendToDoc(studentTemplate,newDoc);
    });
    newDoc.appendPageBreak();
    if( index % 5) {
      newDoc.saveAndClose();
      newDoc = DocumentApp.openById(newDoc.getId());
    }
  });
  // Loop though each group, and create a new document
  // Loop through each student in that group and append to that document
  // Append this document to the main document with a newline.
  
  newDoc.saveAndClose();
  DriveApp.getFileById(courseTemplateId).setTrashed(true); // delete temporary template file
  DriveApp.getFileById(studentTemplateId).setTrashed(true); // delete temporary template file
}