1

I have a Google Sheet which is being populated by a Google Form. I am using Google Apps Script to add some extra functionality. Please feel free to access and modify these as needed in order to help.

My goal is to be able to take responses from a Google Form, have that come into a Google Sheet, and then, using a template file, populate the template with the responses and have that sent out as an email attachment to whomever I specify.

This is a dumbed down version of my ultimate project, but the process should remain the same so that I can transpose from this small scale example to that bigger one.

Currently, in my Apps Script, I have code which is successfully able to make a copy of the template file and name it accordingly:

  //Enter collected info into Requirements Template
  const googleSheetTemplate = DriveApp.getFileById('1wqCwMhpuDLReU1hE1CbcDL-Vdw_4zge1xM6oOl34Ohg');
  const destinationFolder = DriveApp.getFolderById('1GxNZQmP8mxHBhVl5AMoqBFs8sAIYzcm3');

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Form Responses 2');

  const copy = googleSheetTemplate.makeCopy(`${row[3]}, ${row[0]} Vehicle Order` , destinationFolder);
  const newSheet = SpreadsheetApp.openById(copy.getId());
  const A1 = newSheet.getActiveRange();

But the next few lines which are meant to be able to find and replace certain strings within the newly copied Sheet does not seem to function properly.

  A1.createTextFinder("{{Customer}}").replaceAllWith(row[3]);
  A1.createTextFinder("{{Car}}").replaceAllWith(row[1]);
  A1.createTextFinder("{{Color}}").replaceAllWith(row[2]);
  A1.createTextFinder("{{Delivery}}").replaceAllWith(row[5]);

I just get the same dummy lines back in the new copy.

I was following another post which I found on a different issue but with the same goal. The majority of the concept I was looking to copy came from this blog post, but where they used a Google Doc, I was hoping to use a Google Sheet. Ultimately, the person that receives this new sheet will need to do some calculations and things with the provided information, so it still needs to be in Sheet form.

What modifications do I need to make in order to successfully replace the text in the template with the answers provided from the Google Form responses?

Jeff Abrahams
  • 61
  • 10

2 Answers2

1

As far as I can tell, this line means some cells on the sheet were selected:

const A1 = newSheet.getActiveRange();

And script will search and replace within these cells only.

Probably you need to define the range with no reference on active range. Something like this:

const A1 = newSheet.getRange("A1:A"); // or "A1:Z", or .getDataRange()

I don't know what the range you need.

Yuri Khristich
  • 13,448
  • 2
  • 8
  • 23
  • Yup, `const A1 = newSheet.getDataRange();` did the trick. Now I just have to figure out why the new sheet isn't attaching to the email, but that shouldn't be too hard. Thanks bud! – Jeff Abrahams Jun 17 '21 at 16:12
  • I can be wrong, but I suspect it can attach only real files (PDF, etc). If you need to send a spreadsheet by itself (which is not a real file) you have to send URL only. – Yuri Khristich Jun 17 '21 at 16:32
  • I think you're right. Also makes sense since it's not something you can download since it "lives in the cloud." That's not such a pain though. Easy to format the link into the rest of my email.html. – Jeff Abrahams Jun 17 '21 at 18:38
0

From the documentation, createTextFinder(findText) only works on a Sheet class, this means you need to define a Sheet variable before replacing text:

  const copy = googleSheetTemplate.makeCopy(`${row[3]}, ${row[0]} Vehicle Order` , destinationFolder);
  const newSheet = SpreadsheetApp.openById(copy.getId()).getSheets()[0];

  newSheet.createTextFinder("{{Customer}}").replaceAllWith(row[3]);
  newSheet.createTextFinder("{{Car}}").replaceAllWith(row[1]);
  newSheet.createTextFinder("{{Color}}").replaceAllWith(row[2]);
  newSheet.createTextFinder("{{Delivery}}").replaceAllWith(row[5]);
CMB
  • 4,950
  • 1
  • 4
  • 16