Blog

Read our news, ideas & product updates here.

Excel-synch
Best Practices Features Tips 

Step by step guide to synchronize your Excel Sheet with OpenProject

OpenProject is the best Excel alternative for project management. Importing your issues from Excel to OpenProject makes it easy to get started.

As we have written in our plog post OpenProject: The collaborative Excel alternative for project management there are quite a few limitations to use Excel for project management. Not being able to include other project collaboration functionalities is just one limitation to mention. Looking for an alternative to Excel often brings the question how to import the data from Excel to a new software.

Many companies that lack of a real project management system use Excel Sheets to track their tasks, requirements, bugs, risks or whatever needs to be tracked within a project. In order to easily get started with OpenProject for web-based, collaborative project management, there is an easy way to import all issues from an Excel list to OpenProject.

Setting-up the Excel list

1. In order to upload your tasks from an existing Excel list to OpenProject, you need to go to OpenProjectExcel on GitHub.

2. Click on the green Clone or download button.

3. Choose the option Download ZIP.

download-excel

4. Then, you need to extract the ZIP file and open the OpenProjectAPI Excel document.

open-excel

5. Next, within the Excel document you are asked to Enable Editing.

6. Enable Content to activate macros within your document.

enable-content-and-macros

7. Enter settings (URL and API-Token) and choose a project (and optional a work package query) which you want to synchronize your data with.

choose-project

URL: the URL of your OpenProject instance.
API-Token: can be generated within your OpenProject installation ->My Account -> Access token. Generate a new API token and copy & paste it to this form.
Project: this is the project identifier which can be found within the project you want to synchronize -> Project settings -> Information. It is also shown in the URL if you open a project.
Query ID (this field is optional): enter the ID of a work package query within a project with that you want to synchronize your Excel list.

8. Set as default: enable to remember the settings as default.

9. Accept to start the synchronization.

10. You need to select a minimum of columns to start with the synchronization. For column selection, click in the header and open the drop down which opens next to the regular drop down icon: Select the following column headers at a minimum:
Updatestatus
Lock Version
ID
Subject

Additionally, you can select as many other columns that should be synchronized – always by clicking in the header and selecting the drop down icon which appears next to the regular icon. You will have all fields available, incl. custom fields.

Important: make sure you have all fields that need to be synchronized from your Excel list also available in OpenProject. Some might be created beforehand as custom fields in OpenProject.
Attributes that do not exist in the selected project within OpenProject are NOT available in the headline. They cannot be selected in the header (via the drop-down). Neither can you just create a new column with an attribute that does not exist in OpenProject. This will generate an error message.

Copy and paste all data accordingly from your existing Excel list to this sheet.

select-column

11. Important: Ctrl + B will open the synchronization module to choose between the following options:

Ctrl-B

Download workpackages: intitially downloads all work packages from the selected project (and query).
Upload / update work packages: makes changes to the work packges, e.g. adding new work packages or changing any information. This option needs to be chosen in order to initially import and existing Excel list.
Show chosen project: Opens the settings to adapt URL, API key, project or query ID.

Congratulations! You can now synchronize your data between Excel and OpenProject.

Excel-OpenProject-synch

12. To synchronize hierarchies (Parent and child relations), insert the column Parent within the header of the Excel Sheet as described above (screen 10).

To add or edit a new parent-child relation, you have two possibilites:

A) You can add a child to a work package when you enter 4 empty spaces (press 4x Shift) before typing the subject of the child work package.

insert-children

Expert tip: it does not have to be empty spaces (default 4 empty spaces) to separate the next hierarchy. You can also take a „-“ and for the next hierarchy „—“. But the disadvantage is that the hyphen will also be displayed in the work package subject. Therefore we recommend the spaces, because they will not appear in OpenProject.

13. Pressing Ctrl + B and selecting Upload / update work packages will open a window to define a new character for setting a parent-child-relationship (default is 4 empty spaces).

Click OK when the screen for the Search characters (screen 13) appears. The IDs in the Parent column will then be automatically updated.

Search-character-parent

