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

Connecting to the Crunchbase API with Google Sheets

Crunchbase API with Google Sheets

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

Help, my formula doesn’t work!

Admit it, we’ve all been there.

Whether you’re just starting out with Google Sheets or you’re a seasoned pro, sooner or later one of your formulas will give you an error message rather than the result you want.

It can be frustrating, especially if it’s a longer formula where the error may not be obvious.

Auditing and Debugging Formula Errors in Google Sheets

Match the error message in your Google Sheet to the sections below, and find out what might be causing your error.

  1. An error message popup prevents me entering my formula
  2. I’m getting an #N/A error message
  3. I’m getting an #DIV/0! error message
  4. I’m getting an #VALUE! error message
  5. I’m getting an #REF! error message
  6. I’m getting an #NAME? error message
  7. I’m getting an #NUM! error message
  8. I’m getting an #ERROR! error message
  9. I’m getting an #NULL! error message
  10. Other strategies for dealing with errors
  11. Functions to help deal with formula errors in Google Sheets
  12. Help! My formula is STILL not working

Continue reading Help, my formula doesn’t work!

Beginner guide to APIs with Google Sheets & Google Apps Script

The goal of this post is to guide you through connecting Google Sheets to your very first external API using Google Apps Script, to retrieve data from a third-party and display it in your Google Sheet.

We’re going to start by using Google Apps Script to connect to a super simple warm-up API to retrieve some data:

Random math facts from Numbers API in Google Sheet

Then we’ll use Google Apps Script to build a music discovery application using the iTunes API:

Itunes API with Google Sheets

Finally, I’ll leave you to have a go at building a Star Wars data explorer application, with a few hints:

Star Wars API explorer in Google Sheets using Google Apps Script

The basics: what is an API?

You’ve probably heard the term API before. Maybe you’ve heard how tech companies use them when they pipe data between their applications. Or how companies build complex systems from many smaller micro-services linked by APIs, rather than as single, monolithic programs nowadays.

API stands for “Application Program Interface”, and the term commonly refers to web URLs that can be used to access raw data. Basically, the API is an interface that provides raw data for the public to use (although many require some form of authentication).

As third-party software developers, we can access an organization’s API and use their data within our own applications.

The good news is that there are plenty of simple APIs out there, which we can cut our teeth on.

We can connect a Google Sheet to an API and bring data back from that API (e.g. iTunes) into our Google Sheet using Google Apps Script. It’s fun and really satisfying if you’re new to this world.


Connecting Google Sheets to an external API using Google Apps Script

We’re going to be using Google Apps Script to connect to external APIs in the following examples.

Google Apps Script is a Javascript-based scripting language hosted and run on Google servers, that extends the functionality of Google Apps. If you’ve never used it before, then you may want to read through my Beginner Guide before you start these examples.

Warm-up: Connecting Google Sheets to the Numbers API using Google Apps Script

We’re going to start with something super simple, so we can focus on the data and not get lost in lines and lines of code.

We’re going to write a short program that calls the Numbers API and requests a basic math fact.

Step 1: Open a new blank Google Sheet and rename it: Numbers API Example

Continue reading Beginner guide to APIs with Google Sheets & Google Apps Script

Vlookup in Google Sheets using wildcards for partial matches

VLOOKUP with a single wildcard

Imagine we have this table of employee information and we’ve been given a partial name (in this example: “Mye“) to find and retrieve information about.

To do this we use a standard VLOOKUP formula with the wildcard asterisk character: *

Continue reading Vlookup in Google Sheets using wildcards for partial matches

Refugees resettling in the U.S. in 2015: where from, where to and who?

2015 was a challenging year for humanity. The plight of refugees was never far from the headlines, as multiple catastrophes unfolded across the globe.

Origin and state maps

This case study has two aims: first, to show facts on the numbers and details of refugees resettled into the U.S. in 2015, and, second, to discuss the methodology to help and inspire others to explore the data.

Contents

  1. Introduction
  2. Where From?
  3. Where To?
  4. What Religious Affiliation?
  5. Appendix 1: Sourcing the Raw Data
  6. Appendix 2: Setting up the Data in SQL
  7. Appendix 3: Analysis in SQL
  8. Appendix 4: Visualizing in Tableau

Continue reading Refugees resettling in the U.S. in 2015: where from, where to and who?

Using the FILTER() function to return specific values in a Google Sheet

Filter formula in Google Sheets

The FILTER function in Google Sheets is a powerful function we can use to, well, filter our data.

Suppose we want to retrieve all values above a certain threshold? Or values that were greater than average? Or all even, or odd, values?
Continue reading Using the FILTER() function to return specific values in a Google Sheet

Excel tutorial: Building a dynamic, animated dashboard for U.S. political data

Excel dynamic dashboard screenshot

Who doesn’t love a dynamic, animated Excel dashboard?

Here’s one I’ve been working on recently, a data visualization of historical U.S. political data, showing party trends, state populations and sitting presidents over time:

Excel Dynamic Dashboard - US Political data
Click to see full size

In the following post, I delve into the details of how I created this dashboard. It’s not a full cell-by-cell account of how I did it, because that would require an article at least twice as long, but rather a look at the various steps and thought processes along the way.

If it appears a little ragged, that’s because it probably is! Most likely because I’m writing this bleary eyed at 1am, between feeds and diaper changes of my 6 week old son. 😉

Continue reading Excel tutorial: Building a dynamic, animated dashboard for U.S. political data

Using Google Sheets as a basic web scraper

Have you ever wanted to extract a specific piece of information from a website, so you can analyze it in your Google spreadsheet? Turns out, basic web scraping is possible right in your Google spreadsheet.

If you want to import social media data into your Google Sheets, you might also be interested in my follow up post to this one – using the same formulas as below to import popular social media network stats into your Google Sheets.

For example, recently I needed to find out the authors for a long list of blog posts from a Google Analytics report, to identify the star authors pulling in the page views. It would have been extremely tedious to open each link and manually enter each author’s name. Thankfully, there are some techniques available in Google Sheets to do this for us.

Continue reading Using Google Sheets as a basic web scraper