With the Spreadsheet Tool, you can select which card properties you want to export from a given board and show the data in a Spreadsheet.
This helps for better data management: sort and arrange data easily; calculate numerical data, create charts and graphs, etc.
Moreover, with the tool, users can create or update the properties of multiple cards per Board.
Important: With the Spreadsheet tool users can import/export cards only.
The feature does not support import/export of Initiatives.
1. How to get and authorize your Kanbanize Spreadsheet Tool Copy?
To use the Kanbanize Spreadsheet Tool you need to copy it to your own Google account.
Open the link:
As a next step -> go to File -> select Make a copy -> click OK.
Enter the following values:
(see img.1 bellow)
- Your Domain name: https://yourdomainname.kanbanize.com
- API Key: generate an API key from My account
- Board ID number: ID of the board from which you wish to export the data
(You can see the Board ID on the Dashboard /upper right corner of each board/ or get it from the Board URL link.)
Then click on the “Extract data” button and you will be prompted to authorize with your Google account. When you select your account -> you will see the “This app isn’t verified” screen. Click on "Advanced" and then select -> Go to Kanbanize Spreadsheet Tool -> Allow.
You are seeing this screen because when you copy the Spreadsheet you are also copying the Apps Script. This means that you can do changes to the code which Google can’t verify.
After you click Go to Kanbanize Spreadsheet Tool you won’t be asked to log in again and you can directly access the copy file from your Google drive.
2. How to use the Kanbanize Spreadsheet Tool to Extract Data?
The Spreadsheet file contains two sheets: Form sheet and Data sheet.
- FORM sheet
In the Form sheet, you can select which card properties you want to export. There is a checkbox for every property.
- Don't delete any of the fields in the Form sheet.
- Select one of the available Date Formats from the dropdown menu (dd/mm/yyyy, mm/dd/yyyy, yyyy/mm/dd).
The format you select will be applied to all date fields (Deadline, Created at, Updated at, Custom fields of type: date)
Click on the Extract data button and you’ll be automatically redirected to the Data sheet where all results are displayed.
- DATA Sheet
In the Data sheet, you can filter out the data that you have exported -> use the filter button which is located at the top of every column and select a value to narrow down your results.
You can also sort the data alphabetically; by value or by the available conditions.
3. How to Update your Board from the Spreadsheet?
By editing cell values you can update cards in any of the Kanban Boards the ID of which is entered in the Spreadsheet. The editable properties are as follows: "Title", "Description", "Type", "Assignee", "Subtasks Details", "Color", "Priority", "Size", "Deadline", "External link", "Tags", "Blocked", "Blocked reason", "Column name", "Lane name", "Custom fields”, "Links"*.
If you try to edit any of the other properties, the changes won't be reflected.
Note: There is no way to update existing cards with new subtask via the spreadsheet tool. You can only edit the subtask name, assignee, status (finished/unfinished).
* Links: you can add multiple links via the spreadsheet tool. Just insert the IDs of the cards that you want to link in the respective column.
For example, for a card with ID 50, we are adding relative cards with ids: 30, 31, 48,99.
The allowed formats for the IDs are as follows: 43,31,48,99 or 43; 31; 48; 99 or each ID on a new line (select Alt+enter to insert a line break).
You can easily edit or delete a link by editing the cell or the line that contains the linked ids.
When you entering a new value, you should keep the formatting of the original text.
- Tags are separated by a comma
Changes in the file should be made one by one. Don’t forget to wait until the “All changes are saved in Drive” text appears in the upper side of the file.
Click the Update board button and all the changes you made will be reflected on the board.
4. How to Create a New Card?
You can create a new card /multiple cards by entering the card values in an empty row.
Just leave the ID field empty!
Please note that when importing empty/blank values, the system will get the default ones.
Click on the Update board button and the cards will be created on your Board.
- In case that you want to create a new card with subtasks, please keep the following format for the subtask: Title: Title of the subtask Assignee: Assignee name
Press ALT+ENTER to insert a line break and add more than one subtask.
- When you create new cards, you can also link them to existing cards in the system, just type the new card’s parent, children, etc.
5. How to Delete Card Details?
Except for editing the Cards, you can also delete Card details.
Just delete the value from the corresponding cell, wait until the “All changes are saved in Drive” text appears in the upper side of the file and click the Update board button.
Note: To prevent accidental deletion of the whole card, removing a row will not delete the card in Kanbanize. You could only clear specific fields from the row that would reflect on the card.
You cannot delete subtasks.
All buttons in the spreadsheet file are draggable. If you want to change their position -> click one time on the button with the right mouse key and then drag it by holding the left key.
6. Batch Updates
Using this spreadsheet, you can add or update up to 100 cards per batch.
This means that, for example, if you want to add 200 cards using this method, the tool will process 100 at a time and will keep the remaining 100 in a queue until it is ready to process them as well. Once done with the first 100 cards, it will add the next batch, which in this case is the remaining 100 cards.
Note: the only card property treated differently using this export/import approach is links.
When you add or update links per card as well as any other property this will count as two separate changes within the batch. This means that if, for example, you add 100 cards each having link entries, the tool will process them 50 in a batch.