This is a Google Apps Script to create a PDF file using a Google Doc template and the values in a Google Spreadsheet.
It takes the fields from the active row in your Google Spreadsheet (the one with a highlighted cell or row) and using a Google Doc template (identified by GDOC_TEMPLATE_ID) creates a PDF doc with these fields replacing the placeholders in the template. The place-holders are identified by having curly brackets around them, e.g. {{Name}}. It is run using the custom “Create PDF” menu that is created in the sheet.
There are 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.
So to use:
- Create a Google Doc as a template using the placeholders with curly brackets around the words, e.g. {{Name}}. Make a note of the ID number (https://docs.google.com/document/d/ID NUMBER HERE/edit).
- Create a new Google Spreadsheet, or using an existing one, and use placeholders which are the names of the columns with double curly brackets, e.g. Name or Age (it is case-sensitive).
- Copy the script into Code.gs in the script editor (Tools > Script editor).
- Paste the template ID number into GDOC_TEMPLATE_ID value (between the ”s)
- Set the various configuration settings:
- Whether you want to produce a GDoc or PDF
- What you want the name of the file to be
- Whether you want it sent in an email
- Where you would like the new file to be stored
- Put the actual values you want to use in the spreadsheet under the appropriate columns, one row for each PDF file.
- Click a cell on a row of values you would like to use to select that row and use the “Create PDF > Create PDF”.
The new PDF will be created after a few moments.
If you are looking for a “Gmail merge” rather than a “GDoc merge”, take a look at this great article from Martin Hawksey.
There is a Gist where you can also see the code and A demo spreadsheet.
If you found this script useful why not buy me a coffee to support me create more free content list this.
First let me thank you for creating this post. This is a neat little snippet and easy to follow. I have a question though. I’m not familiar with the method you’ve used here- copyBody = copyDoc.getActiveSection() but in testing it looks like it works perfectly. Is it the same as .getBody()? Where did you find out about it? Thanks for your help if you have time to answer.
Thanks for your interest in my snippet!
It looks like getActiveSection() was renamed to getBody() last year.
Hey Andrew,
This script is awesome. I got it working pretty quickly thanks to your instructions. I was hoping you might be able to answer a couple questions for me.
Is it possible to modify this to change the name of the pdf document? This first to columns of my spreadsheet are building and room, would there be a way to include those in the title?
Is there a way to modify this to create multiple pdf from multiple rows simultaneously?
Thanks,
Mike
Hi Mike,
Glad you found the script useful!
“Is it possible to modify this to change the name of the pdf document?”
I’ve not tested this but you could probably achieve this by can replace the line
DriveApp.createFile(copyFile.getAs("application/pdf"));with
DriveApp.createFile('New Text File', copyFile.getAs("application/pdf").getDataAsString());“This first to [sic] columns of my spreadsheet are building and room, would there be a way to include those in the title?”
You can use the headerRow array to concat a title, e.g.
var fileName = 'Building: ' + headerRow[0][0] + ' Room: ' + headerRow[0][1];“Is there a way to modify this to create multiple pdf from multiple rows simultaneously?”
There is: you’d need to put a loop around createPdf() and pass it a row index rather than using the active row.
Thanks for the script! The instructions says “paste the sheet ID number into TEMPLATE_ID value (between the ”s)” . I used the Doc ID to make it work.
Hi Madhu,
Thanks for taking the time to tell me about that mistake, I’ve corrected it now.
Thanks! it helped me a lot!
I have a permission error but this is my document. Any thoughts?
No item with the given ID could be found, or you do not have permission to access it
Hi David, If you want to share your template doc and spreadsheet with me (andrewr1969@gmail.com)) I’ll take a look. Andrew
This script works great, thanks a lot!
How could you change it to create a PDF based on columns rather than rows? Just transpose the data first?
Thanks.
Hi Tim,
You’d need to alter the logic to step down the rows rather than across the columns. Drop me a line if you work it out and I’ll post it here. I’ll have a go if I get time.
Andrew
I have tried to make a similar script to produce an invoice but when I call openById (var invDoc=DocumentApp.openById(invFile.getId());) I get an error message saying that I do not have permission to call openById. Why is this?
Hi, Have you checked you actually do have permission to access the new file? I’d be happy to take a look at your script if you share a link with me (andrewr1969 ‘at’ gmail ‘dot’ com).
Thanks for quick reply! 🙂
Everything is created and run by the same user so I can not see that it is a permission problem.
I just sent you the main part of the code to you in a mail.
May be this is the problem?: https://code.google.com/p/google-apps-script-issues/issues/detail?id=5174
Amazingly (with my level of knowledge), I actually got this script to work! Great work Andrew, thank you.
Probably barking up the wrong tree entirely here, but is there any way to adapt this script at all to incorporate other data in the PDF, say for instance a chart of some kind? Or is it strictly text only?
You can embed whatever info you like in the the doc template before you convert it to a PDF, so yes you could include charts.
Andrew,
Could you elaborate on your answer to Mike? To create a pdf with a specific name, replace
DriveApp.createFile(copyFile.getAs(“application/pdf”));
with
DriveApp.createFile(‘New Text File’, copyFile.getAs(“application/pdf”).getDataAsString());
var fileName = ‘Building: ‘ + headerRow[0][0] + ‘ Room: ‘ + headerRow[0][1];
That gives me a jumbled text file. Can you let me know what I’m missing?
You’re right that just produces garbage so I’ve added an extra variable at the top of the script (PDF_FILE_NAME) that lets you specify the file name. You could set this dynamically in the script with a field from the spreadsheet if that was more useful.
Hi
Any way to get an existing PDF file from Drive and the replace this? Don’t want to have lots of different files, just update the one PDF when this runs.
Thanks
Hi, I’m afraid it’s not possible to edit the existing PDF with this script (you’d have to look at Adobe’s PDF API for that, which would be beyond the scope of this script) just create a new one each time.
Fab.. worked first time and I was able to PDF the GoogleForms data people have been nagging me for .. Thank you for the quick solution.
Thanks for taking the time to comment. Glad you found it useful.
Thanks for this article. I need help with two tweaks
1) how do i save the pdf in a particular folder instead of root folder
2) how do i also add an option which not only saves the pdf in my driver folder but also pops up a save as window to download on my hard disk?
Hi,
1) By default the file is saved in the root, so you’d need to add some code to move it. Take a look at the DriveApp.Folder docs, particularly Folder.addFile(). So get the folder id you want to move it to, use this to get it’s Folder object and then use addFile().
2) That’s an interesting one! Take a look at File.getDownloadUrl() which will give you a link that should initiate a download when you click it. Take a look at my Dialog library for an easy way to show a dialog that includes some HTML (your link).
These are both useful features, so drop me a line if you get them working and I can include them in the script!
Cheers,
Andrew
I have implemented your code in my spreadsheet. It is working fine but how I keep new PDF file in a folder and How can I email as attachment this file.
Thanks in advance.
Prabir
Hi, take a look at DriveApp for moving the file and MailApp to email it as an attachment.
Hello. I appreciate your instructions and believe this is exactly what I am looking for, but I am having problems with the last step. I am unable to find the CreatePDF function. I have saved it to the script editor, but I do not know how to call it from the sheet. Can you please assist?
Hi, Within the script editor click the Run menu and then createPDF().
I\’ve recently created a variation that emails the PDF – https://www.andrewroberts.net/2016/01/google-apps-script-to-create-and-email-a-pdf/
this is a very easy solution
function SheetToPDF() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var gid = sheet.getSheetId();
var id = ss.getId();
var name = “pdfname”;
var blob = DriveApp.getFileById(id).getAs(“application/pdf”);
blob.setName(name + “.pdf”);
DriveApp.createFile(blob);
Browser.msgBox(“PDF created in Drive”);
}
Thanks for the wonderful example. I am beginner for GAS. I tried to execute this but I am getting error “No item with the given ID could be found, or you do not have permission to access it”
Can you please let me know what can be the issue?
Thanks!
I’m guessing this refers to the GDoc used as a template. Check that you have access to it from your account and that you’ve copied the ID over OK. If you still have a problem you can share the script with me (andrew@roberts.net) if you would like me to take a look. Andrew
Hi,
Thank you so much for the code. I have a question:
I can’t modify my template even if I use the % caractere.
If you’d like to share your template and GSheet with me I can take a look.
Very nice script, I could make it work, and I am not an expert at all with this kind of things. Is it possible to generate a pdf automatically, each time a row is filled, and give it a dynamic name?
Hi, Glad you’ve found it useful. Take a look at this script for generating a PDF on a form submission (and a new row being added to the responses sheet: https://www.andrewroberts.net/2016/11/pdf-creator-email-results-form-submission-pdf/
thanks very much for your answer, that is actually too hard for me, I should start studying a bit of JavaScript language, I guess…
Thanks for the post. Can you tell if pdf hyperlinks in the original would be preserved in the newly created file?
Yes, links are preserved.
Thanks for the post, it works beautifully. Can you point me in the direction of a modification to save the pdf link generated into the spreadsheet?
If you wanted to write it to a new row in a new “URLS” sheet:
Is there a variation that would let one pull from a a google slide instead of a doc?
Hi, There was a Slides API released fairly recently, but I’m afraid I’ve not got a variation that uses it.
Hi, Thank you for sharing such a useful stuff. I am stuck at one point. I didn’t understand your instruction before script you written to create documents as a templates etc… Can you explain again. As this script give me issues. I am unable to run it. Thank you
If you share what you’ve got so far (andrewr1969@gmail.com) I can take a look.
Thank you for quick reply. I solved that issue. Thank you.
Hi Andrew,
I’d like to try this script out but I will need to tweak i believe. I’d like to create in invoice pdf, each row would be a different line item in the final pdf.
Any pointers as to which part I need to tweak would be appreciated.
THanks Jono
Hi – thanks for sharing this great script! Couple of questions from a n00b:
1) How could I modify the script to run for all rows of data in my spreadsheet, instead of just the active row?
2) I’m not sure how to use the (PDF_FILE_NAME) to create a name for my PDF. If I have a column in the dataset called “Filename”, how could I incorporate that to be the name of my PDF?
Hi,
Glad you’ve found it useful.
1) https://www.andrewroberts.net/2017/05/create-pdf-rows-google-sheet/
2) var PDF_FILE_NAME = “Filename”;
Hello Andrew,
firstly many thanks for an awesome script!
It works smooth from the begining, however I have an issue when the numbers from sheets in decimals eg. 10.80, 9.00 runs to Docs/PDF and change into 10.8 and 9.
Is there an “easy” way to fix it, that there will be everytime 2 decimals?
You’ll need to look out for numbers and apply your own formatting rather than letting the script use it’s default:
How do I make sure that “$” come through as well with my numerals? In my spreadsheet, I have financial numbers listed with their respective dollar signs yet when I run this script it only grabs the numerals without the dollar sign.
On the sheet make sure this column is formatted as plain text (Format > Number > Plain text), not as a currency.
I have one problem, one variable is in the header, and the script can not change it, there is some advice?
No easy answer I’m afraid. The main assumption of the code is that the placeholders have a matching header.
Sorry . English is not my language. I asked the wrong question. One replacement element is in the doc header and the script can not replace it.
I tested
var copyHeader = copyDoc.getHeader ()
copyHeader.replaceText (‘% element%’, ‘3333333’)
did not work
Difficult to say without seeing the code. If you share it with me (andrew@roberts.net) I can take a look.
I was using Autocrat to pick up fields from a Google Sheet – Autocrat developers (volunteers) were having problems – I found your code which is excellent and gave me full control again – BIG THANKS!
The merge of %fields% into the Google Doc ‘template’ went well and displayed as desired for many days of testing.
This morning, the font size is not being carried through to the pdf file. As a hint, when I first display the *template* there is a lag until Google finishes the formatting. What I am getting with the pdf is the pre-final-Google-formatting which now overflows the page. Suspect this is a Google problem? Any thoughts?
And… I went into the template and changed the font size on one line… ran the merge script… whole page was perfect! RE-RAN the script and the font size was larger and page overflowed!?!?!
I *think* I found the problem. The template was using a Calibri font for everything. Changed to common font Arial and it has been working. Will let you know if it loses the font *size* again.
That’s a handy tip. I’ll keep an eye on my fonts!
Hi Andrew – it has been a few days and the use of a standard font seems to have done the trick. Thank you again for the code example!
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, I’m afraid this isn’t something I’ve done. Sorry I can’t be more help, drop me a line if you work it out.
Hi Andrew, Thanks for the great article. I have an image inserted into one of the cells of the spreadsheet using =image(). When the pdf is generated using the script the image is not coming in the pdf. How to have the image in the pdf.
Hi, I’m afraid this isn’t something I’ve done. Sorry I can’t be more help, drop me a line if you work it out.
Hi Andrew,
I have the data in my spreadsheet set up in columns instead of rows.
Any idea of how to modify this script to work based on the selected column instead of row?
Thanks!
You’d need to update the logic to step through the rows rather than the columns. I’m afraid I’ve not got a version that does that. You could try putting a request on the G+ Apps Script community.
Hi there,
Thanks for the script!
When I have included in the spreadsheet which I want to export to a pdf and mail it I get the following error message:
“The document is missing. It has possible been deleted”
?
I use the ID after /d/ as template ID…
Cheers,
Adam
Hi, It probably means you do not have permission to access the template from that account.
Well, the template (a google form) has been made be my self so I should have the permissions, right ?
Another thing: when you say template it is the same thing as the document or not ?
Cheers,
Adam
This script assumes that the template is a Google Doc, it won’t work with a Google Form.
I get error with the statement “Access denied: DriveApp (line 112, file “Code”)”
What would cause this error? I previously got it working without any perceived changes.
Line 112 of my code is “var copyFile = DriveApp.getFileById(TEMPLATE_ID).makeCopy(),”
You probably no longer have permission to access the template file at TEMPLATE_ID.
This works amazingly! Thank you!
I only have one question. Two of my columns contain dates… Is there anyway to easily format those dates when they are copied over? For instance, instead of writing Thu Jul 19 2018 00:00:00 GMT-0500 (CDT) is it possible to write 7/19/2018?
Take a look at formatCell() in https://www.andrewroberts.net/2017/05/create-pdf-rows-google-sheet/.
Hi Jackson. Did you find a way to get pdf’s url in spreadsheet?
If i want to protect this pdf file by any password. Which function can i ise?
That’s nothing something you will be able to do with Apps Script, that I know of.
Hi, first thanks for sharing the scripts and answering doubts.
Maybe you can guide me with this:
1. I’d like to add several rows to one PDF, (only the ones that accept a given condition).
2. Can the date of creation of pdf be added to the filename?
thanks !
Hi, When I’ve added multiple rows I’ve populated a table using the DocumentApp service – so take a look at that. The date can be added on the setName() line.
Hello, i need to send part of the google sheet ( few ranges) as pdf with automated email. is there is solution??… thanks – kimu
Thanks Andrew. This is super helpful and really cool. I’ve noticed when I try to edit and change place-holders or add more, etc. I go back and “run” the PDF creation again it will usually just return the place-holder names as opposed to the values. I’ve been able to change the file name or create a new Sheet and try that row again and get the first PDF created to work and then the same issue comes up when trying to run a 2nd or 3rd time. Is there a better way to reset or “try again” with the same row from the spreadsheet? Thanks!
You’ve probably made a typo in the script. If you share your GSheet and GDoc template with me (andrew@roberts.net) I can take a look.
Thank you for this great script!!
I am trying to do a variation in order to obtain a google doc instead of a pdf. Which line should I change ?
Hello again. The PDF is created from a GDoc which is usually deleted. If you want to keep the GDoc comment out line 76 (copyFile.setTrashed(true)), if you want to disable the creation of the PDF too comment out line 69 to 74.
Hi Andrew,
nice script!
3 question:
1) How can i create only the doc instead the pdf
2) How to save it on pc
3) how to format a column values in currency like € xxxxxxx,xx
thanks!
1) How can i create only the doc instead the pdf
See the previous answer.
2) How to save it on pc
This is trickier – you’ll need to set up a web app that accesses the download link for the PDF (you can’t download a GDoc unless you convert it to something else)
3) how to format a column values in currency like € xxxxxxx,xx
Look at Utilities.formatString()
Hello, your script works very well, but I have to share the spreadsheet with another person inside gsuite to use it. DriveApp usage permissions are activated for all users.
When a user tries to run the script from the shared spreadsheet, an access denied error appears. Thank you very much for your help.
Check that the other user’s account has edit access to the spreadsheet and “view-only” access to the GDoc template (that is probably your problem).
Hi Andrew. Very cool script! 2 questions:
1. Is there any way to direct that the PDF file get created in the folder location of the google sheet itself?
2. How do use one of the column names as the PDF file name? I am puzzling over the syntax to insert a variable here…I tried double quotes and that didn’t work… My column name is Name of Referred CARE RECIPIENT.
var PDF_FILE_NAME = ‘”Name of Referred CARE RECIPIENT”‘
Thank you!
The script has had a mojor overhaul and you can now specify those things.
Also – it doesn’t appear that the copyFile.setTrashed(true) command is working to delete the GDoc template that is getting copied. Each time the CreatePDF is run, this extra GDoc is created and left in the folder where it was created (which is not My Drive’s root). Any thoughts?
DriveApp does seem to be playing up at the moment. I have seen the same thing. We’ll just have to keep an eye on it and see if Google fix it. And regards your other message I am looking at that now. There is already an updated script.
Hi Andrew, Great script!
I am wanting to modify it to take multiple rows of data and adding them to the pdf template. Is this something you have looked at before?
Im trying to generate a monthly invoice of telephone consultations. each one has a row in the spreadsheet. I want to populate a table in the pdf with the spreadsheet data.
Any help would be greatly appreciated!
Best
John
What I’ve done in the past is programmatically populate an empty table in the GDoc template with multiple rows from the GSheet. Drop me a line if you’d like to discuss commission this update.