This script creates a number of PDF files using a Google Doc template and the rows selected in a Google Spreadsheet.
Further to user requests, I have created many variations on the PDF creation theme.
Other ways to use this script are talked about in this Stack Overflow question.
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 of the selected rows in the GSheet “containing” the script and, using a Google Doc template (identified by TEMPLATE_ID), creates multiple PDFs. The fields replace the place-holders in the template. The place-holders are identified by having a << or >> either side, e.g. <<Name>>.
It is invoked by the “Create PDFs” menu.
You can also:
- specify a name for the PDF file in a column called “File Name”
- automatically email the PDF by specifying a recipient in a “Email” column
- specify the folder that the PDFs are stored in using RESULTS_FOLDER_ID
- use placeholders in the template header
If it finds a “Date Sent” column the script also records the date/time the PDF was sent.
Installation
- Create a Google Doc as a template using the place-holders with << and >> 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, 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.
- Complete EMAIL_SUBJECT and EMAIL_BODY if you are sending the PDF as an email attachment
- Update the GSheet. 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.
- Select the rows that you want to create and send PDFs from.
- Click the custom menu “Create PDFs > Create PDFs for selected rows” in the GSheet.
Lets assume each cell in the Age Column in the google spreadsheet contains a comma delimited value.
Say the Age cell in the 1st row contains; 1, 2, 3, 4, 5 and I wish to be displayed as a new line for each in number in the PDF.
say;
Age
1
2
3
4
5
How will the place-holders with <> be in the Google Doc Template file?
If you have a fixed number of CSV values you could use a split() in the code and update it to write to a fixed placeholder for each in the GDoc. Otherwise you’ll have to use the DocumentApp to append the values to the GDoc.
This is great. Thank you so much.
One Question:
Is it possible to pull the EMAIL_SUBJECT and EMAIL_BODY from the Google Spreadsheet, so that each sent email can have a unique subject and body?
I would like to create “Email Subject” and “Email Body” as column headers in my spreadsheet to pull from.
Thanks again for all the help.
In the same way that the code looks for the special “Email” header, it would need to look for these two special headers so add:
if (headerValue === EMAIL_SUBJECT) {emailSubject = activeCell;
}
where EMAIL_SUBJECT would now be the text used in the GSheet for the subject. And EMAIL_SUBJECT would be replaced with emailSubject:
MailApp.sendEmail(recipient,
emailSubject,
EMAIL_BODY,
{attachments: [pdfFile]});
Then same thing for EMAIL_BODY. You can also do the same search and replace for placeholders in these too.
Hi Andrew, thanks for the script! I’ve followed your instructions and seem to have encountered a permissions error…basically, every time I try to run the script it asks me to choose a domain from which to access the template, then it says that the app isn’t verified by Google. What’s strange is that Google claims that “Review is not required for Apps Script projects whose owner and users belong to the same G Suite domain or customer.” Have you run into this issue before? Please advise – thanks.
Hi, I’ve not seen this before, but my best guess would be you could be logged into multiple accounts – that causes some strange behaviour sometimes?? Could you be logged into a consumer Google account at the same time?
Haha yes, the first time I was working on this yesterday I was logged into one account for my Google Doc template, and the other account for my Google Form and Sheet…I figured that might have been the issue so I scrapped the project and started again from scratch, this time making sure that everything was associated with the same account. But I still got the same error as before…
Could you explain what you mean by “consumer Google account?” Is there another type of Google account besides the “consumer Google account?” Thanks so much!
A consumer account is the free one you get when you sign up for GMail. Drop me a line if you work out what the domain issue is.
I made an entirely new project, with the associated files on one account and it worked! I was wondering however if you could adapt this script to send multiple pdfs based on selected columns rather than rows…would you just switch the instances of getColumns with getRows and vice-versa?
Hi. Not sure how that would work. A row describes a particular set of data, and a column the fields in that data. Why would you select a whole column?
Hi! That’s a very interesting script! Thanks for sharing it!
Do you know how it should be tweaked in order to make it send all pdfs attached on one single email? Is it possible?
The simplest way would be to put the same email address in each row, otherwise you’d need to hard-code the email address and stop the code looking for it.
Hi Andrew, can i save the generated pdfs file into specified destination?
You can. Take a look at DriveApp.addFile()
Hi Andrew, this script is amazing. Just wondering how I would go about logging the link to the PDF created (with the information from the row) in the first column of the sheet?
Hi, Glad you are finding it useful. You can use getUrl() and then look at SpreadsheetApp for how to write to a spreadsheet.
Hi tab. did you get the code to do it ?
hello there.
I think the code is great, thank you very much for sharing it.
I need something very similar but the template should be a document spreadsheet because there are several values that are dependent including images of bar codes.
I am kindly asking you if you can help me?
another thing would be better instead of selecting the row, marking it with a check box in the first column.
regards
The technique for completing a spreadsheet template would require a list of placeholders and the cells the values would be written into. The code could also be customised to use a checkbox.
Drop me a line if you would like me to employ me to develop a custom solution for you.
Hi Andrew, your script has been tremendously helpful to me. Thanks so much for sharing.
I have a question related to the number / date format in the function FormatCell(value):
Function formatCell(value) {
var newValue = value;
if (newValue instanceof Date) {
newValue = Utilities.formatDate(
value,
Session.getScriptTimeZone(),
DATE_FORMAT);
} else if (typeof value === ‘number’) {
newValue = Math.ceil(value)
}
In case of a ‘number’ I would like to return format the number to a currency with two decimal places in the Dutch notation (e.g value = 123456.789 > newValue = €123.456,78). I have tried to get this working in various ways (e.g. Intl.NumberFormat), but I am too much of a novice at this point to crack the code. Any help would be greatly appreciated
Many thanks in advance,
Webko
I would break the original number into it’s constituent parts and then stick them back together with the right formatting:
thousands = 123, units = 456, and hundredths = 78 hundredths.
=> ‘E’ + thousands + ‘.’ + units + ‘,’ + hundredths
Obviously still a bit of a challenge there for you to separate out the original number, but you’ll probably find plenty on Stack Overflow.
If you do come up with code, do post it back on my website.
Hi Andrew,
I am new in JS and don’t know how can i make changes in your script.
I have google sheet name “Tracker” in which data stored in sheet4 and Doc Invoice template with same field eg. row header “Order No.” in sheet 4 and <> in doc Invoice template.
Now I want that separate invoice generate in PDF of every row data and stored in google drive automatically and PDF link will auto stored in last cell infront of that particular row data.
I hope you will help me.
If you share with me what you have so far (andrew@roberts.net) I will take a look.
Hi Andrew,
First of all I would like to thank you for your script – it really helped me in my job!
Nevertheless, I still need to make one more adjustment and I hope you could give ma a hint how to deal with it.
I want to aplly different templates for different rows, depending on cell value in speific column (let’s call this column “template”).
How can I do it? I’ve made nested if, but it keeps using only first template example.
Could you help me out?
You should be able to get the template ID from a row rather than using the hard-coded one. Share your script with me (andrew@roberts.net) and I can take a look at it if you like.
Hi Andrew, Thanks for the Script.
I want to send pdf when someone submit on google form.
Please help me. Thank you.
https://www.andrewroberts.net/2016/11/pdf-creator-email-results-form-submission-pdf/
Hello 🙂
I’m getting an error “Can’t replaceText of null”
Is it possible to remove the email part? So I can select multiple rows and generate a PDF for each.
Greetings Oliver G.
Comment out line 202 if you don’t want the emails to send.
What line are you getting the “null” error?
Hello Andrew,
Thank you so much for the script.It helped me a lot in my job.I want a small change in the script,hope that helps me a lot in my task,I want all pdfs in a single file and store them in a link with given date and store it in a specified cell in the specified sheet.
That’ll take a bit of code, but definitely possible. Look at DriveApp and SpreadsheetApp, or drop me a line if you would like to commision me to make those changes.
Hi Andrew,
Thanks so much for sharing the great work…very useful for me!!!
Im currently using your code for “Create a PDF from each of the rows in a Google Sheet”.
Im here, in this script trying to find a way to only create and send a pdf “when a new row” is added to the spreadsheet.
Hoping there is an easy way to edit your code.
FYI: I use the code to generate a delivery ticket each time I schedule a delivery in my crm. The data is pushed into the GS and creates a new row. Id like to have a record (cell) in that new row, that the script recognizes and new data, and creates and sends the PDF when the script is next run.
Hope you can help, and great work! and thanks again for sharing!!
https://www.andrewroberts.net/2016/11/pdf-creator-email-results-form-submission-pdf/ might give you a better starting point.
Hi Andrew.
this is the script, but i can generate only one doc a time, and not multiple…
why?
var TEMPLATE_ID = ‘1mqANOhwNQ-dynlR5GO9ybC5UgGsDTz_1K8CUyXw4-SM’
var PDF_FILE_NAME = ”
function onOpen() {
SpreadsheetApp
.getUi()
.createMenu(‘Crea Documenti’)
.addItem(‘Proposta per Invio’, ‘createPdf’)
.addToUi()
}
function createPdf() {
if (TEMPLATE_ID === ”) {
SpreadsheetApp.getUi().alert(‘TEMPLATE_ID needs to be defined in code.gs’)
return
}
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
for (;columnIndex < headerRow[0].length; columnIndex++) {
copyBody.replaceText('%' + headerRow[0][columnIndex] + '%',
activeRow[0][columnIndex])
}
copyDoc.saveAndClose()
/** var newFile = DriveApp.createFile(copyFile.getAs('application/pdf'))
* if (PDF_FILE_NAME !== '') {
newFile.setName(PDF_FILE_NAME)
} */
// copyFile.setTrashed(true)
SpreadsheetApp.getUi().alert('Contratto creato!')
}
The script you’ve pasted in is not the one from this page. That is the one for just sending single PDFs.
There was a bug in the code, update your script and try it now. Recording the email send date/time is no longer working however.
Hi Andrew, Just Awesome and I mean it. We are an NGO and your script has helped us to optimize the cost behind this task and to spend that amount for the under privileged kids. I must say this code of yours will bring smile on their face in an indirect way. Thanks a ton! Happy coding. Your script gives us the flexibility to change the entire workflow of volunteers’ registration process and makes it simpler and more organized.
Just a request, is their any way 1) to give dynamic pdf file name like using the real value of any column. 2) Can we put the URL of the created pdf anywhere in the Google Sheet against the particular row. Please let me know.
Tanks a ton!
Thanks for the positive feedback. It’s keeps me cranking out the free scripts!
Both those steps are very straight forward. If you share your spreadsheet with me (andrew@roberts.net) I can pop those changes in.
Done, I have added you there. thanks a ton Andrew for your help!
Andrew, first of all I must say you are a Gem! with that you are kind too. The solution, better to say the customized solution you provided for us is just awesome. You can’t imagine how you make our work life better. The best part is, being an NGO optimizing operational cost is all time challenge to us and you contributed your valuable time to fix it. Lots of love and respect. Take care, stay safe!
Last but not the least, I am now fan of your https://www.andrewroberts.net/, it’s an awesome portal to fix several problems without any additional cost and any restrictions.
Wow. Thanks. That’s the kind of gratitude that keeps me cranking out free code! Glad I could help.
Hi Andrew,
I am getting the error: “Can’t replaceText of null”
I am trying to just create the pdf’s, and not send them. The online place in the code where I can find the replaceText function is in row 166 and 167.
Do you have any Idea how I fix this?
Sounds like the code isn’t able to open you new GDoc. Share your GSheet and GDoc template with me (andrew@roberts.net) and I can take a look.
Hi Andrew,
first of all thanks so much for publishing this, it has opened my mind to the opportunities of Gsuite.
I have tried to deploy this script, but I get and error saying “can’t replaceText of null”.
I saw that a previous post mentioned this, but it didn’t solve the problem to comment out line 202.
I don’t want to send the emails, I just want to save the PDFs to a Google Drive.
Thanks in advance
Hi Andrew,
Thank you so much for these PDF creators. They’ve been incredibly useful for me!
For those having the “replaceText of null” error, I found that unless I’d created a header in the template doc, I’d get the error. But creating a header (or even just opening it) fixed it, as did commenting out line 176 ( copyHeader.replaceText(‘<>’, activeCell);)
I’ve also found that if I control click each row, then it will create and send a pdf for each selected row. But if I shift click the rows, or click and drag, it will only do it for the first selected row. Is there a way around having to click each row individually?
Thanks again!
Is it possible for the pdf file name and Email subject to use a combination of a text string and the value of a cell in the sheet?
Very cool script!
It certainly is. If you want to have a crack at the code and share it with me (andrew@roberts.net) I can look it over for you.
Hi Andrew,
Can you please have a look. I saw previous comments too and there was no resolution mentioned for “can’t replaceText of null” issue. Could you please help me out.
If you share your code with me (andrew@roberts.net) I can take a look.
Hi Andrew! Thank you for sharing. It seems that I can only generate one pdf at the time. I have updated the code multiple times but still it doesn´t work for multiple selction. Do you know why this ,ight be happening?
Are you selecting a contiguous selection of rows, I don’t think it’ll work if they are spread about.
Hi Andrew, how to create dynamic pdf file names. For Ex: if I am creating a pdf file for Akshay’s details then the file name should be Akshay and if I am creating a file of Andrew’s details then it should be Andrew.
Take a look at how it’s done in
https://www.andrewroberts.net/2014/10/google-apps-script-create-pdf/
By looking at HEADER_TO_USE_FOR_FILE_NAME, which allocates a GSheet column to use for the filename.
Hi Andrew,
I don’t know what is happening but on my spreadsheet the menu “Create PDFs” never appears. Do you have an idea whay ?
Thanks
Open up the script editor and look in View>Stackdriver Logging to see what the error is that is stopping “onOpen” displaying the menu.
Hello Andrew,
Thank you so much for this code snippet. I am facing a trouble of have this code generate multiple PDFs. It only generate fore the first selected row in the selection. what is your advice?
The selection that you make has to be contiguous – is that the case?
I was able to get this to work a few weeks ago but now it will only run on the first row selected. I tried starting over with your sample sheet and get the same result. Is it still working for you? Works fine for the top row selected but wont run for the rest. I am doing a contiguous selection. If selecting rows 2 and 3, it will only run for row 2. Really appreciate the script!
If you want to share your script (andrew@roberts.net) I can take a look.
These are so amazing – thank you so much! I’ve managed to put a noreply in too.
I can’t seem to get the Date Sent column to update though. Any tips?
My next step is seeing if I can add a second email column so it can be cc-ed!
Thanks again. This is allowing me to create a school report system for my school!
Glad you have found these useful. If you share you script (andrew@roberts.net) I can take a look at your “Date Sent” issue.
Thanks Andrew. I ‘think’ my issue was that I had two of your scripts in the same Sheet. This was causing an issue as the variable were initialised in both and somehow caused a conflict including updating the Date Sent in the columns below the bottom-most selected row.
I’m currently trying to solve an issue I’m having where it doesn’t recognise rows selected using shift as opposed to rows selected one at a time by CTRL. With shift and click multiple rows are highlighted but only one is used in my current version. I’ll share it with you! Thanks.
The school office will kill me if they have to CTRL click each one….!
I’ve made some updates to the script for selecting multiple rows with SHFT – try it now.
Hi Andrew, Thank you very much for our help. I am using this code/script from past one year and recently found a difficulty with it. Using this code I can generate pdf with with expected text value, however it’s returning unexpected data for a specific case. I have two columns Col1 is image URL and Col2 is “=IMAGE(Col1). Here, <> is returning “com.google.apps.maestro.server.beans.trix.impl.CellImageApiAdapter@6103c9b3” not an image.
Could you please guide me how to display it as an image not a text. Any assistance you can provide would be greatly appreciated.