Multiple-page certificates

Docs-8

//Bound to a spreadsheet
//SCRIPT 1
function makeCerts() {
  const ss = SpreadsheetApp.getActiveSpreadsheet(),
    shCerts = ss.getSheetByName('CERTIFICATES'),
    data = shCerts.getDataRange().getValues();

  //Column array references and row numbers  
  const startRowNum = data[0][4], endRowNum = data[0][6];

  if (endRowNum >= startRowNum) {
    //Get today's date
    const timeZone = Session.getScriptTimeZone(),
      date = new Date(),
      fDate = Utilities.formatDate(date, timeZone,
        'yyyy-MM-dd HH.mm');

    //////MAKE DOC/////
    //Get certificate, folder, and certificate body
    var newCert = DocumentApp.create("Certificates - " + fDate);
    var certFolder = DriveApp.getFolderById('FOLDER ID');
    var newCertId = newCert.getId();
    var openCert = DocumentApp.openById(newCertId);
    var body = openCert.getBody();

    //Get styles
    var style = setupStyling();

    ////ADD DATA TO DOC////
    addData(style, startRowNum, endRowNum, data,
      timeZone, body, shCerts, openCert);

    ////UPDATE SHEET////
    updateSheet(newCertId, certFolder, startRowNum,
      endRowNum, shCerts, ss);
  }
  else {
    Browser.msgBox("Start row entered is larger than end row.");
  }
}

//SCRIPT 2
function setupStyling() {
  //Set up styling   
  var center = DocumentApp.HorizontalAlignment.CENTER;
  var style0 = {}; //Common text
  style0[DocumentApp.Attribute.HORIZONTAL_ALIGNMENT] = center;
  style0[DocumentApp.Attribute.FONT_FAMILY] = 'Helvetica Neue';
  style0[DocumentApp.Attribute.FONT_SIZE] = 12;

  var style1 = {};  //Student text
  style1[DocumentApp.Attribute.HORIZONTAL_ALIGNMENT] = center;
  style1[DocumentApp.Attribute.FONT_FAMILY] = 'Helvetica Neue';
  style1[DocumentApp.Attribute.FONT_SIZE] = 16;
  style1[DocumentApp.Attribute.BOLD] = true;

  var style = [style0, style1];
  return style;
}

