Form's App Script does not replace fields in template accurately

user_4844359 0
Viewed 281

I have a simple script to generate a doc and PDF upon form submission. It worked well on simple template (e.g. Only 1 sentence, First name, Last name and Company name).

However, when I use a template that's longer, having many fields, and formatting, the code runs but replace the text randomly.

I have tried to hardcode the fields of forms in ascending order as the doc template. However it still replace the text randomly

Can anybody points out what have I done wrong?

My code:

function myFunction(e) {
  var response = e.response;
  var timestamp = response.getTimestamp();
  var [companyName, country, totalEmployees,totalPctWomenEmployees,numberNationality,name1,position1,emailAdd1,linkedin1,funFact1,name2,position2,emailAdd2,linkedin2,gameStage,gameStory] = response.getItemResponses().map(function(f) {return f.getResponse()});

  var file = DriveApp.getFileById('XXXXX'); 

  var folder = DriveApp.getFolderById('XXXXX')
  var copy = file.makeCopy(companyName + '_one pager', folder);

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


  var body = doc.getBody(); 


  body.replaceText('{{Company Name}}', companyName);
  body.replaceText('{{Name}}', name1);
  body.replaceText('{{Position}}', position1);
  body.replaceText('{{Email}}', emailAdd1);
  body.replaceText('{{Linkedin}}', linkedin1);
  body.replaceText('{{Fun Fact}}', funFact1);
  body.replaceText('{{Game Stage}}', gameStage);
  body.replaceText('{{Game Story}}', gameStory);


  doc.saveAndClose(); 


  folder.createFile(doc.getAs("application/pdf"));}

My template - Template with double curly bracket

Result - Red boxed are wrongly replaced fields - randomized

Question - Does that mean the array declaration in line 3 was supposed to match the order of my form responses columns?

google-forms google-apps-script google-docs javascript
2 Answers

You can use Regular Expresion:

body.replace(/{{Company Name}}/g, companyName); // /g replace globaly all value like  {{Company Name}}
user_78231520

Finally I found what have went wrong after so many trials and errors!

The reason is because I declared the array variables randomly without following the order of the form responses columns.

The issue is with the part -

var [companyName, country, totalEmployees,totalPctWomenEmployees,numberNationality,name1,position1,emailAdd1,linkedin1,funFact1,name2,position2,emailAdd2,linkedin2,gameStage,gameStory] = response.getItemResponses().map(function(f) {return f.getResponse()});

It's actually pulling responses from the spreadsheet, and should be corrected in order. The wrongly mapped values was what causing the replacement of text went haywire. I corrected the order as per form responses and it is all good now.

Learning points: If you swapped around the variables, what response.getItemResponses().map(function(f) {return f.getResponse()} does is that it will go through the form responses column by column in order, and it will map the content to the wrong variable. As a result, when you replace your text later using body.replaceText('{{Game Stage}}', gameStage), there might be possibility that whatever stored in gameStage might be name1. Hence the replaced text will be wrong. And you will scratch your head until it bleeds without knowing why.

I saw @Tanaike's comment after I found the answer, but totally spot on!

user_48443590
Related