Services used

Google Sheets
Google Docs
Google Apps Script

Department

 Financial

Time to setup

10 minutes

Introduction

At Sheetgo we have a database of paid clients in Google Sheets and we need to send them invoicing. Pretty soon after launching we realized this would be a monumental task to do manually and the existing software out there did not meet our needs. We created this Google Apps Script to automatically generate PDF invoices based on data in Google Sheets. See below for more info on how to configure and use.

How to configure

Below is the step by step instructions to configure the script

1. Create a copy of our invoice document template at this link, and a copy of our invoice spreadsheet template at this link. Now the files are saved to your Google Drive.

2. In the document, add your logo and your company data anywhere you see these brackets ‘< >’ (see below), the words/values contained between the percent symbols (%) will be automatically substituted based on the data in the spreadsheet.

3. For each invoice, you should fill out the requisite data in the spreadsheet (each row for each invoice). Note: Do not change the column headers in the spreadsheet as they correspond to tags in the document.

4. On the tab “count” in the spreadsheet, you can set the invoice number for the first invoice by swapping out the 0 that is in there. For each new invoice, the number will increment by 1.

5. Now, create a folder on Google Drive to store newly created invoices. You’ll also need to copy the ID of the folder for future use.

6. Go back to the spreadsheet and click on Tools and then Script Editor to edit the script.

7. Your script should look like the image below. Now just substitute, the <your_spreadsheet_Url> in line 23 by the spreadsheet URL, the <your_document_Url> in line 29 by the document URL, and the <your_document_Url> in line 32 by the folder URL. Keep the double quotes.

8. Save your changes in the script. You have configured your script. Now to use it go back to the spreadsheet that you just created, and select the menu item Invoice Generator and then Generate Invoices. The document(s) will be created if the column “PDF Url” is blank. After generating the PDF file, it’s automatically saved in your folder and the value is changed to the link for the respective PDF file.

Congrats! Hope you liked the script! There will be more similar solutions soon. 🙂 

If you are a programmer and would like to collaborate on this project, help us improve our code so that the solution keeps evolving. Here is the link to this solution on GitHub. Thanks a lot!