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 a Google Sheets 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:

// add custom menu
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom GitHub Menu')
      .addItem('Get User Repos','getUserRepos')
      .addItem('Get rate quota','getGitHubRateLimit')
      .addToUi();
}


/***************************************/
// Get User Repos
function getUserRepos() {
   var service = getGithubService_();
   
   if (service.hasAccess()) {
     Logger.log("App has access.");
     var api = "https://api.github.com/users/benlcollins";  // example
     
     var headers = {
       "Authorization": "Bearer " + getGithubService_().getAccessToken(),
       "Accept": "application/vnd.github.v3+json"
     };
     
     var options = {
       "headers": headers,
       "method" : "GET",
       "muteHttpExceptions": true
     };
     
     var response = UrlFetchApp.fetch(api, options);
     
     var json = JSON.parse(response.getContentText());
     
     Logger.log(json); // example
   }
   else {
     Logger.log("App has no access yet.");
     
     // open this url to gain authorization from github
     var authorizationUrl = service.getAuthorizationUrl();
     Logger.log("Open the following URL and re-run the script: %s",
         authorizationUrl);
   }
 }

/***************************************/
// Get Rate limit
function getGitHubRateLimit() {
  // set up the service
  var service = getGithubService_();
  
  if (service.hasAccess()) {
    Logger.log("App has access.");
    
    var api = "https://api.github.com/rate_limit";
    
    var headers = {
      "Authorization": "Bearer " + getGithubService_().getAccessToken(),
      "Accept": "application/vnd.github.v3+json"
    };
    
    var options = {
      "headers": headers,
      "method" : "GET",
      "muteHttpExceptions": true
    };
    
    var response = UrlFetchApp.fetch(api, options);
    
    var json = JSON.parse(response.getContentText());
    var responseCode = response.getResponseCode();
    
    Logger.log(responseCode);
    
    Logger.log("You have " + json.rate.remaining + " requests left this hour.");
    
  }
  else {
    Logger.log("App has no access yet.");
    
    // open this url to gain authorization from github
    var authorizationUrl = service.getAuthorizationUrl();
    Logger.log("Open the following URL and re-run the script: %s",
        authorizationUrl);
  }
}

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:

var CLIENT_ID = '';
var CLIENT_SECRET = '';

// configure the service
function getGithubService_() {
  return OAuth2.createService('GitHub')
    .setAuthorizationBaseUrl('https://github.com/login/oauth/authorize')
    .setTokenUrl('https://github.com/login/oauth/access_token')
    .setClientId(CLIENT_ID)
    .setClientSecret(CLIENT_SECRET)
    .setCallbackFunction('authCallback')
    .setPropertyStore(PropertiesService.getUserProperties())
    .setScope('user'); 
}

// Logs the redict URI to register
// can also get this from File > Project Properties
function logRedirectUri() {
  var service = getGithubService_();
  Logger.log(service.getRedirectUri());
}


// handle the callback
function authCallback(request) {
  var githubService = getGithubService_();
  var isAuthorized = githubService.handleCallback(request);
  if (isAuthorized) {
    return HtmlService.createHtmlOutput('Success! You can close this tab.');
  } else {
    return HtmlService.createHtmlOutput('Denied. You can close this tab');
  }
}

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 set up 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!

