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

How to create an annotated line graph in Google Sheets

Animated e-junkie sales chart in Google Sheets
Animated line chart 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?
Click here to access your copy of this template >>

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:

Google Sheet e-junkie real-time dashboard
Google Sheet e-junkie real-time dashboard

Continue reading How to create an annotated line graph in Google Sheets

How to build a real-time sales dashboard for E-junkie using Google Sheets and Apps Script

It’s day two of a four day product launch. You’ve worked hard all year to create a fantastic product, test your sales systems and tell the world about this amazing offer. You know you’ve sold 100 products so far, but…

…you don’t know whether your ads are effective, which affiliates are really killing it versus which have forgotten about your launch, or even whether your own emails are converting.

Looking at your sales log only, and having to decipher what’s happened since the last time you looked an hour ago, is like trying to drive in the dark without headlights.

Thankfully there is a better way to track your sales, so you can see your data, get insights about what’s working and what’s not, and immediately act to increase your bottom line.

This post looks at how to build a real-time dashboard for the E-junkie digital sales platform using Google Sheets:

Google Sheet e-junkie real-time dashboard
Google Sheet e-junkie real-time dashboard (fictitious data)
Want your own copy of this dashboard?
Click here to access your copy of this template >>

E-junkie is a digital shopping cart, used for selling digital products and downloads. The system handles the shopping cart mechanics, but does not do any data analytics or visualizations.

You can view a transaction log (i.e. a list of all your sales) but if you want to understand and visualize your sales data, then you’ll need to use another tool to do this. Google Sheets is a perfect tool for that.

You can use a Google Sheet to capture sales data automatically in real-time, and use the built-in charts to create an effective dashboard.

You’d be crazy not to have a tracking system set up, to see and understand what’s going on during sales events or product launches. This E-junkie + Google Sheets solution is effective and incredibly cheap ($5/month for E-junkie and Google Sheets is free).

The Write Life ran a Writer’s Bundle sale this year, during the first week of April. It’s a bundled package of outstanding resources for writers, including ebooks and courses, heavily discounted for a short 4-day sales window.

I created a new dashboard for The Write Life team to track sales and affiliates during the entire event. This year’s dashboard was a much improved evolution of the versions built for the Writer’s Bundle sales in 2014 (which, incidentally, was my first blog post on this website!) and 2015.

The biggest improvement this year was to make the dashboard update automatically in real-time.
Continue reading How to build a real-time sales dashboard for E-junkie using Google Sheets and Apps Script

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

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

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