iSheets API version 2 - iSheet admin - API - Column CRUD operations - Get columns

Jason Jones I am afraid we did not manage to resolve all iSheet JSON issues in Collaborate 4.3.4, however, we should be able to resolve them in Collaborate 4.3.5. Collaborate 4.3.5 should become available in few weeks time.

@Imran Aziz thanks for the update. I was querying the timing bug when returning column results as raised by @Andrew Quinn which I see you are still working on.

@Andrew Quinn sorry for the delay in response on this performance issue. I am following this up with the team.

@Jason Jones the json issues with the API have been resolved in a point release. We r in the process of testing those fixes, we will update the sandbox instance as soon as the point release is ready.

Hi Imran Aziz are you able to provide me with an update on the resolution of this bug please? Thanks Jason

Hi Imran Aziz, I'm afraid there seems to be a performance issue with this API endpoint. Attached is a graph that depicts the problem being experienced.

• The x-axis represents iSheets tested, with the number of columns in each
• The y-axis represents the time taken, in seconds, averaged from several repetitions
• The red line shows the time taken for the List of iSheets endpoint (that returns all iSheets in the site, plus a basic list of column definitions for each)
• The blue line shows the time taken for the this endpoint (that returns a list of column definitions with more details, e.g. column specifics and conditions)
• The green represents the trend line for the blue data series

You can see that one of the iSheet's we have has 501 columns, and that this API endpoint takes almost 2.5 minutes to complete, compared to 3.8 seconds for the List of iSheets endpoint. I appreciate that this endpoint contains more detailed column information, however the List of iSheets endpoint also included several other iSheets (which included another 95 columns in total), and the previous iSheet, with166 columns, took (only!?!) 17 seconds.

I've also cloned the iSheet in question into our (Corrs) site in the beta site for the HighQ developers to use for analysis (sheet ID #121). I then ran the same process on this cloned iSheet in which this API endpoint took on average 200 seconds to complete.

Even though I still think 17 seconds is too long, everything pretty much follows the exponential trend one would expect to see. However, the last data point (501 columns) sees a radical departure from this trend line, indicating an issue.

Thanks in advance,

Andrew

Andrew Quinn the team is able to regenerate the issue and as you pointed out the issue is with JSON response, we are going to get this sorted and make sure that the JSON response is consistent to the XML response.

Andrew Quinn thanks for providing additional details, the team is sorting this out now, so the information you have provided will help debug and resolve this issue.

Thanks Imran Aziz, just for completeness I also want to highlight that this is not just happening with responses, but also requests. As an example below I have two requests to the Edit Column end point where I provide the JSON of the update request. The JSON contents are exactly the same between the requests except for the description has been changed from 'Test Update 3' to 'Test Update 4', and the requests are 30secs apart.

Request 1:

PUT https://********************/api/3/isheets/admin/1292/columns/12704 HTTP/1.1 Accept: application/json Auth-Type: OAUTH2 Authorization: Bearer ****************** Content-Type: application/json Content-Length: 427 host: ********************* Connection: close {"columnid":12704,"name":"Title","type":3,"section":2390,"description":"Test Update 3","addtodefaultview":"0","columnspecificdetail":{"defaultvalue":"","columnwidth":100,"mandatory":"0","allowsearch":"1","displaymethod":"DROPDOWN","singleormultileselectioninsearch":"MULTIPLE","choices":{"choice":[{"label":"Mr"},{"label":"Mrs"},{"label":"Miss"},{"label":"Ms"},{"label":"Dr"},{"label":"Sir"}]},"includeinalertpreferences":"0"}}

Response 1:

HTTP/1.1 200 200 Date: Thu, 22 Feb 2018 01:32:20 GMT Server: Apache Cache-Control: no-cache, no-store Pragma: no-cache X-XSS-Protection: 1; mode=block Strict-Transport-Security: max-age=31536000; includeSubDomains X-Content-Type-Options: nosniff X-Ratelimit-Limit: 500 X-Ratelimit-Remaining: 498 X-Ratelimit-Reset: 1519224117 X-Ratelimit-Relative-Reset: 577 Expires: Thu, 01 Jan 1970 00:00:00 GMT Content-Type: application/json;charset=utf-8 Content-Length: 226 Connection: close {"progressivekey":"hM0ZFSqmCj_1519263140375","siteid":null,"progressivekeystatus":"INPROGRESS","userid":null,"responsebody":null,"message":null,"messagecode":null,"integrationResponseDBO":null,"error":null,"downloadlink":null}

Request 2:

PUT https://********************/api/3/isheets/admin/1292/columns/12704 HTTP/1.1 Accept: application/json Auth-Type: OAUTH2 Authorization: Bearer ****************** Content-Type: application/json Content-Length: 427 host: ********************* Connection: close {"columnid":12704,"name":"Title","type":3,"section":2390,"description":"Test Update 4","addtodefaultview":"0","columnspecificdetail":{"defaultvalue":"","columnwidth":100,"mandatory":"0","allowsearch":"1","displaymethod":"DROPDOWN","singleormultileselectioninsearch":"MULTIPLE","choices":{"choice":[{"label":"Mr"},{"label":"Mrs"},{"label":"Miss"},{"label":"Ms"},{"label":"Dr"},{"label":"Sir"}]},"includeinalertpreferences":"0"}}

