Tag: data

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) } }

Continue reading How to add a total row to a Query Function table 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.

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

Connecting to the Crunchbase API with Google Sheets

UPDATE: This article was originally written in 2017. Since then, the Crunchbase API is now part of their enterprise tier. The old API, which this article is based on, is no longer accessible. As a result, the code I share below will no longer return Crunchbase data. I leave it here for reference. It may be useful if you do use the paid tier.

Crunchbase API with Google Sheets

The Crunchbase API is easily accessible with Apps Script, meaning you can retrieve Crunchbase company data to display, or analyze further, in your Google Sheet. This article shows you how to connect to the Crunchbase API.

How to import data from the Crunchbase API into Google Sheets

Crunchbase is a business information platform; a sort of giant database of information on organizations and people in the business world, especially the digital/technology/startup world.

They have an API so you can programmatically access and retrieve business data. There’s a free tier, which is what I’ll show in this article, and a paid, pro tier, which has a much richer dataset available through the API.

On the free tier, you’re limited to data on organizations and people profiles, through their Open Data Map. It’s a RESTful API with two endpoints (/odm-organizations and /odm-people) and you need to apply for basic access first to get a user key for access.

Continue reading Connecting to the Crunchbase API with Google Sheets

2015 in review and a look forward to 2016

Wow, that time of year again! Time flies.

New Year's Eve Fireworks
New Year’s Eve fireworks by: Christine Matthews

2015

2015 was a year of huge growth, both personally and in a work capacity.

By far the biggest event of 2015 (or in fact, life so far) was the birth of my son which has enriched and changed life in so many ways. It’s been challenging to figure out how to care for and nurture a new human being. Balancing that with rest of life hasn’t been easy for my wife and me. But we’re finding our feet as new parents and the experience is so special that it trumps anything else. When he smiles back or laughs it makes every night ops session worth it.

In a work capacity in 2015, I focussed on establishing a freelance career in data analytics through client work and teaching for General Assembly. Early in the year, I expanded my technical skills with a foray into web development but things really took off for me when I doubled-down on my true work passion – making sense of data.

I worked with Excel, MySQL, PostgreSQL, Google Sheets, Geckoboard and Tableau for client projects and at General Assembly, teaching data analytics to students.

Some of the highlights of 2015:

  • Teaching Data Analytics and Excel courses for General Assembly was a fantastic experience. I’m delighted to continue into 2016, teaching these courses again.
  • All sorts of interesting client work, including building dashboards and SQL data analysis projects.
  • Learning web development through building several web applications, using d3, Rails and Javascript.
  • Finally building that Excel dashboard of US Political data that I had in the back of mind for far too long.
  • Figuring out a workaround using CSS and text widgets to create pie charts in a Geckoboard dashboard. This was undoubtedly the most detailed and time consuming side project of the year, but for that reason one of the most satisfying!
  • And finally, on a personal level, publishing stories and photos from the 2014 climbing trip to the beautiful Rocky Mountains – Part 1 and Part 2.

2016

I love the first few days of January, when the whole year stretches ahead and the options seem limitless. I’m really excited about 2016 and the work projects I have planned. It’s going to build on the foundations I laid in 2015, as I expand the teaching, client and website offerings.

Specifically, I have a couple of digital products launching in the first quarter, which I can’t wait to share. Watch this space!

So, goals for 2016:

  • Teach Data Analytics and Excel courses for General Assembly again. I’m signed up to teach the cohort starting on January 30th.
  • Launch my first ebook, featuring all of the most interesting, weird and wonderful spreadsheet tricks I’ve come across. I’ve nearly finished writing it and can’t wait to share it. It’s been hugely fun to research and write. Coming your way soon!
  • Launch my own dashboard course, likely through Udemy. I’m working on my first digital course which I plan to launch in the first quarter of this year. Again, I’m really excited about this.
  • Write more frequently on this blog. This website is critical to my business since the majority of my leads come through it, so I’m going to make a big push to create lots of interesting and valuable content on here this year.
  • Do more public speaking. I enjoyed speaking at GA’s graduation event last year and would love to speak at some events or meetups this year. It’s a great way to meet new people and share ideas with an interested audience.

What’s my secret mantra for 2016?

Focus!

This was my biggest single takeaway from last year. Things really started to move for me when I zeroed in on a niche and poured my heart and soul into it. So I plan to keep this in mind, stay focussed and avoid distractions this year.

So cheers to big things happening this year!

Happy New Year and all the best for 2016!

Cheers,
Ben