5 tips to build a fully automated dashboard using Google Sheet.

Are you tired of spending your time collecting, organising and presenting data while someone else is doing the interesting job ? Would you rather be a Reporting Squirrel or Analytics Ninja ? Here are five tips to automate your dashboard and reporting. They will help you save a lot of time so you can actually deliver valuable work.

All the examples in this article are illustrated in the following spreadsheet : Dashboard tips automation . Make a copy of the spreadsheet to get the edit access.

1 — Organise your spreadsheet

It may sound too obvious but I’ve seen so many spreadsheets unorganized. When you start importing a lot of data and especially from a wide range of sources you can be easily overloaded. You end-up losing your time switching between a ton of sheets without realising it.

https://docs.google.com/spreadsheets/d/17NJx9D0VMjn-vacOfiLkeJIDnPSQw4vogTfxd5CuGek/edit?usp=sharing

I usually split my spreadsheet in three parts :

  • One or several sheets called Data. These sheets are used to gather your raw data. I saw it as an unexploited gold mine.
  • The Dispatcher sheet is where you start organising your data the way you need it to fit in your dashboard structure. The dispatcher sheet can still be messy but it must only contain the data you’ll need for your dashboard or reports.
  • The final sheet is the Dashboard.

You may have noted the presence of a selector sheet in the screenshot above, will talk about it later.

2 — Automatically import and refresh data with Supermetrics or similar tools.

Supermetrics is a great tool to help you import a ton of data very easily in a spreadsheet for various sources in a few clicks ! Following the spreadsheet organisation as seen previously I recommend you to make one big query and pull the raw data in your data sheet. It makes it easy to refresh and help you keep some consistence with your data.

One of the best advantage of Supermetrics is the refresh schedule option. You can call all the data you need for the last year and this year with a daily refresh. It might be a lot for one sheet, but using the right formula and function you can organise these data very quickly.

3— Use VLOOKUP and MATCH function

I guess that a lot of people are already familiar with the VLOOKUP/MATCH function combo. However you really need to make it one of your bread and butter tool, so you know exactly when and how to use it.

Dynamically get the channel you want for a specific month

Here is above a simple illustration. Playing with the Google analytics acquisition channel, sources or medium is always a pain at some point. You always end up mixing various data sets going against the Google Analytics acquisition structure. Here is my advice. Call all the data you need from all the channels you need in your raw data sheet. Then in the dispatcher sheet you can just use VLOOKUP and MATCH function with some cell references. Then you can dynamically get the value you need. It’s a life saver when your boss is changing is mind about the metrics he wants to track every hour !

4— The query function

Once you’ve been mastering the VLOOKUP/MATCH combo, you’ll rapidly see some strong limitation. You always need some cell as reference, plus you actually need to write and copy the formula for each data you need.

With the query function, which is a kind of easy to use SQL formula, you can easily and effectively query the data you want from your raw data by crossing row and columns parameters. It also allows you to build dynamic pivot table and obtain aggregated data without adding another sheet ! The best of it, is that you can add cell reference in almost every part of the formula and build some nice dynamic selections.

Build dynamic chart for free !

Here is what a query formula looks like, it might sound complicated at first but it’s actually very easy to understand :

=QUERY( ‘data — source 1(fb)’!A:F, “select A, “&Selector!B7&” where A<date ‘“&text( Dashboard!A2 ,”yyyy-MM-dd”)&”’ label A ‘Date’”)

First you need to add the range of cells referring to your raw data (‘data — source 1(fb)’!A:F). Then you fill the select function with the letter directly referencing the name of every column in your data sheet (obviously you will choose the one that contain the data you want !). The letter is literally the name of the column you want to select, independently of your cell range. The where function work as a filter in this example. You select a column and give it a conditional statement. So in the example above, I’m selecting the data from the column referenced in cell B7(“&Selector!B7&”) and where the column (where A) is inferior to the date provided in cell A2(Dashboard!A2).

For a better comprehension I strongly encourage you to take a look at the google spreadsheet I have shared with you. Make a copy of the sheet to get the edit access.

As you might start to understand, the magic with the Query function is that almost everything can be defined with a referenced cell. Associated with validation cell in the selector sheet it allow you to dynamically build you’re formula.

5— Build your own data collector with Google Apps script.

Here are probably the most advanced tip I can give you. Tools like Supermetrics don’t always have all the data you need. It also happens that you want to archive data calculated dynamically on daily basis. To answer these two issues you need Google App Script. It’s free and not that complicated to use.

I have to admit that I totally stole this idea from an article written by Ben Collins, accessible here. It will give you all the details on how to save data. Here is the main part of the script presented in this article :

function saveData() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
var data1= sheet.getRange(‘Sheet1!A1’).getValue();
var data2 = sheet.getRange(‘Sheet1!B1’).getValue();
var data2 = sheet.getRange(‘Sheet1!C1’).getValue();
sheet.appendRow([data1,data2,data2]);
}

You don’t need to know a lot of Javascript (even not at all if you’re a bit geeky) to use this script. Just go to the Google Script editor of any sheet, copy and past the script above, try to understand it and you should be able to do something with it ! I’ve recently built a script allowing you to automatically collect and save Daily, Weekly and Monthly reach from FB, accessible here.

This article isn’t meant to be a full tutorial, but rather aims to show you that with a little bit of work it’s easy to almost fully automate your daily or weekly dashboard, and almost for free.

Beyond technical issues, if you need some inspiration (or if you are too lazy to build you own dashboard!) you can always take a look at Supermetrics Template Gallery. It will offer you a wide variety of template and great dashboard examples.

Please if you have any questions, use the commentary section or contact me directly on Twitter !