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)
- Personal access token. Please check the appendix for detailed instructions to retrieve your token.
Contents
- 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/repos
endpoint 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:
- Application:
Custom
- Method:
GET
- Request URL:
https://api.github.com/search/repositories?q=google tag manager
- OAuth:
GitHub
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. GitHub's documentation shows that next page URLs are provided in a field called "Link", so you would paginate through responses like this:
Pagination type:next page URL
Next page path:Link
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:
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 (-: