I'm trying to automate a google form to create a formatted google doc. I got the code from Jeff Everhart's video and changed it as needed

Question

I created a google form and the form is working fine. We were manually using that information to fill another form on paper and now I'm trying to automate it.

Here's the process I used -

• Created a google form

• In the response spreadsheet of the google form I opened the Apps Script under the Extensions tab and wrote the following code that I got from youtube

function CHALLANDETAILS(e) {
  var Timestamp = e.values[0];
  var CHALLANNUMBER = e.values[2];
  var VEHICLENUMBER = e.values[3];
  var PARTYNAME = e.values[4];
  var DESCRIPTION = e.values[5];
  var QUANTITY = e.values[6];
  var WEIGHT = e.values[7];

  var templateFile = DriveApp.getFileById("1G6SMzSmSl50rmz_zJizUFYUopcdLf3GsnauRMtYwk18");
  var templateResponseFolder = DriveApp.getFolderById("1RTJ2CIwU-bYXVNTGQzUyiwpPdaYceWH5");
  
var copy = templateFile.makeCopy(PARTYNAME + ',' + CHALLANNUMBER, templateResponseFolder);

var doc = DocumentApp.openById(copy.getID());

var body = doc.getBody();

body.replaceText("{{DATE & TIME}}", Timestamp);
body.replaceText("{{G.P No}}", CHALLANNUMBER);
body.replaceText("{{VEHICLE NO}}", VEHICLENUMBER);
body.replaceText("{{M/s}}", PARTYNAME);
body.replaceText("DESCRIPTION", DESCRIPTION);
body.replaceText("{{QUANTITY}}", QUANTITY);
body.replaceText("{{WEIGHT}}", WEIGHT);

doc.saveAndClose();

}

• I then added the trigger with the following selections function to run - CHHALLANDETAILS run at deployment - head event source - from spreadsheet event type - on form submission failure - notify me immediately

• When I tried to run the code, it gave the following error:

e: undefined
Timestamp: undefined
CHALLANNUMBER: undefined
VEHICLENUMBER: undefined
PARTYNAME: undefined
DESCRIPTION: undefined
QUANTITY: undefined
WEISHT: undefined
templateFile: undefined
templateResponseFolder: undefi
copy: undefined
doc: undefined
body: undefined

• I have double checked the placeholders and replacement texts and everything matches but the code won't work without errors.

• When I filled a test form, it created a new doc in the right folder and copied the format of the old doc but did not replace the placeholders with the form data. So essentially it created the copy of template and nothing else.

The script is working partially I believe as its creating new doc as per template but it isn't reading the variables and replacing the placement text with them

Answer 1

Following your steps, you might be wrongly getting value var CHALLANNUMBER = e.values[2]; so first column Timestamp is fit to e.values[0] and second column in your case CHALLANNUMBER is fit to e.values[1]

• Timestamp -> e.values[0];
• CHALLANNUMBER -> e.values[1];

from google spreadsheet but this script will create new google doc on every form submission because you are copying document here:

var copy = templateFile.makeCopy(PARTYNAME + ',' + CHALLANNUMBER, templateResponseFolder);

var doc = DocumentApp.openById(copy.getID());

but I think you want to store all responses into one google doc. So if you want to store all responses into one google doc on tabular format you need to change script to this:

function CHALLANDETAILS(e) {
  // e.values is an array of form values
    var Timestamp = e.values[0];
  var CHALLANNUMBER = e.values[1];
  var VEHICLENUMBER = e.values[2];
  var PARTYNAME = e.values[3];
  var DESCRIPTION = e.values[4];
  var QUANTITY = e.values[5];
  var WEIGHT = e.values[6];

  // File is the template file, and you get it by ID
  var templateFileId = '1G6SMzSmSl50rmz_zJizUFYUopcdLf3GsnauRMtYwk18';

  // Folder to store the generated documents
  var folderId = '1RTJ2CIwU-bYXVNTGQzUyiwpPdaYceWH5';

  // Open the existing document by ID
  var doc = DocumentApp.openById(templateFileId);

  // Access the body of the document
  var body = doc.getBody();

  // Find the table in the document (assuming it's the first table in the body)
  var tables = body.getTables();
  var table;
  if (tables.length > 0) {
    table = tables[0];
  } else {
    // If no table is found, you can handle this case according to your requirements
    Logger.log('No table found in the document.');
    return;
  }

  // Append a new row to the table with form responses
  var newRow = table.appendTableRow();
  newRow.appendTableCell(Timestamp);
  newRow.appendTableCell(CHALLANNUMBER);
  newRow.appendTableCell(VEHICLENUMBER);
  newRow.appendTableCell(PARTYNAME);
  newRow.appendTableCell(DESCRIPTION);
  newRow.appendTableCell(QUANTITY);
  newRow.appendTableCell(WEIGHT);

  // Save and close the document
  doc.saveAndClose();
}

Also you need to create table in your template google doc file. Hope this helps.