Google Apps Script to create and email a PDF

By /

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:

  1. 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).
  2. 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.
  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.
  6. 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.

 

Share

47 thoughts on “Google Apps Script to create and email a PDF”

  1. Brett

    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.

  2. Albert

    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)

  3. Jackson

    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

  7. Glory

    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?

    1. Andrew Roberts

      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.

  8. Dan H

    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.

  9. Michael

    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!

    1. Andrew Roberts

      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.

          1. Michael

            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?

  10. Dylan

    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!

  11. Edson Luiz Barbieri

    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

    1. Roger Pollard

      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

  12. Col

    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)

    1. Andrew Roberts

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

  13. Paige

    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]})

  15. barbd

    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?

  19. Mike Ortega

    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?

  20. Fernanda

    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.

  22. Swati Singha

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

Leave a Comment

Scroll to Top