iSheets API version 2 - Common objects - Column object

iSheets API

OVERVIEW

The column object consists of the following elements:

Common fields

<column>
    <columnid></columnid>
    <name></name> (String)
   <type></type> (Numeric) {Type of the column, column types are obtained from a call to the column types API}
<systemgeneratedtype></systemgeneratedtype> (String) 
<columntype>{ISHEET_CUSTOM / TASK_STANDARD}</columntype>
   <section></section> (Numeric) {ID of the section obtained from the sections call}
   <description><![CDATA[]]></description> (This is a multiline text)
   <addtodefaultview>{0/1}</addtodefaultview>
   <columnconditionstate>Display field / Hide field</columnconditionstate>
    <columnconditions>
       list of rows of column condition common object
    </columnconditions>
   <columnspecificdetail></columnspecificdetail> (you can check fields below based on column type)
</column>

Column specific details

1. Single line text
<columnspecificdetail>
   <defaultvalue></defaultvalue> (String)
   <columnwidth></columnwidth> (numeric value for the column width that applies to the interface)
   <mandatory>{0/1}</mandatory>
   <allowsearch>{0/1}</allowsearch>
   <maxchars></maxchars> (numeric value of no of maximum characters for a column)
   <allowfieldfromanotherisheet>{0/1}</allowfieldfromanotherisheet>
    <lookupglossarysheetid>{isheet id}</lookupglossarysheetid> (only applicable if allowfieldfromanotherisheet = 1)
    <lookupglossarysheetviewid>{isheet view id}</lookupglossarysheetviewid> (only applicable if allowfieldfromanotherisheet = 1)
</columnspecificdetail>

2. Multiple line text
<columnspecificdetail>
   <defaultvalue></defaultvalue> (String)
    <width></width> (numeric value for the column width that applies to the interface)
   <columnwidth></columnwidth> (numeric value for the column width that applies to the interface)
   <mandatory>{0/1}</mandatory>
   <allowsearch>{0/1}</allowsearch>
   <allowrichhtmltext>{0/1}</allowrichhtmltext>
   <numberoflines></numberoflines> (numeric value of no of line in text area)
   <allowfieldfromanotherisheet>{0/1}</allowfieldfromanotherisheet>
    <lookupglossarysheetid>{isheet id}</lookupglossarysheetid> (only applicable if allowfieldfromanotherisheet = 1)
    <lookupglossarysheetviewid>{isheet view id}</lookupglossarysheetviewid> (only applicable if allowfieldfromanotherisheet = 1)
</columnspecificdetail>

3. Choice
<columnspecificdetail>
   <defaultvalue></defaultvalue> (String but from entered choice value)
   <columnwidth></columnwidth> (numeric value for the column width that applies to the interface)
   <mandatory>{0/1}</mandatory>
   <allowsearch>{0/1}</allowsearch>
   <displaymethod>DROPDOWN/RADIO/CHECKBOX</displaymethod>
   <includeotheroption>{0/1}</includeotheroption>
   <singleormultileselectioninsearch>SINGLE/MULTIPLE</singleormultileselectioninsearch>
   <choices>
       <choice>
           <id></id> (this will be used while get list of columns or get column detail)
           <label></label> (String)
       </choice>
       <choice>
           <id></id>
           <label></label>
       </choice>
   </choices>
   <includeinalertpreferences>{0/1}</includeinalertpreferences>
</columnspecificdetail>

4. Number
<columnspecificdetail>
   <defaultvalue></defaultvalue> (Numeric but if max and min value is set then Default value must be less than or equal to maximum value.)
   <columnwidth></columnwidth> (numeric value for the column width that applies to the interface)
   <mandatory>{0/1}</mandatory>
   <allowsearch>{0/1}</allowsearch>
   <min></min> (Numeric) {min value of this number field}
   <max></max> (Numeric) {max value of this number field}
   <decimalplaces>0/1/2/3/4/5</decimalplaces> (value for decimal places)
   <showthousandseparators>{0/1}</showthousandseparators>
   <showaspercentage>{0/1}</showaspercentage>
</columnspecificdetail>

5. Date and time
<columnspecificdetail>
   <formattype>DATE_ONLY/DATE_TIME</formattype>
   <columnwidth></columnwidth> (numeric value for the column width that applies to the interface)
   <mandatory>{0/1}</mandatory>
   <allowsearch>{0/1}</allowsearch>
   <dateformat><![CDATA[]]></dateformat> (value from, DD MMM YYYY, DD/MM/YYYY, MM/DD/YYYY, DD.MM.YYYY)
   <defaultvalue>NONE/TODAY/CUSTOM</defaultvalue>
   <defaultdateandtime>28/07/2017 15:30</defaultdateandtime> (date and time should be in fix format of DD/MM/YYYY HH:MM)
