Google Sheets and Mailchimp integration using Apps Script

In this post, we’re going to see how to setup a Google Sheets and Mailchimp integration, using Apps Script to access the Mailchimp API.

The end goal is to import campaign and list data into Google Sheets so we can analyze our Mailchimp data and create visualizations, like this one:

Google Sheets and Mailchimp integration for campaign data analysis

Mailchimp is a popular email service provider for small businesses. Google Sheets is popular with small businesses, digital marketers and other online folks. So let’s connect the two to build a Mailchimp data analysis tool in Google Sheets!

Once you have the data from Mailchimp in a Google Sheet, you can do all sorts of customized reporting, thereby saving you time in the long run.

I use Mailchimp myself to manage my own email list and send out campaigns, such as this beginner API guide (Interested?), so I was keen to create this Mailchimp integration so I can include Mailchimp KPI’s and visualizations in my business dashboards.

For this tutorial I collaborated with another data-obsessed marketer, Julian from Measure School, to create a video lesson. High quality video tutorials are hard to create but thankfully Julian is a master, so I hope you enjoy this one:

(Be sure to check out Julian’s YouTube channel for lots more data-driven marketing videos.)

If you’re new to APIs, you may want to check out my starter guide, and if you’re completely new to Apps Script, start here.

Otherwise let’s begin.
Continue reading Google Sheets and Mailchimp integration using Apps Script

Connecting to the Crunchbase API with Google Sheets

UPDATE JAN 2018: Cruncbase recently updated their API to version v3.1 and deprecated v3, which this article is based on. As a result the code I share below will no longer return Crunchbase data. I’ll update the code when I can. In the meantime, hopefully it’ll still prove useful in your own explorations of the Crunchbase API.

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.

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

How can I combine text and numbers in a cell and keep the number formatting?

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

e.g. Total: $5,250
e.g. 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
e.g. 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.

How do you combine numbers and text in a cell in Google Sheets?

To combine text, in a cell or denoted by quotes "Text", and numbers, use the TEXT function, as shown in these examples.

For these examples, I’m assuming I have numbers, currency numbers, percentages or dates in column A:

Combining numbers and text in Google Sheets

What’s the formula?

With numbers in column A, then you can combine text and numbers with:

="Text and number: "&text(A1,"0.00")
="Text and number: "&text(A2,"#.##")

What’s the difference?

The 0 forces your cell to display zeros if a number has fewer digits than the format specifies, whereas the # does not force the display of zeros.

So TEXT(19.9,"000.00") displays 019.90, whereas TEXT(19.9,"###.##") displays 19.9.

Thousands separator

If your number is in the thousands, and you want to show the thousand separator comma, simply add that into the format section of the text formula. It works the same for the “0” or “#” notation:

="Text and thousands number: "&text(A3,"0,000")
="Text and thousands number: "&text(A3,"#,###")

Currency

Combining text and currency is easy enough, now you know how to format numbers. Simply add the currency symbol to the front of the number and it will show in your text:

="Currency: "&text(A4,"$0,000.00")

Percentages

Combining text and percentages:

="Percentage: "&text(A5,"0.00%")

Dates

Combining text and dates:

="Date: "&text(A6,"mm/dd/yyyy")

Can I see an example worksheet?

Yes, here you go.

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

When you click on the FORMAT DATA POINT button, you’re prompted to choose which data point you want to format (what you see here will depend on your chart):

format individual datapoint in google charts

This data point is added under the Series menu in the Chart Editor sidebar, so you can specify unique formatting. For example, I’ve formatted the largest revenue month in red in the following chart:

single datapoint formatted

It’s possible to add multiple data points to be formatted (and each can be uniquely formatted):

multiple datapoints formatted

Note, custom formatting can be applied to individual data points by right clicking them from within the chart:

right click format datapoint

How to add labels to specific data points only?

In the example below, I used data labels to clearly indicate the sales figures for the end of each day, during a 3-day digital flash sale, which helped the client easily see their performance.

Data labels on specific data points

Let’s start with this dataset:

Basic chart data

which records the number of products sold during a 3-day sale.

If you plot a default line chart and add data labels in the Chart Editor, you’ll notice you have no choice of their placement; it’s all or nothing, as shown in this image:

Chart data labels in chart tool

So, we need an alternate approach.

Annotation columns

The trick is to create annotation columns in the dataset that only contain the data labels we want, and then get the chart tool to plot these on our chart.

Add annotations in new columns next to the datapoint you want to add it to, and the chart tool will do the rest. So if you set up your dataset like this:

Chart annotation columns in Google Sheets

then the chart tool will interpret those annotations and add them to the correct points on your chart:

Annotated chart in Google Sheets

Note, you’re not restricted to text in these annotation columns. In this example the formulas in columns C and E are respectively:

=B5&" sales"

and

="End of day 1: "&D5&" sales"

Can I see an example worksheet?

Yes, here you go.

How do I add data labels to only the last datapoint of my series in Google Sheets?

Using exactly the same technique as illustrated above, you can label the last points of your series. You can do this instead of a legend and, in many cases, it can make your charts easier to read, as your viewer’s eye doesn’t need to scan back and forth between the series lines and the legend.

Data labels on last data point

The dataset to create this effect is as follows:

Dataset for annotated chart in Google Sheets