Response 2:

HTTP/1.1 400 400 Date: Thu, 22 Feb 2018 01:32:50 GMT Server: Apache Cache-Control: no-cache, no-store Pragma: no-cache X-XSS-Protection: 1; mode=block Strict-Transport-Security: max-age=31536000; includeSubDomains X-Content-Type-Options: nosniff X-Ratelimit-Limit: 500 X-Ratelimit-Remaining: 498 X-Ratelimit-Reset: 1519224140 X-Ratelimit-Relative-Reset: 570 Expires: Thu, 01 Jan 1970 00:00:00 GMT Content-Type: text/plain;charset=utf-8 Content-Length: 321 Connection: close Unrecognized field "columnspecificdetail" (Class com.os.api.dbo.isheet.ColumnDBO), not marked as ignorable at [Source: org.glassfish.jersey.message.internal.ReaderInterceptorExecutor$UnCloseableInputStream@21c3cb7e; line: 1, column: 135] (through reference chain: com.os.api.dbo.isheet.ColumnDBO["columnspecificdetail"])

So you can see that Response 1 correctly comes back with a Progressive Key response object (which subsequently completes successfully), whereas the second response details an error in the request - yet the two are identical (bar the description field contents changing). Again, this seems to point to two different paths being taken by the system, each handling JSON differently. Thanks again, Andrew

Andrew Quinn I have raised a bug report with the technical team to investigate and resolve this issue. Thanks for raising the inconsistency.

Hi Lisa Thompson, can you add to the investigation that when the response (JSON) includes "columns" that some column properties have mixed-case.

For an example, look at the second attachment "...GetColumns[columns].txt" and look at the columnSpecificDetail property, in the first attachment it correctly returns columnspecificdetail.

These differing responses can cause issues when:

1. Code is looking for a particular property, e.g. column[0].columnspecificdetails.allowsearch, as JSON is case-sensitive.
2. Using the response as a subsequent request to another API end-point, e.g. GetColumns, then use response to AddColumns to another (new) iSheet

Both these scenarios will fail when we receive mixed-case properties in the response. The second scenario is ironic, because the Collaborate server complains that the request contains an Unrecognized field "field name"...  which it supplied in the first place :)

Another thing to include is that when sending a request to the Add Column API endpoint if you send:

{

  "columns":[

    { column details },

    { column details },

    { column details },

    ...

  ]

}

It fails, unable to recognise "columns", returning the response:

Unrecognized field "columns" (Class com.os.api.dbo.isheet.ColumnsDBO), not marked as ignorable

 at [Source: org.glassfish.jersey.message.internal.ReaderInterceptorExecutor$UnCloseableInputStream@38f44a21; line: 1, column: 13] (through reference chain: com.os.api.dbo.isheet.ColumnsDBO["columns"])

Hi Andrew Quinn, I have shared and highlighted this separately so it can be investigated for you.  We hope to have a response as soon as possible. Thank you.

Hi Imran Aziz,

There seems to be an issue with this API endpoint when you request JSON results. Sometimes the JSON comes back with "column" array attribute, other times with "columns" array attribute. I've attached two files that capture the responses, to highlight this problem. Both are calls to the same iSheet endpoint, so it can't be any properties of the iSheet, or columns, that are causing the different responses. When the response comes back with "column" array, certain fields have values wrapped in a CDATA section format (which has no meaning in JSON), whereas when "columns" array is returned, they are not. Thanks, Andrew

CollaborateAPI-Admin-iSheet-GetColumns[column].txt

CollaborateAPI-Admin-iSheet-GetColumns[columns].txt

Hi Imran Aziz,

The return XML from this API endpoint incorrectly HTML encodes CDATA sections. This is an example response received from the endpoint:

<columns>
	<column>
		<columnid>842</columnid>
		<name>Created by</name>
		<type>6</type>
		<section>117</section>
		<description>&lt;![CDATA[]]&gt;</description>
		<addtodefaultview>1</addtodefaultview>
		<columnconditionstate>Display field</columnconditionstate>
		<columnconditions/>
		<columnspecificdetail>
			<columnwidth>150</columnwidth>
			<mandatory>0</mandatory>
			<allowsearch>1</allowsearch>
			<sheetlookup>SHEET_LOOKUP_CONTENT_ADMINISTRATORS</sheetlookup>
			<fielddisplay>Username</fielddisplay>
			<allowmultipleusers>0</allowmultipleusers>
		</columnspecificdetail>
	</column>
	<column>
		<columnid>843</columnid>
		<name>Created date</name>
		<type>5</type>
		<section>117</section>
		<description>&lt;![CDATA[]]&gt;</description>
		<addtodefaultview>1</addtodefaultview>
		<columnconditionstate>Display field</columnconditionstate>
		<columnconditions/>
		<columnspecificdetail>
			<defaultvalue>NONE</defaultvalue>
			<columnwidth>140</columnwidth>
			<mandatory>0</mandatory>
			<allowsearch>1</allowsearch>
			<formattype>DATE_TIME</formattype>
			<dateformat>&lt;![CDATA[dd MMM YYYY]]&gt;</dateformat>
			<defaultdateandtime>0</defaultdateandtime>
		</columnspecificdetail>
	</column>
</columns>

Can we log this as a bug. Thanks, Andrew