Github API data in Google Sheets

Many programmers rely on GitHub to host their code and create a portfolio that employers can view. Developers also use Github to collaborate together on code.

In this tutorial, you’ll learn how to connect the Github REST API to Google Sheets in 5 steps, using the Apipheny Google Sheets add-on:

  1. Install the Apipheny Add-on
  2. Get a Github access token
  3. Get the Github endpoints’ URL
  4. Enter the Github API endpoint into Apipheny
  5. Run the Github API Endpoint Request

Step 1.) Install & Open the Apipheny Add-on in Google Sheets

To bring data from the Github API to Google Sheets, you’ll first need to install and open the Apipheny Google Sheets add-on.

You can install Apipheny by opening the following link in your browser and then clicking the Install button on the page: https://gsuite.google.com/marketplace/app/apipheny/966163326746

After you installed Apipheny, open a Google Sheet and click on the Add-Ons menu item at the top. When the menu opens, you should see the Apipheny add-on. Click on Apipheny > Import API to accept the permissions and open the Apipheny sidebar:

Open Apipheny
Open Apipheny

Step 2.) Get a Github Access Token

In order to access most Github REST API endpoints, you will need an access token. To obtain an access token, follow these instructions:

1. Log in to Github. Then, in the upper-right corner, click your profile photo and then click Settings:

Click Settings in Github
Click Settings in Github

2. On the left, click Developer settings:

Developer Settings
Developer Settings

3. In the left sidebar, click Personal access tokens:

Personal access tokens
Personal access tokens

4. Click Generate new token:

Generate new token
Generate new Github access token

5. Give your token a descriptive name:

Name your access token
Name your access token

6. Select the scopes, or permissions, you’d like to grant this token:

Github access token permissions
Github access token permissions
Github access token permissions
Github access token permissions


7. Click Generate token:

Generate token
Generate Github access token

8. Click the small button next to the token, to copy it to your clipboard. Then paste it somewhere safe because you will need it in the next step.

For security reasons, after you navigate off the page, you will not be able to see the token again.

Copy access token somewhere safe
Copy Github access token somewhere safe

Step 3.) Get the Github endpoints’ URLs

In this section I show you how to browse the Github API documentation to create an API URL that retrieves the information you need. If you already know your URL, or you want to use the same example URL as us, just skip to step 4.

First, open the Github API documentation page: https://developer.github.com/v3/

Github API Documentation
Github API Documentation

The menu on the right contains a list of the available API groups, linking to individual APIs or to API categories, with each API page linking to a list of its endpoints.

Clicking on an API group opens the main page for that API or API category and expands a list of associated APIs.

For instance, the Projects group opens the main Projects API page and expands the list of three other related APIs: the Project Cards API, the Project Collaborators API and the ProjectColumns API:

Github Projects API
Github Projects API

Clicking on an associated API in the expanded list opens that API’s page.

As an example, this is the page for the Project Cards API:

Github Project Cards API
Github Project Cards API

Another example of an API category is Activity. It contains the Events API, the Atom Feeds API, the Notifications API, Repository Starring and RepositoryWatching APIs:

Github Activity APIs
Github Activity APIs

Clicking on one of the APIs on this page opens its dedicated documentation, containing the list of available endpoints.

For instance, clicking the Events API reveals its description and a summary listing of its endpoints:

One of the available endpoints is List events for the authenticated user:

https://developer.github.com/v3/activity/events/#list-events-for-the-authenticated-user

It’s documentation contains a short description, the HTTP method used (GET) and the link you should append to the root API url for querying this endpoint (/users/:username/events/public):

List events for authenticated users
List events for authenticated users

For endpoints that accept parameters, this documentation is where you would find them listed and explained, along with example inputs and responses.

Step 4.) Add your URL for a Github REST API endpoint into the Apipheny Add-on

Now, open your Google Sheet back-up, with the Apipheny sidebar open (screenshot below). With the Import Tab open, enter these details into the add-on:

Method: At the top of the Apipheny sidebar, select the HTTP method (GET or POST) required by your API endpoint. For this example, we are using the GET method.

API URL: In Step 3 I explained how you can find the Github REST API endpoint that you need. Now copy the complete URL into the Apipheny add-on, where it says API URL Path (JSON / CSV), followed by any GET parameters required for your query.

For this example, we are using the User API endpoint, to get the current user’s details:

https://api.github.com/user

Headers: In the Headers section, add two rows with the following keys and values:

Key:
Accept
Value:
application/vnd.github.v3+json
Key:
Authorization
Value:
token your-access-token

your-access-token is the value you generated previously, in Step 2. There should be a space between “token” and “your-access-token”

The Accept and Authorization keys and values are required, so you should add them in to the add-on like this:

Github API URL and Headers entered into the Apipheny add-on
Github API URL and Headers entered into the Apipheny add-on

Step 5.) Run the Github API Request

Click the Run button at the bottom of the Apipheny add-on and then wait for the Github API data to be retrieved and added to your Google spreadsheet:

Github API data in Google Sheets
Github API data in Google Sheets

After making a successful request to Github’s API, try querying a different endpoint, or try using one of the more advance features in the Apipheny add-on, such as:

  • Save and schedule your API request
  • Make a POST request to the Github API
  • Use the custom =APIPHENY() function to call the API request in your sheet
  • Create an API request by referencing the value of a cell in the API URL, headers, or POST body (with three curly braces eg. {{{Sheet1!A1}}})

Related articles:

Related Posts

Leave a Comment