## Data Cleaning and Pivot Tables in Google Sheets — My New Course has launched!

There’s a joke in the data community that goes:

“You’ll spend 90% of your time cleaning data, and the other 10% of your time complaining about that…”

Do you deal with badly formatted data that takes you hours to tidy up, preventing you from making progress on your “real” work?

Ever wondered what the heck a Pivot Table is, and why you should care about them?

Do you ever find yourself repeating a process manually, like extracting zip codes from addresses?

This new course is designed to help you work with messy, real-world datasets. It’ll teach you techniques and best practices for cleaning data and preparing it for analysis and reporting.

This new course is aimed at beginner-to-intermediate Google Sheets users.

## 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:

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.

## How to reply to Google Forms survey responses quickly with Sheets & Apps Script

You’re happy!

They’re sitting pretty in a Google Sheet but now you’re wondering how you can possibly reply to all those people to say thank you.

Manually composing a new email for each person in turn will take forever. It’s not an efficient use of your time.

You could use an ESP like Mailchimp to send a bulk “Thank You” message, but it won’t be personal. It’ll be a generic, bland email and nobody likes that. It won’t engage your customers and you’ll be missing an opportunity to start a genuine conversation and reply to any feedback from the survey.

Thankfully, there is another way.

Of course there is, otherwise why would I be writing this tutorial! ðŸ˜‰

By adding a reply column to your Google Sheet, next to the Google Forms survey responses, you can efficiently compose a personal response to every single survey respondent.

Then, using Google Apps Script (a Javascript-based language to extend Google Apps, start here if you’re new), you can construct an email programmatically for each person, and send out the responses in bulk directly from your Google Sheet.

## How to create an annotated line graph in Google Sheets

This post looks at how to create a more advanced line graph in Google Sheets, with comparison lines and annotations, so that the viewer can absorb the maximum amount of insight from a single chart.

For fun, I’ll also show you how to animate this line graph in Google Sheets.

Want your own copy of this line graph?

This chart was originally developed for The Write Life during their 4-day product sale earlier this year. It featured as part of a dashboard that was linked to the E-junkie sales platform and displayed sales data in real-time:

## How to extract email addresses tagged with a specific Gmail label

Everyone uses email in different ways. For me, email is at the very centre of my business. Everything flows through my Gmail account.

Being able to categorize all work enquiries or questions with a specific label, for example Queries (which I nest under my Work label) is one of the most useful features of Gmail.

Recently I needed to extract all of the email addresses for the hundreds of messages under this label. Super tedious to do manually, but thankfully there’s a much quicker way using Apps Script.

In this post, we’ll see how to setup a Google Sheet to extract all the email address for a specific Gmail label. I’ll share the code and walk through it with you.

# How to use the Gmail Service with Apps Script and Google Sheets

## Step 1: Set up the Google Sheet

The Google Sheet for this example is super simple for a change.

Cell B1 is where we type the label that we want to extract emails from, and then on row 3 are the two column headings, Name and Email.

The sheet looks like this:

Really important note

In this example I have a nested label, where I want email addresses from the label “queries” which belongs to the parent label “work”. Hence I need to write the label with a dash to show this relationship, with the parent label listed first, hence: “work-queries”.

If you are just looking at a single label with no relationship with other labels, then you can just type that label, e.g. “work”.

## How to import social media statistics into Google Sheets: The Import Cookbook

Google Sheets has a powerful and versatile set of IMPORT formulas that can import social media statistics.

This article looks at importing social media statistics from popular social media channels into a Google sheet, for social network analysis or social media management. If you manage a lot of different channels then you could use these techniques to set up a master view (dashboard) to display all your metrics in one place.

The formulas below are generally set up to return the number of followers (or page likes) for a given channel, but you could adapt them to return other metrics (such as follows) with a little extra work.

Caveats: these formulas occasionally stop working when the underlying website changes, but I will try to keep this post updated with working versions for the major social media statistics.

Example workbooks: Each example has a link to an associated Google Sheet workbook, so feel free to make your own copy: `File > Make a copy...`.

## 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.

## Learn how to build dashboards with Google Sheets and Data Studio

Or create dashboard reports that are both beautiful and functional?

Build Business Dashboards With Google Sheets and Data Studio, my new online course, launches today.

This is a comprehensive, online course that will teach you all the key techniques for building beautiful, interactive dashboards with Google Sheets and Google’s new visualization tool, Data Studio.

Dashboards are fantastic tools that give you a high-level view of your business, showing KPIs, trends and data comparisons.

• Showing the growth of your website or clients’ websites, by tracking traffic and social media metrics over time.
• Understanding your business better by collecting and analyzing user data in real-time.
• Comparing sales data across different time periods, sales channels or geographic regions to get deeper insights into your opportunities.
• Measuring student attendance and performance to easily see where to allocate resources.
• Tracking and visualizing financial data to uncover high- and low-performing areas of your company, so you can make adjustments going forward.

By learning some relatively simple techniques, you can create dashboards that are automated, interactive and visually appealing. They’ll get people’s attention and wow your colleagues and bosses.

# Build dashboards with Google Sheets and Data Studio

This course is dashboard-focused and entirely practical. Concepts are taught through real-life examples, starting with a basic static dashboard all the way through to the complex web marketing dashboard you can see in the screenshot above.

The course covers data visualization and dashboard best practices, dynamic dashboards, how to use Google Forms to collect data, how to import external data, how to use Apps Script to automate processes and add extra features, Google’s new dashboard tool, Data Studio, and much, much more.

## What you get when you enroll:

> Over 9 hours of video content.

> Copies of all the finished dashboard templates for the five dashboard examples and the one Data Studio report.

> Access to all the raw data files, copies of the formulas and links to online documentation and other great resources.

> Membership to our dedicated Facebook group where you can post questions and get answers, share insights and interact with other students.

## What others say about the course:

 I’d recommend this course for anyone trying to make great dashboards in Google Sheets. Ben is a great teacher and he explains everything very thoroughly. Five stars from me. – Jakob R. This course is a fantastic way to discover how to build dashboards. Ben takes you step by step through what you need to do and his approach is well thought and organised. The information covers the basics and introduces more complex subjects in a simple and easy to follow format. – Marilyn C. Everyone talks about data culture in companies, but only a few know how to do it. Good dashboards are a great first step to democratize access to data in a simple way, and this course will definitely change the way you see your data today. – Gabriel O.

## 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:

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

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

# 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

Admit it, we’ve all been there. Getting frustrated trying to find a specific sheet inside a huge workbook, and not being able to see it amongst all the other sheets.

Well, here’s a quick Apps Script to create a hyperlinked index page at the start of your workbook.

Quick caveat first: There’s one drawback – the hyperlinks open the sheets in new tabs, which unfortunately I can’t do anything about. However, it should still be useful for anyone working with workbooks with 10+ sheets.

Here it is in action:

What if we already have a sheet by the name ‘Index’?

Well, you’ll be prompted to enter a different index name or to cancel the operation:

Step 1: Open up the workbook you want to add the index sheet to and open the apps script editor (`Tools > Script editor...`).