Automatically Updating GitHub Inventory in Google Sheets

by about JavaScript, Cloud, API in Technology

Create a GitHub dashboard that automatically updates into Google Sheets

If you’ve ever gotten lost in the sea of repositories of 1+ GitHub organizations, there’s a quick way to get an overview within minutes. Let’s take a look at how to achieve that for free using just Google Sheets and the GitHub API.

Show me the code!
If you don’t need a walk-through, you can just check out the commented code on GitHub instead.

There are several companies that let you create a GitHub dashboard or similar types of things - and they usually cost quite a lot of money. Even if you just need the data for your company, you might want to rethink using an external product and leverage existing tools. If you decide for an external tool for your company, chances are you’ll have to export this information into a spreadsheet in the end anyway - so why not do it right there?

This is what we’ll have at the end of this article:

Screenshot showing Google Sheets spreadsheet with filled rows for the column names: repository, description, forks, stars

This is basic data you can display for any organization on GitHub. If you run into API rate limit errors or are interested in private repositories, check out Step 4 in this article.

Step 1 - Create a Google Sheets document ▲ Back to top

Create a new Google Sheets document and then go to the menu item Tools > Script Editor. This will open a script window with various tabs on the left side. You should see the source code window. Let’s start from scratch and delete the initial empty function myFunction(). We’re looking at an empty source code window now. We’ll start by defining our function and the name of one organization we want to check.

function inventory() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var org = "containerssh"
}

You’ll want to replace containerssh with an organization of your choice here - you can pick any organization on GitHub. This article focuses on organizations and repositories. For different use cases such as users, you’ll need to use different parts of the GitHub API later in the code.

Step 2 - Code to Sheet ▲ Back to top

So, what’s our goal here? We could just navigate to https://api.github.com/orgs/containerssh/repos in our browser and there we have all the data for the first organization we’re looking at. Unfortunately, it’s JSON-formatted, so we can’t really read, aggregate, and make sense of it. That means, we need to call that address and fetch the data, then parse it so that it makes more sense to humans.For that, we create an array repos in which we will put the parsed response data from the API.

We’re using the repository name to create a string that will represent a hyperlink and text in the sheet cell later. Since we also want to see the results right away, we’ll add them to the current sheet in the spreadsheet and use the WRAP text formatting in case the descriptions are longer than the current column size.

function inventory() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var org = "containerssh"
    
    var repos = [];
    var response = UrlFetchApp.fetch("https://api.github.com/orgs/"+org+"/repos");
    
    var json = response.getContentText();
    var data = JSON.parse(json);
    
    data.forEach(function(item) {
        repos.push([
            "=hyperlink(\"https://github.com/"+org+"/"+ item["name"]+"\",\""+item["name"]+"\")",
            item["description"],
            item["forks_count"],
            item["stargazers_count"]]);
    });

    var sheet = ss.getActiveSheet();
    columns = repos[0].length;

    sheet.getRange(2,1,repos.length,columns).setValues(repos).setWrapStrategy(SpreadsheetApp.WrapStrategy.WRAP);
}

If your Run button to run the script is grayed out, you’ll need to save the project first. You can either press the Save project button represented by the disk icon or you can just press CTRL+S. Now the Run button should work. If you click it for the first time, Google will ask you for permission to run the script (This project requires your permission to access your data.). You’ll have to allow it in order to combine the script with the current spreadsheet you’re using. Our code so far bears the following result:

Screenshot showing Google Sheets spreadsheet with filled rows for one organization and a sheet called Sheet1

Step 3 - For Loops and Formatting ▲ Back to top

So far, we’re displaying the public repositories of one organization and soft-wrapping the cells in case the text is longer than the cell width. If that’s all you need, you’re good to go.

Right now, we need to click the Run button every time to get the public repositories of just one organization. What we want is to loop over a list of organizations and format it a little better.

In the next code step, we’re adding a comma-separated list of organizations instead of a single organization. Then we’re looping through the orgs array, checking whether there is already a sheet with the organization name and creating one if not.

