Parse Google Form answers in SpreadSheets to separated Documents

Google Docs - Spreadsheet Parser

var numberInPage1 = 7; //Number of questions in Page 1
var numberInPage2 = 6;
var numberInPage3 = 7;
var parsedColor = '#ff0000'


// Define a custom Paragraph style.
  var parStyle = {};
  parStyle[DocumentApp.Attribute.HORIZONTAL_ALIGNMENT] 
    = DocumentApp.HorizontalAlignment.CENTER;
  parStyle[DocumentApp.Attribute.FONT_FAMILY] 
    = DocumentApp.FontFamily.CALIBRI;
  parStyle[DocumentApp.Attribute.FONT_SIZE] 
    = 21;
  parStyle[DocumentApp.Attribute.BOLD] 
    = true;
// Define a Question style.
  var queStyle = {};
  queStyle[DocumentApp.Attribute.HORIZONTAL_ALIGNMENT] 
    = DocumentApp.HorizontalAlignment.LEFT;
  queStyle[DocumentApp.Attribute.FONT_FAMILY] 
    = DocumentApp.FontFamily.CALIBRI;
  queStyle[DocumentApp.Attribute.FONT_SIZE] 
    = 11;
  queStyle[DocumentApp.Attribute.ITALIC] 
    = false;  
  queStyle[DocumentApp.Attribute.BOLD] 
    = true;
// Define a Answer style.
  var ansStyle = {};
  ansStyle[DocumentApp.Attribute.HORIZONTAL_ALIGNMENT] 
    = DocumentApp.HorizontalAlignment.LEFT;
  ansStyle[DocumentApp.Attribute.FONT_FAMILY] 
    = DocumentApp.FontFamily.CALIBRI;
  ansStyle[DocumentApp.Attribute.FONT_SIZE] 
    = 11;
  ansStyle[DocumentApp.Attribute.ITALIC] 
    = true;
  ansStyle[DocumentApp.Attribute.BOLD] 
    = false;  


function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [ {name: "Get new answers", functionName: "ParseTable"},
                      {name: "Coming soon..", functionName: "sayHello"} ];
  ss.addMenu("SynergyPartners", menuEntries);
}


function onSubmit(e) {
  //ParseTable();
  try {
    var work = e.values[1].toString();
    var name = e.values[2].toString();
    var date = e.values[0].toString();
    var message  = "Дата заполнения: "+date+"\n" + "ФИО кандидата: "+name+"\n" + "на должность: "+work+"\n\n";
    var contacts = "Связаться с кандидатом\n" + "Email: "+e.values[5].toString()+"\n" + "Телефон: "+e.values[6].toString()+"\n\n";
    
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
    
  var headerRange = sheet.getRange(1, 1, 1, sheet.getLastColumn());
  var headerValues = headerRange.getValues();
  var range = sheet.getRange(sheet.getLastRow(), 1, sheet.getLastRow(), sheet.getLastColumn());
  var values = range.getValues();
  var firstCell = range.getCell(1, 1);
    
    var url = ParseRow(headerValues, values, 0);
    firstCell.setBackground(parsedColor);
    
    var documentUrl = "Посмотреть анкету: "+url;
    
    
    MailApp.sendEmail("[email protected]", "Анкета Synergy Partners: кандидат "+name+" на должность "+work, message+contacts+documentUrl);
  } catch (alert) {
    MailApp.sendEmail("[email protected]", "Error with form submission response email", alert.message);
  }
}


function sayHello() {
  Browser.msgBox('Hello, Sattar Stamkulov!');
}


