Pulling data into Google Sheets from Phabricator

Phabricator is a suite of web-based software development collaboration tools, used by Wikimedia and many other organizations. One of these tools is a fairly customizable task tracker.

In a recent post I described the current Technical Debt backlog prioritization for Wikidata and Wikibase. Part of that process currently makes use of Google Sheets for a consolidated overview of a whole collection of tasks that are tracked in Phabricator. (I’m looking forward to trying out the new Github Issues table views soon, but I won’t be able to make use of this for Phabricator tasks)

Copying data between Phabricator and Google Sheets constantly would be a complete pain, especially as new tasks get added to the sheet every day and details of tasks can also change on Phabricator itself, such as titles, and statuses.

Which is where Google Apps Script for Google Sheets and the Phabricator API come in to automate this part of the process, at least in one direction.

If you don’t want to read any more of this post, you can find the source on the addshore/phabricator-google-apps-script Github repository.

All you need to do is paste this into a script for your Google Sheet. You can find this by heading to Tools -> Script Editor.

Google Apps Script for Google Sheets

Google Apps Script allows you to write Javascript that will run on a V8 engine, can interact with Google sheets and many other services to enhance the functionality.

At the simplest level you could define a simple function to return a string, and call it from a Google sheets cell. You can also pretty easily take parameters.

function sayFoo(){return "foo"}
function sayHello(name){return "Hello " + name}Code language: JavaScript (javascript)

To use these functions in a sheet, all you would need to do is something like =sayFoo() or =sayHello("Ben").

Phabricator API & Caching

You can make requests to APIs or other web endpoints using UrlFetchApp.

var response = UrlFetchApp.fetch(
    'https://' + PHAB_DOMAIN + '/api/maniphest.search',
    {
        'method' : 'post',
        'payload' : {
            'api.token': API_KEY,
            'constraints[ids][0]': taskId.substring(1),
            'attachments[columns][boards][columns]': true, 
        },
        'headers' : {
            'User-Agent': USER_AGENT
        }
    }
);Code language: JavaScript (javascript)

And you can easily cache responses, so as to avoid hammering APIs as cells and sheets get moved around and recalculated.

var cache = CacheService.getDocumentCache();
var content = cache.get( taskId )
if(!content) {
    // Fetch the content
    cache.put( taskId, content, 60 * 90 )
}Code language: JavaScript (javascript)

Sending data back to Phabricator

I did plan on sending data back to Phabricator at the push of a button. Buttons (or shapes) can easily be created and trigger any functions defined in a script. But I need to wait for an upstream bug to get fixed for my specific use case.
https://secure.phabricator.com/T13651