Search API Connector Documentation

Print

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 handle authentication, and then set up a request to pull in code repository details from Github to your spreadsheet.

CONTENTS

The Github API requires authentication for access, through either your own API token or an OAuth2 integration. This article primarily discusses connecting through API Connector’s paid OAuth2 Connection Manager, which handles authentication for you. If you’d prefer to manage the process manually for free, please skip to the Appendix for step by step instructions on connecting with an API token.

BEFORE YOU BEGIN

Click here to install the API Connector add-on from the Google Marketplace.

PART 1: CONNECT TO THE GITHUB API

We’ll first initiate the connection to the Github API.

  1. Open up Google Sheets and click Add-ons > API Connector > Manage Connections.
  2. In the list of available connections, find GitHub and click Connect.
    github-api-img1
  3. You will see a modal asking you to approve the connection. Click Authorize.
    github-api-img2
  4. You’ll then be returned to your Google Sheet, and can verify that your GitHub connection is active in the Connections screen.
    github-api-img3

PART 2: CREATE YOUR API REQUEST URL

We’ll first search for repositories matching a specific search term.

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

Putting it all together, we get a 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 our URL into API Connector and import some Github data into Google Sheets.

  1. Open up Google Sheets and click Add-ons > API Connector > Open.
  2. In the Create tab, enter the API URL we just created.
    github-api-img4
  3. We don’t need any headers, so just skip that section.
  4. Under OAuth2 Authentication, select GitHub from the drop-down menu.
    github-api-img5
  5. Create a new tab and click ‘Set current’ to use that tab as your data destination.
  6. Name your request and click Run. A moment later you’ll see Github data populate your Google Sheet:
    github-api-img6

PART 4: MORE EXAMPLE GITHUB API URLS

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

PART 5: HANDLE PAGINATION

  1. By default, Github limits the number of records returned at once, usually to 30 or 100 at a time.
    github-api-img12
  2. To get more, run requests using the ‘page’ and ‘per_page’ parameters, like this:
    https://api.github.com/search/code?q=addClass+user:mozilla&per_page=100&page=1
    https://api.github.com/search/code?q=addClass+user:mozilla&per_page=100&page=2
  3. As an alternative to changing these parameters manually, you can loop through and grab all your data automatically with pagination handling (paid feature). Github’s documentation shows that next page URLs are provided in a field called “Link”, so you would paginate through responses like this:
    API URL: enter your request URL as usual, making sure to include per_page=100
    Pagination type: next page URL
    Field name: Link
    Number of pages: enter the number of pages you’d like to fetch
    github-api-img13

APPENDIX: CONNECT WITH A PERSONAL ACCESS TOKEN

This section is provided as an alternative to the method described above. Instead of clicking GitHub in the Connections manager, you will retrieve your personal access token yourself. Once you have it, you need to apply Basic Authentication by encoding it to base 64 and including the encoded token as a header. Since you’re manually including an API key, leave OAuth2 authentication set to None.
  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.
    github-api-img7
  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.
    github-api-img8
  3. Click the “Generate token” button at the bottom of the page:
    github-api-img9
  4. Your GitHub personal access token is now ready:
    github-api-img10
  5. One more 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):
    github-api-img6

  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).
  7. Now, when you run your request, enter a key-value pair like this into the Headers section:
    github-api-img11
AuthorizationBasic YOUR_BASE64_ENCODED_TOKEN

3 thoughts on “Import GitHub Data to Google Sheets”

  1. It is possible to do a POST request that allow us to create an invitation for a new member or collaborator in the organization?

    Reply

Leave a Reply to Erika Cancel reply