This script automatically emails the submission from a Google Form as a PDF. The PDF is created by populating a Google Doc template from the values in the Google responses spreadsheet.
Further to user requests, I have created many variations on the PDF creation theme.
The script takes the fields from the form submission row and using a Google Doc template (identified by TEMPLATE_ID) creates a PDF doc with these fields replacing the placeholders in the template. The place-holders are identified by having a % either side, for example, %Name%.
As well as being automatically triggered by a form submission it can also be invoked using the “Create PDF” custom menu in the responses spreadsheet.
You can optionally specify a name for the PDF file by providing a field called “File Name”.
Take a look at the Form Publisher add-on if you want an off the shelf solution.
Get in touch now if you need any help with this or any other GAS scripts or have a look through some other free scripts and snippets.
Setting up the script
The easiest way to get going is to take a copy of the demo GSheet (File > Make a copy…). This contains the script and creates a new GForm for you to customise. You then need to create the “on form submission” trigger”, create a Google Doc template and link it to the script.
UPDATE: The menu has now been updated to allow you to enable/disable the form submission “create PDF” trigger.
- Open the script editor (Tools > Script editor…)
- Open the trigger’s (Edit > Current project’s triggers)
- Trigger createPdf() “on form submission”
- On clicking Save “Allow” the permissions the trigger requests
- Create your own Google Doc template for the PDF using place-holders of the form %[field name]%. For example %Name%. This is an example GDoc.
- In the script editor add the template doc ID (https://docs.google.com/document/d/ID NUMBER HERE/edit)
- Add you own EMAIL_SUBJECT and EMAIL_BODY in the script
You can now open the Google Form (probably called “Copy of PDF Creator – Email on form submit” at this point) and do a test submission to your own email address.
If you have already created the form you can copy the script below into your own responses sheet.
Here is the Gist of the script.
We added my email address to the script, but it doesn’t send me a copy of the PDF. We get an error when trying to save the trigger. My husband is the developer of my form, and we found your script. We want to use it, but he has missed something in the script setting it up. I will email my form to my customers for them to fill out. I no longer have a website for my small business. Thank you in advance. Rena Stapenhorst.
If you share your sheet and form with me (andrew@roberts.net) and what the error message is I can take a look.
Hi Andrew,
I have shared my form and sheet with you. Thank you for looking at this for me.
Rena
Hi Andrew, Thanks for the scripts, they work really well. However one quick question, how would you use this to send a pdf created from a specific sheet rather than the formrepsonses sheet. I’ve tried the setactivesheet function but can not get this to work. Any help would be really appreciated. KR John
Are you saying that this didn’t work for you?
Hi Andrew, The script did work but as I was using a form submission it worked on the formresponses sheet, I wanted to use an index and match function so I set up another sheet to run this and wanted to change the script to produce the PDF from this other sheet. Hope this makes sense, which is why I was trying to set the active sheet to the new sheet I’d created. KR John
If you share your sheet with me (andrew@roberts.net) I can take a look.
Hello Andrew,
I am new at google app scripting. I used your response as a script. Everything is working well, however, I cannot get the script to run automatically when a new row is populated. Can you assist please ?
Check that you have set the trigger setup. Although it can sometimes take a while (1/2 hour sometimes) for the new response to appear in the responses sheet. If there is this long a delay the trigger may not fire.
Hello Andrews,
I have used your script and it works great when triggered by the create pdf menu, but it is not responding automatically when I add a new row. Each new row is added by a web form with another script . Could help as the trigger is not firing automatically.
Can you help please?
script run by my web form:
/*
————-
*/
var sheetName = ‘Sheet1’
var scriptProp = PropertiesService.getScriptProperties()
function intialSetup () {
var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet()
scriptProp.setProperty(‘key’, activeSpreadsheet.getId())
}
//———————————-
function doPost (e) {
var lock = LockService.getScriptLock()
lock.tryLock(10000)
try {
var doc = SpreadsheetApp.openById(scriptProp.getProperty(‘key’))
var sheet = doc.getSheetByName(sheetName)
var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]
var nextRow = sheet.getLastRow() + 1
var newRow = headers.map(function(header) {
return header === ‘timestamp’ ? new Date() : e.parameter[header]
})
sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow])
return ContentService
.createTextOutput(JSON.stringify({ ‘result’: ‘success’, ‘row’: nextRow }))
.setMimeType(ContentService.MimeType.JSON)
}
catch (e) {
return ContentService
.createTextOutput(JSON.stringify({ ‘result’: ‘error’, ‘error’: e }))
.setMimeType(ContentService.MimeType.JSON)
}
finally {
lock.releaseLock()
}
}
The script is only automatically triggered by a form submission if it if the form is attached to the sheet. To process a submission from another source you will have to poll the sheet for new submissions using a regular clock trigger.
This looks very useful, but I’m new to Google Forms (and Apps in general) so I’m following the instructions exactly and have hit some issues:
1. I grabbed a copy of the demo sheet, but opening it did not create ‘a new GForm for you to customise’, nor is there any Form attached, as indicated by the absence of a ‘Form’ choice in the sheet menu. Did I do something wrong (or fail to do something that is necessary)?
2. FYI, ‘Current project’s triggers’ seems to have moved from the Resources tab to the Edit tab within the Script Editor.
Looks like Google have been tweaking things! I’ve just checked and you are right in both cases – I’ll update the post. For now you’ll need to create your own form and link it to the sheet.
Thanks again for the updates. Google had updated GForms so they no longer inherited the permissions of the GSheet, so that’s fixed now. I’ve also updated the text for the new triggers menu position.
‘Feature disabled’ has been resolved by enabling Drive services in the Admin console. Andrew, you probably did this years ago, but someone using Drive Apps for the first time will need to do it. See https://stackoverflow.com/questions/22336429/where-to-enable-drive-service-in-the-admin-console (but note that the Google wording has changed slightly).
On to the next (cosmetic) issues, FYI:
1. Question mark and apostrophe in table column names have to be escaped somehow in the doc template. (The column names are the questions from the Form and can be rather long, ex: “What else could [ORGANIZATION} do to make your volunteer experience more rewarding and effective for you and your learner?” This also apparently clobbered the email address I provided. Backslash did not work.
2. Field formatting is spotty. My Form accepts some time Duration values, the spreadsheet also formats them as such, but the .pdf output shows them as dates, with expansive detail. 2:00:00 becomes Sat Dec 30 1899 02:00:00
GMT-0500 (EST)
I’ll probably find other things and will try to post solutions as I find them, since I think a few tips could help those who come along later.
Maybe you’d like to share your updates to allow for date formatting (you can do a “pull request” on the Gist)??
Resolved issues:
1. Not all apostrophes are created equal. In my case I had either typed in the template field-name entries or had copied them from somewhere other than the sheet column headers. As a result, my template had apostrophes from one code point set, while the column headers, as viewed in the Apps Script debugger, were from another (though to the user they all appeared the same). The fix was to open the sheet, select a column header, and copy the text, then paste it directly into the template doc and surround it with percent signs.
2. Not all question marks are equal. In the Form and in the column headers, the question mark is just a graphic. In this Script, however, the column header is used as part of a regular expression (regex) in which a question mark means ‘match the prior character zero or one times’, which will never match an actual question mark in the template. The column header comes verbatim from a question on the Form, so it, and the resulting regex, cannot be changed. The solution is to simply remove the question mark from the field-name in the template. So if the Form question is ‘How many?’, the column header will also be ‘How many?’ and the template field name should be ‘%How many%’.
3. Although a Form allows Time to represent either a clock time or a Duration, regardless of which you specify the result is placed into the sheet as a Date object, containing both the date and time, with the date somehow being filtered out of the visible sheet display. But this script sees the whole thing and will substitute all of it into your template doc. If the question specifies a Duration time, it appears that the spreadsheet is populated with a base default date, i.e. Sat Dec 30 1899, which we can look for. To deal with this, I added the following code inside the loop that fills in the template:
if(/^Sat Dec 30 1899/.test(activeCell)) { // If data indicates a Duration time
var duration = /^Sat Dec 30 1899 (…..)/
var result = duration.exec(activeCell) // Strip off unwanted date, keep only hh:mm (discard seconds, etc.)
activeCell = result[1] // Replace original text
}
Note that I did not want seconds in the .pdf output, so I just left them out, along with the following time zone text.
4. In the course of trying to understand all this, I found that getActiveSection() has been deprecated and replaced by getBody().
I hope all this is useful to anyone who comes along later. Andrew, I’m delighted with the result, which would have taken me much longer without your example to follow.
Hmmm, yet another issue, which seems to be a Catch 22. My code now works as intended, but there is a permission issue:
If I share the spreadsheet as ‘View only’, the menu created by onOpen() does not appear to the user. This is apparently by design (Google’s, not mine. See the second bullet under Restrictions at https://developers.google.com/apps-script/guides/triggers/#Installable).
If I share the spreadsheet as ‘Edit’ the menu appears and functions as intended, but the script is unprotected, i.e. the user can see it (which I don’t care about) and can modify it (which I DO care about). The best solution I’ve seen involves Publishing the script as a private Add-on, which I’ll look into. BUT this seems to be a logical hole in the permissions architecture. I cannot code a script bound to a spreadsheet, and which will open a menu, without exposing the script to editing by any user. Have I missed something?
P.S. The sheets within the spreadsheet are already protected. No data enters them except from the Form.
That would seem to be the case. By adding a menu to the sheet you are “editing” it, unfortunately you can’t set “add menu, but not edit script” permissions. One thing you could do to provide some “security by obscurity” is to put the code in a library and access that library from your script. The user could then stop it working by deleting the one line of code, but not easily access the library.
Andrew, thanks for the fantastic script! Also, great name there!!
Question for you, is there are way to process scripts in a specific order?
I currently have two scripts: Generate_ID & your script for generating a PDF. They both trigger upon form submit.
I would like to ensure that the Generate_ID always runs first before the Generate PDF script does.
Any thoughts?
You could just have one form submit trigger function and from that call the other two scripts in order.
Dear Sir,
I am using your script, and it works very nicely, but somewhere I have read that OnFormSubmit trigger stop working silently in course of time means slowly it become irresponsive. Is it true ? What is the solution ?
Hi, I’m afraid I’ve not found a way round this, apart from occasionally checking it is still working (maybe with a clock trigger).
Dear Sir,
Thanks for your valuable reply. So I need not to worry about it.
One more thing if you please help me, that I want to embed picture in specific cell of table as per the size of the cell. The picture shall fit in the cell’s predefined width and height. Width of the Google Doc Template can be fixed but Height cannot be fixed. I want to embed pictures of different size. I shall be glad if you please help me in this regard.
Hi, I’m afraid that’s not something I’ve ever tried.
Dear Sir,
I am very much thankful that I have got your script, but my spreadsheet contain a column with image link which I upload through form. Even Irrespective of Size modification if you please tell me how I can place image from google sheet to google doc template. I do this but it just place image link, but not Image.
Again, I’m afraid it’s not something I would know how to do off the top of my head.
Hi Andrew, is there a way to keep the formulas when the form submits. I tried creating a second tab with the formulas, however, when I run the script I keep getting this error and no email – Cannot call SpreadsheetApp.getUi() from this context. (line 167, file “PDF Creator”).
I’m afraid I don’t understand what you mean by “keep the formulas”, maybe you could share you GSheet with me (andrew@roberts.net) and I’ll take a look. The reason you get the error is that the code is running on the form submission trigger which is not a context when it has access to the UI, as it would if you were running the script manually from the sheet.
Hi Andrew,
I have the same question as this user “john on Monday, November 6th, 2017”. Were you able to fix John’s issue? If so, can you provide feedback on how to change the script to produce the PDF from this other sheet?
Hi, It would certainly be possible to pull in the data from another sheet, but that’s not some code I have to hand. Drop me a line if you would like to employ me to create a custom script.
Great guide Andrew – used the barebones of it to edit for my own requirements and it works a proper treat.
Hi Andrew, thanks for this great script. Would it be possible to use a sheet instead of a doc for the pdf template?
It would. You’d have to search the body of the sheet cell by cell for placeholders, rather than the text of a GDoc, and then replace them in the same way.
Hello. Thank you for this very useful post. I tried it and it works. But it sends 2 times the same email (with Pdf attached) instead of only once. Is there any way to solve this please ? (I don’t have any programming knowledge, any easy answer would be appreciated). Thank you sir.
You’ve probably accidentally created two form submission triggers. Take a look in https://script.google.com/home/triggers to see how many there are.
I followed all of the instructions in your post, just checked and there’s only one trigger.
This was a known issue Google-side a few years back so it may be that.
There are solutions using the LockService in the old issue tracker but that is off-line now, but basically you get the lock with the first trigger that arrives and that stops the second from running.
I have a problem when user submit form in closest same time it will miss send pdf . How to solve this problem?
Could you explain the problem in a little more detail? Thanks
Andrew- This is fantastic. I work at a school and we have a tradition to send thank you notes at the end of the year. Due to our stay-at-home order where I live, we were looking for an e-card solution but we didn’t have enough time to consider and vet 3rd parties. I decided to see if I could whip together something in G Suite that would be our own private e-card system for sending thank you notes and your script was the shortcut I needed to turn this project around in a very short amount of time. Thank you!
Dear Andrew, good year.
Let me say that the script works perfectly, many thanks!
I have some questions related:
1. Is it possible that the PDF name as a combination between a content of two columns?
2. Is it possible to attach into the PDF images obteined form the google form?
Thanks again