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.

1. What are Git and GitHub?

Before going any further, you need to understand what Git is.

It’s a version control system, for tracking changes in your code.

It keeps copies of all your code and changes locally, so you can easily recover if you make a mistake. Additionally, you can do things like make a new branch of your code, work on it for a while until you’re satisfied it’s correct, then merge it back into the master codebase. It’s an essential tool for a professional development workflow.

Phew, what’s GitHub then? In the most simplified sense, it’s a cloud-based version of Git. In other words, you can store your code, your changes, manage branches, share code, clone code etc. all managed through your web browser. The typical workflow is to use Git locally on your machine and then “push” your code up to GitHub to keep copies in the cloud. You can “pull” code down from GitHub back to your local machine, for example, if you want to see someone else’s work.

GitHub Resources

Sign up for your own GitHub account here.

Here’s the getting started resource from GitHub.

As an example, here’s my profile on GitHub.

Here’s a more detailed primer on GitHub from my friend Kevin, who runs the awesome online data science school: Data School.

2. Importing GitHub data into Google Sheets using Basic Authentication

Before we dive into Oauth – what it is and how we use it – it’s worth noting that you can connect to the GitHub API using Basic Authentication, without resorting to the complexity of Oauth.

It’s limited to 60 API calls per hour, so won’t get you very far if you want to build a useful application.

However, it’s enough to get started and understand how the GitHub API works.

I created this basic application, which takes a GitHub username (e.g. benlcollins) as an input, returns the list of repositories (code stores) and then returns the languages for a chosen repository. There’s also an option to see how many of your 60 basic API calls you’ve used.

GitHub API Sheets connection

To create this application, open a blank Google Sheet and create two tabs called “Github dashboard” and “Workings”.

In the Github dashboard sheet, add the following headings:

Basic Authentication sheet for Github intregration

In cell B4 of the Github dashboard tab, add a data validation (Data > Data validation…) and choose the criteria “List from a range” and use “Workings!A:A”, as shown in this image:

Data validation Google Sheet

When you run the script and retrieve the list of repos for a given user, they are stored in the Workings tab in column A. This data validation will pick up this list of repos, so the user sees the repo list in the drop down menu in the Github dashboard tab (see the app image above).

Next, open your script editor (Tools > Script editor…), remove the existing code and add the code from this GitHub repo.

Run the onOpen() function and grant the necessary permissions (note – Google changed the verification procedure recently, so you’ll hit the “unverified app” screen. You can continue by choosing advanced settings, and if you intend to share your app, you can apply to have it verified):

Unverified Apps Script authentication

Now that you’ve run the onOpen function, you’ll have a menu available in your Sheet. Type in a GitHub username (e.g. benlcollins), then choose “Get User Repos” from the menu.

Once this function runs, you should see a list of repos in the drop down menu in cell B4. Choose a specific repo and choose “Get Repo Languages” from the menu. This will populate the table with the languages and character count for that repo.

You can select the “Get GitHub Rate Limit” to see how many of your 60 API calls you’ve used within this hour.

3. Importing GitHub data into Google Sheets using OAuth2 Authentication

Ok, ready to build an authenticated GitHub API to Sheets application? You’ll get 5000 API calls per hour!

What is OAuth authentication? It’s a standard way of granting websites (or applications) access to your data on other websites (or applications) without needing to enter your username/passwords, so that the applications can talk to each other programmatically, without you needing to authorize it every time.

OAuth 2.0, which is what we’ll use here, provides specific authorization flows for web applications.

Essentially, we’re creating a pipeline between the GitHub API and Google Sheets and giving this particular Sheet application permission to talk to and access data from our GitHub account, once it’s been authorized the first time by us.

OAuth workflow steps

Step 1. Open a new Google Sheet and open up Script Editor. As per usual, you can clear out the boilerplate myFunction() code.

Step 2. Click on Resources > Libraries...:

Add Oauth library

A library is a re-usable body of code to do a specific function, in this case handle the OAuth process, which we can use by linking to the library from within our Apps Script project.

Step 3. Add the OAuth2 library project key:

1B7FSrk5Zi6L1rSxxTDgDEUsPzlukDsi4KGuTMorsTQHhGBzBkMun4iDF

and select the most recent version (version 21 at the time of publishing this post, August 2017):

Add google oauth library

Step 4. Add a second apps script file, called Oauth.gs, to your project, so you have the following two apps script files in the left pane of your Script Editor:

  • Code.gs
  • Oauth.gs

Step 5. Add the following code to the Code.gs file:

Note: this example has my username, benlcollins, hard-coded into line 18, so feel free to change that to your own username. Of course, you’ll want to probably change this to pick up the username that’s typed into a cell of your Google Sheet, as we did in the Basic example, but this tutorial is focussing on the OAuth part, so we only need this single example for that.

Step 6. Add the following code to the Oauth.gs file:

This is the standard OAuth service code from section 1 here, with some modifications for the GitHub application.

Let’s discuss what changes I made, as you’d need to make similar changes if you’re wanting to connect to other APIs.

> I started with the standard OAuth service code from section 1 here.

