Populate a single google doc using all the data from a google sheet

Question

I am trying to populate a google doc using all the data from a google sheet. What I'm trying to do is to generate a report based on the data in the sheet.

1. I will be submitting daily report using a google form.
2. The report will be submitted into the google sheet.
3. The report recorded in the sheet will be then auto populate the google doc (Only one google doc will be populated).

Currently I have found the way to populate the data in a google doc but it will be generating a new google doc for every row of data.

This is the code that I found

function autofill(e) {
 var timestamp = e.values[0];
 var name = e.values[1];
 var report = e.values[2];

 var templateFile = DriveApp.getFileById("Google Doc ID");
 var templateResponseFolder = DriveApp.getFolderById("Folder for Google Doc");

 var copy = templateFile.makeCopy(name + ',' + report, templateResponseFolder);

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

 var body  = doc.getBody();

 body.replaceText("{{Timestamp}}", timestamp);
 body.replaceText("{{Name}}", name);
 body.replaceText("{{Report}}", report);

 doc.saveAndClose();
}

Record from Google Sheet

+-----------------+----------+----------+
|Timestamp        |Name      |Position  |
+-----------------+----------+----------+
|3/2/2021 14:12:13|John Doe 1|Position 1|
+-----------------+----------+----------+
|3/2/2021 14:15:13|John Doe 2|Position 2|
+-----------------+----------+----------+

This is the ouput of the above code.

+------------------------+
| John Doe 1             |
| Position 1             |
|                        |
|                        |
|                        |
|                        |
|                        |
|                        |            
|                        |
+------------------------+
John Doe 1, Position 1.docs

+------------------------+
| John Doe 2             |
| Position 2             |
|                        |
|                        |
|                        |
|                        |
|                        |            
|                        |
+------------------------+
John Doe 2, Position 2.docs

It will make a new Google Docs document every time a new data is recorded in Google Sheet.

This is what I am trying to achieve

+------------------------+
| John Doe 1             |
| Position 1             |
|                        |
|                        |
|                        |
|                        |
|                        |            
|                 Page 1 |
+------------------------+
| John Doe 2             |
| Position 2             |
|                        |
|                        |
|                        |
|                        |
|                        |            
|                 Page 2 |
+------------------------+
       Report.docs

I am trying to populate all the data recorded in Google Sheet in a single Google Docs which is Report.docs

Answer 1

Modified Script

Since you were calling var copy = templateFile.makeCopy(name + ',' + report, templateResponseFolder); this was making a copy of the file.

With this modification, the templateFile will be updated every time.

function autofill(e) {
 var timestamp = e.values[0];
 var name = e.values[1];
 var report = e.values[2];
 
 var templateFile = DriveApp.getFileById("Google Doc ID");

 var body  = templateFile.getBody();

 body.replaceText("{{Timestamp}}", timestamp);
 body.replaceText("{{Name}}", name);
 body.replaceText("{{Report}}", report);

 doc.saveAndClose();
}

If this is not what you mean, then perhaps you can share a sample spreadsheet with sample data, and what you would like the result to be.

Reference

• makeCopy()