Blog

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

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. Continue reading How To Extract Email Addresses Tagged With A Specific Gmail Label

How To Combine Text And Numbers In Google Sheets

This tutorial will show you how to combine text and numbers in Google Sheets, and keep the the correct number formatting in place.

Combining text and numbers is common in spreadsheets, for example when you want to add some context, e.g.:

Total: $5,250
Discount: 35.5%

However, if you try to combine text with numbers (or currencies/percentages/dates) directly in a cell in Google Sheets, you’ll notice the numbers loses any formatting, which will make the number harder to read or even less meaningful, e.g.:

Total: 5250
Discount: 0.355

You can use the TEXT function to fix this, and apply formatting to numbers that are displayed with text in a cell.

Continue reading How To Combine Text And Numbers In Google Sheets

How can I format individual data points in Google Sheets charts?

In this article, you’ll see how to annotate your Google charts, by adding formatting and data labels to specific data points only.

Formatting specific datapoints or adding data labels are a very powerful way of adding context to your charts, to bring attention to certain data points and add additional explanation.

How to add formatting to individual data points only?

Custom formatting for individual points is available through the chart sidebar: Chart Editor > CUSTOMIZE > Series > FORMAT DATA POINTS

Chart Editor format series

Continue reading How can I format individual data points in Google Sheets charts?

First And Last Days Of The Prior Month In Google Sheets?

In this example, we’re going to see how to extract the first and last dates of the prior month, i.e. the last full month before this current one.

First and last date of prior month

It’s the sort of date filter that’s frequently used in digital marketing, when you want to restrict your data to just web traffic or revenue in this period for comparison.

How do I get the first and last dates of the previous month?

We combine two functions, TODAY and EOMONTH, and a little bit of math to create the formulas to extract first and last dates.

What’s the formula?

For the first day of the prior month:

=EOMONTH(TODAY(),-2)+1

For the last day of the prior month:

=EOMONTH(TODAY(),-1)

Can I see an example worksheet?

Yes, here you go.

How does this formula work?

The heart of both of these formulas is the function TODAY(), which outputs today’s date in your Sheet. It updates automatically whenever the spreadsheet is recalculated (when you make an edit somewhere else). It’s known as a volatile function because it automatically recalculates, so if you were to have a huge number of these formulas, it would affect the performance of your spreadsheet.

So we start with:

=TODAY()

Next we wrap that with the EOMONTH function to get the date at the end of the month.

For the first day of the previous month, we offset by -2 to go back two months (which gives the last day of December in this example). To this we then add 1 day to nudge us into the first day of the previous month (January 2017 in this example), as follows:

=EOMONTH(TODAY(),-2)+1

The formula for the last day of the prior month is simpler. We offset -1 to go back one month and we don’t need to add a day to the output. So the formula is:

=EOMONTH(TODAY(),-1)

These types of date formulas are super useful if you do any data analysis work, and want to group and compare data for set periods. As an example, you may want to automatically generate Start and End Date fields in the Google Analytics Add-On, to always extract the most recent data for current and prior month periods for comparison.

Google Analytics Add-On start and end date

See also: We can perform similar calculations to get weekly dates, prior months, quarterly dates and yearly dates.

Check out this article on creating a custom Google Analytics report in a Google spreadsheet, to see date formulas in action.