I have a working script to create a Google Doc from a Google Form, but I want to change one of my values to sheet2. I am using sheet2 to =CLEAN a column due to line breaks I can’t get rid of in column 3 on sheet1. I replicated my script for a simple form/doc below to test. I want var title to pull values from column 5 on sheet 2 instead of column 3 on sheet 1. Or perhaps there is a better way to solve my formatting issues of line breaks remaining in my created Doc from template?
function autoFillGoogleDocFromForm(e) { var timestamp = e.values[0]; var firstName = e.values[1]; var lastName = e.values[2]; var title = e.values[3]; var templateFile = DriveApp.getFileById("17HH3zii-MfT3vl7-JU9Xx8XNgWpPAcNG5244iJHnzo0"); var templateSubmittedFolder = DriveApp.getFolderById("1QNaCCyueyLvcuB7w8wkYvjt-OW75scPP"); var copy = templateFile.makeCopy(lastName + ',' + firstName, templateSubmittedFolder); var doc = DocumentApp.openById(copy.getId()); var body = doc.getBody(); body.replaceText("{{FirstName}}", firstName); body.replaceText("{{LastName}}", lastName); body.replaceText("{{Title}}", title); var currentDate = new Date(); doc.saveAndClose(); }
Advertisement
Answer
Based on what you’ve provided, one small change is needed. Instead of
var title = e.values[3];
Just add in a replace to remove all nonprintable text like so
var title = e.values[3].replace(/[^x20-x7E]/g, '');
Below is the full code with the edit. Good luck!
function autoFillGoogleDocFromForm(e) { var timestamp = e.values[0]; var firstName = e.values[1]; var lastName = e.values[2]; var title = e.values[3].replace(/[^x20-x7E]/g, ''); var templateFile = DriveApp.getFileById("17HH3zii-MfT3vl7-JU9Xx8XNgWpPAcNG5244iJHnzo0"); var templateSubmittedFolder = DriveApp.getFolderById("1QNaCCyueyLvcuB7w8wkYvjt-OW75scPP"); var copy = templateFile.makeCopy(lastName + ',' + firstName, templateSubmittedFolder); var doc = DocumentApp.openById(copy.getId()); var body = doc.getBody(); body.replaceText("{{FirstName}}", firstName); body.replaceText("{{LastName}}", lastName); body.replaceText("{{Title}}", title); var currentDate = new Date(); doc.saveAndClose(); }