Item Export API

XLSX Item Export API

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. 

Ofie Profile PicPro 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. 

Ofie Profile PicWarning: 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

  1. All dates use ISO 8601 format. Example: "2019-04-29T16:30:40.000+0000"
  2. 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 

}