I’ve had a few people ask me about variations to my write-up of how to auto-populate Google Docs from Google Forms submissions. One of the most recurring involves appending all responses to a single doc instead of creating a new one from a template.
Does anyone have a better method than manually updating a Google doc to collect signatures for an open letter? I feel like there *must* be a more elegant solution out there?
— will pooley lecturer in management of the deceased (@willpooley) May 18, 2020
After getting a more specific use-case via Twitter and a shout out to the original post, I was looped in via Sourabh Choraria and decided to just make an example. There are bound to be lots of open letters circulating in Higher Ed over the coming months, so this should be someplace to point those people.
Setting Up the Google Form and Doc
There is a lot of overlap and more in-depth explanation in the post I linked above, so I’d recommend following reviewing that post as well. I created a pretty basic form here to collect some of my personal info, but you can collect whatever you want and even lock down the Google Form to just your G Suite domain.
From here, I also created a responses Spreadsheet from the Form to store the responses. A Spreadsheet is require to use the code snippet below, so keep that in mind. If you put your script in the Google Form, it will not work.
After creating these basic parts, I created a Google Doc that will serve as my open letter. Again, that can be as fancy or as plain as you want it to be, but the part that is required here is that all of our signature lines should be stored in a table.
And in this example, this signature table needs to be the first and preferably only table in your Google Doc:
Now that we have the G Suite resources created, we can use a simple script to take our form responses and add them as signature lines.
Adding in Google Apps Script
In the responses spreadsheet, you can open the Tools > Script Editor menu to access the scripting environment. Then you can add this function to the existing script project:
function appendSignatureRow(e){
//Since there could be a bunch of people submitting, we lock the script with each execution
//with a 30 second timeout so nothing gets overwritten
const lock = LockService.getScriptLock();
lock.waitLock(30000);
//Here we read the variables from the form submission event
const date = new Date(e.values[0]).toLocaleDateString();
//of you can use toLocaleString method if you want the time in the doc
const name = e.values[1];
const department = e.values[2];
//Next format those values as an array that corresponds to the table row layout
//in your Google Doc
const tableCells = [name, name, department, date]
//Next we open the letter and get its body
const letter = DocumentApp.openById('YOUR_DOC_ID_HERE')
const body = letter.getBody();
//Next we get the first table in the doc and append an empty table row
const table = body.getTables()[0]
const tableRow = table.appendTableRow()
//Here we loop through our table cells from above and add
// a table cell to the table row for each piece of data
tableCells.forEach(function(cell, index){
let appendedCell = tableRow.appendTableCell(cell)
})
//Once we've appended the table cells, we can format the font of the
//signature to look more legit
const style = {}
style[DocumentApp.Attribute.FONT_FAMILY] = 'Yellowtail';
const signatureCell = tableRow.getChild(0).asTableCell()
signatureCell.setAttributes(style)
//here we save and close our letter and then release a lock
letter.saveAndClose();
lock.releaseLock();
}
After we’ve done this step, save the script project and then add a trigger. That step is the same as is outline in the post on auto-populating a Google Doc, so check there if you need step-by-step.
After that, you should be able to submit the Form and have a row appended to the table.
Ok
can i insert image from google from and display it to google doc?
Yes, someone figured it out on the comments in this post:https://jeffreyeverhart.com/2018/09/17/auto-fill-google-doc-from-google-form-submission/
Do a search for Jul Moreau and there is a code example.
“this signature table needs to be the first and preferably only table in your Google Doc”
how would you add a second auto-populated table from google form submissions to the same open letter? 1st table being signature as seen in this example, 2nd being a separate form and table for public comments?
I would be very similar, but you would just access the second table in this way:
const secondTable = body.getTables()[1]How would I go about automatically getting my G-Doc to be also saved as a PDF? I have been unsuccessful and figuring out the script to do this. (BTW – your video on writing script to get form data into a g-doc template was excellent – I have never written a script before and ?I got yours to work for me – many thanks!!)
Sorry for the late reply, but I’ve got a few code snippets on how to get the Google Doc as a PDF here:https://jeffreyeverhart.com/2015/01/30/tech-tip-google-forms-confirmation-email-with-attachments/
I’m getting an error when I used the code from your youtube video. I am NOT a programmer…
After doing this for a 10 page document, I’m getting the following error
TypeError
Cannot read properly “values” of Undefined (line2, file “code”)
Do you know what may be cause ing this?
Check out this article that describes the issue: https://jeffreyeverhart.com/2016/08/06/fix-typeerror-cannot-read-property-values-undefined/
How could we add a column with information regarding the number of signatures? So if they’re the 10th person to sign it would say ’10’ before their signature, name, and department.
Sorry for the late reply. That is an interesting question.
I would create add a line like this to store the correct index of the table row:
const tableRowNum = table.getNumRows() - 1We want to subtract one from the actual number of rows because we want to exclude the header row.
But we also need to modify where we construct the
tableCellsvariable and move it down to right before theforEachloop:const tableCells = [tableRowNum, name, name, department, date]Best of luck. Thanks for reading!
Hi, I have a business where therapists visit people in their home to assess them, take notes and then write up a report. Quite a bit of the report is similar and lots phrases of are too. I’d like them to have a Form on an iPad with drop down boxes they can select options and then have that form populate the appropriate spot in a Google Doc that be further edited. Bit like a merge field in Word. Is this a possibility or still a way off? Is it something you can do? Cheers
Hey Grant,
It sounds like what you want is here: https://jeffreyeverhart.com/2018/09/17/auto-fill-google-doc-from-google-form-submission/
With this you can create a fairly elaborate Google Doc template and fill in only the necessary bits.
Feel free to comment back if you need more guidance.
Regards,
Jeff
Can it be done simply with a numbered list and not a table?
Seems like that would be possible with the ListItem class here: https://developers.google.com/apps-script/reference/document/list-item
As an alternative, you could also change the styling of the table and remove borders to make things seems simpler. Thanks for reading and please post back with any other questions or updates. Cheers, Jeff.