An example on how to integrate Apollo and Google Documents using Apollo's API

I was really excited to hear about the Apollo API because it offered the prospect of pulling data from Apollo to suit some of the 'non-standard' reporting that we needed in our company. Here I will explain how we used Google Docs to access the API and pull time reporting information from Apollo. You can copy the example spreadsheet from here: Apollo API Example. Remember to make a copy of it in order to use it and change it.

Here's an example of the kind of output you can create: it shows monthly hours on each project since the beginning of January. What's great is that because it uses the API, it fetches the hours from Apollo automatically and is up to date whenever you open it. (Pro tip: Google Docs caches the data it fetches, and sometimes there's a delay of up to two hours before the data is updated: to get around this, a dummy parameter can be added to the API call)

(Project time dashboard fetched from Apollo - live!)

How it works

The sheet works by pulling the list of projects from Apollo, and then fetching monthly time records for each active project.

First of all, you need to 'seed' the spreadsheet with the list of projects: you can do that in the 'Projects' tab by clicking the 'Refresh Projects' button. This will fetch your projects and categories. Once you have them (along with their IDs), the sheet automatically fetches the time records (note that you may need to copy the functions in the Report sheet down if you've got many projects).

The Report sheet uses Google Script's functions to fetch the project time entries, and the Summary sheet uses a pivot table to group things nicely. Once you've got the pivot table you can create pretty charts of of all your project data like the one below.

Note that on the Projects sheet the 'month starts' column is used to allocate time entries to different months. For example, sometimes you might bill time for a project from the middle of a month. Most of the time, however, this will be set to 1 (the first day of the month).

An example will probably help: say you bill time for project X from the 20th of each month, so any time logged between 20th January to 19 February should go on the February invoice. In the projects sheet, just set the 'month starts' column to 20 for that project and in the Summary sheet any hours logged after the 20th January will be shown in the February column.

API calls and functions

The 'Refresh Projects' button calls the getProjects function (you can find the source code via the menu under Tools > Script editor... in your spreadsheet). The call, https://api.apollohq.com/beta/projects, pulls the lists of projects back in JSON format.

Here's some of the code used to fetch the data from Apollo:

function makeAPICall (url) {
  var jsonStringResponse = UrlFetchApp.fetch(apiURL + "/" + url, {
    headers: {
      "Authorization": "Basic "+Utilities.base64Encode(apiInfo)     }
   });
  var data1 = jsonStringResponse.getContentText();
  var data2 = JSON.parse(data1);
  return data2;
}

In the code snippet above:

• apiInfo contains your API key and apiURL contains the base url for the API (both of these can be set on the config tab of the Google spreadsheet).
• data2 contains the result of the request

To avoid the risk of having too many ImportXML or ImportData calls in the spreadsheet (Google limits you to 50), I created the getTimeForMonth function which each cell in the Summary sheet uses to fetch the time entries for a given month and project. This uses https://api.apollohq.com/beta/report/timeEntries?from=X&to=X&project_id=X (where X is replaced with the appropriate value for that month and project). You can see how the code makes that API call using the makeAPICall function:

var data = makeAPICall("report/timeEntries?from="
+ fromDate 
+ "&to=" 
+ toDate 
+ "&project_id=" 
+ projectID);

To insert the actual time logged on a project for a given month into a cell simply use getTimeForMonth. For example, entering the function below in a cell fetches the number of seconds logged to project ID 1234 in February 2012:

=getTimeForMonth( 1234, "2012/2/1",1)

The rest of the sheet uses functions written by Google to manipulate data on the sheets.

Final thoughts

Using the API has certainly saved us time and given us a quick way of seeing how our business is running. Hopefully you can see from the functions used how to make the calls. If you're at all familiar with using APIs you'll find the Apollo API documentation pretty good: finding the right calls is very straightforward.

I'd love to hear from anyone that uses the sheet (here it is again) so drop me a line if you do make use of it or have any questions - you can connect with me on Twitter or G+ here: Jas Sahota.

---

Jas Sahota is a director at Vitis PR, a PR company based in the UK where he writes on public relations, online marketing and (occasionally) tools based on Google Docs