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.
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
- Click the button below to copy the Generate and send PDFs from Google
Sheets spreadsheet.
Make a copy - Click Extensions > Apps Script.
- In the
Code.gs
file, update the following variables:- Set
EMAIL_OVERRIDE
totrue
. - Set
EMAIL_ADDRESS_OVERRIDE
to your email address.
- Set
- Click Save
.
Step 2: Generate and send invoices
- Return to the spreadsheet and click Generate and send PDFs > Process invoices.
- When prompted, authorize the script.
- Click Generate and send PDFs > Process invoices again.
- To view the PDFs, switch to the Invoices sheet and click the links in the Invoice link column.
- Click Generate and send PDFs > Send emails.
- Check your email to review the emails and attached PDFs. Because you set
EMAIL_OVERRIDE
totrue
in the previous section, all the emails are sent to the email address you specified forEMAIL_ADDRESS_OVERRIDE
. If you setEMAIL_OVERRIDE
to false, the script sends the emails to the email addresses listed on the Customers sheet. - (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.