How to Automatically Send Monthly Invoices From Google Sheets
Whatsapp Pinterest

If you work from home or run your own business, it’s not always easy to remember to send your invoices out on time.

In this article you’ll see how to write a Google Script (VERY simple, don’t worry).

We’ll also show you how to do the same with a macro. A macro can nicely packages up your invoice and emails it out to any email address (or addresses) you like.

Step 1: Prep Your Monthly Invoice

If you don’t currently track your invoices using Google Sheets, you really should. Even if you’ve never programmed anything in your life The 10 Best Beginner Projects for New Programmers The 10 Best Beginner Projects for New Programmers Want to learn programming but don't know where to start? These beginner programming projects and tutorials will start you off. Read More .

Google Sheets is stored on the cloud, where you also have the power of Google scripts 3 Sure Ways to Boost Your Productivity with Google Scripts 3 Sure Ways to Boost Your Productivity with Google Scripts It's possible to do all kinds of really cool automations with Google Scripts, and you don't even have to create them from scratch. Boost your productivity in a big way. Read More at your fingertips. Because of this, Google Sheets is far more “internet-enabled” than Excel is.

It doesn’t really matter how you format your invoice for this solution to work. Let’s take a few things that you should get organized before tackling your Google Script.

set up invoice

This isn’t complicated. All you need to do is make sure your current month’s sheet is always named the same. In my case I call it “Invoice”.

On the 1st of the month, the invoice is automatically sent. Then some time later during the first week, I just rename it to that month and year.

Then recreate the “Invoice” sheet and start logging for the current month again.

Step 2: Create the Google Script

To get into the Google Script editor from Google Sheets, just click on Tools in the menu. Then click Script editor.

It’s smart to rename the Project name to something you’ll remember later if you ever need to go back through your scripts and tweak it.

create google script

Next, paste in the following script.

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var filename = "Current Month " + ss.getName();
  var SendBook = ss.copy(filename);
  var ToUser = ""
    to: ToUser,
    subject: 'Here is my invoice for last month!',
    body: 'Hi! I have attached my invoice for this month. Thanks for the work, really appreciate it! -Ryan',
    attachments: [SendBook.getBlob().setName(filename)]

We’ve covered the sendEmail function How to Send Email in a Google Sheet With Google Scripts How to Send Email in a Google Sheet With Google Scripts Learn how to use Google Scripts to automate Google productivity tools like Sheets and Docs and save yourself hours of time. Read More in the past if you’re interested in how that works.

Edit the right parts of that script so that it’s going to the correct email address. Make sure it uses the right name of the person you’re sending it to in the body as well.

To test that your script is working, fill out the “ToUser” text with your own email address.

Click on the Save disk icon in the menu to save the script. Next click on the Play icon (looks like a right arrow).

play google script

It’ll ask you to confirm authorization for the script to access your other Google data (like Gmail and Sheets).

authorize google script

You might get a warning that the script is unsafe. This is only because you’re writing a custom script that Google hasn’t reviewed or authorized for wider use.

You can ignore this warning since you created the script and you know it’s safe.

If it runs properly, you should have received an email that looks like this:

automated invoice email

As you can see, the spreadsheet gets attached to the email as a PDF. This makes it really easy for the recipient to open and view it even if they don’t use Google Sheets.

Now we need to automate this so it runs on the 1st of every month.

Step 3: Automate Your Invoice Script

To automate your script, you need to create what’s called a “trigger”.

Go back to your Google Script, click Edit in the Menu, and choose Current project’s triggers.

current project triggers

Since you haven’t created any triggers yet for this project, this will be blank.

You’ll see a blue link that reads “No triggers set up.” Click it.

setting up trigger

Choose Time-driven events, set the timer to Month timer, and leave the number at 1.

Once you click Save, Google Scripts will send off the PDF format of your invoice, via email, on the 1st of every month.

That’s all there is to it!

The important thing is just to make sure you keep your invoice well updated during the month. Make sure it’s finished and ready to go on the first day of the next month.

Step 4: Create a Macro

There is another non-programming approach you can take to accomplish the same thing as the script above: Google Sheets now includes a macros feature How to Automate Repetitive Tasks in Google Sheets With Macros How to Automate Repetitive Tasks in Google Sheets With Macros Macros are finally available to Google Sheets users. You don't need any coding know-how to automate repetitive tasks in documents and spreadsheets. Read More . Macros let you record a series of actions to accomplish a task. In this case, you’d first create the macro, and then trigger it the same way you triggered your Google Script.

First, in your Invoice Sheet, go to Tools > Macros, and select Record Macro.

record google sheets macro

Next, just go through the normal steps of sending someone the sheet as a PDF attachment.

Go to File, and click Email as attachment.

email as attachment

Then, fill out all of the form fields so the subject and body looks natural.

Then click Send.

On the “Recording new macro” pop-up, click on Save. This finishes the macro recording process. Give the macro a name you’ll remember, and click Save again.

You should have seen your test email arrive with the PDF attached.

Step 5: Automate the Macro

Now it’s time to automate the macro. You can trigger macros just like you trigger Google Scripts.

Go back into Tools and Script editor. Click on the code.

get macro function name

Make note of the name of the macro function. In the example above, the macro function name is myFunction1.

Now go into Edit, and Current project’s triggers.

This time, in the functions dropdown box, you’ll see the macro function in the list. Select it.

trigger for macro function

Then, just set up the rest of the trigger like you did above for the Google Script trigger.

Set Events to Time-driven. Choose Month timer. Leave the number at 1.

set up macro trigger

Now your recorded macro will run every month.

It will send out the Invoice sheet as a PDF to whatever email addresses you defined when you recorded the macro.

Automated Google Sheets Can Save Time

The triggers feature in Google Sheets is really the power behind this automation. It lets you automate all of those mundane tasks you do frequently, so that you can focus on more important things.

If you’re just getting started with Google Scripts, and you’d like to learn more, we’ve covered other examples of useful Google Scripts 4 Google Scripts That Make Google Sheets Much More Powerful 4 Google Scripts That Make Google Sheets Much More Powerful Google Sheets is great out of the box, but it can be even more powerful if you use Google Scripts to enhance its capabilities. Read More you can try right now.

Enjoyed this article? Stay informed by joining our newsletter!

Enter your Email

Leave a Reply

Your email address will not be published. Required fields are marked *