//SCRIPT 3
function addData(style, startRowNum, endRowNum, data,
  timeZone, body, shCerts, openCert) {

  const LANG = 0, LEVEL = 1, STUDENT = 2, START = 3,
    FINISH = 4, HOURS = 5, PASSED = 6, LINK = 7;

  var s0 = style[0];
  var s1 = style[1];

  /////LOOP THRU STUDENT DATA/////
  for (sRow = startRowNum - 1; sRow < endRowNum; sRow++) {

    //Get student data - Set up variable
    var language = data[sRow][LANG],
      studentName = data[sRow][STUDENT],
      level = data[sRow][LEVEL],
      startDate = data[sRow][START],
      startDate = Utilities.formatDate(startDate, timeZone,
        'dd/MM/yyyy'),
      finishDate = data[sRow][FINISH],
      finishDate = Utilities.formatDate(finishDate, timeZone,
        'dd/MM/yyyy'),
      hours = data[sRow][HOURS],
      passed = data[sRow][PASSED],
      borderColor = '#FFFFFF';

    //Add blank lines at the top of the doc 
    for (p = 0; p < 7; p++) {
      body.appendParagraph('');
    }

    //Confirms
    var t1Cells = [['', 'CONFIRMS THE ATTENDANCE OF']];
    var t1 = body.appendTable(t1Cells);
    t1.getRow(0).getCell(0).setWidth(180);
    t1.getCell(0, 1).getChild(0).asParagraph()
      .setAttributes(s0);
    t1.getRow(0).getCell(1).setWidth(350);
    t1.setBorderColor(borderColor);

    //Student Name      
    var t2Cells = [['', studentName]];
    var t2 = body.appendTable(t2Cells);
    t2.getRow(0).getCell(0).setWidth(180);
    t2.getCell(0, 1).getChild(0).asParagraph()
      .setAttributes(s1);
    t2.getRow(0).getCell(1).setWidth(350);
    t2.setBorderColor(borderColor);

    var t3Cells = [['', 'COMPLETING THE COURSE']];
    var t3 = body.appendTable(t3Cells);
    t3.getRow(0).getCell(0).setWidth(180);
    t3.getCell(0, 1).getChild(0).asParagraph()
      .setAttributes(s0);
    t3.getRow(0).getCell(1).setWidth(350);
    t3.setBorderColor(borderColor);

    //Language      
    var t4Cells = [['', language]];
    var t4 = body.appendTable(t4Cells);
    t4.getRow(0).getCell(0).setWidth(180);
    t4.getCell(0, 1).getChild(0).asParagraph()
      .setAttributes(s1);
    t4.getRow(0).getCell(1).setWidth(350);
    t4.setBorderColor(borderColor);

    if (passed === "Y") {
      var t5Cells = [['', 'PASSING THE LEVEL']];
    }
    else { var t5Cells = [['', 'LEVEL']]; }

    var t5 = body.appendTable(t5Cells);
    t5.getRow(0).getCell(0).setWidth(180);
    t5.getCell(0, 1).getChild(0).asParagraph()
      .setAttributes(s0);
    t5.getRow(0).getCell(1).setWidth(350);
    t5.setBorderColor(borderColor);

    //Level      
    var t6Cells = [['', level]];
    var t6 = body.appendTable(t6Cells);
    t6.getRow(0).getCell(0).setWidth(180);
    t6.getCell(0, 1).getChild(0).asParagraph()
      .setAttributes(s1);
    t6.getRow(0).getCell(1).setWidth(350);
    t6.setBorderColor(borderColor);

    //Hours
    var t7Cells = [['', 'CONSISTING OF A TOTAL OF', hours, 'HOURS']];
    var t7 = body.appendTable(t7Cells);
    t7.getRow(0).getCell(0).setWidth(180);
    t7.getCell(0, 1).getChild(0).asParagraph()
      .setAttributes(s0);
    t7.getRow(0).getCell(1).setWidth(180);
    t7.getCell(0, 2).getChild(0).asParagraph()
      .setAttributes(s1);
    t7.getRow(0).getCell(2).setWidth(60);
    t7.getCell(0, 3).getChild(0).asParagraph()
      .setAttributes(s0);
    t7.getRow(0).getCell(3).setWidth(60);
    t7.setBorderColor(borderColor);

    //Dates      
    var t8Cells = [['', 'FROM', startDate, 'TO', finishDate]];
    var t8 = body.appendTable(t8Cells);
    t8.getRow(0).getCell(0).setWidth(170);
    t8.getCell(0, 1).getChild(0).asParagraph()
      .setAttributes(s0);
    t8.getRow(0).getCell(1).setWidth(50);
    t8.getCell(0, 2).getChild(0).asParagraph()
      .setAttributes(s1);
    t8.getRow(0).getCell(2).setWidth(135);
    t8.getCell(0, 3).getChild(0).asParagraph()
      .setAttributes(s0);
    t8.getRow(0).getCell(3).setWidth(40);
    t8.getCell(0, 4).getChild(0).asParagraph()
      .setAttributes(s1);
    t8.getRow(0).getCell(4).setWidth(135);
    t8.setBorderColor(borderColor);

    for (p = 0; p < 5; p++) {
      body.appendParagraph('');
    }

    //Signatures
    var t9Cells = [['', 'DIRECTOR OF STUDIES', '', 'TEACHER']];
    var t9 = body.appendTable(t9Cells);
    t9.getRow(0).getCell(0).setWidth(170);
    t9.getCell(0, 1).getChild(0).asParagraph()
      .setAttributes(s0);
    t9.getRow(0).getCell(1).setWidth(150);
    t9.getCell(0, 2).getChild(0).asParagraph()
      .setAttributes(s0);
    t9.getRow(0).getCell(2).setWidth(70);
    t9.getCell(0, 3).getChild(0).asParagraph()
      .setAttributes(s0);
    t9.getRow(0).getCell(3).setWidth(150);
    t9.setBorderColor(borderColor);

    //Add additional page
    body.appendPageBreak();

    //Add quotes to rows without link    
    shCerts.getRange(sRow + 1, LINK + 1).setValue('----- " -----');
  }
  //save edits
  openCert.saveAndClose();
}

//SCRIPT 4
function updateSheet(newCertId, certFolder, startRowNum,
  endRowNum, shCerts, ss) {
  //Move Doc
  var newDoc = DriveApp.getFileById(newCertId);
  newDoc.moveTo(certFolder);

  //Add cert URL to Sheet         
  var noOfCerts = (endRowNum - startRowNum) + 1;
  var cell = shCerts.getRange(startRowNum, 8);
  cell.setFormula('=HYPERLINK("https://docs.google.com/document/d/'
    + newCertId +
    '/edit";"' + noOfCerts + ' Certificates")');
  ss.toast("Certificates made.", "Finished", 3);
}

//SCRIPT 5
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('CERTIFICATES')
    .addItem('Make Certificates', 'makeCerts')
    .addToUi();
}