</columnspecificdetail>

6. User lookup
<columnspecificdetail>
   <sheetlookup>SHEET_LOOKUP_ALL_SYSTEM_USERS/SHEET_LOOKUP_ALL_SITE_USERS/SHEET_LOOKUP_CONTENT_ADMINISTRATORS/SHEET_LOOKUP_SITE_ADMINISTRATORS</sheetlookup>
   <columnwidth></columnwidth> (numeric value for the column width that applies to the interface)
   <mandatory>{0/1}</mandatory>
   <allowsearch>{0/1}</allowsearch>
   <fielddisplay>Username/Email Address/Username, Organisation</fielddisplay>
   <allowmultipleusers>{0/1}</allowmultipleusers>
</columnspecificdetail>

7. Hyperlink
<columnspecificdetail>
   <allowsearch>{0/1}</allowsearch>
   <columnwidth></columnwidth> (numeric value for the column width that applies to the interface)
   <mandatory>{0/1}</mandatory>
</columnspecificdetail>

8. Image
<columnspecificdetail>
   <methodtype>IMAGE_AS_ATTACHMENT/IMAGE_AS_URL</methodtype>
   <columnwidth></columnwidth> (numeric value for the column width that applies to the interface)
   <mandatory>{0/1}</mandatory>
</columnspecificdetail>

9. Attachment
<columnspecificdetail>
<columnwidth></columnwidth> (numeric value for the column width that applies to the interface)
</columnspecificdetail>

10. File link
<columnspecificdetail>
<allowsearch>{0/1}</allowsearch>
<columnwidth></columnwidth> (numeric value for the column width that applies to the interface)
</columnspecificdetail>

11. Folder link
<columnspecificdetail>
<allowsearch>{0/1}</allowsearch>
<columnwidth></columnwidth> (numeric value for the column width that applies to the interface)
</columnspecificdetail>

12. iSheet link
<columnspecificdetail>
   <defaultvalue></defaultvalue> (String)
   <columnwidth></columnwidth> (numeric value for the column width that applies to the interface)
   <allowsearch>{0/1}</allowsearch>
   <sheetids></sheetids> (This field is mandatory. Mutiple isheets with comma seperator)
   <allowuserstorenamelinks>{0/1}</allowuserstorenamelinks>
</columnspecificdetail>

