10 techniques to use when building budget templates in Google Sheets

It probably won’t surprise you to hear that I use Google Sheets to build financial/budget templates and track my incomings and outgoing, both at home and for my business.

The dashboards available through online banking sites are pretty rudimentary and don’t give me much insight into what’s happening with my finances, particularly over longer time frames.

I like using Google Sheets, as opposed to another third party service like Mint, because it’s fully customizable, it’s easy to use and I can share any spending or budget templates easily with my wife.

Credit Card budget template with Tiller

I’m not a financial expert, so I won’t be dispensing any financial advice here. I won’t opine on what you should or shouldn’t show in your spending and budget templates in this post, nor will I talk about what your financial goals should be or how to get there.

What I will do in this post however, is show you some useful techniques in Google Sheets that you can use for building your own budget templates. Techniques to make them more insightful and more helpful for reaching your goals. They are:

  1. Why are you doing this?
  2. Invest time little and often
  3. Get basic formulas dialed
  4. Leverage power of more advanced formulas
  5. Don’t reinvent the wheel! Google has specific financial formulas
  6. Use comments to record specific details
  7. Get all your financial data into your Google Sheet with Tiller
  8. Set budgets and highlight spend over the budgeted amount
  9. Build drop-down menus to show different categories in your reports
  10. Use words!

Continue reading 10 techniques to use when building budget templates in Google Sheets

Filtering with dates in the QUERY function

If you’ve ever tried to filter on a date column in the Query function in Google Sheets, then you know how tricky it can be.

In a nutshell, the problem occurs because dates in Google Sheets are actually stored as serial numbers, but the Query function requires a date as a string literal in the format yyyy-mm-dd, otherwise it can’t perform the comparison filter.

This post explores this issue in more detail and shows you how to filter with dates correctly in your Query formulas.
Continue reading Filtering with dates in the QUERY function

Show data from the GitHub API in Google Sheets, using Apps Script and Oauth

This post shows you how to connect a Google Sheet to GitHub’s API, with Oauth and Apps Script. The goal is to retrieve data and information from GitHub and show it in your Google Sheet, for further analysis and visualization.

If you manage a development team or you’re a technical project manager, then this could be a really useful way of analyzing and visualizing your team’s or project’s coding statistics against goals, such as number of commits, languages, people involved etc. over time.

Contents

  1. What are Git and GitHub?
  2. Access GitHub API using Basic Authentication
  3. Access GitHub API using OAuth2 Authentication
  4. Resources and further reading

Note, this is not a post about integrating your Apps Script environment with GitHub to push/pull your code to GitHub. That’s an entirely different process, covered in detail here by Google Developer Expert Martin Hawksey.

Continue reading Show data from the GitHub API in Google Sheets, using Apps Script and Oauth

Data Cleaning and Pivot Tables in Google Sheets — My New Course has launched!

Data Cleaning in Google Sheets

There’s a joke in the data community that goes:

“You’ll spend 90% of your time cleaning data, and the other 10% of your time complaining about that…”

Do you deal with badly formatted data that takes you hours to tidy up, preventing you from making progress on your “real” work?

Ever wondered what the heck a Pivot Table is, and why you should care about them?

Do you ever find yourself repeating a process manually, like extracting zip codes from addresses?

This new course is designed to help you work with messy, real-world datasets. It’ll teach you techniques and best practices for cleaning data and preparing it for analysis and reporting.

This new course is aimed at beginner-to-intermediate Google Sheets users.

Continue reading Data Cleaning and Pivot Tables in Google Sheets — My New Course has launched!

How to add a total row to a Query Function table in Google Sheets

This article looks at how to add a total row to tables generated using the Query function in Google Sheets. It’s an interesting use case for array formulas, using the {...} notation, rather than the ArrayFormula notation.

So what the heck does this all mean?

It means we’re going to see how to add a total row like this:

How to add a total row to a Google Sheets QUERY table
Table on the left without a total row; Table on the right showing a total row added

using an array formula of this form:

= { QUERY ; { "TOTAL" , SUM(range) } }

Now of course, at this stage you should be asking:

“But Ben, why not just write the word TOTAL under the first column, and =SUM(range) in the second column and be done with it?”

Well, it’s a good question so let’s answer it!

The reason for using this method is because the total line is added dynamically, so it will be appended directly at the end of the table, and won’t break if the table expands or contracts, if more data is added.

It’ll always move up or down, so it sits there as the final row.

Continue reading How to add a total row to a Query Function table in Google Sheets

How to reply to Google Forms survey responses quickly with Sheets & Apps Script

Google Forms survey email tool system
1. User submits Google Forms survey
2. Response logged in Google Sheet
3. Google Apps Script parses responses and sends emails
4. Original user receives reply!

You’re happy!

You sent out a Google Forms survey to your customers and received hundreds of responses.

