How can I automate moving form responses from a google sheet to a google doc using google scripts?

Question

I have applications coming into a google spreadsheet. Essentially, I am trying to mail merge each application (row) to a page within a long google doc that I can read through the applications on. So I am trying to write a google script that 1. pulls each row from the form's response spreadsheet, 2. copies a template doc, 3. pastes the template in a newly made doc, and 4. replaces the text of the template with the form's response.

Rather than make an individual document for each application, I am hoping to just add a page break after replacing the text from the previous row and then copy the template again. That way, the code just loops through all of the rows in the spreadsheet and I only have to open one doc to read through the applications.

Here is the code I have been working with. I have used a multitude of tutorials to get to this point, but I can't figure out how to loop through the rows/paragraphs properly. The last iteration of my code resulted in all of the names on the first page, then the Id, then the email. Rather than all of one individual's information on the first page. So I've made more changes and now nothing is showing up in the newly made google doc.

The nested function, updateDoc() is what I am having issues with. Everything else seems to be working properly. The foreach and for loop are what is throwing me off. An array within an array is not something I have worked with prior to this project. (See lines 4 and 5 in the updateDoc() function)

function AutoFill() {
  // Global variables
  var templateFile = DocumentApp.openById('theID');
  var newDoc = DocumentApp.create('CopyOf'+ templateFile.getName());
  var copyTemplate = templateFile.getBody().getParagraphs();
  
  newDoc.getBody().clear();

  var sourceFileId = newDoc.getId();
  var targetFolderId = 'thisID';
  
  // Pull the data from spreadsheet
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  Logger.log('data pulled');
  data.forEach(function(r){
    updateDoc(data, copyTemplate, newDoc);
  });
  
  moveFiles(sourceFileId, targetFolderId); 
  Logger.log('File Moved');
  // move newDoc to 'Populated Applications' Folder
  function moveFiles(sourceFileId, targetFolderId) {
    var file = DriveApp.getFileById(sourceFileId);
    var folder = DriveApp.getFolderById(targetFolderId);
    file.moveTo(folder);
  } 

  // Update the doc w/ data from spreadsheet
  function updateDoc(data, copyTemplate, newDoc) {
    var rows = data.length;
    var cols = data[0].length;
    var rowNumber = 1;
    copyTemplate.forEach(function(p){
      for (var a=1; a < rows; a++){
          newDoc.getBody().appendParagraph(
          p
          .copy()
          .replaceText('{{name}}', data[a][2])
          .replaceText('{{ccaId}}', data[a][3])
          .replaceText('{{email}}', data[a][1])
          .replaceText('{{academicProgram}}', data[a][4])
          .replaceText('{{year}}', data[a][5])
          .replaceText('{{positions}}', data[a][6])
          .replaceText('{{Response1}}', data[a][7])
          .replaceText('{{Response2}}', data[a][8])
          .replaceText('{{Response3}}', data[a][9])
          .replaceText('{{Response4}}', data[a][10]));
          Logger.log(a + 'page completed'); 
      };
      newDoc.getBody().appendPageBreak();
    }); 
  }
}

Answer 1

Your code sends all the rows of data for every row of data to your updateDoc() function, which then processes all rows of data for every paragraph in your template - that's why you get what you do as a result.

Replace

data.forEach(function(r){
  updateDoc(data, copyTemplate, newDoc);
});

with just

updateDoc(data, copyTemplate, newDoc);

and change

copyTemplate.forEach(function(p){
  for (var a=1; a < rows; a++){
    newDoc.getBody().appendParagraph(
          p
          .copy()
          .replaceText('{{name}}', data[a][2])
          .replaceText('{{ccaId}}', data[a][3])
          .replaceText('{{email}}', data[a][1])
          .replaceText('{{academicProgram}}', data[a][4])
          .replaceText('{{year}}', data[a][5])
          .replaceText('{{positions}}', data[a][6])
          .replaceText('{{Response1}}', data[a][7])
          .replaceText('{{Response2}}', data[a][8])
          .replaceText('{{Response3}}', data[a][9])
          .replaceText('{{Response4}}', data[a][10]));
          Logger.log(a + 'page completed'); 
  };
  newDoc.getBody().appendPageBreak();
});

to

for (var a=1; a < rows; a++){
  copyTemplate.forEach(function(p){
    newDoc.getBody().appendParagraph(
      p
      .copy()
      .replaceText('{{name}}', data[a][2])
      .replaceText('{{ccaId}}', data[a][3])
      .replaceText('{{email}}', data[a][1])
      .replaceText('{{academicProgram}}', data[a][4])
      .replaceText('{{year}}', data[a][5])
      .replaceText('{{positions}}', data[a][6])
      .replaceText('{{Response1}}', data[a][7])
      .replaceText('{{Response2}}', data[a][8])
      .replaceText('{{Response3}}', data[a][9])
      .replaceText('{{Response4}}', data[a][10])
    );
    Logger.log(a + ' page completed');
  }
  newDoc.getBody().appendPageBreak();
}