function ParseTable() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
    
  var headerRange = sheet.getRange(1, 1, 1, sheet.getLastColumn());
  var headerValues = headerRange.getValues();
  var range = sheet.getRange(2, 1, sheet.getLastRow(), sheet.getLastColumn());
  var values = range.getValues();
  
  var createdDocs = 0;
  
  // This should get every row
  for (var row in values) {
    // Skip if 1) Row parsed(if first Row is red);  2) %ФИО% is empty;
    var firstCell = range.getCell(parseInt(row)+1, 1)
    if ( values[row][2].length == 0 || firstCell.getBackground() == '#ff0000' ){
      continue;
    }
    
    ParseRow(headerValues, values, row);
    
    // Mark as parsed
    firstCell.setBackground(parsedColor);
    
    //count of created Docs
    createdDocs = createdDocs + 1;
    Logger.log(createdDocs);
  }
  
  Browser.msgBox('Добавлено ' + createdDocs + ' документов');
  return
}

function ParseRow(headerValues, values, row) {
    // Create a new document for every answer and set the title "%Должность% - %ФИО%"
    var doc = DocumentApp.create( values[row][1] + ' - ' + values[row][2] );
    
    
    //FIRST PAGE
    // Append a first paragraph.
    var par1 = doc.appendParagraph("Сведения о кандидате");
    par1.setAttributes(parStyle); // Apply the custom style.
    
    // Build and Append a table from the array.
    var cells = [
      [ 'На должность:', values[row][1]],
      [ 'Дата заполнения:', values[row][0]],
      [ 'Кандидат:', values[row][2]],
      [ 'Дата рожд.', values[row][7]],
      [ 'Адрес:', values[row][4]],
      [ 'Email:', values[row][5]],
      [ 'Телефоны:', values[row][6]]
    ];
    doc.appendTable(cells);
    

    //Create new page
    //doc.appendPageBreak();
    
    //SECOND PAGE
    // Append a second paragraph.
    var par2 = doc.appendParagraph("Общие анкетные данные");
    par2.setAttributes(parStyle); // Apply the custom style.
    
    //Create ListItem for Page2 questions
    var i = numberInPage1;
    var list = doc.appendListItem('')
    //Insert the First Answer
    list.insertText(0, values[row][i] + '\n');
    list.asText().setAttributes(ansStyle)
    var ans_len = list.getText().length
    //Insert the First Questions (before Answer) 
    list.insertText(0, headerValues[0][i] + '\n');
    list.asText().setAttributes(0, list.getText().length-ans_len-1, queStyle)

    // add next Questions and their Answers in 'ListItem of Page2 questions'
    for (var i = numberInPage1+1; i < (numberInPage1 + numberInPage2); i++){
      var list2 = doc.appendListItem('');
      list2.insertText(0, values[row][i] + '\n');
      list2.asText().setAttributes(ansStyle)
      ans_len = list2.getText().length
      list2.insertText(0, headerValues[0][i] + '\n');
      list2.asText().setAttributes(0, list2.getText().length-ans_len-1, queStyle)
      
      list2.setListId(list);
    }
    
    
    //Create new page
    doc.appendPageBreak();
    
    //THIRD PAGE
    // Append a third paragraph.
    var par3 = doc.appendParagraph("Интервью на продуктивность");
    par3.setAttributes(parStyle); // Apply the custom style.
    
    //Create ListItem for Page3 questions
    var i = numberInPage1+numberInPage2;
    var list = doc.appendListItem('')
    //Insert the First Answer
    list.insertText(0, values[row][i] + '\n');
    list.asText().setAttributes(ansStyle)
    var ans_len = list.getText().length
    //Insert the First Questions (before Answer) 
    list.insertText(0, headerValues[0][i] + '\n');
    list.asText().setAttributes(0, list.getText().length-ans_len-1, queStyle)

    // add next Questions and their Answers in 'ListItem of Page2 questions'
    for (var i = numberInPage1+numberInPage2+1; i < (numberInPage1 + numberInPage2 + numberInPage3); i++){
      var list2 = doc.appendListItem('');
      list2.insertText(0, values[row][i] + '\n');
      list2.asText().setAttributes(ansStyle)
      ans_len = list2.getText().length
      list2.insertText(0, headerValues[0][i] + '\n');
      list2.asText().setAttributes(0, list2.getText().length-ans_len-1, queStyle)
      
      list2.setListId(list);
    }
    
    // Save and close
    doc.saveAndClose();

    return doc.getUrl();
}