Export Items and Item Lists in XSLSX format.
What can XLSX Item Export API do?
An Item Export API call will export the information for an individual Item or many Items in a List in XLSX format. This helps you retrieve information generated in KaiNexus and export it to other software platforms.
How does it work?
KaiNexus provides an endpoint where you can access Item information. To make a call, you will need an API Key and an API tool.
Pro Tip: We recommend using Postman for your API calls. It's easy to use and works well with KaiNexus API.
Authentication
API Keys are used for authenticating calls to KaiNexus API. Check out this article for instructions on how you can find your organization's API Keys and copy them to your clipboard.
If you are using Postman, select Basic Auth as the Authorization Type.
- Enter "api" as the username.
- Enter the API Key as the password.
Exporting Items
To export an individual Item in XLSX format, execute a GET request to this URL:
https://api.kainexus.com/api/public/v1/excel/item?id=XXXXX
- Replace "xxxxx" with the Item's ID. An Item's ID is located to the right of its title.
To export an Item List in XLSX format, execute a GET request to this URL:
https://api.kainexus.com/api/public/v1/excel/itemList?id=XXXXX
- Replace "xxxxx" with the Item List's ID. You can find an Item List's ID by navigating to the Board where the Item List exists as a Card. Hover over the Card header to see the ID.
Warning: There is a 20,000 Item limit per API call. If you want to export a List with more than 20,000 Items, refer to the pagination information in this article.
Pagination
For Item exports, two parameters can be specified to control paging:
- offset: The (zero-based) offset of the first Item in the collection to return. Offset is optional and defaults to 0.
- limit: The max number of Items to return. Limit is optional and defaults to 20,000, which is the maximum number of Items that can be exported in one call. It cannot exceed 20,000.
Example:
To retrieve the first 1000 Items in a List:
https://api.kainexus.com/api/public/v1/excel/itemList?id=xxxxx&offset=0&limit=1000
- Replace "xxxxx" with the Item List ID.
To retrieve the second 1000 Items in a List:
https://api.kainexus.com/api/public/v1/excel/itemList?id=xxxxx&offset=1000&limit=1000
- Replace "xxxxx" with the Item List ID.
To retrieve the third 1000 in a List:
https://api.kainexus.com/api/public/v1/excel/itemList?id=xxxxx&offset=2000&limit=1000
- Replace "xxxxx" with the Item List ID.
Things to Note
- All dates use ISO 8601 format. Example: "2019-04-29T16:30:40.000+0000"
- In XLSX, there are separate worksheets for Milestones, Impacts, Investments, and Custom Schedules. When you perform a JSON Item export, all this information is nested within the same object.
Mapping data between XSLX and JSON format
The tables below provides information on how data in XSLX format maps to JSON format.
Top Level Worksheet
The top level worksheet of your XLSX export contains basic information about the Items.
Top Level Worksheet |
XLSX Location |
JSON Location |
Additional Note |
Template Name |
Worksheet Name |
templateName |
In the XLSX workbook, each template type was broken out into a separate set of worksheets. In JSON format, the items are exported in a flat array called items. The templateName key indicates the name of the Template Type. |
Item ID |
Column: ID |
id |
|
Item Summary/Title/Name |
Column: Title |
summary |
|
Deep Link |
Column: Link |
link |
|
Configurable Text Fields (e.g. Description, Proposed Solution, Outcome) |
Column: Configured name of field |
Name: fields[x].name Value: fields[x].value |
In XLSX, the column was named using the name of the field and each cell contained the value of the field for each item. In JSON, configurable fields are placed into an array of fields. To obtain the value, search the array by the name of the field and then use value/numericValue/dateValue.
Note that fields that currently have a blank or null value may be excluded from the fields array. |
Configurable Number Fields |
Column: Configured name of field |
Name: fields[x].name Value: fields[x].numericValue |
|
Configurable Date Fields |
Column: Configured name of field |
Name: fields[x].name Value: fields[x].dateValue |
|
Configurable Attributes (e.g. Category, Priority) |
Column: Configured name of each configured attribute |
Name: attributes[x].name Values: attributes[x].values |
In XLSX, the column was named using the name of each attribute and each cell contained a comma separated list of the attribute values. In JSON, configurable attributes are placed into an array of attributes. To obtain the value(s), search the array by the name of the attribute and then use values array to obtain the values of the specific attributes for that item.
Note that attributes that currently have no configured values for a particular item may be excluded from the attributes array. |
Status |
Column: Status |
status |
One of: DRAFT, NEW, PLANNED, OVERDUE, ACTIVE, RES_SUBMITTED, COMPLETE, DEFERRED |
Parent ID |
Column: Parent ID |
parentId |
|
Parent Summary/Title/Name |
Column: Parent Name |
parentSummary |
To optimize performance, parentSummary will only be populated if the includeParentSummary=true parameter is included in the request. |
Private |
Column: Private |
private |
One of: true, false |
Responsible |
Column: Responsible |
responsible |
In XLSX, users were populated in a comma delimited cell with the username and email address of the user like this: arnold (Arnold Smith),greg (Greg Jacobson)
In JSON, users are represented by an object like this: { "id": 40, "username": "arnold", "firstName": "Arnold", "lastName": "Smith", "email": “arnold@companyXYZ.com” }
Multi user field are represented by an array of these user objects.
|
Assigner |
Column: Assigner |
assigner |
|
Author |
Column: Author |
authors |
|
Collaborator |
Column: Collaborator |
collaborators |
|
Follower |
Column: Follower |
followers |
|
Sponsor |
Column: Sponsor |
sponsors |
|
Facilitator |
Column: Facilitator |
facilitators |
|
Participant |
Column: Participant |
participants |
|
Participating Location(s) |
Column: Participating Location(s) |
participatingLocations |
In XLSX, locations were populated in a comma delimited cell with the name of the Location.
In JSON, locations are represented by an array of object like this: { "id": 23, "name": "Transportation" }
|
Originating Location(s) |
Column: Originating Location(s) |
originatingLocations |
|
Responsible Location(s) |
Column: Responsible Location(s) |
responsibleLocations |
|
Created Date |
Column: Created Date |
createDate |
JSON dates use ISO 8601 format. Example: "2019-04-29T16:30:40.000+0000" |
Activate Date |
Column: Activate Date |
startDate |
|
Start Date |
Column: Start Date |
startDate |
|
Assigned Date |
Column: Assigned Date |
assignDate |
|
Due Date |
Column: Due Date |
dueDate |
|
Review Date |
Column: Review Date |
reviewDate |
|
Resolution Submit Date |
Column: Resolution Submit Date |
resolutionSubmitDate |
|
Complete Date |
Column: Complete Date |
completeDate |
|
Last Updated Date |
Column: Last Updated |
lastUpdateDate |
|
Resulted In Change/Outcome |
Column: Resulted In Change |
resolutionActual.result |
One of: CHANGE , NO_CHANGE , THIRD
|
No Change Reason(s)/Third Option Reasons |
Column: Reason(s) |
resolutionActual.reasons |
|
Total Financial Impact - Actual |
Column: Financial Impact - Actual |
impactSummary.financialTotalActual |
To optimize performance, impactSummary will only be populated if the includeImpactSummary=true parameter is included in the request. |
Quality Impact - Actual |
Column: Quality Impact |
impactSummary.qualityCountActual |
|
Safety Impact - Actual |
Column: Safety Impact |
impactSummary.safetyCountActual |
|
Satisfaction Impact - Actual |
Column: Satisfaction Impact |
impactSummary.satisfactionCountActual |
|
Health Impact - Actual |
Column: Health Impact |
impactSummary.healthCountActual |
|
Environment Impact - Actual |
Column: Environment Impact |
impactSummary.environmentCountActual |
Impact/Investment Worksheet
The Impact/Investment worksheet will be included in your XLSX export if any Items have Impacts or Investments logged in the Items' Resolution.
Impact/Investment Worksheet |
XLSX Location |
JSON Location |
Additional Note |
Item ID |
Column: ID |
N/A |
The impacts are nested under the item object so there is no need to reference the Item ID. |
Impact/Investment ID |
Impact/Investment ID |
resolutionX.impacts[x].id |
|
Impact Type |
Impact Type |
resolutionX.impacts[x].typeName |
|
Title |
Title |
resolutionX.impacts[x].title |
|
Comment |
Comment |
resolutionX.impacts[x].comment |
|
Value Type |
Value Type |
resolutionX.impacts[x].valueType |
One of: ONE_TIME, RECURRING, RANGE, CUSTOM, UNKNOWN; |
User Entered Amount |
User Entered Amount |
resolutionX.impacts[x].amount |
Numeric value |
User Entered Amount Time Unit |
User Entered Amount Time Unit |
resolutionX.impacts[x].amountTimeUnit |
One of: SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, YEAR |
Currency |
Currency |
resolutionX.impacts[x].currencyCode |
|
Recurring Interval |
Recurring Interval |
resolutionX.impacts[x].recurringInterval |
One of: SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, YEAR |
Past Count |
Past Count |
resolutionX.impacts[x].pastCount |
|
Past Unit |
Past Unit |
resolutionX.impacts[x].pastUnit |
One of: SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, YEAR |
Future Count |
Future Count |
resolutionX.impacts[x].futureCount |
|
Future Unit |
Future Unit |
resolutionX.impacts[x].futureUnit |
One of: SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, YEAR |
Decreased Chance |
Decreased Chance |
resolutionX.impacts[x].decreasedChance |
One of: YES, NO, UNKNOWN |
Person/Product/ Service/Resource |
Person/Product/ Service/Resource |
resolutionX.impacts[x]. personProductServiceResource |
|
Number of People |
Number of People |
resolutionX.impacts[x].numberOfPeople |
Numeric value |
Range Start |
Range Start |
resolutionX.impacts[x].rangeStart |
ISO 8601 Date |
Range End |
Range End |
resolutionX.impacts[x].rangeEnd |
ISO 8601 Date |
Unit Value/Rate |
Unit Value/Rate |
resolutionX.impacts[x].unitValueRate |
Numeric value |
Calculated Value |
Calculated Value |
resolutionX.impacts[x].calculatedValue |
Numeric value |
Classification |
Classification |
resolutionX.impacts[x].classification |
One of: MINOR, MODERATE, SIGNIFICANT |
Type |
Column: Type |
N/A |
In Excel, the Impact/Investment worksheet contained a type column (ACTUAL, TARGET, FORECAST) that indicated if the impact/investment corresponded to the Actual, Target, or Forecast resolution, respectively.
In the JSON structure, these 3 different types of resolution are defined separately as resolutionActual, resolutionTarget, resolutionForecast and the impacts corresponding to each are nested below them:
"resolutionTarget": { "result": "CHANGE", "impacts": […] }, "resolutionForecast": { "impacts": […] }, "resolutionTarget": { "impacts": […] } |
Location |
Column: Location |
resolutionX.impacts[x].locations |
In XLSX, locations were populated in a comma delimited cell with the name of the Location.
In JSON, locations are represented by an array of objects like this: { "id": 23, "name": "Transportation" } |
Custom Impact Schedule Worksheet
The Custom Impact Schedule worksheet will be included in your XLSX export if any Items have Impacts logged as a Custom Schedule.
Custom Impact Schedule Worksheet |
XLSX Location |
JSON Location |
Additional Note |
Impact ID |
Column: Impact Id |
N/A |
The milestones are nested under the impact object so there is no need to reference the Impact ID. |
Year |
Column: Year |
resolutionX.impacts[x].monthlyValues[x].year |
In XLSX, each row corresponded to 1 year, with 12 columns for each month.
In JSON, the monthlyValues are an array of objects like this: { “year": 2019, "month": 1, value": 2500 } |
January-December |
|
resolutionX.impacts[x] . monthlyValues[x].month |
Milestone Worksheet
The Milestone worksheet will be included in your XLSX export if any Item contains Milestones.
Milestone Worksheet |
XLSX Location |
JSON Location |
Additional Note |
Item ID |
Column: ID |
N/A |
The milestones are nested under the item object so there is no need to reference the Item ID. |
Milestone Name |
Column: Name |
milestones[x].name |
|
Status |
Column: Status |
milestones[x].status |
One of: NOT_READY, READY, APPROVED, NOT_APPROVED, NOT_ACTIVATED |
Notes |
Column: Notes |
milestones[x].notes |
|
Last Updated By |
Column: Last Updated By |
milestones[x].lastUpdatedBy |
JSON object representing the user: { "id": 40, "username": "arnold", "firstName": "Arnold", "lastName": "Smith", "email": “ arnold@companyXYZ.com ” } |