Search API Connector Documentation
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 GitHub data to your spreadsheet.
There are 2 ways to connect to the GitHub API:
- Preset "Connect" button (OAuth) premium
- Personal access token. Please check the appendix for detailed instructions to retrieve your token.
- Before You Begin
- Part 1: Connect to the GitHub API
- Part 2: Pull Data from GitHub into Sheets
- Part 3: Create a Custom Request
- Part 4: Handle Pagination
- Part 5: API Documentation
- Appendix: Connect with a Personal Access Token
Before You Begin
Click here to install the API Connector add-on from the Google Marketplace.
Part 1: Connect to the GitHub API
The easiest way to get started with the GitHub API is through API Connector’s built-in integration.
- In Sheets, open API Connector and create a new request (Extensions > API Connector > Open > Create request)
- Select GitHub from the drop-down list of applications
- Under Authorization, click Connect to GitHub
- You will see a modal asking you to approve the connection. Click Authorize.
- You’ll then be returned to your Google Sheet, and can verify that your GitHub connection is active.
Part 2: Pull Data from GitHub into Sheets
Now that we’re connected, let’s pull some data into Sheets.
- Under Endpoint, choose the data you'd like to retrieve. The
/user/reposendpoint will return all your GitHub repositories
- Optionally set any parameters, such as affiliation, before/after dates, and so on.
- Select a destination sheet, name your request, and click Run.
Part 3: Create a Custom Request
Alternatively, you can create a custom request instead of using API Connector’s built-in integration, using any of the endpoints and parameters shown in the API documentation. To create a custom request, add the complete URL into the request URL field and select GitHub from the OAuth menu (or enter an access token). Here's a sample request:
- Request URL:
https://api.github.com/search/repositories?q=google tag manager
Part 4: Handle Pagination
- By default, GitHub limits the number of records returned at once, usually to 30 or 100 at a time.
- 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:
next page URL
Next page path:
Run until: choose when to stop fetching data
Part 5: API Documentation
Official API documentation: https://docs.github.com/en/rest/reference
Appendix: Connect with a Personal Access Token
- 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.
- 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 choose an expiration date and enter a description in the Note field.
- Click the "Generate token" button at the bottom of the page:
- Your GitHub personal access token is now ready:
- One more step: Basic 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):
- Copy the output that appears in Developer Tools to your clipboard, excluding the quotation marks at the beginning and end of the string.
- Now, when you run your request, leave OAuth set to None and enter a key-value pair in the Headers section, where Key = Authorization and Value = Basic YOUR_ENCODED_TOKEN:
3 thoughts on “Import GitHub Data to Google Sheets”
It is possible to do a POST request that allow us to create an invitation for a new member or collaborator in the organization?
I took a quick look at the documentation, is this what you're looking for? https://developer.github.com/v3/orgs/members/#create-organization-invitation
Thanks you for this, Ana (-: