Community Connectors: Access all your data in Google Data Studio 💥💥

GitHub commit dashboard, built with the new native data Community Connector
GitHub commits dashboard, built in Data Studio using a native Data Connector

Data Studio is relatively new dashboard tool from Google, launched in mid-2016. It’s a superb tool for creating professional looking reports, easily and quickly, and it connects seamlessly to other Google data sources (e.g. Analytics, AdSense, Sheets, …).

Here’s an introduction to Data Studio and a look at the more advanced features.

Do you work with data outside of Google’s ecosystem though?

I’ll go out on a limb here, and say, yes, most likely you do.

Perhaps you’re a digital marketing analyst looking at Facebook, Twitter, Instagram, MailChimp data (etc…) for example.

Many of us work with other web services and that data has been unavailable or difficult to display in Data Studio (requiring a sub-optimal workaround of importing it into a Google Sheet and connecting that to Data Studio).

Until now!

Well, with the launch of native data Community Connectors today, you can now connect to your favorite web services and access data that lives outside the Google ecosystem directly!

What is a Data Studio Community Connector?

Continue reading Community Connectors: Access all your data in Google Data Studio 💥💥

Show data from the GitHub API in Google Sheets, using Apps Script and Oauth

This post shows you how to connect a Google Sheet to GitHub’s API, with Oauth and Apps Script. The goal is to retrieve data and information from GitHub and show it in your Google Sheet, for further analysis and visualization.

If you manage a development team or you’re a technical project manager, then this could be a really useful way of analyzing and visualizing your team’s or project’s coding statistics against goals, such as number of commits, languages, people involved etc. over time.

Contents

  1. What are Git and GitHub?
  2. Access GitHub API using Basic Authentication
  3. Access GitHub API using OAuth2 Authentication
  4. Resources and further reading

Note, this is not a post about integrating your Apps Script environment with GitHub to push/pull your code to GitHub. That’s an entirely different process, covered in detail here by Google Developer Expert Martin Hawksey.

Continue reading Show data from the GitHub API in Google Sheets, using Apps Script and Oauth

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

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

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

Creating a dynamic d3 visualization from the GitHub API

GitHub Repo Visualization
Try out the GitHub Repo Visualization here

As someone who works with data on a daily basis, I’m always impressed and inspired by interactive charts and dashboards. I’ve built plenty of dynamic dashboards within Excel (here) and, more recently, within Google spreadsheets (here, here and here), but never my own custom web charts. I’ve wanted to learn d3 for a while, but until recently didn’t have the necessary Javascript chops to do this.

This year I’ve focussed on deepening my coding skills, so I’ve finally been able to give d3 a proper go. And let me tell you, it’s brilliant. It’s exciting to hook up a data source to a custom chart that changes dynamically, and be able to see it on a live website, which other people can view.

In this post, I’m going to discuss the steps I took to create this d3 visualization of the GitHub API.

The app is live here!
Continue reading Creating a dynamic d3 visualization from the GitHub API