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.
4. Then, you need to extract the ZIP file and open the OpenProjectAPI Excel document.
5. Next, within the Excel document you are asked to Enable Editing.
6. Enable Content to activate macros within your document.
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.
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.
11. Important: Ctrl + B will open the synchronization module to choose between the following options:
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.
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.
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.
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.
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!
Will this work though Libreoffice as well?
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.
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.
Disregard, I worked it out.
Thanks, Dave. Let us know if you have any further questions.
How to use query ID? Can you please share an example here?
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.
Dave, how could you do this?
I hope the tutorial makes it clear. We will try to add a video shortly to show the steps in detail.
how to download work packages all status without only status that open?
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
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
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)
Weren’t able to reproduce the issue, we tested it on a local installation and it works fine.
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
Can you please provide more information? Maybe here https://community.openproject.com/projects/excel-sync/work_packages
On the MacOS, the cut & paste does not work. What is the correct way to do this or optional way to bypass it.
The excel-file unfortunately doesnt works on Mac yet.
What was the update required?
Dave, could you please post your solution, it will be helpful. Thanks.
If the step by step guide does not make it clear so far, we will try to add a video for the different steps.
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
The excel-file unfortunately doesnt works on Mac yet.
How did you work it out, I have the same issue?
We will definitely need a video to make the steps clear 😉
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.
Can you please provide more information? maybe here https://community.openproject.com/projects/excel-sync/work_packages
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.
That has been fixed, in the meantime.
+1 for a Libreoffice plugin.
I´m struggling to import around 200 tasks from Teamwork to Openproject.
Any advice?
Best regards.
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 🙂
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.
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
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.
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.
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).
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.
Weren’t able to reproduce the issue, we tested it on a local installation and it works fine.
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
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
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
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
To have another header in the sheet, you need to click a little bit next to the header dropdown. See number 10 screenshot.
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. 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.
Is it possible to upload relationships (like “Requires / Required by”)?
Not at the moment, but you download relations.
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 ?
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
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)
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
Thanks a lot for spotting this. I have updated the link to the documentation for the custom fields.
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.
We will try to add a video soon to make the steps clear.
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
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
Thanks a lot Brearne for sharing your experiences.
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
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
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.
Is it possible to Upload/Update the relations (Follows or Precedes) from the Excel file?
Not at the moment, but you can download relations.
Thanks a lot for your response. Also can I download the Gantt chart view with the tasks?
You can synchronize start and finish date and with this always generate the Gantt chart view.