This script creates a number of PDF files using a Google Doc template and the values in a Google Spreadsheet. It creates a PDF of all of the rows in the Google Sheet.
Further to user requests, I have created many variations on the PDF creation theme.
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.
There is a Gist and a demo spreadsheet you can take a copy of to use and see the script.
The script takes the fields from each row in the sheet the script is contained in and using a Google Doc template (identified by TEMPLATE_ID) creates a PDF doc. The fields replace the place-holders in the template. The place-holders are identified by having a % either side, e.g. %Name%. It is invoked by the “Create PDFs” menu. You can also optionally:
- specify a name for the PDF file in a column called “File Name”
- automatically get the PDF emailed to someone by specifying a recipient in a “Email” column
- specify the folder that the PDFs are stored in using RESULTS_FOLDER_ID
So to use:
- Create a Google Doc as a template using the place-holders with angle brackets around the words, e.g. <<Name>>. Make a note of the ID number (https://docs.google.com/document/d/ID NUMBER HERE/edit). Like this for example.
- Create a new Google Spreadsheet and give it header names (first row) that match up with the placeholders minus the angle brackets’s, e.g. Name or Age (it is case-sensitive), and optionally one called “File Name” to specify the name of the PDF file created and “Email” to specify who the PDF should be emailed to.
- Copy the script into code.gs in the script editor (Tools > Script editor), paste the template ID number into TEMPLATE_ID value (between the ”s) and add a file name for the new PDF in PDF_FILE_NAME if you don’t want to use the template name.
- If you are sending the PDF as an email attachment also complete EMAIL_SUBJECT and EMAIL_BODY.
- Put the actual values you want to use in the spreadsheet under the appropriate columns, one row for each PDF file. This could be the responses from a Google Form.
- Click the custom menu “Create PDFs > Create PDF for each row” in the GSheet.
The new PDFs will be created after a few moments in the root of your Google Drive.
Hi Andrew,
Thanks for this script. I am having success using it for our local car show.
Question:
Is there any way to increase the daily mail sending limit on a free Google account?
Request:
I too would be interested to have the ability to send HTML emails rather than plain text ones. Have you had chance to review “Albert’s” June 22,2017 comment/request on the page
https://www.andrewroberts.net/2016/01/google-apps-script-to-create-and-email-a-pdf/
Thanks again for your help.
Kind regards,
Larry
Hi,
There is no way to increase the email quota.
To add values into HTML you could simply add them into the HTML string:
Hello Adam,
fisrt of all your script works wonders, would it be possible to use it one row at time?
If you want to be able to select individual rows to make a PDF, take a look at https://www.andrewroberts.net/2016/01/google-apps-script-to-create-and-email-a-pdf/
Hello and thank you very much for this script!
One question, some of the column headers from my Google Sheet end in a question mark, so the placeholders for these columns in my template appear like so: %example?%
with the closing % directly preceded by a ?. This appears to be causing an issue in my PDFs created using this script, because instead of the placeholders being replaced by the spreadsheet values they represent, I am just seeing the placeholder itself (%example?%). Do you know of a workaround for this issue?
Thanks!
Replace the opening and closing %s with {{…}} in your template and the code, as these are less likely to be mistaken for real text.
line 121 would be:
copyBody.replaceText('{{' + headerValue + '}}', activeCell)Dear Andrew and Dylan, I tried the solution with {{ and }} but doesn’t work. the problem is that to search, GAS uses regex, in which ? is a special char. the only way I found to workaround is:
add this before line 121:
var h1=(“”+headerValue).replace(/\?/g,’\\?’)
and change line 121 with this:
copyBody.replaceText(“%” + h1 + “%”,activeCell).
NOTE: use h1 any time you need to *search* for the header, but headerValue any time you need to print. If you print h1, it will come with fwd-slashed question marks -> \?.
It took me ages to figure out where was the issue and a viable workaround.
NOTE 2. Apparently the same Issue is with other regex special characters, like ( ) [ | . * + \ ^ $ so if the Header would include such chars, before line 121 can add
var h1=(“”+headerValue).replace(/\?/g,’\\(‘)
var h1=(“”+headerValue).replace(/\?/g,’\\)’)
var h1=(“”+headerValue).replace(/\?/g,’\\[‘)
var h1=(“”+headerValue).replace(/\?/g,’\\|’)
…etc for each of the characters.
Great, thanks for the response Andrew! Also, I am using your script that executes this same function on a single row, but I am unable to get it to trigger properly. I have configured the trigger to execute the script on form submission, but it appears the trigger is not working properly. Any ideas?
Thanks,
Dylan
Could you send me a link to the script you are talking about.
Same concept, but .rtf file can be produced instead of pdf? I need to download rtf files to my computer.
Hi, I’m afraid it’s not something I’ve tried before, or could find with a quick Google.
If I want you to add this feature (saving each row as rtf instead of PDF), you can do? Your fee?
Sorry about the delay responding, been away on holiday. On line 140 change ‘application/pdf’ to ‘application/rtf’.
I keep getting the UI alert ‘TEMPLATE_ID needs to be defined in code.gs’, but I’m certain that I haven’t missed any fields meant for the template ID. What am I missing?
If you want to share your script I can take a look – andrew@roberts.net
Hi robert!
Tanks for the excellent script!
Is it possible to name the output file from the row fields?
By example I have a gsheet where each row is an attendee profile, with Name Surname Phone and Biography headers.
I would like the output file to be “Name_Surname.pdf” and contain the full profile, with phone and Biography.
Thanks!
In the same way that the code looks out for ID and recipient in createPDF(), look out for “Name” and Surname” then concatenate them to create the name, and set the PDF filename.
Hi Andrew, thanks for this great code. It’s very useful and I’m using it to create student records. But I have a problem as exporting images to pdf. I have student photos in google drive and I want to place images with row data in pdf file. Everything is fine except images don’t show up. What can I do for image printing? Thanks again.
Hi Dave, I’m afraid I’ve not tried conversions with images in. Add a comment on the post if you work it out. Sorry I couldn’t be more help. Andrew
Andrew,
Is there a way to preserve formatting? For example, if a number is set to show with two decimals in sheets (2.00), it currently shows as a 2 when I use your code. Any ideas?
Thanks.
Hi, I’ve updated the script to format dates and numbers with 2DP.
Hi Andrew,
I used your sample template with some modifications and am able to create the PDF’s from the spreadsheet fields but the keys are not being replaced. Just in case I shared the folder and documents with you.
Thank you,
Joe
There was a discrepancy between the template and the script – the template should have used <<...>> not %…%. I’ve corrected that.
Andrew,
Thank you for resolving the issue so quickly. The script works great. Not only is it super practical but the process provides an opportunity for many (including me) to learn from the code.
Thanks, again.
Joe
Hello, great script.
Is it possible to save the created pdf’s in one pdf with a page for every row of data.Thanks
Not something I’ve tried to do. Do share it in the comments if you work it out.
Hello Andrew,
How do we retain the formatting of numbers as they are carried over to the Word doc/PDF? I need to have $1234 appear and not 1234 appear. Thanks in advance.
You need to check whether a value is a date and format it using Utilities.formatDate(). If you look through the older comments I think someone else covered this.
I’m VERY new at coding. I am currently creating my template in Docs. I know how to insert a chart into a doc, and how to get it to insert my data, but not how get it into the PDF created at the end. Or if that is even possible. Please help. Thanks.
Hi, If the chart is in the GDoc it should automatically appear in the PDF.
HI Andrew,
This is great code, thank you! I’m having trouble with an error that says I don’t have access to the Template – I think this could be because I’ve got an organization Google account so we’ve got a centralised template store. Is there any way around this?
Thanks
You’ll need to take a copy of the template which will belong to you, so you will then have access to it.
I still can not get my data into the PDF chart. Here is what I have https://docs.google.com/spreadsheets/d/1Yt-DPny8NFx4YK1kARgUrg8t5wO_8SFwfxQc533SFlc/edit#gid=1278715566 and template https://docs.google.com/document/d/1KsFksZQdqpsYCuNSFyFl-nYq_1nGqU4pX3oweimzYig/edit Can you please see what I’m doing wrong? Thanks
I’ve requested access to these files.
Dear Andrew,
thanks for the great code.
Just have question I have one column which include % sign and i want to export it to doc file unfortunately after create pdf it export just the value without % sign.
For example I have this value 25% but after export I see 0.25 in PDF file.
Do you have any solution?
Thanks
Change the placeholder in the code from “%” to something like “{{” and “}}” (what I use these days).
Can you skip a row if a column has a specific text?
Put a “continue” in the for loop that processes the rows that is run on finding a specific value.
OMG so happy I found this.
I get a bunch of empty pages at the end of my PDF though. Suggestions?
I think it is something to do with the different margins that GDoc and PDFs use. Try tweaking your GDoc template page settings:
https://webapps.stackexchange.com/questions/106791/google-docs-pdf-export-has-extra-blank-pages-at-the-end-of-the-document
Hi Andrew thanks for this! I cant get my info to pull through on the template. I used Name and Surname and in the script its %Name% and %Surname% but still not working? Any ideas?
If you share your script and template doc with me (andrew@roberts.net) I can take a look.
Hi Andrew. What if I wanted to do exactly this but create the pdf automatically every time a new row is added. Also, could I use a pdf filename that is in the row (like a number) to make the filenames unique?
Thanks!
Take a look at https://www.andrewroberts.net/2016/11/pdf-creator-email-results-form-submission-pdf/
To use a PDF file name from the GSheet store it in the same way as ID and recipient are and then set it at line 154.
Hi Andrew,
Thanks a lot for all you avise.
My question is to have a pdf automatically when we have a new row in the sheet.
Is it possible ?
Thanks!
Take a look at https://www.andrewroberts.net/2016/11/pdf-creator-email-results-form-submission-pdf/
Hi Andrew, how to put more then 1 names in the pdf templates. Or if 1 person have two email?
Thanks
Replicate the code around line 133 in the Gist, where the recipient is being stored.
Hi Andrew,
Thanks for this beautiful script.
But it is generating PDFs of blank row too.
Can it possible that it will generate PDF only of New data pasted in google sheet row and a word “PDF Generated” will be auto inserted in last column with that PDF file link.
Hi Andrew, I tried using this script however no custom menu appears in my google sheet. Any advice to help with this problem?
Take a look at the Execution Transcript that will tell you why the “onOpen” is failing to display the menu.
Can i deploy this script as web apps?
With parameter, e.g. unique ID, so the script will only take the specific row eith unique ID
You can do. Take a look at how to create a doGet() to wrap it in.
hi, Andrew! thank you so much for your scripts! I’m getting a lot of help!! My question is.. What should I do if I want to change header row to the another row in this script?
Hi, Glad you are finding it useful. Update line 84 to get the row you want rather than just taking the first row.
Hi Andrew,
Thanks for your time and effort on this! I’m running into a bit of an issue. I’m able to run the script to create PDFs and email them to separate users. However, we’re looking further into how we can customize the email and the name of the PDF.
Can you point me to which line of code I should be editing to:
1) Customize the Email_Body to address the recipient, such as “Hello [USER],”
2) Customize the name of the PDF to include a unique field, such as someone’s full name
We were able to get something similar to this, but from the second row and after, it was just addressing and taking the values meant for the first row.
I hope that makes sense and I apologize in advance if I’m asking for a lot here.
Hi, Glad you are finding this useful.
1) Search and replace for things like USER in EMAIL_BODY using [String].replace()
2) Similar to 1
You can always share what you’ve got so far with me (andrew@roberts.net) and I can take a look
Hello Andrew,
First of all thanks for your script Andrew.
I still don’t see how to change the file name into a dynamic field from a row. For example ‘Street’ + ‘Postal code’.
See at line 127 how the special values “email” and “filename” are looked out for, well do the same thing to store “Street” + “Postal code”. Then use these variables when naming the filename around line 145:
newFile.setName(street + ” – ” + postalCode)
Hi! Works great for me. One fix i need. Is it possible to set page size from the script?
I change page setup but dont work.
Take a look at:
https://yagisanatode.com/2018/03/31/set-the-paper-size-and-orientation-in-a-doc-using-google-apps-script/
Dear Andrew,
Thank you very much for your wonderful script.
I was wondering if there’s any way to skip some rows if another column has certain values, e.g. to skip every row that has “no” in their “Create PDFs” column.
And if it’s possible, could you please write the additional lines and where in the code I should put them?
Thank you again.
Another way around this is just to select specific rows for sending (use CTRL-click to de-select those in a batch). Take a look at https://www.andrewroberts.net/2017/08/pdf-creator-send-multiple-pdfs-based-selected-rows/
Thank you very much for your response.
Unfortunately, the script you linked does not work properly if there’s at least one row in the middle of the range which is de-selected.
For example, if you have 5 rows in total and you want to create PDFs for all of them – no problem. If you want to create PDFs for the rows 1-3 – again, the script works perfectly. But as soon as you de-select a row in the middle (let’s say you’re trying to create PDFs for the rows 1, 2, 4 and 5, skipping the third one), the script will only create one PDF for the row which was last selected.
There was a bug in the code, try it now.
Dear Andrew,
First of all thanks for your all your script.
I’m a beginner, I was wondering how to report the sending of emails with a flag at the end of the line made. In other scripts I have seen the line “sheet.getRange (1 + i, emailCol) .setValue (‘TRUE’)”
I was wondering how and where to implement it.
Take a look at the script on this post, which does what you are after.
https://www.andrewroberts.net/2017/08/pdf-creator-send-multiple-pdfs-based-selected-rows/
Very cool script
Is it possible to combine text and a cell value in the Email subject field?
Thank you!
It is. If you want to have a crack at it and share the script with me I can take a look.
} else if (headerValue === SUBJECT_COLUMN_NAME) {
EMAIL_SUBJECT = activeCell || null;
Hi! Thank you for sharing this. Very useful. Is it possible to generate a link to the document and attach the link to a row instead of emailing the document?
What if I wanted to use this script but instead of filling a Google Doc template it fills out and prints a template I made in another google sheet?
This would be a fairly different script, where rather than replacing the GDoc body it looks at each cell in the GSheet.
Hello. Im very much a beginner with this coding and I cant get it to work. When I try to execute it from the “create PDF” menu and hit the play button this error comes up…
Cannot call SpreadsheetApp.getUi() from this context. (line 72, file “Code”
Hey again. Just solved the issue but now have another one. The placeholders with {{…}} dont pick up the info from the first row in each column.
this has been fantastic. I have 2 questions. 1. can i run the script on more than 1 row at a time to produce individual pdf’s per row (similar to mailmerge in excel) and 2. no matter what format i do dates in sheet, pdf format is Sat Jun 01 1957 00:00:00 GMT-0400 (Eastern Daylight Time)
There are various versions of the “create PDF” script here, and if you look at this script you’ll see how by using getDisplayValues() you can use the same formatting at that used on the sheet.
Hello and thank you for this awesome script! I have it working to create PDF’s, however I wanted to use it to created different file types — HTML or Word, etc. But when I change var newFile = DriveApp.createFile(copyFile.getAs(‘application/pdf’)) to var newFile = DriveApp.createFile(copyFile.getAs(‘application/rtf’)) per your comment above, or another other Export formats here: https://developers.google.com/drive/api/v3/ref-export-formats I get an error that says “Exception: Converting from application/vnd.google-apps.document to application/rtf is not supported.” My document template is a simple table if that makes any difference. Thanks!
I’m afraid I don’t have an immediate answer for that. Try asking on https://groups.google.com/g/google-apps-script-community or stackoverflow.com.
Hi Andrew,
how could I generate only one pdf from all the documents generated, instead to generate each doc for single row?
Beyond the scope of this post I’m afraid. You would be best to try Stack Overflow.
Hi Andrew,
is there any way to show the pdf url in the data sheet.
Hi, Use file.getUrl() and then spreadsheet.getSheetByName().getRange().setValue() to write it to the sheet.
Thank you so much for your prompt reply sir…
as i’m not an expert in script. i tried to use
file.getUrl() spreadsheet.getSheetByName().getRange().setValue()
but i am not getting the result. can you please help me?