Create a PDF from each of the rows in a Google Sheet

By /

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:

  1. 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.
  2. 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.
  3. 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.
  4. If you are sending the PDF as an email attachment also complete EMAIL_SUBJECT and EMAIL_BODY.
  5. 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.
  6. 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.

Share

84 thoughts on “Create a PDF from each of the rows in a Google Sheet”

  1. Larry

    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

    1. Andrew Roberts

      Hi,

      There is no way to increase the email quota.

      To add values into HTML you could simply add them into the HTML string:

      var newValue = ‘abcd’;
      var html = ‘‘ + newValue +
      MailApp.sendEmail(
      recipient,
      EMAIL_SUBJECT,
      EMAIL_BODY,
      {
      name: ‘your name’,
      bcc: “additionalmail@mail.com”,
      htmlBody: html,
      attachments: [newFile]
      }
      )

  3. Dylan

    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!

    2. Acauan Matias

      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.

  4. Dylan

    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

  7. Acauan Matias

    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!

  8. Dave

    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.

  10. Joe Fahs

    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

  13. Tammy

    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.

  14. Kponey Angle

    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

  16. bamse

    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

  20. Bart

    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!

  23. Titu Singh

    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.

  27. Zang

    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.

  29. Andrew Roberts

    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)

  31. Mike

    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.

    1. Mike

      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.

  33. Gabriele Simonetta

    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.

  37. Per

    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”

  39. Kevin MacNiven

    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)

  40. Scott McCoskery

    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!

  43. NIRAJ

    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?

Leave a Comment

Scroll to Top