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

How to extract email addresses tagged with a specific Gmail label

Extract email from Gmail

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:

Setup sheet for gmail app

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

Continue reading How to extract email addresses tagged with a specific Gmail label

Learn how to build dashboards with Google Sheets and Data Studio

Dashboard on monitor screen

Have you ever wanted to improve your reporting in Google Sheets?

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.

Google Sheets Training

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.

You can use dashboards to make sound marketing and business decisions that help you grow your organization in smart ways, for example:

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

This course covers everything you need to know about building beautiful business dashboards with Google Sheets and Google Data Studio.

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.

> Lifetime access to all the content, to learn at your own pace.

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

Dashboard course preview

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.
Testimonial image
Testimonial image 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.

Are you ready to start building
beautiful, functional dashboards?

Any other questions? Leave a comment or drop me a line here.

Beginner guide to APIs with Google Sheets & Apps Script

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

We’re going to start with a super simple warm-up API to retrieve some data:

Random math facts from Numbers API in Google Sheet

Then we’ll 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

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. It’s fun and really satisfying if you’re new to this world.


Connecting Google Sheets to an external API using Apps Script

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

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

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 & Apps Script

How to add a hyperlinked index sheet to your large Google Sheet workbooks

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:

Create hyperlinked index sheet

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:

Create hyperlinked index sheet

Steps to create your own hyperlinked index sheet in Google Sheets

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

Continue reading How to add a hyperlinked index sheet to your large Google Sheet workbooks

Google Sheets, Apps Script and Data Studio Resources: The Ultimate List for 2017

Want tons of great Google Sheets, Apps Script and Data Studio resources in one place?

Then you’ll love this list.

These are my go-to resources when I’m building spreadsheet applications for clients or developing content for this blog. I have hundreds of bookmarks on the subject but here I’ve whittled it down to just the very best.

Feel free to suggest any other resources in the comments below!


Google Sheet Resources

Google Help Resources

Starting this list off is the official Google Sheets documentation. This guide covers everything about sheets, including all 346 formulas.

Google spreadsheets function list – all 346 of them! A good place to start when you want to find out the syntax details or how formula X works.

The Google Docs blog – the official source for product announcements, updates and tips from Google.

Forums and G+ communities

Aka, good places to ask questions!

Google Sheets Help Forum. The place to go to get your Google Sheet questions answered. Read this getting started guide if you’re new to it. There are also forums for the other Google Docs.

Google Spreadsheets Tips & Tricks. Another great community for asking and answering questions on Google Sheets. It’s full of experts willing to give their time to answer questions.

Blogs

Continue reading Google Sheets, Apps Script and Data Studio Resources: The Ultimate List for 2017

Funnel charts in Google Sheets using the chart tool, formulas and Apps Script

Let’s talk about funnel charts in Google Sheets.

The charts themselves are a bit of a novelty. Yes, they’re aesthetically pleasing because of that resemblance to a real-world, tapering funnel, which reinforces their message, but a plain ole’ bar chart would be equally suitable and actually easier to read data from (because the bars have a common baseline).

However, they throw up some interesting techniques in Google Sheets and for that reason, merit this long article.

We’ll build them using tricks with the chart builder tool, then with two different types of funky formula and finally, and best of all, we’ll build a tool using Apps Script, as shown in this image:

Funnel charts with apps script in Google Sheets

As with the waterfall charts in Google Sheets, they’re not one of the out-the-box charts available to us, so we have to manually create them with a crafty workaround. Thankfully, they’re relatively simple to create, certainly simpler than the waterfall chart.

For all of these examples, we’ll use this fictitious real-estate dataset:

Google Sheets funnel chart data

Here, I’m imagining the real estate agency collects data relating to their sales funnel, and they want to display it in a funnel chart format.

Click here to open up the Google Sheet template and make your own copy (File > Make a copy...).

Continue reading Funnel charts in Google Sheets using the chart tool, formulas and Apps Script

How to create waterfall charts in Google Sheets

Waterfall charts, eh?

Ok, waterfall charts are real. And useful. They don’t actually involve falling water in any capacity, but they do show the cumulative effect of a series of positive and/or negative values on an initial starting value.

The following waterfall chart shows the headcount changes for a department, visually depicting the cumulative effect of the additions and deletions to the start value:

Headcount Waterfall Chart

It shows the number of staff in our department at the start of the year (left grey bar), the number of people added from other departments or as new hires (green bars), the number of people who left (red bars) and finally the balance which is the headcount at the end of the year (right grey bar).

The waterfall chart above is relatively easy to create in Google Sheets but does still require some data wrangling to set it up. Notice that all of the bars are above the x-axis (Case 1), which makes the data set up vastly simpler than the case when we have a mix of bars above and below the x-axis, or spanning the x-axis (see Case 2 below).

I’ll show you how to create both of these cases, starting with the easier, positive-bar case.

After creating the simple and complex versions manually with formulas, I’ll show you some Apps Script code to automate the majority of the process and massively speed up creating complex waterfall charts.

Templates are available for all three methods, with links at the end of each section and at the end of this post.
Continue reading How to create waterfall charts in Google Sheets

Build time-based triggers in Apps Script projects to automate programs

Ever find yourself needing to repeat an action in your Google Sheets?

For example, on a recent client project I wanted to cycle through my spreadsheet data, compare it to another dataset and apply a matching algorithm. To do this and stay within the 6 minute execution limit for Apps Script, I split my data into “blocks” of 10 rows, counted how many blocks I had and then ran the matching algorithm once for each block until they’d all been processed.

By adding time-based triggers, I setup my Google sheet to do this automatically, rather than having me click a button each time.

Using Apps Script (read my getting started guide here), you can add these time-based triggers programmatically, so they can run and stop automatically.

In this post I’m going to show a skeleton example of this time-based architecture, to which you can add your own functionality.

The entire code file is here on GitHub and the spreadsheet is available here (feel free to make a copy: File > Make a copy… and then you can open the script editor to modify the code).

Time-based triggers with Google Apps Script

In this simple example, I’m going to output a random number in a new line of my spreadsheet every minute until I’ve looped through a set number of times (2 in this case). It’s deliberately basic as the focus is on the trigger/timing architecture.

Here’s a screencast of this simple program in action (speeded up):

Auto trigger loop

Behind the scenes, everything is running on autopilot once I’ve clicked that first Run button.

Continue reading Build time-based triggers in Apps Script projects to automate programs

Creating a d3 chart with data from Google Sheets

In the following image, I have two browser windows open. On the left, a Google Sheet containing a simple table of data. In the right browser window, a web page with a d3 chart that displays the data from the Google Sheet and can be refreshed dynamically when the data changes.

d3 + Google Sheets

Continue reading Creating a d3 chart with data from Google Sheets