Generate and send PDFs from Google Sheets

Coding level: Beginner
Duration: 15 minutes
Project type: Automation with a custom menu

About this solution

Automatically create PDFs with information from sheets in a Google Sheets spreadsheet. Once the PDFs are generated, you can email them out directly from Sheets. This solution focuses on creating custom invoices, but you can update the template and script to fit your needs.

Screenshot of invoice template

How it works

The script uses the Invoice template sheet as a template to generate PDFs. Information is sourced from the other sheets to fill in specific cells on the template. To email the PDFs, the script iterates through the Invoices sheet to get the PDF link and associated email address. The script creates a generic email subject and body, and attaches the PDF before sending.

Apps Script services

This solution uses the following services:

  • Spreadsheet Service: Provides all of the information for generating invoice PDFs and creating the email. Clears data from the template when a user clicks Reset template in the custom menu.
  • Utilities Service: Pauses the script with the sleep() method while iterating through each customer to help ensure the correct information is added to each invoice.
  • URL Fetch Service: Exports the Invoice template sheet to a PDF.
  • Script Service: Authorizes URL Fetch Service to access the spreadsheet.
  • Drive Service: Creates a folder for the exported PDFs. Attaches the PDF files to the emails.
  • Gmail Service: Builds and sends the emails.

Before you begin

To use this sample, you need the following prerequisites:

  • A Google Account (Google Workspace accounts might require administrator approval)
  • A web browser with access to the internet

Try it

Step 1: Set up the Apps Script project

  1. Click the button below to copy the Generate and send PDFs from Google Sheets spreadsheet.
    Make a copy
  2. Click Extensions > Apps Script.
  3. In the Code.gs file, update the following variables:
    1. Set EMAIL_OVERRIDE to true.
    2. Set EMAIL_ADDRESS_OVERRIDE to your email address.
  4. Click Save Save icon.

Step 2: Generate and send invoices

  1. Return to the spreadsheet and click Generate and send PDFs > Process invoices.
  2. When prompted, authorize the script.
  3. Click Generate and send PDFs > Process invoices again.
  4. To view the PDFs, switch to the Invoices sheet and click the links in the Invoice link column.
  5. Click Generate and send PDFs > Send emails.
  6. Check your email to review the emails and attached PDFs. Because you set EMAIL_OVERRIDE to true in the previous section, all the emails are sent to the email address you specified for EMAIL_ADDRESS_OVERRIDE. If you set EMAIL_OVERRIDE to false, the script sends the emails to the email addresses listed on the Customers sheet.
  7. (Optional) To clear the data from the Invoice template sheet, click Generate and send PDFs > Reset template.

Open the Apps Script project

The sample script's code includes in-line comments to help you understand how it works. To access the code, in the spreadsheet, click Extensions > Apps Script.

Contributors

This sample is maintained by Google with the help of Google Developer Experts.