XLSX Item API

XLSX Item Export API

An XLSX Item Export API can be used to export Items from KaiNexus in the XLSX format.

Two Line OfieREQUIRED: Your organization must have the API Module enabled to perform the actions mentioned in this article. Reach out to your Customer Success Manager if you're interested in this module.


An Item Export API call will export the information for all Items in an Item List Card. This helps you take information stored in KaiNexus and export it to other software platforms. 

Customers use an Item Export API when they are planning to connect to a project management system for higher-capacity strategic projects. Any idea that needs advanced project management features gets exported, and when it's completed, the resolution will be updated in KaiNexus.

Two Line OfieWe strongly recommend using the JSON Item Export API instead of the XLSX Item Export API. To learn about the JSON Item Export API, check out this support article.

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. 

Two Line OfieMake sure your API Key has been granted the necessary permission to "view" any Items you want to export. For an API to be able to export all Items in KaiNexus, the View, View New, and View Private permissions need to be granted and set to "Everywhere" for all Workflows. Check out this support article for more information on each of these permissions.

Exporting Items

After authentication, you can make your first XLSX Item Export API call.

Two Line OfieTo learn about exporting Charts with an XLSX Chart Export API, check out this support article instead.


To export a specific Item List Card 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 Card's ID.
  • You can find an Item List Card's ID by hovering over the Card's header. A tooltip will appear that shows the Card's ID.
An Item List Card. The Card's tooltip is visible and an arrow is pointing to the Card's ID.

  • If the Card includes fewer than 20,000 Items, this call will retrieve a list of all its Items in XLSX format. 
  • If the Card includes more than 20,000 Items, this call will retrieve a list of its first 20,000 Items.

Helpful Tips

  1. In XLSX format, there are separate worksheets for each Template that will contain a list of all Items of that Template Type.
  2. Each Template will also have separate worksheets for its Items' Impacts, Impact Attributes, Impact Custom Schedules, Investments, Investment Custom Schedules, Investment Attributes, Milestones, and AdHoc Fields.

    If no Items of a given Template require one of these worksheets, that worksheet will be excluded from the XLSX document for that Template.

Mapping data between XSLX and JSON format

The tables below provide 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 is 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.

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 is named using the name of the field and each cell contains 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 is named using the name of each attribute and each cell contains a comma-delimited 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 the 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

customStatus

Refers to the unique name of the Item Status. 

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 are populated in a comma-delimited cell with the username and email address of the user.

In JSON, users are represented by an array of objects containing their ID, username, first and last name, and email. Multi-user fields 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 are populated in a comma-delimited cell with the name of the Location.

In JSON, locations are represented by an array of objects containing their ID and name.

Originating Location(s)

Column: Originating Location(s)

originatingLocations

Responsible Location(s)

Column: Responsible Location(s)

responsibleLocations

Created Date

Column: Created Date

createDate

 

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

 

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

 

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

 

Range Start

Range Start

resolutionX.impacts[x].rangeStart

 

Range End

Range End

resolutionX.impacts[x].rangeEnd

 

Unit Value/Rate

Unit Value/Rate

resolutionX.impacts[x].unitValueRate

 

Calculated Value

Calculated Value

resolutionX.impacts[x].calculatedValue

 

Classification

Classification

resolutionX.impacts[x].classification

One of: MINOR, MODERATE, SIGNIFICANT

Type

Column: Type

N/A

In XLSX, the Impact/Investment worksheet contains a type column (ACTUAL, TARGET, FORECAST) that indicates if the impact/investment corresponds to the Actual, Target, or Forecast resolution, respectively.

In JSON, these 3 different types of resolution are defined separately as resolutionActual, resolutionTarget, resolutionForecast and the impacts corresponding to each are nested below them.

Location

Column: Location

resolutionX.impacts[x].locations

In XLSX, locations are populated in a comma-delimited cell with the name of the Location.

In JSON, locations are represented by an array of objects containing their ID and name.

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

 

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 containing the year, month, and value.

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

 

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

In XLSX, users are populated in a comma-delimited cell with the username and email address of the user.

In JSON, users are represented by an array of objects containing their ID, username, first and last name, and email.