32 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.

  2. Ben this is an amazing post. Very very helpful. One question, is I suspect either the API I am working with won’t play nicely with Google App Script or that I’m off with my .setTokenUrl endpoint. Can you describe the difference between this and the setBaseUrl endpoint to help me decipher the correct endpoints in the API I am trying to connect? I am using the Classy API https://developers.classy.org/overview/welcome

    Thank you in advance!

    1. Hey Angel,

      The setBaseUrl redirects users to request access with the web service, and then exchanges the setTokenUrl for an access token. I’ve scoured the docs/online sources but haven’t found this url listed anywhere. Try it without the line setTokenUrl (remove or comment out with //) and see if you can get the token from the response data anyway:

      var serviceData = getTestService_();
      var access_token = serviceData.getAccessToken();

      where you change the name of the service 'getTestService_' to match yours in your script.

      When you request access to this url 'https://api.classy.org/oauth2/auth', the response contains the access token you need, so we should be able to access it this way.

      Hope that helps! Let me know if it works for you.

      Cheers,
      Ben

      1. Thank you Ben for the reply – very helpful. However, I’m still running into an error. I’ve done some searching and still can’t seem to remedy the issue. I’ve included a link to my app script in the website URL on this comment (I’ve removed the ID and secret). The error I am showing is :
        “Access not granted or expired (line 352, file “Service”, project “OAuth2″)”.
        Also note, in Classy, I’ve set my OAuth2 Redirect URI to my Redirect URL with my script ID inserted.

        1. Hey Angel,

          I took a quick look at your code and ran some tests, but I just keep hitting this message in my browser:

          Google Apps Script Token URL required

          Not sure how you get past this point without having the Token URL, or whether it’s possible without it. So I would try posting your question in the apps script forum about what to do when you don’t have a token URL for the OAuth library, and second, drop a line to Classy and see if they can help.

          Hope that helps!

          Ben

          1. Ben, I wanted to follow up in case anyone runs into the same issue in the future. The Classy API uses something called client_credentials OAuth flow, which is not currently compatible with the apps-script-oauth2 library. I’m not an API expert and this wasn’t immediately apparent, but the author sent me the link below about OAuth2 flows, which help clarified the different authentication flows. https://tools.ietf.org/html/rfc6749#section-4.4

      1. Ben, I checked the link, it lists repositories being starred by an user,

        what if I want to check how many stars a respository has?

        thanks

  3. Great post, Ben. Very informative.
    One question. Can I use OAuth2 to get reports from Amazon MWS? I wonder because MWS needs three types of credentials (AWS Access Key ID, Secret Key, and Seller ID) but it seems we modify the Oauth.gs with only client ID and client Secret. I appreciate any hints.

    Thanks,
    Comerun

    1. Hey Jek,

      I’m pretty sure this is just a pagination issue you’re encountering. The Github API endpoints are paginated meaning they’ll send you back blocks of data (“the fixed page size is 30 items”).

      So you’ll need to put the API call into its own function that you can call inside a loop, keeping track of your page count and stop when you get the last packet of data back (it’ll have fewer or zero items in it).

      Hope that helps!

      Ben

  4. Great post Ben, thanks.

    I would like to make a next step.
    I am looking to import data from repo’s from an organisation’s page into Google Sheet. I would like to edit your script for that.

    Any suggested reading you can provide for editing your script so I can import the right data?

    Secondly, the script imports the data, however, it is not shown in the Google Sheet. How would I go about editing the script to add it to certain fields in Google Sheets?

    Again, any recommended reading is much appreciated!

    Thanks!
    Timo

  5. Hi Ben,

    Thanks for this post. I am right now kind of stuck at step#16. I get a “Sorry, unable to open file at this time” error when my script is called back after authorisation. My call back function doesn’t even get invoked.

    I am trying to connect to Pocket APIs. Any pointers on what could be going wrong?

    Thanks,
    Mukesh

  6. Ben,
    Thanks for this Tutorial. I’ve followed it step by step and made a few modifications to work with another service. It seems that the oauth set works properly, however, when passing the token back through the callback i get the below error.

    Error: Error retrieving token: {“id”:”401″,”name”:”unauthorized”,”detail”:”Unauthorized”} (line 541, file “Service”)

    Are you able to help point me in the direction? I’m finding very little to support this online. Thank you in advance!

  7. Ben,
    I’ve got the basic script working. I’m trying to expand to get a list of all the repos in my organisation.
    I’ve changed the api url to /orgs/[org name]/repos.
    I’ve added “type”: “all” to the headers.
    I’ve changed the scope in getGithubService to ‘repo’.
    Still, I only get public repos, not the private ones.
    What am I missing?
    Thanks.

    1. Hey Ronald, I suggest you looking at my reply in the question below of this one. Perhaps your issue get solved by creating a new spreadsheet from scratch and adding the repo scope. In my case, I’ve updated the scope but the spreadsheet wasn’t asking me for re-authorization…

      If you are not sure which scopes to add, I recommend adding these ones:
      .setScope(‘user repo admin:org admin:org_hook’);

  8. Ben this is a great post.
    I’m able to get info about my repositories but I’m trying to fetch data from a private org (from which I’m a contributor) and it’s not working.
    I transferred the ownership of the Github OAuth application to the org and add “repos” to OAuth scope but it didn’t work.

    I’m not sure what else I should try. I keep receiving a 404 when performing a GET /repos/:owner/:repo and 200 [] when performing /orgs/:org/repos.

    So, it looks like somehow I don’t have permission to request for the private repos. Am i missing something?

  9. Hi Ben,
    Thanks for an excellent article. I’m just starting out with using Apps Script. I’m a little stuck with trying to get authentication with aws-cognito going in Apps Script.
    One specific question is: How did you get the project key of Oauth2? And do you know if I can get the same for aws-cognito?

  10. Hi Ben
    Thanks for the time and care you put into this. I’m new to Apps Script and OAuth and Github so this covered all those bases. You’re a great communicator!

Leave a Reply

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