Pressing Cancel will ignore the Hierarchy – no matter what has been inserted. This is relevant for option B:

B)  you can edit the IDs in the parent column manually. If you have edited the IDs manually in the parent column, just click Cancel when the above Search character option (screen 13) appears. Then, the hierarchy will be updated according to the IDs in the column Parent.

14. The parent-child-relation will be displayed in OpenProject.

Parent-child-OpenProject

Hopefully, this will save you lots of time to import all your old Excel To-Do lists and get started with OpenProject for project management.

Have fun!

65 responses to “Step by step guide to synchronize your Excel Sheet with OpenProject

  1. Daniel Chou says:

    Will this work though Libreoffice as well?

    1. Birthe Lindenthal says:

      At the moment this plugin is developed only for MS Excel. But it would be great to have a Libreoffice plugin as well in the future.

  2. Dave says:

    Am I completely missing how to add new tasks or phases? I connected the Excel file to my blank project, worked my way through a few errors (wrong Estimated Time format, etc), but nothing ever shows up in my project after I click Upload. I thought maybe it wasn’t connected to the project or server correctly, but I can add a sample task and click Download and it goes right onto the bottom of that sheet. Then I can edit that particular task and edits show up when I upload. But none of the other lines show up. Something in the Updatestatus field I have to enter? Right now they are all blanks.

    1. Dave says:

      Disregard, I worked it out.

      1. Birthe Lindenthal says:

        Thanks, Dave. Let us know if you have any further questions.

  3. Krishna says:

    How to use query ID? Can you please share an example here?

    1. Birthe Lindenthal says:

      That is an Example URL from OP community Edition: https://community.openproject.com/projects/excel-sync/work_packages?query_id=2540
      As you can see, the URL leads to a query called “Open Bugs – Excel Sync”
      To get exactly this workpackages into excel you’d have to type 2540 (look into the URL) into the field “query ID” in the formular.

  4. Sebastián Betancourth says:

    Dave, how could you do this?

    1. Birthe Lindenthal says:

      I hope the tutorial makes it clear. We will try to add a video shortly to show the steps in detail.

  5. benny says:

    how to download work packages all status without only status that open?

    1. Birthe Lindenthal says:

      You have to create a query for this purpose. Simply create a query right in OpenProject, without any restrictions re. status. Then save this query and take the query ID from the URL, e.g. 2541 from https://community.openproject.com/projects/excel-sync/work_packages?query_id=2541

  6. George says:

    So i got the URL (local server) , Access Token and Project Identifier
    First it said it didn’t find any work-spaces in the project.
    When I continued the drop down boxes weren’t showing me any information.
    Does this still even work?
    I’m using office 2016

    1. Birthe Lindenthal says:

      Can you please provide more information? maybe here https://community.openproject.com/projects/excel-sync/work_packages
      ==> in general: if nothing could be found, its no surprise, there are no values in drop down boxes (these are depending on a selected project)

    2. Birthe Lindenthal says:

      Weren’t able to reproduce the issue, we tested it on a local installation and it works fine.

  7. George says:

    I did everything it said over here
    both on mircosoft excel 2016 and 2019
    and the rows do not load up ANY of the fields pointing to my openproject server, localhost:9999

    1. Birthe Lindenthal says:

      Can you please provide more information? Maybe here https://community.openproject.com/projects/excel-sync/work_packages

  8. J H says:

    On the MacOS, the cut & paste does not work. What is the correct way to do this or optional way to bypass it.

    1. Birthe Lindenthal says:

      The excel-file unfortunately doesnt works on Mac yet.

  9. Sam says:

    What was the update required?

    Dave, could you please post your solution, it will be helpful. Thanks.

    1. Birthe Lindenthal says:

      If the step by step guide does not make it clear so far, we will try to add a video for the different steps.

  10. Lakshmanan C says:

    Hi, I’m trying to do this using Microsoft Excel (Mac OS).
    Gives error as follows:

    Compile error:

    Can’t find project or library

    In OpenProjectAPIModul,
    objHTTP As New WinHttp.WinHttpRequest

    1. Birthe Lindenthal says:

      The excel-file unfortunately doesnt works on Mac yet.

  11. Matthew Clark says:

    How did you work it out, I have the same issue?

    1. Birthe Lindenthal says:

      We will definitely need a video to make the steps clear 😉

  12. jm says:

    Mine would not create work packages. I had to create blank ones manually that could be updated. I was unable to figure out why this was the case. I’m running 8.32 in a docker container.

    1. Birthe Lindenthal says:

      Can you please provide more information? maybe here https://community.openproject.com/projects/excel-sync/work_packages

  13. Carlos says:

    Every time I try to open the file I get this error and it doesn’t work:

    Compile error:
    The Code in this Project must be updated for use on 64-bit
    systems. Please review and update Declare statements and
    then mark them With the PtrSafe attribute.

    1. Birthe Lindenthal says:

      That has been fixed, in the meantime.

  14. Gabriel says:

    +1 for a Libreoffice plugin.

    I´m struggling to import around 200 tasks from Teamwork to Openproject.
    Any advice?

    Best regards.

  15. Gabriel says:

    Hi!

    We are using openproject at work, we host our instance in our servers.
    Is there a way to import/sync tasks between Libreoffice, Google spreadsheets, csv?

    Thanks 🙂

    1. Birthe Lindenthal says:

      Not yet, nor near future for Libreoffice, sorry. Happy to support any effort to pick this up from the Community. Google spreadsheet and csv can be converted to Excel and then synchronized.

  16. Benson says:

    HI,

    I am unable to create a new work package under a particular project using this tool. Updation of existing-working fine. Can you
    Please explain how we achieve this feature

    1. Birthe Lindenthal says:

      I assume a input-problem. if there would be an error re. the connection to your openproject instance, you would receive a message in the column “updatestatus”. (e.g.: if you were not allowed to create workpackages (due to permission settings of your) you would receive something like this: “workpackage not created, due to users permissions”)
      For creating new workpackages you have to take care of the following points:
      -make sure there is no empty row ==> background: the code stops at the first empty row, btw.: for reasons 🙂
      -make sure the ID column is empty ==> background: in general it works like this: if theres an ID in the column “ID”, the specific workpackage will be updatet, if its empty, a new one will be created and the ID will be set into the column afterwards ==> that is general concept, nothing else is important about this
      -make sure theres is no column “project” or similar (concerning users language settings) ==> background: there are two ways to define the target project: 1) user form (“global”), 2) column “project” (you can set different projects for each workpackage/ row). 2 is prioritized and that means, if the column “project” is filled, the excel will try to set this value as target project. So deleting the column simply reduces the probability of an error.

  17. Peter Kropp says:

    I tried to use this Excelsheet, but it does not fill the headers.
    It fills Updatestatus, Lock Version, ID, Subject, the rest is empty. No startdate, no enddate…
    I try to connect to a vmware-Version 10.2 of the Software to figure out whether it can be used for our project management.
    Any help is appreciated.

    1. Birthe Lindenthal says:

      You have to define by yourself, what attributes you want to see. after filling the form with a specific project, try the dropdown in the first row (next one right to the 4 columns/ attributes that are mandatory).

  18. Pekka says:

    I am using openproject in a local installation on UNIVENTION UCS . If i try to Download or Upgrade the Worksheet, I got Unknow Error ( mostly invalid http) . My Testaccount on OpenProject Cloud Hosting works fine.

    1. Birthe Lindenthal says:

      Weren’t able to reproduce the issue, we tested it on a local installation and it works fine.

  19. Ruben Enslin says:

    Hi! Could you please advise! Unable to get beyond step 10. When pressing “Accept”, the following error is encountered: “Unknown error (mostly invalid http). Abort.”
    Access token: 948d594a461a6f171f1c8904626228cc868bb291aa6f5da1cd0bb6673f498586
    Project name: 3. LSD Optimisation
    Importing using Excel 2013.
    SABS = South African Bureau of Standards

    1. Birthe Lindenthal says:

      Dear Ruben, does this error still exist or have you managed to work it out? Can you please file a bug report in our community forum: https://community.openproject.com/projects/openproject/forums/13

    2. Birthe Lindenthal says:

      You obviously picked the full project-name. Thats not right. you’ll need to fill in the projectidentifier, e.g.: “excel-sync” from the URL https://community.openproject.com/projects/excel-sync/work_packages

  20. Thomas says:

    whatever I do I only get the first 4 column headers:
    Updatestatus
    Lock Version
    ID
    Subject
    and I can not selected any other al other columns are empty

    1. Birthe Lindenthal says:

      To have another header in the sheet, you need to click a little bit next to the header dropdown. See number 10 screenshot.

  21. Harshal Vora says:

    Hello,
    I have managed to link the project with the excel, I have following observations/issues:
    1. Every time i download it create new rows instead of updating existing rows of line itmes
    2. If i create new items “tasks” in the excel it does not sync back, i keep on getting error “Run time error ’13’: Type Mismatch”
    3. Debug has highlighted following row ” For Each Key2 In ResponseJsonObj(“_embedded”)(“schema”)”
    4. what should one input in Lock version columns against new items

    Thanks in advance to the community

    1. Birthe Lindenthal says:

      1. that is how its intended. we dont want to delete rows without user interaction and that means: workpackages will start to be listed in the first empty row ==> background: you can download multiple projects/ querys and manipulate all the workpackages in one step
      2 +3. can you please provide more informations? maybe here https://community.openproject.com/projects/excel-sync/work_packages
      ==> in general: try as less columns/ attributes as possible. maybe your able to pin it down to a particular attribute (in this case, the specific type and maybe items ot this attribute would be of help)
      4. Lockversion is not a column for the users, by creating and changing it is automatically filled (it makes sure, that there are no concurrent inputs, e.g. you download a workpackage and change attributes. in the meantime someone else changes attributes of the same workpackages. so you are not allowed to simply overwrite this. beacause of lockversion you’ll get a message, that your workpackage is not up to date anymore and you have to redownload.

  22. Thorsten says:

    Is it possible to upload relationships (like “Requires / Required by”)?

    1. Birthe Lindenthal says:

      Not at the moment, but you download relations.

  23. Benoit says:

    Hello,
    I managed to get a project linked to my excel file, but after downloading the workpackages, i only get 4 colums.
    Additional headers in the columns are not available so i can’t sync them.
    I’m running the latest openproject and excel 365

    How can I solve that ?

    1. Birthe Lindenthal says:

      Der Benoit,

      can you please add a detailed bug report on our community forum so we will be able to have a look at this: https://community.openproject.com/projects/openproject/forums
      Thank you
      Birthe

    2. Birthe Lindenthal says:

      You have to define by yourself, what attributes you want to see. After filling the form with a specific project, try the dropdown in the first row. (next one right to the 4 columns/ attributes that are mandatory)

  24. Hi, There is a lost Link “… created beforehand as custom fields …” on this Page to “https://www.openproject.org/?page_id=3639” – can you please fix it

    1. Birthe Lindenthal says:

      Thanks a lot for spotting this. I have updated the link to the documentation for the custom fields.

  25. Kean says:

    HI Dave,
    I have the exactly same issue with yours, that new rows were not being upload to the server, May i know how you worked that out please?

    Thanks in advance.

    1. Birthe Lindenthal says:

      We will try to add a video soon to make the steps clear.

  26. Birthe Lindenthal says:

    For all German speaking users, here are the videos how to synchronize OpenProject with Excel:
    1. Hoch- und herunterladen: https://youtu.be/kL4u7ew0yHk
    2. Benutzerdefinierte Ansichten: https://youtu.be/Fs-luE7gSXA
    3. Hierarchien: https://youtu.be/SaCD8B9-4tM

  27. Brearne says:

    Hello,

    Initially I also had difficulties using this feature. I eventually got it working by using the following:

    1. Enter the URL, API key and project (name).

    2. If a workpackages popup appears (the one containing the ‘Download workpackages’, ‘Upload/update workpackages’ and ‘Show chosen project), close it. It seems that after clicking any of these options, data from new columns cannot be exchanged.

    3. A blank spreadsheet should appear. To add columns, click in a header. A second dropdown arrow should appear on the right -hand side. This is the dropdown menu that will allow for the selecting of columns.
    a. To enable a parent/child scenario, a parent column must be selected. It may be convenient to place the ‘Parent’ column where the ‘Subject’ column is. To do this, first, click on the column header. Then, click on the second arrow to the right to enable the dropdown menu. From this menu, select ‘parent’. Do the same with the next column to the right, only select ‘Subject’. Note that a ‘Subject’ column is necessary.

    4. Once all of the desire columns are added, press ctrl+b to show the workpackages popup. First click on the ‘Download workpackages’ option to download the data. Then again press ctrl+b to again show the workpackages popup. This time, click ‘Upload/update workpackages’ option. This will synchronise the data in the spreadsheet with the data in the portal. Note that the columns labelled ‘Updatestatus’, ‘Lock Version’ and ‘ID’ should autopopulate.

    I am posting this here in the hope that someone else may find this useful.

    Kind regards,

    Brearne

    1. Birthe Lindenthal says:

      Thanks a lot Brearne for sharing your experiences.

  28. Birthe Lindenthal says:

    And here are the English tutorials to synchronize OpenProject with Excel:
    1. Download and upload: https://youtu.be/ZPK-l-e-lKI
    2. Custom queries: https://youtu.be/86X334cg808
    3. Hierarchies: https://youtu.be/17yqO0uXE5k

  29. Stephan says:

    Salut, vielen Dank für Eure Arbeit. Ich habe Euer excel-synch werkzeug unter mac osx Catalina getestet. Hierbei kommt es in Excel zu einem Fehler beim kompilieren. Er lautet «Fehler beim Kompilieren.Das Projekt oder die Bibliothek ist nicht auffindbar. » Mit Verweis auf «objHTTP As New WinHttp.WinHttpRequest».

    Ich bin völlig ahnungslos mit VBA.
    Hat jemand den gleichen Fehelr bekommen, und hat eine Idee wie man den behebt?
    Danke!

    Beste Grüsse

    Stephan

    1. Birthe Lindenthal says:

      Hallo Stephan,
      aktuell wird macOS für den Excel Sync leider noch nicht unterstützt. Die Implementierung sollte nicht zu kompliziert sein – liegt offenbar am http request. Vll. kann sich das hier jemand einmal angucken.
      ***
      Currently macOS for Excel Sync is unfortunately not yet supported. The implementation should not be too complicated – it seems to be due to the http request. Maybe someone here can have a look at this.

  30. Ashraful says:

    Is it possible to Upload/Update the relations (Follows or Precedes) from the Excel file?

    1. Birthe Lindenthal says:

      Not at the moment, but you can download relations.

  31. Ashraful says:

    Thanks a lot for your response. Also can I download the Gantt chart view with the tasks?

    1. Birthe Lindenthal says:

      You can synchronize start and finish date and with this always generate the Gantt chart view.

  32. André Jeannin says:

    Hello,
    I’m new to OpenProject.
    I have the sync working fine: my Excel worksheet is populated with our OpenProject data.
    My problem is with the next step: how do I get a Gantt chart from here. There are many Excel templates for Gantt charts, including this one from Microsoft that I am tying to use: https:// templates.office.com/fr-fr/planificateur-de-projet-gantt-tm02887601
    But it appears that it it’s not possible to add a template to an existing workbook!
    I haven’t found any information about this step in the comment or in github.com/opf/OpenProjectExcel.
    Thanks for any reply.

    André

  33. david eade says:

    Hi, I have got the download / upload working a treat many thanks!

    The question I have is about “Relations” on the Excel there is a field thats shows relations in the download, but whenever I try to add relations to work packages that don’t yet have a relation it falls over.

    Is the functionality not available in the upload?
    Thanks
    Dave

Leave a Reply

Your email address will not be published. Required fields are marked *