They’re sitting pretty in a Google Sheet but now you’re wondering how you can possibly reply to all those people to say thank you.

Manually composing a new email for each person in turn will take forever. It’s not an efficient use of your time.

You could use an ESP like Mailchimp to send a bulk “Thank You” message, but it won’t be personal. It’ll be a generic, bland email and nobody likes that. It won’t engage your customers and you’ll be missing an opportunity to start a genuine conversation and reply to any feedback from the survey.

Thankfully, there is another way.

Of course there is, otherwise why would I be writing this tutorial! 😉

By adding a reply column to your Google Sheet, next to the Google Forms survey responses, you can efficiently compose a personal response to every single survey respondent.

Then, using Google Apps Script (a Javascript-based language to extend Google Apps, start here if you’re new), you can construct an email programmatically for each person, and send out the responses in bulk directly from your Google Sheet.
Continue reading How to reply to Google Forms survey responses quickly with Sheets & Apps Script

How to create an annotated line graph in Google Sheets

Animated e-junkie sales chart in Google Sheets
Animated line chart in Google Sheets

This post looks at how to create a more advanced line graph in Google Sheets, with comparison lines and annotations, so that the viewer can absorb the maximum amount of insight from a single chart.

For fun, I’ll also show you how to animate this line graph in Google Sheets.

Want your own copy of this line graph?
Click here to access your copy of this template >>

This chart was originally developed for The Write Life during their 4-day product sale earlier this year. It featured as part of a dashboard that was linked to the E-junkie sales platform and displayed sales data in real-time:

Google Sheet e-junkie real-time dashboard
Google Sheet e-junkie real-time dashboard

Continue reading How to create an annotated line graph in Google Sheets

How to extract email addresses tagged with a specific Gmail label

Extract email from Gmail

Everyone uses email in different ways. For me, email is at the very centre of my business. Everything flows through my Gmail account.

Being able to categorize all work enquiries or questions with a specific label, for example Queries (which I nest under my Work label) is one of the most useful features of Gmail.

Recently I needed to extract all of the email addresses for the hundreds of messages under this label. Super tedious to do manually, but thankfully there’s a much quicker way using Apps Script.

In this post, we’ll see how to setup a Google Sheet to extract all the email address for a specific Gmail label. I’ll share the code and walk through it with you.

How to use the Gmail Service with Apps Script and Google Sheets

Step 1: Set up the Google Sheet

The Google Sheet for this example is super simple for a change.

Cell B1 is where we type the label that we want to extract emails from, and then on row 3 are the two column headings, Name and Email.

The sheet looks like this:

Setup sheet for gmail app

Really important note

In this example I have a nested label, where I want email addresses from the label “queries” which belongs to the parent label “work”. Hence I need to write the label with a dash to show this relationship, with the parent label listed first, hence: “work-queries”.

If you are just looking at a single label with no relationship with other labels, then you can just type that label, e.g. “work”.

Continue reading How to extract email addresses tagged with a specific Gmail label

How to import social media statistics into Google Sheets: The Import Cookbook

Google Sheets has a powerful and versatile set of IMPORT formulas that can import social media statistics.

This article looks at importing social media statistics from popular social media channels into a Google sheet, for social network analysis or social media management. If you manage a lot of different channels then you could use these techniques to set up a master view (dashboard) to display all your metrics in one place.

The formulas below are generally set up to return the number of followers (or page likes) for a given channel, but you could adapt them to return other metrics (such as follows) with a little extra work.

Caveats: these formulas occasionally stop working when the underlying website changes, but I will try to keep this post updated with working versions for the major social media statistics.

Example workbooks: Each example has a link to an associated Google Sheet workbook, so feel free to make your own copy: File > Make a copy....

Continue reading How to import social media statistics into Google Sheets: The Import Cookbook

Help, my formula doesn’t work!

Admit it, we’ve all been there.

Whether you’re just starting out with Google Sheets or you’re a seasoned pro, sooner or later one of your formulas will give you an error message rather than the result you want.

It can be frustrating, especially if it’s a longer formula where the error may not be obvious.

Auditing and Debugging Formula Errors in Google Sheets

Match the error message in your Google Sheet to the sections below, and find out what might be causing your error.

  1. An error message popup prevents me entering my formula
  2. I’m getting an #N/A error message
  3. I’m getting an #DIV/0! error message
  4. I’m getting an #VALUE! error message
  5. I’m getting an #REF! error message
  6. I’m getting an #NAME? error message
  7. I’m getting an #NUM! error message
  8. I’m getting an #ERROR! error message
  9. I’m getting an #NULL! error message
  10. Other strategies for dealing with errors
  11. Functions to help deal with formula errors in Google Sheets
  12. Help! My formula is STILL not working

Continue reading Help, my formula doesn’t work!