This script creates a PDF file using a Google Doc template and the values in a Google Spreadsheet. It is a development of this script , where this one allows you to specify the name of the PDF in the GSheet and a recipient to send it to.
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.
The script takes the fields from the active row in the active sheet the script is contained in and using a Google Doc template (identified by TEMPLATE_ID) creates a PDF doc with these fields replacing 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 PDF” menu. You can also optionally specify an name for the PDF file in a column called “File Name” and automatically get the PDF emailed to someone by specifying a recipient in a “Email” column.
So to use:
- Create a Google Doc as a template using the place-holders with %’s 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 and give it header names (first row) that match up with the placeholders minus the %’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.
- Click a cell on a row of values you would like to use to select that row and click the custome menu”Create PDF > Create PDF”.
The new PDF will be created after a few moments in the root of your Google Drive.
There is a Gist and A demo spreadsheet take a copy to use and see the script.
Hey Andrew, thanks for sharing the code, it works great for me!
I hope you can help me update the code a little. Is there a way to email and save the PDF on a folder in google drive? If its is, could you update the code for this or share how to do so?
I’m fairly new to .gs so i’m hoping to learn more examples like this as I go.
Hi Brett,
Glad you are finding it helpful.
Add this at line 122 to save the PDF to a specific folder:
DriveApp.getFolderByID(YOUR_FOLDER_ID_HERE_IN_QUOTES).addFile(newFile)DriveApp.getRootFoler().removeFile(newFile)
Thank you for this useful script!
I expanded the script with a html mail:
var html = ‘HTML here’
MailApp.sendEmail(
recipient,
EMAIL_SUBJECT,
EMAIL_BODY, {
name: ‘your name’,
bcc: “additionalmail@mail.com”,
htmlBody: html,
attachments: [newFile]
}
)
But I wasn’t able to use the values (from the spreadsheet) into the html. Do I have to separately define these fields again? Even the previously defined EMAIL_COLUMN_NAME doesn’t return any value when used inside the HTML.
And another question (or basically a request): right now this script creates and mails a pdf from the active row. So one at a time. Is it possible to have the script run through a spreadsheet, and process more than one row?
Thanks in advance for your (or any else’s) help (y)
Hi Albert,
You are probably best to share your script with me (andrew@roberts.net) and I’ll take a look. Difficult to say from your snippet.
Take a look at https://www.andrewroberts.net/2017/05/create-pdf-rows-google-sheet/ for an example of sending to all rows.
Hi Andrew,
First of all thank you so much. This was my first real exposure to any code and script in general and now I feel hooked (supposing it works).
I think I am running into a rather basic issue.
“No item with the given ID could be found, or you do not have permission to access it. (line 72, file “Code”)”
Thanks for any/all help.
Jackson
Ahh I figured it out, sorry for the silly comment. Feel free to delete.
Hello,
I find your script extremely useful!
Is there a way to copy all the data rows to one PDF?
That would certainly be possible, but the script can’t do that at the moment. There is something similar here but this creates a series on individual PDFs.
Hi, Your script worked fine but what would I need to add to get more columns of data than the four, my spreadsheet requires 6.
You don’t need to do anything apart from add the columns and the placeholders with the same name in the template. As long as you use exactly the same names for the placeholders in your template and the column header names the script will work automatically.
Hi,
I am getting a “New PDF file created in the root of your Google Drive and emailed to null” message, any advice ?
In the script there is a constant EMAIL_COLUMN_NAME, which by default is set to “Email”. If you have no column header with this name – or whatever you may have set it to – the email address will be set to “null”.
I have an excel column called RESULTS and each contains a comma delimited say: CS101 A, CS102 B CS 103 C
for each email.
I want it to display in the PDF as a new line for each comma separation say:
Results
CS101 A
CS 102 B
CS 103 C
How do I achieve this?
If there are a fixed number of elements, split the CSV (results.split(‘,’) and then hardcode a set of placeholders in the GDoc. Otherwise you’ll need to use the DocumentApp to add extra fields into the GDoc before it gets converted into a PDF. Drop me a line if you’d like to commission a new version of the code that does this.
Good Day Andrew,
Great Stuff, I am using it to print Safety forms submitted by our employees. Would it be possible to save the PDF based on field values. Example : Employee+”_”+ Request, then ask the user if there was more detail to add to the description. This would result in a file named Ryan_Hazard_Broken lights.pdf.
You could use Spreadsheet.getUi().prompt() to get the extra fields and then add them to the end of the filename. Drop me a line if you’d to commission a new version of the code that does this.
Andrew,
This is fantastic! So far I have not been able to add in email recipients. This is the error I am getting: Failed to send email: no recipient
var copyFile = DriveApp.getFileById(TEMPLATE_ID).makeCopy(),
copyId = copyFile.getId(),
copyDoc = DocumentApp.openById(copyId),
copyBody = copyDoc.getActiveSection(),
activeSheet = SpreadsheetApp.getActiveSheet(),
numberOfColumns = activeSheet.getLastColumn(),
activeRowIndex = activeSheet.getActiveRange().getRowIndex(),
activeRow = activeSheet.getRange(activeRowIndex, 1, 1, numberOfColumns).getValues(),
headerRow = activeSheet.getRange(1, 1, 1, numberOfColumns).getValues(),
columnIndex = 0,
headerValue,
activeCell,
ID = null,
recipient = null,
I’m guessing that I need to change recipient = null to something…What do I change in order to identify the recipients and send the email?
Thanks Again!
recipient is set to null at the beginning to allow the code to check later on if an email address has been found in the GSheet. Do you have a column headed “Email” with a value in the active row (the one you click before running the script).
If you are still having problems share the GSheet with me (andrew@roberts.net) and I can take a look.
That makes sense. It does work when i am on the active cell. How would I change the script to send emails to every email in the column?
This version does that: https://www.andrewroberts.net/2017/05/create-pdf-rows-google-sheet/
I should have known you would have something like that! One last question. I have a column that contains the date and when I create the PDF it changes the date from November 10, 2017 to Fri Nov 10 2017 00:00:00 GMT-0700 (MST). I have been reading everything I can to have the date be in the format of November 10, 2017 but all I found was that MMMM would change the month from Nov to November but I do not know how to add this to your script. Any thoughts?
At line 94 add:
if (activeCell instanceof Date) {activeCell = Utilities.formatDate(activeCell, Session.getTimezone(), "MMM dd, yyy")
}
Thanks for this script Andrew, very helpful!
Right now, I am successfully using the script to send myself an email on form submission, and it is working well.
I have a question regarding the EMAIL_BODY and EMAIL_SUBJECT variables.
Right now, the variables are assigned as follows:
var EMAIL_SUBJECT = ‘Integration Questionnaire Submission’
var EMAIL_BODY = ‘An Integration Questionnaire has been submitted’
This is useful, but it is generic and not very descriptive. I was wondering if there is a way I can add the value of the variable PDF_FILE_NAME to the email subject/body? That way, I can know the name of the PDF by looking at the subject line, rather than having to actually open the email to see.
Any help is appreciated!
On line 116 change it to:
Hi Andrew,
Congratulations for your work.
Thanks for the feedback you gave us through the email.
I would like to know if it is possible to adapt this script so that it only prints the last line recorded in the spreadsheet, because when it is triggered and the spreadsheet already has 50 lines it generates 50 PDF files
How do I adapt this script if I need to select the line but can always print the last one?
Thank you
Edson
Can this script be adapted to convert whole sheets to pdf and email each one to an address contained in cell on the sheet (using the same email body content for all the emails). Thanks for any help you are able to give. Roger
That would certainly be possible. If you ask the question on the G+ Apps Script community you might find someone who can make those changes for you, I’m afraid I’ve not got a quick solution.
…fantastic, now works a treat! thank you
Re: save the PDF to a specific folder
Hello – could you expand/clarify on this – only get error message plus I notice typo – based on your code above this is where I have placed [see below]
TypeError: Cannot find function getFolderByID in object Drive.
===========
Add this at line 122 to save the PDF to a specific folder:
DriveApp.getFolderByID(YOUR_FOLDER_ID_HERE_IN_QUOTES).addFile(newFile)
typo Foler?? –> DriveApp.getRootFoler().removeFile(newFile)
=========
[120] newFile.setName(ID)
[121] }
[122] ??HERE??
[123] copyFile.setTrashed(true)
Well you can tell I wrote that from memory and didn’t check it actually ran. Should be:
DriveApp.getFolderById(YOUR_FOLDER_ID_HERE_IN_QUOTES).addFile(newFile)
DriveApp.getRootFolder().removeFile(newFile)
“getFolderById” not “getFolderByID”, and the one you noticed “getRootFolder” not “getRootFoler”.
Hi! I’m running into a small issue with my script & wondering if you can help. It is saving a copy in my root directory, emailing a copy, AND saving the original copy of the template on the Team Drive. The template is located on the Team Drive. Is there any way to avoid saving the copy on the Team Drive?
Here are all the lines I have pertaining to getting/copying/saving/emailing the document:
var templateId = ‘1csu33nINe_xTn_PgBoipKwN28oVWiece2BNk9kQcxxxx’;
var docID = DriveApp.getFileById(templateId).makeCopy(),
copyId = docID.getId(), //get reference to template, make a copy, get the new ID
copyDoc = DocumentApp.openById(copyId),
body = copyDoc.getBody(),
copyDoc.saveAndClose()
//document stuff
var newFile = DriveApp.createFile(docID.getAs(‘application/pdf’));
newFile.setName(vals[0][4] + ‘ ‘ + formDate + ‘ SAF Return’); //name the new document with variables from sheet
//docID.setTrashed(true) //can’t trash documents on the team drive
MailApp.sendEmail(email, newFile, body, {attachments: [newFile]})
Hi,
I see you’ve also posted this on G+. My sentiment is similar to those expressed on there: I’d avoid using a Team Drive and it does get tricky when you try and script to a team drive, and it’s not something I’ve tried for this script.
i got message “TypeError: Cannot find function getFolderByID in object Drive.” when i want to save PDF to a folder. hpw to solve it ?
If you’ve written the error message precisely, you should be using the DriveApp service, rather than the advanced Drive service.
Hi Andrew–thanks for this code, it is just what I was looking for! I do have a question related to the code you posted in your answer to Michael from 11/15/17:
At line 94 add:
if (activeCell instanceof Date) {
activeCell = Utilities.formatDate(activeCell, Session.getTimezone(), “MMM dd, yyy”)
}
Since I was having the same issue he described, I added this code where indicated, and am now getting this error: TypeError: Cannot find function getTimezone in object Session.
My date format is mm/dd/yyyy, I tried using that format to replace the above, but am getting the same error. Can you advise?
Could just be a typo, it’s Session.getScriptTimeZone().
Fabulous! That is working for me. Thanks for the quick response.
Hi andrew.. i find it very useful. can you point out me how to get PDF’s url in the column ?
Look at DriveApp…getUrl() and SpreadsheetApp for writing back to the spreadsheet.
really appreciate if you copy that code here in your spare time.
I am trying to include a QR Barcode obtained from the formula =image but it doesn’t show up…I was reading on other forums that the images referenced by formula =image are not showing up even when printing…Is there a fix for this?
Here’s some link from last time I tried this, although I was inserting the image of a chart rather than a JPG:
https://developers.google.com/apps-script/reference/document/inline-image
https://stackoverflow.com/questions/20830086/correctly-insert-chart-from-google-sheet-into-document-using-google-apps-script
https://stackoverflow.com/questions/21239152/inserting-chart-images-from-spreadsheet-into-document-using-gas
Hi Andrew,
Thanks a lot for this fantastic script. This is the first time for me working with scripts but with your explanation I manage to adapt it and make it work!
However, I keep failing when trying to add specific cell values to the email body.
How can I get data from specific columns from the active row (but different from the active cell)?
Specifically, I need to add to the email body the data (a link) contained in the column “Receipt link” of my spreadsheet.
Thanks a lot for your time and sorry my unintelligent question.
Hi, I’m glad you are finding the script helpful. I’m afraid I don’t have code to hand that would do that, but drop me a line if you would like to employ me to create a custom script.
Hi! Great Script! A question… I would to generate only the doc and not the PDF, and the name of the file must be the doc generated.
How to do that?
Hi Andrews,
Thank you so much for the wonderful script. It work fine. However the only problem is the details of the row are not getting populated in the pdf. Please recommend a code for this or where do i need to make changes in your existing script. I am getting the pdf in email as
Name: {{Name}}
Age: {{Age}}
The values are not merged into the email, just the GDoc template.