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 seemlessly 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.
 

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 to insert data into the right Spreadsheet
     
    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
     

    So, 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.
     

    Example:

    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.com/api/v3/work_packages“
     
    Your function:
    Function ImportJSON(url)
     
    Response:
    Response to the function ImportJSON(url)
     

    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.
    Set up triggers Google Spreadsheet ‚All triggers‘

    Drawings
    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!
     
    Author: Inga Mai
    Published: December 12, 2018
    Image rights: Fotolia, OpenProject
    Original blog post: https://www.openproject.org/de/connect-google-spreadsheet-openproject/

    Ähnliche Beiträge