> Changed lines 5 and 6 to reflect the name of the service you’re creating, in this case for GitHub.

> Changed the Authorization Base Url and the Token Url on lines 7 and 8 to be GitHub specific (see step 7 below on how to find these URLs).

> Set the scope to 'user' on line 13.

> Since this is not a Google specific API, I removed all of the setParam lines from the standard code.

Step 7. Find the Authorization Base Url and the Token Url for GitHub, which we get from here.

The authorization base url is this one:

Github authorization base url

The URL we’re after is:

https://github.com/login/oauth/authorize

From this webpage, we also want the token url, which is found a short way down the page:

Github access token url

The URL is:

https://github.com/login/oauth/access_token

These two URLs replace the Google ones in the standard service code, as mentioned above.

That’s our basic code in place.

Now we need to publish the app and set things up on the GitHub side, get our authorization credentials so the Google Sheet can talk to the API.

We need two more URLs to be able to setup our application in GitHub: the redirect URL and the Web App URL, so let’s get them now.

Step 8. The redirect URL takes this form:

https://script.google.com/macros/d/{SCRIPT ID}/usercallback

where we need to replace {SCRIPT ID} with our own script ID, which we get from the “File > Project properties”:

Apps script project properties

This is the script id:

Script id from project properties

Copy this script ID into the redirect URL, replacing {SCRIPT ID} (including the curly braces {}) with your own script ID, and then make a copy of the full URL somewhere (for step 10).

Step 9. To get the application’s URL, publish the project as a web application: “Publish > Deploy as web app…”:

Deploy as web app

Note: you’ll be prompted to grant permissions to your app. Follow the steps. Since July 18, 2017, Google requires Apps Script projects to be verified, which you can do through this form, although for the purposes of this example, you can continue with an unverified app, which will look something like this:

Unverified Apps Script authentication

When you get to the final deployed popup screen, copy this web app URL somewhere, ready for Step 10:

Deploy as web app

Step 10. Time to register our application in GitHub.

Go to the developer GitHub pages, login and create a new application.

Create github developer app

You’ll be prompted to enter some application details:

Github register new oauth application

Step 11. Enter a project name, the web app url (from step 9) and redirect url (from step 8) into the respective fields, as follows:

Create GitHub application

Step 12. You should then see your app credentials page where you can get your Client ID and Client Secret:

Github app credentials

Copy the Client ID and the Client Secret into the placeholders on lines 1 and 2 of the code in the OAuth.gs file.

Make sure you don’t share these with anyone (don’t save to GitHub for example!) as that would give anyone access to your GitHub application.

Step 13. Back in your Script Editor, run the onOpen() and getUserRepos() functions. From the vantage point of your script editor it’ll look like nothing has happened, but the onOpen function will have added the menu to your Sheet, and the getUserRepos will have logged the URL you need to complete the authorization (see the next step, step 14).

Step 14. In your script editor, open the logs to get this authorization URL (go to View > Logs, or press Cmd + Enter):

authorization url required

Step 15. Copy this entire url and paste it into your browser, which should redirect to the GitHub authorization page:

Github authorize application

Step 16. Click “Authorize” and you’ll be prompted to enter your password:

Oauth application password github

You’ll then be redirected to the success page, meaning your Apps Script successfully connected to GitHub’s API using OAuth:

Oauth success callback page

Rock on!

Step 17. Now run your getUserRepos() function again and you should see all the repo information returned from the API:

GitHub API with Oauth from Google Sheets

Congratulations!!

You now have a working OAuth2 application that pulls data from the GitHub API into your Google Sheets environment.

Step 18. Go forth and build your GitHub/Sheets applications!

Now it’s up to you to see what you want to do with all that data at your fingertips.

For fun, I recreated the contribution graph that you see on your GitHub profile page in a Google Sheet, using this code and conditional formatting:

GitHub OAuth to Google sheet

4. Resources and further reading

The OAuth2 library for Google Apps Script.

Apps Script documentation on the Class UrlFetchApp, which is at the heart of this application.

GitHub documentation for their REST v3 API (what we connected to above).

Setting up OAuth2 access with Google Blogger and Apps Script, an example from Martin Hawksey.

Google documentation for the Blogger API, if you want to try this one.

I connected to the Blogger API, so that I write a blog in my Google Sheet and publish it to my Blogger site Datachops!

4 thoughts on “Show data from the GitHub API in Google Sheets, using Apps Script and Oauth”

  1. cool stuff Ben. I need to try this out sometime.

    One question: For me a big draw with OAuth would be that a normal user wouldn’t need to go into the Script editor. Could you open that URL (which you had in your Log window) automatically after, let’s say, a button click in the menu?

    1. Good question Julian! Yes, I agree this aspect could be improved from a user experience. It would be great to automatically open this URL perhaps, although I have a feeling that’s not possible with apps script, but I’ll look into it and see what’s possible. It’ll make a good follow up post. 🙂

    2. Great question. You’re not allowed to open the url directly, so probably the best workflow would be to show the link in the sidebar as a hyperlink, so the user can click straight onto it.

Leave a Reply

Your email address will not be published. Required fields are marked *