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.
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:
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:
2. On the left, click Developer settings:
3. In the left sidebar, click Personal access tokens:
4. Click Generate new token:
5. Give your token a descriptive name:
6. Select the scopes, or permissions, you’d like to grant this token:
7. Click Generate 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.
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/
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:
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:
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:
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:
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):
For endpoints that accept parameters, this documentation is where you would find them listed and explained, along with example inputs and responses.
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:
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:
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:
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: