My First Dashboard in Google Spreadsheets

SQL update

The progress so far on understanding SQL is unfortunately very early days. Last week however, I caught up with an experienced Business Analyst, who has opened my eyes, as it were, to the world of spreadsheet dashboards. I spent a fair few hours this week scrawling through Internet lessons on how to present data from spreadsheets in a dynamic way, and hopefully I've now arrived somewhere quite interesting. This takes advantage of the QUERY function, practically the same as learning pure SQL, so this is my present diversion.

Google Spreadsheets

The advantage of using Google is two fold. 1) It is far easier to build something I can share with people at work (Google Ireland). After spending time learning this stuff, I want it easily accessible and familiar for everyone, and believe it or not, some people in our office simply don't run Excel on their laptops. Moreover, it just seems un-Googley to not use Spreadsheets! 2) I can keep it updated and current. I did start building in Excel, but the kind of data I want to show need to be updated on a daily basis. Sending out an Excel file every day is impractical, so I'm building my dashboard in Google.

A case for Excel

Just before we leave the point, I have to point out that Excel does seem to be significantly more powerful, so once I start working with much bigger sets of data in more complex ways, I wouldn't be surprised if I swapped, particularly with Office 365 looking much more Internet friendly.

The Dashboard - the good bit!

The way I see it, a dashboard needs to turn a whole shed load of dump data into a report - some kind of presentation that makes it useful. For starters, you need a database:

https://docs.google.com/spreadsheet/ccc?key=0Ap52YEdfS7NLdEFyUlR4MzI2S21KaXFyTFZCNUtyWmc#gid=0

I made the mock above about how a fictitious Alice and Bob played a game to win Coins and Stars. It's really simple data. The idea on the dashboard is that you can choose either Alice or Bob, and choose how many Coins they won. The result is how many Stars they got for each time they won this many Coins. While this seems completely useless, you will appreciate that being able to query tables of data like this in a Google Spreadsheet could be kind of handy.

The Query

Just getting the hang of putting this query together took a while. The one I've settled for (for now) is:

"=Query(Database!A1:D200, "select A, B, C, D where C = "&F3&" and B contains """&C3&""" ",1)"

Entered into Dashboard!C9, this returns a table based on the entries above. Now to break it down:

a) =Query(data, query, headers): This is the function. Pretty simple stuff. Enter a 1 for headers when starting out.

b) Database!A1:D200: This is my source, which references the first sheet. You can reference other spreadsheets too using importRange("SPREADSHEET KEY","RANGE"

c) "select A, B, C, D: I started by selecting all the columns. In the same file you use A, B, C etc. but if referring to another file, use Col1, Col2, Col3 etc.

d) where C = "&F3&": Notation for referring to numbers should be in quotes and &s.

e) and B contains """&C3&""": Notation for strings has two more quotes. Not sure why but it took me a lot of faffing to clock this. Also, note the and as opposed to adding another where.

You can find a complete list of syntax on the Query Language Reference page here.

The Chart

So easy, but oh so cool. Just make a chart as you normally would, and as you change the value the chart will move. This will impress your boss, I promise you. For good formatting, you might move your entry fields and charts around so that you have a nice clean page where the user doesn't have to see data at all, but just charts.

Next Step

The plan is to go and apply some of this strategy to relevant data we collect at work, to show some useful things. I've also got some learning to do on how to use dates, hence why in my spreadsheet the dates don't work.

Links/References

A really good video by CrestWebServices that broke it all down for me

Google's Query Language Reference

Using Data Validation for the drop down menu

This is day one of my mission to self learn SQL. Started here:

My Family and Hangouts

My family is a close one. Even though we live across the globe, we do our best to meet frequently, in Hyderabad, Warrington, Chennai or this year even in Cairo. But this is hard - travel is expensive, but it also takes time and planning. So frequently usually means bi-annually, and often just one or two of us can fly over at a time. I'd like to share our story of how over the past few months we've been using Google+ Hangouts to make the world a little bit easier to fly around. Since mid July, me and family have been meeting weekly, every Sunday afternoon, to have a coffee, a biscuit, and to catch up on the "craic" (read: banter, gossip, lols and chit-chat).

The first time my nan video chatted with her grandkids - 12th August '12

The reason for my blog is really to encourage this sort of thing. As new technologies become available, it sometimes take a while before everyone really understands what's possible. I decided in July that a lot was possible, and that we would lead the way. I'm a G+ advocate anyway (see my blog about the end of facebook), but maybe this will give me some ammunition in my argument.

My Family...

...on my Mum's side are in some ways just perfect for Hangouts. We are: my Auntie, Uncle and Grandma in Hyderabad, my eldest cousin and her husband in Warrington, my Mum and Dad, also in Warrington, my other cousin sister in Manipal University in Karnataka, my sister in Keele University, my other Aunty and youngest cousin in Rainhill, my uncle in Chennai, and finally me and my girlfriend in Dublin, Ireland. In case you weren't counting, that's 14 people across 6 different locations.

Troubleshooting

Google+: The first objective was suggesting everyone get a Google account. This part was easier than I thought. Most of us already had one, used for Gmail or YouTube etc, and the rest set one up at the same time as accessing Google+ for the first time. My extensive customer services experience prepared me well for an email with screenshots and step by step instructions, ensuring even my Grandma got an account and was ready to Hangout.

Sound: was an unexpected issue. Early on we noticed that some users' mic pick up volume differently and some laptop settings needed tweaking. Fortunately, this landed with some of the more savvy locations, and doesn't disrupt much at all now.

My Nan mistook FaceTime for Google+...

Tablets: are not going to be ideal. Although I am able to hangout just fine on my iPad, its not the easiest for those unfamiliar with the device and unfamiliar with Hangouts. Beware troubleshooting this one - it's just not worth it. Maybe this will get better, but for the time being, desktops and laptops are easiest. With so many of us, the extra screen space is needed too. No news yet on how Android copes with this, but I'll try it out soon enough.

Timezones: Also solved easier than I thought - we meet at 5pm UK time / 9:30pm India time, which suits everyone.

The Sell

The above issues aside, this has gone brilliantly. Some of the big events we would not have been able to share include seeing my sister move into University, seeing my cousin showing off her first doctor's coat, celebrating Rakhi, seeing my parent's home extension develop, but most of all, seeing my grandma's happiness from being with her global grandchildren every week. I'd say we all feel closer together, much more up-to-date, and really happy my Grandma can spend this time with us, even when she is so far away. Furthermore, with my sister and cousin being in University, living away from home for the first time, I'm really pleased they both have a time of the week where they know at least some of us will be free to spend some time with them, and give them eyes and ears if they need it.

Kala showing off her badge

I'm hoping that by now you are more encouraged to try Hangouts. Just to make my sales pitch here, note that Facebook and Skype don't offer multiple chat at the same time or the same kind of app support available with Google. Beyond our family hangouts, G+ came into it's own when me and my mum sat down together to work on some documents, and when me and my sister spent idle time watching YouTube videos together, as though we were at home, avoiding chores like we do. This truly is the new MSN Messenger in the age of high speed broadband. Don't miss the opportunity to be with people who seem miles away. If you have any questions about how certain things work or need any help getting set up, leave me a comment, give me a ring, or you could even start a Hangout with me.