How do I get a column value from multiple sheets

Tags: , , ,



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();
  
}

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();
  
}


Source: stackoverflow