How to connect your Google Spreadsheet with OpenProject

OpenProject offers a powerful API which allows you to exchange data between OpenProject and other applications. This allows you to seamlessly integrate OpenProject with other systems.

One of the most frequently used tools is Google Suite. Let’s take a look at how to connect a Google Spreadsheet to OpenProject to easily display data from OpenProject in your spreadsheets.

The script editor

First of all, let’s start with the basics: the script editor.

• Create a new Google Spreadsheet
• Open the editor by clicking on Tools > Script Editor
• Create new script for your Spreadsheet by clicking File > New > Script file, e.g. ‘ExampleScript.gs’
• Overwrite the placeholder content with your own function(s)

There are different options how you can use your functions in a Spreadsheet, e.g.:

1. Insert the name of your function directly into a cell of your Spreadsheet as below: “=ExampleFunction()” On Enter the function will be executed.
2. Or you can tell your function to insert data into the right Spreadsheet:

function ExampleFunction() {
    // Get the correct Spreadsheet by its name
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName('ExampleName');

    // Write data into a specific cell
    sheet.getRange('A1').setValue(value);
}

There are a lot more ways you can modify the cells, rows and columns of your Spreadsheet using the script editor. See also: https://developers.google.com/apps-script/reference/spreadsheet/sheet#top_of_page

How do we connect to OpenProject?

Basically you need a function that sends a GET request with a Basic Auth authentication and a request url to the matching OpenProject endpoint, which handles the received data, e.g. a JSON.

You will find a detailed overview of all OpenProject endpoints you can address, which data (formats) they will send back and how it is structured in the OpenProject API.

Tip: It’s always nice to test the requested urls first (and if it returns you the data you need). A useful, free available tool to do that is Postman.

Beispiel:

Let’s send a request to /api/v3/work_packages/{id} to get a JSON of all Work Packages. This could look like the following short example:

request url: https://community.openproject.org/api/v3/work_packages

Your function:

function ImportJSON(url) {
     var headers = {
         "Authorization" : "Basic <your_token>" // Insert a Basic Auth Token of an OpenProject account to get access to the API
     };
     var params = {
         "method": "GET",
         "headers": headers
     };
     var response = UrlFetchApp.fetch(url, params);
     var json = response.getContentText();
     var data = JSON.parse(json);
     return data;
     // OR return data[“attribute“];
     // OR return data.attribute;
} 

Response:

{
  "_type": "WorkPackageCollection",
  "total": 1767,
  "count": 20,
  "pageSize": 20,
  "offset": 1,
  "_embedded": {
    "elements": [
      {
        /* Work Packages */
      }
    ],
    "_links": {
      /* … */
    }
  }
}

Nice to know

Triggers

To keep your data up-to-date you can set up triggers by clicking Edit > All your triggers in the script editor . There you can decide how often and at which time a function should be executed automatically.

Google Spreadsheet ‘All triggers’

Drawings

Google Spreadsheet drawings editor

Besides that, there is another option to use your functions directly in the Spreadsheet, which will make it a lot easier for others to refresh data (without opening the script editor) . If you open a drawing by clicking Insert > Drawing in the Spreadsheet you can insert anything you like (such as a text or button). Saving it will insert it into your Spreadsheet. With a right click you can now assign one of your scripts to the drawing. And there you have a clickable trigger that will execute your code!