function inventory() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var orgs = ["containerssh", "haveyoudebuggedit"]

    for (var i=0; i<orgs.length; i++) {
        if (!ss.getSheetByName(orgs[i])) {
            ss.insertSheet(i);
            ss.renameActiveSheet(orgs[i]);
        }
        // continued below

The API results are paginated, so we’ll need to loop through the pages in order to get a full list of repositories into our repos array. We achieve that with a finished trigger variable that will end our while loop when there is no more response data to load from the API, adding ?page= to the URL we’re calling and incrementing the page variable after having pushed all the results we want into repos.

Other results
If you don’t want to show repositories, descriptions, forks, and stars like we’re doing here, you can pick different responses from the GitHub API.
        var finished = false;
        var page = 1;
        var repos = [];
        
        while (finished == false) {
            var response = UrlFetchApp.fetch("https://api.github.com/orgs/"+orgs[i]+"/repos?page="+page);
        
            var json = response.getContentText();
            var data = JSON.parse(json);
        
            if (data.length == 0) {
                finished = true;
            }
        
            data.forEach(function(item) {
                repos.push([
                    "=hyperlink(\"https://github.com/"+ orgs[i]+"/"+item["name"]+"\",\""+item["name"]+"\")",
                    item["description"],
                    item["forks_count"],
                    item["stargazers_count"]]);
            });
        
            page++;
        }

We’re still in the for loop that iterates over the organizations array. Next up, we fill the sheet with the corresponding organization name. We make sure to clear it first as we want an always up-to-date list here.

For historic data, we could create a timestamp to the sheet names and not clear the sheets.
        var sheet = ss.getSheetByName(orgs[i]);
        sheet.clear();

Now comes the formatting overhaul. We want to add column descriptions by appending a row - in this case, we’re working with an empty sheet as we just cleared it, so it appears in the top row. In order to know how many columns we have, we check the length of any one entry of the repos array, which is also an array. We will need this columns variable in several places, for setting the font weight for only the columns we have in the top row (sheet.getRange(1,1,1,columns).setFontWeight("bold");) to actually filling up the sheet with results.

To make it a little bit prettier without excessive column widths for long descriptions (remember, we already set the long descriptions to wrap before), we change the column sizes for our repositories and descriptions. Forks and stars are just numbers, so we can leave them at the default size.

        columns = repos[0].length;
        
        sheet.appendRow(["Repository", "Description","Forks","Stars"])
        sheet.getRange(1,1,1,columns).setFontWeight("bold");
        sheet.getRange(1,1,1,columns).setBackground("yellow");
        sheet.setColumnWidth(1,200);
        sheet.setColumnWidth(2,500);

After formatting the columns, we might want to sort our data. For this, we implement a simple sorting function by the count of stars each repository has. The a[3] in this case is the fourth element of our array. If you’d want to sort by the number of forks, you’d pick a[2].

        repos.sort(function(a, b) {
          if (a[3] < b[3]) {
            return 1
          } else if (a[3] == b[3]) {
            return 0
          } else {
            return -1
          }
        })
        
        sheet.getRange(2,1,repos.length,columns).setValues(repos).setWrapStrategy(SpreadsheetApp.WrapStrategy.WRAP);
    }
}

And with that, we’re at the end of our for loop.

Step 4 - API Rate Limits and Private Repositories ▲ Back to top

You might have by now noticed two things: depending on the number of repositories in the organizations you’ve chosen, you’ve probably already exceeded the API rate limits (API rate limit exceeded for (...)) and you’re not seeing any private repositories in that list. We can fix that by authenticating against the GitHub API. If you’re looking at creating this type of dashboard for your company, you might want to ask your IT department for an OAuth token, but for this article, you can just create a personal access token on GitHub or use your existing token. Pick the token scopes you need. For our simple script, all we want is the repo scope in order to be able to display private repositories.

You can include the next piece of code somewhere before the for loop.

    var options = {
        "method" : "get",
        "headers" : {
            "authorization" : "token YOURTOKENHERE"
        }
    };

Replace the YOURTOKENHERE text with your own GitHub personal access token. Then change the line, where we’ve previously fetched the data by adding our new options parameter.

var response = UrlFetchApp.fetch("https://api.github.com/orgs/"+orgs[i]+"/repos?page="+page, options);
If you don’t want to show private repositories of an organization you have access to but still want to have higher API query rates, you could either uncheck the repo scope when creating the token or you’ll need to create an additional query for the "private": false, JSON tag and then filter that in your results.

Step 5 - Cleanup ▲ Back to top

You should now have an automatically updating Google Sheet using the GitHub organizations you want to list and if you added a personal access token that has access to these organizations' repositories, you will also see the private ones. There is one more thing we could take care of. If you started with an empty Google Sheet, you’ll notice that a Sheet1 sheet is still there and empty. We could have taken care of that in the for loop where we check if a sheet with the organization name already exists and create one if it doesn’t, but that would mean we would be performing a check on every iteration of the for loop. Instead, we opt to do this check once at the end of the script before we close the main inventory() function.

    if (ss.getSheetByName("Sheet1")) {
        if (ss.getSheetByName("Sheet1") == "") {
            ss.deleteActiveSheet();
        }
    }

In Google’s Apps Script interface, you’ll find a Triggers button right below the Editor button where you are typing your script. There you can select to let the script run at your chosen intervals to ensure that you always have up-to-date data. And that’s it - good luck and have fun playing around with even more functions, results, or improving this code by sending a pull request.