Automatically Email Google Sheets Invoice as PDF and Track Records

As a small business owner, every minute counts. If you create invoices in Google Sheets, then manually generate PDFs, attach them to emails, archive them in Drive, and update your records – that’s valuable time you could be spending elsewhere. In this blog post, I’ll show you how to reduce these repetitive tasks to a single click with a bit of Google Apps Script. This is especially important for those who currently handle invoice delivery and record-keeping manually; let’s streamline your workflow!

Video Tutorial

If you’d prefer written instructions with additional tips, just keep reading.

The Power of Automation: Why Bother?

• Time Savings: Eliminate the tedium of creating PDFs, attaching them to emails, and maintaining multiple records.
• Reduced Errors: Minimize the potential for mistakes when copying data between files or composing emails.
• Professional Touch: Polished and consistent PDF invoices enhance your brand image.
• Improved Organization: Automatically store invoices in Google Drive, with a linked record in Google Sheets for easy reference.

What is Google Apps Script?

Think of Google Apps Script as a tool that lets you unlock hidden superpowers within Google Sheets. It uses a straightforward coding language (similar to JavaScript) to automate tasks that would normally take a lot of clicking around. In our case, we’ll use a bit of code to turn your invoicing into a one-click operation.

But, don’t worry if you do not have experience in coding, you can make use of this method without any coding knowledge or touching the code.

How does it work/ how do you email Google Sheets invoices as a PDF

The invoice we refer to here is just a template in Google Sheets that you can use to add a list of goods or services you provided. This invoice itself does not contain scripts. We use a script to convert this Google Sheets invoice to a PDF, store it in Google Drive, and attach and email the PDF to your customers.

Here’s a breakdown of how you’ll email invoices as PDFs from Google Sheets:

• Create Your Invoice: Design your invoice directly in Google Sheets. For this setup, include your customer’s email address in the designated cell (in our example, B15:C15; you can add multiple emails with commas).
• Define the Range: This is the range in Google Sheets that contains the invoice. At the beginning of the code, we specify this range as a constant.
• Custom Menu: The code adds a new menu named “Invoice” to your Sheets menu bar. Clicking it reveals the “Email as PDF” option.
• One-Click Magic: Select “Email as PDF.” The code will then:
  • Generate a PDF of your Google Sheets invoice.
  • Send the PDF to the specified email address(es).
  • Save the PDF to your Google Drive.
  • Create a record in your Sheets with a link to the saved PDF for easy reference.

Step-by-Step Guide to Set Up Google Sheets to Email Invoices as PDFs

To save you time, I’ve provided a ready-to-use Google Sheets template with the necessary code. This sheet includes a sample invoice from the Google Sheets template gallery. To get started:

• Copy the Google Sheets to your Google Drive from the following link.

Link to Google Sheets with the Invoice and Apps Script

• Navigate to the Invoice tab (sheet) in your copied Google Sheets. Locate cell B15 (Merged with C15) and enter your email address.
• Once the Google Sheets is fully loaded, you can see a custom menu named “Invoice” in your Google Sheets. Click on it and select “Email as PDF“.
• The first time you run this, you’ll be asked to authorize the script:
  • Click OK to proceed and select your email address.
  • Click Advanced.
  • Click the link with the text “Go to Email Invoice as PDF (unsafe)“.
  • Click “Allow” to grant the script the necessary permissions.
• Once authorized, click “Invoice” > “Email as PDF” again to run the code and send your invoice.

If you follow the above steps successfully, you will receive an email with the invoice PDF as an attachment. Additionally, a folder named “Invoice_PDF” will be created (if it doesn’t already exist) in the same directory as your saved Google Sheets. Finally, the Invoice List tab will include a new record containing a link to the generated PDF.

The Code

The Google Sheets linked above contains the following Apps Script Code. You can see this code in the Google Sheets you copied above by going to Extensions > Apps Script.

Explaining the Code

The above code contains two files namely Code.gs and Menu.gs.

The Menu.gs file

The Menu.gs file contains the code behind the custom menu created in the Google Sheets. It creates a custom menu named “Invoice” and a menu item named “Email as PDF”.

The Code.gs file

This file contains four functions namely, emailInvoice(), createRecord(), createPDF(), and getOrCreateInvoicesFolder() and a set of global variables.

Global Variables

At the top of the Code.gs file, I have defined a set of constants that will be used throughout the code (e.g., INVOICE_SHEET_NAME, INVOICE_RANGE, etc.). You can update these constants to match the names and data ranges within your own Google Sheets.

The main function: emailInvoice()

This function handles the whole process.

• It grabs spreadsheet data
• Create the PDF of the invoice
• Send the invoice email with the PDF attachment.
• Log the sent invoice in a separate “Invoice List” sheet.

It calls the relevant helper function (explained below) to perform these tasks during the process.

The Helper Functions

• The getOrCreateInvoicesFolder(ssId) function check for the “Invoice_PDF” folder in the same directory of the Google Sheets located in. It creates a folder if it does not exist.
• The createPDF() handles converting the Google Sheets invoice into a PDF. It saves the generated PDF in the Drive folder created above.
• The createRecord() function Updates the “Invoice List” sheet with the invoice filename, Google Drive link, and date created.

Wrapping Up

By automating the process of creating and sending invoices as PDFs from Google Sheets, you’ll gain significant benefits. This approach saves you valuable time, minimizes the potential for errors, and enhances the overall organization of your invoicing process. In this blog post, I’ve demonstrated how to achieve this with a simple Google Apps Script code snippet. Even if you’re unfamiliar with Apps Script, you can easily adapt the code by adjusting the cell ranges to match your specific invoice layout.

Ready to streamline your invoicing process? Copy our free Google Sheets Invoice template and the accompanying Apps Script code to start generating and sending professional PDF invoices effortlessly.

Know someone who could benefit? Don’t forget to share.