iSheets API version 2 - Common objects - iSheet item - common object - add/edit

Hi Andrew Parsons Because we are using OAuth2 Authorization Code flow, (which requires a user authorize an API application's use of their account), the actions in our system which are instigated from your API calls, will show as having been made by the user account that you are logged in as, during that Authorization step.

And Yes, having a service account to 'funnel' all your API calls through is an acceptable 'workaround'. We use something similar for some of our microservices, and I've employed it successfully in integrations which I have built.

There is a page on this hidden away here:
https://developerportal.thomsonreuters.com/authentication/documents/how-are-permissions-determined-for-the-api-calls

Edited for grammar and emphasis

Hi Imran Aziz,



We are sending data from our software to an iSheet; we programatically add and edit iSheet records using this common object format (albeit in JSON).



To preserve a record of both programatic and human-made changes, we wish to record which iSheet rows (interchangably called "records" or "items"?) were last modified by whom.



As of now, the system-generated "Created by" column seems to be randomly populated with a random user. Is this expected behavior? Is there logic to how the "Created by" column value is decided when creating iSheet items via the API?



What is the canonical approach for such an integration? Should we create a user called something like "Integration" or "External System" and make that user the iSheet record "Creator" via the API? I don't see a way to specify the user using this common object format; is this even possible?



Thank you in advance!



edit 1: grammar

Thanks Andrew Quinn for your quick reply, I can work with that.

Hi Matt Herry, point 3 in the wiki above details the answer you are looking for - the Get columns API endpoint. Sweet, you say? Ha! This endpoint is for admin users only, so depending on your workflow, if the user adding/updating the item isn't an admin, you'll need to implement additional logic to obtain these dynamically. You either:

• Use 'magic numbers' - which is far from ideal
• Create another (offline) service that caches and stores these ID's - again, janky
• Or create an external API that can retrieve these on behalf of the user process - supplying the iSheet ID, the API (admin token) can retrieve all column(s) info

If going down the 3rd route, you should also look at supplying the users ID so that the service can impersonate them, and check that user has access to the iSheet, before continuing on (as admin) and returning iSheet (detailed) column info - which will have the choice ID's, amongst other useful info.

Having access to detailed column info for non-admin's has been a long-time request - the status quo hasn't changed as far as I'm aware.

Hope this helps,

Andrew

When trying to update Choice fields using the put verb, the common object requires the choice ID. I cannot see a way of getting the choice ID from api calls, I managed to get the choice IDs by inspecting the front end with browser tools. Is there a better way to get the choice IDs?

We noticed that when using this api to edit a record in an existing iSheet, the call is not accepted if a filter is placed in the default view for that iSheet. The response is 'access denied'. Removing the filter gets everything working again, but we need the filter on the view to prevent unedited rows to be seen by the enduser. Is there any solution/ way around this?

Sorry Andrew thought I had answered this. I switched to JSON format and all now working. I suspect was something I was doing wrong on XML. That particular issue still being looked at by HighQ Support. However  this was a massive help for me right  now so thanks. Imran look forward to swagger documentation in time!

Andrew Quinn thank you for helping out. We do plan to implement Swagger documentation for all of our Collaborate API, and this should help in using the API endpoints. Will confirm that in todays webinar.

There are a few examples of JSON peppered about in the documentation, mainly in the newer endpoints. HighQ are planning on changes to the documentation which perhaps might address this. Tomorrows Webinar might shed some light on this.

To receive content from the endpoints, as JSON, set the accept header as 'application/json'. Likewise, when sending JSON content, set content-type header to the same, 'application/json'.

An example in your case would be:

{
  "data": {
    "item": [
      {
        "itemsequence": 1,
        "alerteroption": 0,
        "column": [
          {
            "attributecolumnid": "299",
            "rawdata": {
              "value": "My test value"
            }
          }
        ]
      }
    ]
  }
}

Thanks very much Andrew. I think I did have cdata in previous version and utf just fragment of me outputting the xml but will check. My preference would be Json. Am I missing something or is there sample formats for json for each method or do I just derive them from xml? Thanks again.

Paul Ryan, at first glance it's missing the start and end angle brackets from your CDATA section, i.e. <![CDATA[.....]]>. Also, is there a reason you are using utf-16 encoding? I don't know if the API supports this or not, as I don't use XML much these days. If you still are getting issues after amending the CDATA section, try switching to utf-8 encoding instead. Lastly, I'd reset your "itemsequence" from 7 (or whatever index it's come from) to 1, just as a matter of cleanliness

Hi - I'm trying to send data to endpoint to edit an item in an isheet - I am sending to /api/3/isheet/32/items/969. I have checked these tes values are all valid

I am sending XML below but js keep getting a 400 error Request resource or xml is not valid

![CDATA[My test value]]

Agree - thank you very much Andrew Quinn!

Andrew Quinn thank you very much for responding. Really appreciate your contribution to the developer community.

Andrew Quinn Thanks! I didn't set the content-type header to XML

Jeffrey Le, and you've correctly set the content-type header to "application/xml" and the accept to "application/xml"? You also should remove the itemid attribute on the item node, as this is superfluous for a new Sheet Item record.  Also, at sequence "3', column (#783) you are missing the opening quotes for the attributecolumnid, so the XML is not valid.

Jeffrey Le, can you provide the JSON/XML you are posting (replace any sensitive info with dummy data - that includes sheet/column ID's).

Imran Aziz I'm trying to add to an item to an iSheet and it's not working. I'm using the "GET-Single iSheet item" to get all the elements/format for one item. Then I'm using the "POST - Create iSheet item" and testing it on Postman but keeps on getting 415 error.

Andrew Quinn, Of course you are correct, column names (or enumerations) might be to volatile to use in this way. I am probably looking at iSheets as a form of database table too much, and transposing SQL like namebased functionality on them. I was problably also assuming that the name of a column was internal, thus separated from the column header title in a view, which seems an incorrect assumption on my side.  Still learnin:-)

Maarten Van.duijn (Imran Aziz), the problem with using the column name in any code-base is that site admins are able to change the column name at any point (e.g. to fix a typo, or to append additional info). We've already discussed this same issue in this blog post, where we discussed the possibility of adding a "Column Alias" property that doesn't change in the columns lifetime. This column alias would allow the auto-discovering you are seeking in the above (namecolumn="Risk"). The same principle applies to setting choices by name. Although it may seem improbable that someone will change choice labels, it will happen, and the first you'll know about it is when you get a support call asking why it's not working anymore :) It's one of the fundamental laws of software development - Users are the bugs, not the code ٩(^‿^)۶

[The internal ID for the improvement request is CFR-531]

Maarten Van.duijn Thanks for the suggestion. I think the best way would be to return both the ID and the value.

Andrew Quinn Thank you for your extensive answer! We will probably go the route of automating site specific configuration as much as possible for the time being. Imran Aziz, might it be a suggestion to provide (as an alternative next to the current resources) namebased access to columns and value based access to choices? You would not need applications to have access to the metadata of each specific isheet, while still providing a generic (useable from site to site) access for applications that know the generic structure of the iSheet?

So instead of sending

               

                   

                       

                             101

                       

                       

               

           

I could send

               

                   

                       

                             High

                       

                     

               

           

Andrew Quinn , Maarten Van.duijn we have the improvement listed in our backlog to get iSheet choice column options as a non-admin (CFR-547) but unfortunately we have not been able to pick that up till now. I have highlighted that to the iSheets team so they can prioritise this requirement accordingly.

CC: Andy Neill, Lisa Thompson​​​​​​​

Hi Maarten Van.duijn,

Firstly we need to find out from HighQ (Imran Aziz) if these requirements are in the pipeline, and what timescales we are looking at - as a solution will require significant effort.  If you were to embark on creating a solution, then my advice would be to opt for something that, in terms of effort, is related to the time that which HighQ will provide us with the functionality we need.

Short-term Solution: A JSON configuration file that the client uses, which was created by the Admin user that created the iSheet.  Doing this by hand is tedious, so creating a utility (that uses the API's) is easily achieved, e.g. create a Node app that the Admin can run from the command line to generate the JSON file, can be created in a few hours.

Long-term Solution: Create a (Middleware) service that a client can call to retrieve iSheet (column) details.  The client can provide the access token that is used by the service to call the Collaborate API's on it's behalf.  However, the service will also need to have details of an admin (token) account so that iSheet column details can be retrieved.  For example, the service runs as User A (with the provided token), and calls the "Get iSheet details" endpoint to verify that User A has access to the site/iSheet.  If successful then the service uses the internal Admin token to call the iSheet Admin API endpoints, to "fill in the blanks".

We've taken the long-term approach, however, we tied this into a bigger solution that solves more than just this particular Use Case.  Five months ago I wrote an article on this site about the usefulness of using GraphQL.  We've been using this to great effect for more than 6 months now.  An example of using this for your dilemma would be the following:

This example returns the iSheet column details for site (2235), sheet (126) on the IntegrationBeta instance, returning details only for Single Line, and Choice columns (as an example).  Making the GraphQL query is as simple as POSTing the JSON to the GraphQL service, supplying the client's user access token (although for the IntegrationBeta example access tokens are irrelevant).

Regards, Andrew

Additional info on "Get iSheet details" endpoint you can use for non-admin check:

Endpoint Url:

Success Response:

HTTP STATUS CODE: 200

{
  "isheet": [
    {
      "name": "Planet",
      "id": 126,
      "type": "normal"
    }
    ...
    ...
  ]
}

No Access Response:

HTTP STATUS CODE: 403

{
  "summary":"Access denied"
}

Andrew Quinn - Let me find out and I'll come back to you.

Hi Andrew Quinn,

You obviously have a lot of experience accessing iSheets through the API, and I would like to invite you to comment on the following usecase. It borders on the above question about ID based access to choices and columns.

We might often have to set op new collaboration sites, that will make use of iSheets that are structurally exactly the same, having the same fieldnames in the same order with the same datatypes. The new site has their own uniques ID's for columns and choice enumerations and such. So we need to analyze the new isheets and put the id's that we need somehow in a configuration file that differs from site to site. We are looking to create more generic solution, but preferably without the need to elevate a user to admin for any call to getcolumn to make the translation from column name to column id (or choice value to choice value id). Do you have any ideas or experience with this?

Hi Lisa Thompson, do you know if this extends to the suite of iSheet admin calls for a non-admin. We have real need for these as currently we are having to solve this using a site admin login, however, we lose the security walls around whether the current user can access the iSheet/Views/Columns - we have to rely on the calling application to know these security fences, which is not ideal. Thanks in advance, Andrew

Hi Maarten Van.duijn - we are just looking into this and hope to come back to you later today. Thank you.

Lisa Thompson Is there any new information regarding the choice columns? This is regarding the fact that it is a bit hard to find the identifiers that correspond with the values that are to be edited?

Andrew Quinn yes we have merged the fix in 4.4.x as well. Thank you very much for responding to Maarten Van.duijn and providing a suggestion.

Andrew Quinn Thanks again, and a good workaround. The issue hurts us a bit since our servicebusplatform is confused about the 'iteration' of column, containing different datastructures in each iteration, wich forces me to split the calls to this endpoint. Hopefully the regression will be solved quickly.

Hi Imran Aziz, Maarten is having the same issue with hyperlink columns that was fixed for 4.3.10. Can you confirm whether this fix was merged into the 4.4 .x release branch? Thanks in advance, Andrew.

Maarten, the only way I worked around this issue (until it was fix) was to 'get' the iSheet item, and copy hyperlink column values into my 'put' iSheet item, so that even if I wasn't wanting to change the column value, it included the current value in the edit iSheet payload.

Obviously once the fix was in place, I could remove that additional 'get' step.

Andrew

Andrew Quinn Yes! I certainly do have a hyperlink in my I-sheet, and not including it in any update seems to block all changes (without an error though). I am running Collaborate 4.4 (integrationbeta). (maybe 4.4 was forked before 4.3.10?)

Hi Maarten Van.duijn,

See comments in iSheet - Edit wiki. There was an issue with Hyperlink columns. Do you have any hyperlink fields in your iSheet? It was resolved in Collaborate 4.3.10. What version are you running?

No update actually occurs if; iSheet contains a Hyperlink column, and where there is a value in the record column, and the column wasn't provided in the update (PUT) request.

• Hyperlink field wasn't mandatory
• Create endpoint was fine
• Update (PUT) return code is 200 (Successful) but record unaffected
• Including Hyperlink value in Update (PUT) request results in record updated.
• Deleting Hyperlink field results in Update (PUT) updating record.

Basically, if you try updating an iSheet record without providing a hyperlink value (i.e. you're not wanting to update this column in the record), and the iSheet record already has a value, then no update occurs.

hi Imran Aziz, I would like to update the files isheet (in my own metadata files) , and in that proces only send in the field(s) that really have to change. This works for certain fields (like type link), but not for others fields (type string). The reply in both cases is a 200 OK, but in the latter case the change is not shown in the Isheet. Since no error is generated but the change is not accepted, it could also be that there is another reason for the change not showing?

Experimenting further, I get the feeling that this behaviour could be linked to the fact that my link-type field (the one that allways has to be included) is also the first of the extra user defined fields. Partial changes are shown as long as I include this first field......

Thanks for any anybody that could shed some light on this...

Hi Imran Aziz, I have a question regarding the use of adding/updating iSheet items, when the iSheet has "Enable modified date override" ticked.

When using the API's, the modified date is always being set to the current date/time even though in the POST/PUT I am specifically setting the modified date value.  What is the correct sequence to allow the modified date to be the custom date/time, e.g. Here's an example where the modified date of the iSheet is column ID #99933, the date format DD MMM YYY, and the modified date of the external record (#1111000003) is 15 Jun 2018 13:01?  This successfully creates the record, but the modified date is not 15 Jun 2018 13:01 but the date of insertion.

Obviously this is a requirement so that when syncing with the external source, we can know if external record #1111000003 has been modified since the last sync

{
   "data": {
      "item": [
         {
            "itemsequence": 1,
            "externalid": 1111000003,
            "alerteroption": 3,
            "column": [
               {
                  "attributecolumnid": 99934,
                  "rawdata": {
                     "value": "Test 1"
                  }
               },
               {
                  "attributecolumnid": 99946,
                  "rawdata": {
                     "choices": {
                        "choice": [
                           {
                              "id": 99994,
                              "label": "Yes"
                           }
                        ]
                     }
                  }
               },
               {
                  "attributecolumnid": 99951,
                  "rawdata": {
                     "choices": {
                        "choice": [
                           {
                              "id": 99928,
                              "label": "No"
                           }
                        ]
                     }
                  }
               },
               {
                  "attributecolumnid": 99933,
                  "rawdata": {
                     "date": "15 Jun 2018",
                     "time": "13:01"
                  }
               }
            ]
         }
      ]
   }
}

Hi Al Hounsell, the only way to get the ID's of the choice items is to make a prior call to the admin API endpoint Get Columns. This is a design flaw that I have raised with HighQ.

Andrew Quinn - Hi, I have sent your comment to our development team. Thank you.

Hi Al Hounsell, I have logged your comment with our developers and hope to come back to you as soon as we have an answer. Thank you.

Anyone know where to find the ID value for a choice. When I pull an iSheet, it just includes a "label" field for choice columns - no ID value indicated.

To help others in the future, when dealing with adding items in JSON. The request body should look something like the follow (I've also added optional External ID references) :

{
  "data": {
    "item":[
      {
        "itemsequence":1,
        "externalid":"1000",
        "alerteroption":3,
        "column": [
          {
            "attributecolumnid":100,
            "rawdata": {
              "value":"Item#1: Value for my Column #100"
            }
          },
          {
            "attributecolumnid":102,
            "rawdata": {
              "choices": {
                "choice":[
                  { "id":2001 }
                ]
              }
            }
          }
        ]
      },
      {
        "itemsequence":2,
        "externalid":"1001",
        "alerteroption":3,
        "column": [
          {
            "attributecolumnid":100,
            "rawdata": {
              "value":"Item#2: Value for my Column #100"
            }
          },
          {
            "attributecolumnid":101,
            "rawdata": {
              "value":"Item#2: Value for my Column #101"
            }
          }
        ]
      }
    ]
  }
}

Imran Aziz - this is the one.

Lisa Thompson.. I have reviewed this specification and it is up to date. Thank you.

Dan Hey, We can ask client to refer this specification again. make sure we will highlight what changes we have made ('columnid' to 'attributecolumnid' in column tag attribute). Thank you.

Vijay Vora - this is the page I have messaged you about.

Hi Dan Hey, we are looking into this and hope to respond to you as soon as we can. Thank you. Lisa.

In the choice type column, is there a way to edit the item no have no choices selected?