13. Lookup
<columnspecificdetail>
   <sheetid></sheetid> (isheet looup id)
   <columnids></columnids> (isheet lookup's columns with comma seperator)
   <viewid></viewid> (Numeric)
   <allowmultiplevalues>{0/1}</allowmultiplevalues>
   <restrictdelete>{0/1}</restrictdelete>
   <displaycolumnnameprefix>{0/1}</displaycolumnnameprefix>
</columnspecificdetail>

14. Join
<columnspecificdetail>
   <columnwidth></columnwidth> (numeric value for the column width that applies to the interface)
   <linkname></linkname> (String)
   <sheetid></sheetid> (Numeric)
   <viewid></viewid> (Numeric)
   <joinconditions>
       <joincondition>
           <currentsheetcolumnid></currentsheetcolumnid> (Numeric)
           <targetsheetcolumnid></targetsheetcolumnid> (Numeric)
       </joincondition>
       <joincondition>
           <currentsheetcolumnid></currentsheetcolumnid>
           <targetsheetcolumnid></targetsheetcolumnid>
       </joincondition>
   </joinconditions>
</columnspecificdetail>

15. Calculation
<columnspecificdetail>
   <columnwidth></columnwidth> (numeric value for the column width that applies to the interface)
   <formula><![CDATA[]]></formula> (For ex. (col_1-col_2)/(col_3*col_4)+col_5+10)
   <decimalplaces>0/1/2/3/4/5</decimalplaces> (value for decimal places)
   <showthousandseparators>{0/1}</showthousandseparators>
   <showaspercentage>{0/1}</showaspercentage>
   <allowsearch>{0/1}</allowsearch>
</columnspecificdetail>

16. Auto increment
<columnspecificdetail>
   <columnwidth></columnwidth> (numeric value for the column width that applies to the interface)
   <startvalue></startvalue> (Numeric)
   <prefix></prefix> (String)
   <postfix></postfix> (String)
   <minlength></minlength> (Numeric)
   <allowsearch>{0/1}</allowsearch>
</columnspecificdetail>

Note :
1. 'addtodefaultview' is only applicable while adding columns.
2. columntype : ISHEET_CUSTOM / TASK_STANDARD
         - This tag is only applicable if iSheet is 'Task custom metadata'
         - This tag is only applicable for CRUD API of iSheet view
         - This tag is supported from Collaborate 5.0 only.
         - If columntype = TASK_STANDARD then column id should be from the below possible values. (and 'type' tag will not be considered)

Column ID	Title (for task standard column)
2	Due date
3	List
4	Priority
5	Status
6	Assignee
7	Start date

Comments

19 Comments

Andrew Quinn Imran Aziz

Incase this helps - I imagine that addtodefaultview is not returned in the column object because it is an attribute used only during column creation and serves no real purpose after creation. If the default view was changed to another view after column creation this datapoint would be returning an incorrect value.This option is not visible to the user in the UI when editing a column either.

Andrew Quinn thank you for raising this issue and following up on it. As you have pointed out it seems that the "addtodefaultview" and "includeotheroption" has not been implemented in the Choice column, so I will raise this as a bug and request the engineering team to get it resolved ASAP.

CC: Andy Neill , Amaresh Pandey

UPDATE to above issue (Imran Aziz): Looking at the equivalent XML response, I can now see why the "addtodefaultview" and "includeotheroption" values are null in JSON - they are missing from the XML payload.

Example XML response:


    9999
    Some Type
    3
    1111
    <![CDATA[]]>
    Display field
    
    
        
        100
        1
        1
        DROPDOWN
        MULTIPLE
        
            
                99990
                <![CDATA[Type #1]]>
            
            
                99991
                <![CDATA[Type #2]]>
            
            
                99992
                <![CDATA[Type #3]]>
            
        
        1

Hi Imran Aziz, another issue with the iSheet - Get Columnsendpoint is that for Choice fields you can't tell if "Other option" is yes or no. The JSON response for that field is always null. I haven't tried it using an XML response(see update below), but certainly in JSON the column property never comes through.

"addtodefaultview" is the same too (for all column types)

Example JSON response received:

{
  "columnid": 9999,
  "name": "Some Type",
  "type": 3,
  "section": 1111,
  "description": "",
  "addtodefaultview": null,
  "columnconditionstate": "Display field",
  "columnconditions": {
    "row": []
  },
  "columnspecificdetail": {
    "defaultvalue": "",
    "columnwidth": 100,
    "mandatory": "1",
    "allowsearch": "1",
    "displaymethod": "DROPDOWN",
    "includeotheroption": null,
    "singleormultileselectioninsearch": "MULTIPLE",
    "choices": {
      "choice": [
        {
          "id": "99990",
          "label": "Type #1"
        },
        {
          "id": "99991",
          "label": "Type #2"
        },
        {
          "id": "99992",
          "label": "Type #3"
        }
      ]
    },
    "includeinalertpreferences": "1"
  }
}

Thanks in advance,

Andrew

David Corrin I am afraid the current API does not support this capability, we have raised an internal request to address this going forward.

Hi Imran Aziz , choice columns via the UI allow users to select a colour for the choice (e.g Yes is red, No is blue). Is it possible to set this colour via the API during column creation. It is not mentioned above.

Andrew Quinn - Hi, I have asked our developer team to look into this for you. Thank you for letting us know.

Hi Imran Aziz, the date format string returned, for DateTime columns, does not match the format strings mentioned above (5. Date and time). For example, setting a field to Date format: "DD/MM/YYYY" in Collaborate, when retrieving column data from the API endpoint, the format string is "dd/MM/yyyy". It looks like the format string returned is the pure Java format string, not the format strings mentioned above (or in Collaborate iSheet Admin pages). Created/Modified date columns are dd MMM YYYY.

Hi Ryan D'Souza - thank you again, this has now been updated.

Hi Ryan D'Souza - I have logged this with our developer for him to check and update. Thank you for letting us know about these.

has invalid closing tag, missing the slash.

Andrew Quinn thanks for the suggestion that would be an easier option to implement.

...alleviate the potentials for 403/500 errors, I would either need to call the existing endpoints sequentially (which would take forever to complete on large iSheets) or develop some batching method to query N endpoints over T time period which though possible distracts unnecessarily anyone trying to use the API.

To help others coming here with the same problems, where large no. of concurrent requests result in a percentage of 403/500 errors, if using NodeJS (or indeed client-side), there is an excellent npm package called p-ratelimit that can handle both rate limits and/or concurrencies for Promise-based calls/activities.

So if I had an async call (returning a Promise) that I wanted to limit into batches of no more than 25 at a time, then:

const limit = pRateLimit({ concurrency:25 });

function getColumnPermissions(conn, columnID) {
	return limit(() => ExampleAPI.Sheet.Columns.Permissions.get(conn, columnID))
		.then(result => {
			// if you want to do any post-processing
			// ...
			return result;
		});
}

// e.g. some array of column ID's
const sheetColumns = [100, 101, 102, 103, 104, 105, 106, 107, ..., 191];

// Use Promise.all to collate our list of column permissions
// using map to translate array of ID's to an array of Promises
// containing our resolved column permissions...
// ... pRateLimit will ensure we never make more than 25 concurrent 
// calls at any time even though we created 192 Promises all at once :)

Promise.all(sheetColumns.map(id => getColumnPermissions(conn, id)))
	.then(result => {
		// post-ops...
		result.forEach(permissions => {
			// do whatever...
		});
	});

NOTE: If using client-side, remember to poly-fill for Promises, and replace arrow functions with anon. functions for IE

There are other rate-limit packages out there so I encourage to find the best that suits your needs - I have no affiliation with the package mentioned above.

Imran Aziz Thanks for the update.

If it's any easier, an alternative could be to add a property to the column object that shows whether the column permissions are enabled or not?

e.g.

...

{0/1}

It wouldn't be as efficient as receiving all in one hit, but at least with column details you would know whether to make the additional call to the column permissions endpoint or not. I'd presume that the majority of iSheet forms out there have a small percentage of columns with permissions enabled, so even a 25%-75% split would save on superfluous calls. Plus there is also "system" fields that aren't applicable to column permissions.

Not sure how this property would behave when adding/updating a column record though - as we already have the endpoint to enable/disable column permissions.

I'm not a fan of this approach I have to say, but I thought I'd add it as an alternative in case the original request breaks the architectural pattern too much.

Andrew Quinn - Thank you for your first request regarding including a new property to the Column object. Imran Aziz has now added this to the backlog and the feature requests are in our JIRA system under CFR-478 and CFR-479.

Andrew Quinn thank you for your feedback. I will raise this request internally however it will take sometime to get this sorted as I am afraid there is no Collaborate interface where you can see the permissions for each column in one go and they are set up individually after breaking inheritance from the root iSheet.

Hi Imran Aziz, can I request an additional endpoint for Column permissions moving forward:

GET /api/{version}/isheets/admin/{isheetid}/columns/permissions

This is essentially providing an equivalent to the Get iSheet-column permissions endpoint, but for all columns in the iSheet.

The reason for this is so that we don't have to perform N x calls to the Get iSheet-column permissions endpoint for it to return (N - M) of the same iSheet permissions (assuming iSheet permissions is enabled), plus a smattering of 403's and 500's when dealing with large iSheets, when the majority of columns are defaulted to "inherit" - where N is the number of columns in the iSheet, and M is the number of columns with (non-inherited) permissions.

For my use-case, I have an iSheet with 124 columns, of which only 5 have permissions enabled. That's a lot of superfluous calls via REST API (which also eats substantially into the token limit).

Plus, for me to alleviate the potentials for 403/500 errors, I would either need to call the existing endpoints sequentially (which would take forever to complete on large iSheets) or develop some batching method to query N endpoints over T time period which though possible distracts unnecessarily anyone trying to use the API.

Thanks in advance, Andrew

Imran Aziz - is there an update for Andrew please? Thank you.

Hi Imran Aziz, can I request an enhancement to include a new property to the Column object:

{0/1}

This will allow, programmatically, to determine if a column is a system field. As an example, we cannot call iSheet-column permissions on a system field without a 403 response. System fields can be the common 4 fields; or if it's a Folder iSheet there's an additonal system field, Document iSheets have an additional two. It would be better if the meta-data of columns included this property so we don't have to hard-code these scenarios.

Thanks, Andrew

Last Updated: Nov 08,2024

iSheets API version 2 - Common objects - Column object

OVERVIEW

Common fields

Column specific details

Comments

19 Comments

Write a Review