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