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

Community Connectors: Access all your data in Google Data Studio 💥💥

GitHub commit dashboard, built with the new native data Community Connector
GitHub commits dashboard, built in Data Studio using a native Data Connector

Data Studio is relatively new dashboard tool from Google, launched in mid-2016. It’s a superb tool for creating professional looking reports, easily and quickly, and it connects seamlessly to other Google data sources (e.g. Analytics, AdSense, Sheets, …).

Here’s an introduction to Data Studio and a look at the more advanced features.

Do you work with data outside of Google’s ecosystem though?

I’ll go out on a limb here, and say, yes, most likely you do.

Perhaps you’re a digital marketing analyst looking at Facebook, Twitter, Instagram, MailChimp data (etc…) for example.

Many of us work with other web services and that data has been unavailable or difficult to display in Data Studio (requiring a sub-optimal workaround of importing it into a Google Sheet and connecting that to Data Studio).

Until now!

Well, with the launch of native data Community Connectors today, you can now connect to your favorite web services and access data that lives outside the Google ecosystem directly!

What is a Data Studio Community Connector?

Continue reading Community Connectors: Access all your data in Google Data Studio 💥💥

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

Dashboard Design Checklist: From a Blank Google Sheet to Business Insights

Dashboard design is hard.

You start with a blank canvas, or a blank Google Sheet in this case, and you have to somehow turn that into business insights, which will grow your bottom line, make your organization more efficient or help you understand your customers better.

Dashboard design process
Notice the blank sheet is Draft 1 and the final dashboard is Draft 4. It’s an iterative process. Click to open larger image in new browser tab.
Want your own copy of this Facebook dashboard?
Learn more about the template & Supermetrics here >>

The blank screen stares back at you, waiting for you to do something.

It feels overwhelming.

You clasp your hands around the back of your head, lean back in your chair and rue the day you mentioned building a dashboard to your boss.

It was supposed to be easy. Easy to create a masterpiece, a thing of beauty to wow your team.

Meanwhile, that blank Sheet continues to stare back at you, emptier than ever.

Dashboard design starts with a blank Google Sheet
Dashboard design starts with a blank Google Sheet

Continue reading Dashboard Design Checklist: From a Blank Google Sheet to Business Insights

How to build a real-time sales dashboard for E-junkie using Google Sheets and Apps Script

It’s day two of a four day product launch. You’ve worked hard all year to create a fantastic product, test your sales systems and tell the world about this amazing offer. You know you’ve sold 100 products so far, but…

…you don’t know whether your ads are effective, which affiliates are really killing it versus which have forgotten about your launch, or even whether your own emails are converting.

Looking at your sales log only, and having to decipher what’s happened since the last time you looked an hour ago, is like trying to drive in the dark without headlights.

Thankfully there is a better way to track your sales, so you can see your data, get insights about what’s working and what’s not, and immediately act to increase your bottom line.

This post looks at how to build a real-time dashboard for the E-junkie digital sales platform using Google Sheets:

Google Sheet e-junkie real-time dashboard
Google Sheet e-junkie real-time dashboard (fictitious data)
Want your own copy of this dashboard?
Click here to access your copy of this template >>

E-junkie is a digital shopping cart, used for selling digital products and downloads. The system handles the shopping cart mechanics, but does not do any data analytics or visualizations.

You can view a transaction log (i.e. a list of all your sales) but if you want to understand and visualize your sales data, then you’ll need to use another tool to do this. Google Sheets is a perfect tool for that.

You can use a Google Sheet to capture sales data automatically in real-time, and use the built-in charts to create an effective dashboard.

You’d be crazy not to have a tracking system set up, to see and understand what’s going on during sales events or product launches. This E-junkie + Google Sheets solution is effective and incredibly cheap ($5/month for E-junkie and Google Sheets is free).

The Write Life ran a Writer’s Bundle sale this year, during the first week of April. It’s a bundled package of outstanding resources for writers, including ebooks and courses, heavily discounted for a short 4-day sales window.

I created a new dashboard for The Write Life team to track sales and affiliates during the entire event. This year’s dashboard was a much improved evolution of the versions built for the Writer’s Bundle sales in 2014 (which, incidentally, was my first blog post on this website!) and 2015.

The biggest improvement this year was to make the dashboard update automatically in real-time.
Continue reading How to build a real-time sales dashboard for E-junkie using Google Sheets and Apps Script