PDF Creator – Send multiple PDFs based on selected rows

By /

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

  1. 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.

GDoc template example

  1. 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.

GSheet example

 

  1. 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.
  2. Complete EMAIL_SUBJECT and EMAIL_BODY if you are sending the PDF as an email attachment
  3. 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.
  4. Select the rows that you want to create and send PDFs from.
  5. Click the custom menu “Create PDFs > Create PDFs for selected rows” in the GSheet.

Share

64 thoughts on “PDF Creator – Send multiple PDFs based on selected rows”

  1. Glory

    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?

  2. Justin

    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.

    1. Andrew Roberts

      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.

  3. Gyu

    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.

      1. Gyu

        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!

  4. Gyu

    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?

  8. Leno

    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

    1. Andrew Roberts

      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.

  9. Webko Wuite

    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

    1. Andrew Roberts

      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.

  10. Titu Singh

    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.

  11. Michal

    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?

  14. ramakrishna boya

    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.

  15. Al Harris

    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!!

  16. Espe

    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!')

    }

  18. Somnath Saha

    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!

        1. Somnath Saha

          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.

  19. Christian

    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?

  20. Christian

    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

  21. Doug

    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!

  24. Mariana

    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?

  25. Akshay

    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.

  27. Rafik

    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?

  28. David Crouse

    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!

  29. Dugal

    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!

      1. Dugal

        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….!

  30. Somnath Saha

    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.

Leave a Comment

Scroll to Top