How to Best Format Your Google Sheets for Databox Syncing

Databox lets us visualize the data from spreadsheets which makes it easier to get insights, share, and track over time. But in order to get the most from this option, we need to have properly formatted Google Sheets. So in this episode of Data Snacks, we are going to show you how to –

• Properly format your Google Sheets
• Use our new Google Sheet Wizard for easier metrics building

Below, you can view the full episode or keep reading for a fully transcribed version of the episode, complete with relevant screenshots.

So let’s start.

So let’s start with a brand new Google Doc, and Sheet – I will name it Test Sheet one – 

Now, let’s talk about the data table – we have two options: the vertical and the horizontal view. To get the most from your Google Sheet the vertical setup is recommended – it allows you to include all Date Ranges, values, and dimensions in columns, therefore you can create the entire Databoard with an overview and drill-down data with just a few metrics. While the horizontal has its limitations when it comes to adding more data.

That’s all about the view, easy right. And don’t worry if you already have a horizontal sheet you can easily switch it to Vertical – select – copy – paste special, paste transposed.

Now we come to date formats – as you know date formats are very important when it comes to formatting your Google Sheets, but they also play an integral role in extracting and visualizing data with Databox as the Query Builder will only recognize correct formats.

Doing this manually ensures that you will get it right more easily. So,  The Date should be formatted using a Google Sheets format to look like mm/dd/yyyy or dd/mm/yyyy. Either way, the Date needs to include information on the day, month, and year that this Metric value should be pushed to.

Now keep in mind that if you are using specific Date formats in Google Sheets that are not formatted in a way that works with Databox, you can either quickly reformat the entire column by selecting Format > Number > Date, or if it’s important to you to keep a record of the dates formatted as they are, it’s advisable to create an additional column in your Google Sheet for Dates that will be used by Databox.

So let’s reformat these so that they don’t work with Databox. I’ll choose Date time. Then, I’ll create my new column and call it Databox Dates. Type in = and the cell you’d like to transfer, so =A2 here. Then drag the setting across the other cells. You already know the next trick, select the Databox Dates column click Format > Number > Date. There we go. 

Ok, so far so good, how about we try to format the conversion of currency next.

Let’s suppose you have 3 currencies in the Google Sheet and need to get it all into 1 unified currency. In this case, we are going to use the US Dollar, the British Pound, and the Euro. And let’s say we want to report on everything in Euros.

Here’s how we can do it :

Again, I’ll create a new column. I’ll call this one Amount (Euros) and change the other to Amount (Any) since it will hold any kind of currency. To Column E I will add the formula: =GOOGLEFINANCE(“CURRENCY:USDEUR”)

Notice that when I do this, it shows us the exchange rate. To convert the currency, I will just multiply the cell in Column B and add that to my formula. Then I’ll drag the cell settings down. Right now our formula is assuming everything is converting from USD to the Euro, so I’ll just pop into each cell and make sure it’s converting the right currency. If one is already in Euros I can just delete the formula and do =cell, that way we aren’t missing any data in this column.

For the British Pound I’ll use GBR, aka Great British Pound. Alright. Done! Let’s put everything we learned together and input a new row of data.

Let’s say on April 11, 2022 Monise brought in $755 pounds. Now let’s just stretch down our date format in Column D, and then update the currency formula in Column E. Perfect! It adds very little time and will save your team a ton of headaches. Okay, this is ready to go. Let’s get all of this into Databox and build a dashboard!

Time to Build Our Metrics

This is one of my favorite parts of the process. In Databox we offer the Google Sheet Wizard to help you create metrics from Google Sheets more easily so first let’s try to do that from the sheet we have formatted together – 

So first we will connect our GS – the one we created together – here you can see we are just confirming that everything looks right and we see that it is all formatted properly – Yay Us! And now it’s time to move on and create a metric – 

Begin by selecting a metric value. This is basically just a number that you want to track. From there, you will have the option to further segment it by a dimension which I’ll explain a bit more in a second. Then you’ll add finishing touches like naming your metric, trending preferences, etc. Let’s walk through it together.

For our first one, I want to create an Amount (Euros) metric, that way I can track the money coming in. So select the column, and since we aren’t adding any additional dimensions, we’ll skip this part. For the date column, we want to choose Databox Dates since it is formatted correctly. Then I name it. Trending up is the good one.

Click Continue.

And it is that easy.

Let’s add it to our dashboard. I’m just going to drag it directly from the Metrics Library here… If you can see here, nothing is showing up… that’s because, if you remember, we don’t have any data that goes past April 2021. So I’ll choose all time so we can see all of the data, and let’s graph by year to start. Great! What would be really cool now is to be able to see who is bringing in the most money according to our data, so let’s create a new metric to do that.

In this case, we select Amounts (Euro) as the Value again. Now is the time to add a dimension, in other words, a way to slice and dice this data. We want to be able to see how much money is coming in from each team member, so column C, Owner,  would be our Dimension. Again we’ll choose the Databox Dates column.. Now let’s name it – Amount by Owner – and here is our final product. 

Don’t forget you can always choose the preferred visualization before adding it to the dashboard.

Properly formatting your Google Sheets from the get-go will save you time going forward as it will allow you to use Databox to its full potential. 

It takes a bit of time to master all the formatting but once you see how quickly your data blocks populate on your dashboard and verify the accuracy of the data, it is more than worth it.

And remember, if you lack the time to take this on you can always contact our support team and get a free Google Sheets Setup Service to help you start things off. 

If you haven’t already, just create your free Databox account and reach out to our team either via chat or email.

See you next time.