Import GitHub Data to Google Sheets

In this guide, we’ll walk through how to pull data from the Github API directly into Google Sheets, using the API Connector add-on for Google Sheets. We’ll first get a personal access token (aka API key) from Github, and then set up a request to pull in code repository details from Github to your spreadsheet.

• Part 1: Get your Github Personal Access Token
• Part 2: Create your API Request URL
• Part 3: Pull Github API data into Sheets
• Part 4: Notes and Expansions

PART 1: GET YOUR GITHUB PERSONAL ACCESS TOKEN

❓ If you are using API Connector’s new OAuth2 connections manager (paid feature), you can skip this entire section. Instead of getting an access token and converting it to base 64, just click GitHub on the Connections menu and go directly to part 2 of this tutorial. You don’t need to enter an Authorization header as Authorization will be handled through the OAuth2 connection.

1. 1. Log in to Github and navigate to https://github.com/settings/tokens. Click either “Generate a personal access token” or the “Generate new token” button.
      
   2. This will take you to a screen to set up scope details for your token. For our purposes here we don’t need any additional scopes, so just enter a description into the required Note field and scroll to the bottom.
      
   3. Click the “Generate token” button at the bottom of the page:
      
   4. Your GitHub personal access token is now ready:
      
   5. One last step: Basic Access Authentication requires us to encode our authentication info to base 64. You can do this opening up Developer Tools in your browser (F12 on Windows/Linux or option + ⌘ + J on OSX). In the console, type in the following and click enter:

      encodedData = "Basic " + window.btoa('YOUR_USERNAME+YOUR_PERSONAL_ACCESS_TOKEN')

      Substitute your own Github username and personal access token values from above.  It should look like this (don’t forget the plus sign in the middle):
      

   6. Copy the output that appears in Developer Tools to your clipboard, excluding the quotation marks at the beginning and end of the string. (If you have any problems with the above, check the article on Encoding Credentials to Base 64 (Basic Authentication) for alternative methods).

1. Congrats, you’re done! You’re now ready to access the Github API, and can start pulling Github data into Google Sheets.

PART 2: CREATE YOUR API REQUEST URL

We’re going to follow the Github API documentation to search for repositories matching a specific search term.

• API root: https://api.github.com
• Endpoint: /search/repositories
• Parameters: q=SEARCH_TERM
  Parameters Example: q=google tag manager

Putting it all together, we get the full API Request URL:

https://api.github.com/search/repositories?q=google tag manager

PART 3: PULL GITHUB API DATA INTO SHEETS

We can now enter all our values into API Connector and start importing Github data into Google Sheets.

1. 1. Open up Google Sheets and click Add-ons > API Connector > Create New API Request.
   2. In the Create Request interface, enter the Request URL we just created
      

1. Under Headers, enter a key-value pair like this (again, you don’t need to enter this header if you’re connecting through the OAuth2 connections manager):
   

   Authorization

   Basic YOUR_BASE64_ENCODED_TOKEN

   You should be able to paste in the value you got in step 6 above.

   It should look like this:
   

2. Create a new tab. You can call it whatever you like, but here we’ll call it ‘Github’. While still in that tab, click ‘Set’ to use that tab as your data destination.
3. Name your request. Again we’ll call it ‘Github’
4. Click Run and a moment later you’ll see Github data populate the Github tab in your Google Sheet:
   

PART 4: GITHUB API + SHEETS NOTES AND EXPANSIONS

You can experiment with endpoints as described in the documentation to see other types of Github data. If you just want to jump in and get an idea, you can play around with the URLs you enter in the API URL path field. Try the following (one at a time):
https://api.github.com/user/repos
https://api.github.com/users/freeCodeCamp
https://api.github.com/users/freeCodeCamp/gists
https://api.github.com/users/freeCodeCamp/repos
https://api.github.com/orgs/freeCodeCamp
https://api.github.com/orgs/freeCodeCamp/repos
http://api.github.com/repos/freeCodeCamp/freeCodeCamp/contributors