• State Verified Answer
  • +1 person also asked this people also asked this
  • Replies 3 replies
  • Answers 1 answer
  • Subscribers 4 subscribers
  • Views 918 views
  • Users 0 members are here
Options
Related

Assistance Required with PUT API Request to Update iSheet Column in HighQ

Bryon Brown

I am currently working on integrating our system with HighQ by automating the process of updating iSheet columns via the HighQ API. My goal is to update the "attributecolumnid" 1691 with values extracted from a JSON file containing student information.

Here is a sample of the JSON structure I am working with:

{
  "HighQ ID": "HQ01",
  "Student Street Address": "3539 Kingsland Avenue",
  "Student City": "Bronx",
  "Student State": "",
  "Student Zip Code": "10469",
  "Student Home District": "14"
}

I intend to use the following PUT API request to update the "Student Home District" value into the iSheet:

PUT {{URI}}/api/18/isheet/3/items/1691

Below is the body of my PUT request:

{
  "type": "Http",
  "inputs": {
    "uri": "{{URI}}/api/18/isheet/3/items/1691",
    "method": "PUT",
    "headers": {
      "Accept": "application/json",
      "Authorization": "Bearer @{body('Get_secret')?['value']}"
    },
    "body": {
      "data": {
        "item": [
          {
            "column": [
              {
                "attributecolumnid": "1691",
                "string": {
                  "rawdata": "@{variables('School District Goat')}"
                }
              }
            ]
          }
        ]
      }
    }
  },
  "runAfter": {
    "Append_to_array_variable": [
      "SUCCEEDED"
    ]
  },
  "runtimeConfiguration": {
    "contentTransfer": {
      "transferMode": "Chunked"
    }
  }
}

Unfortunately, my attempts have been met with this response:

"403 Validation failed | Forbidden"

This leads me to believe there might be an issue related to the structure of my request as I validated my permission with another PUT API that I was able to successfully run for a choice HighQ column.  This particular column is a single-line text.

Could you provide guidance or an example of how to successfully use the PUT API for updating a single column in HighQ? Any insights into resolving the 403 error would also be greatly appreciated.

Parents
  • 0

    I couldn't edit my inital post so I wanted to provide the update body code used in my API PUT request above:

    {
  "data": {
    "item": [
      {
        "column": [
          {
            "attributecolumnid": "1691",
            "string": {
              "rawdata": "@{item()?[variables('School District Goat')]?['value']}"
            }
          }
        ]
      }
    ]
  }
}

  • +1 in reply to Bryon Brown

    Hi Bryon,

    Not sure why you have "string" as an attribute of the column object, it should be:

    {
  "data": {
    "item": [
      {
        "itemsequence": 1,
        "alerteroption": 1,
        "column": [
          {
            "attributecolumnid": "1691",
            "rawdata": {
              "value": "@{item()?[variables('School District Goat')]?['value']}"
            }
          }
        ]
      }
    ]
  }
}

    Where itemsequence and alerteroption are optional, the latter depending on whether you want to suppress any notifications that have been set up by users or not.

    FYI: taken from older developer portal

    alerteroption: This is only applicable if the iSheet configuration option "Enable sheet alerter"  is selected.

    • Respect user preference (value: 1)
    • Send now to all users with access (value: 2)
    • Suppress all notifications (value: 3)

    As for itemsequence, this isn't necessary for updates (PUT) but useful for POST's when creating more than one iSheet record in one API call.

    Here is the original post describing the XML format of the different types of columns, which will dictate what format is required below the rawdata property:

    <isheet recordcount="3">
    <data>
        <item itemsequence="1" externalid="3" alerteroption="1">
            <!-- single line column type -->
            <column attributecolumnid="101">
                <rawdata>
                    <value>
                        <![CDATA[name as defautl value]]>
                    </value>
                </rawdata>
            </column>
            
            <!-- multiple line column type -->
            <column attributecolumnid="102">
                <rawdata>
                    <value>
                        <![CDATA[name as defautl value]]>
                    </value>
                </rawdata>
            </column>
            
            <!-- choice type column -->
            <column attributecolumnid="103">
                <rawdata>
                    <choices>
                        <choice>
                             <id>101</id>
                        </choice>
                        <choice>
                             <id>{choice option id}</id>
                             <label><![CDATA[{choice option value}]]></label> (It is only applicable if "Include 'Other' option" property is enable.)
                        </choice>
                    </choices>
                </rawdata>
            </column>
            
            <!-- number type column -->
            <column attributecolumnid="104">
                <rawdata>
                    <value>
                        <![CDATA[10.00000000]]>
                    </value>
                </rawdata>
            </column>
            
            <!-- date and time column type -->
            <column attributecolumnid="105">
                <rawdata>
                    <date><![CDATA[15.11.2017]]></date> <!-- this must conform to the format set at the column level --> 
                    <time><![CDATA[11:14]]></time>
                </rawdata>
            </column>
            
            <!-- user lookup type column -->
            <column attributecolumnid="106">
                <rawdata>
                    <lookups>
                           <lookup>
                                 <id><![CDATA[110]]></id>
                           </lookup>
                           <lookup>
                                 <id><![CDATA[111]]></id>
                           </lookup>
                    </lookups>
                </rawdata>
            </column>
            
            <!-- hyperlink type column -->
            <column attributecolumnid="107">
                <rawdata>
                    <linkdisplayname><![CDATA[google.com]]></linkdisplayname>
                    <linkdisplayurl><![CDATA[http://www.google.com]]></linkdisplayurl>
                </rawdata>
            </column>
            
            <!-- image type column (as attachment) -->
            <column attributecolumnid="107">
                 <rawdata>
                        <attachments>
                            <attachment>
                                <id></id>
                            </attachment>
                        </attachments>
                </rawdata>
            </column>
            
            <!-- image type column (as URL) -->
            <column attributecolumnid="108">
                <rawdata>
                    <value>
                        <![CDATA[https://www.google.co.in/url?sa=i&rct=j&q=&esrc=s&source=images&cd=&cad=rja&uact=8&ved=0ahUKEwjX06Ls3ePWAhUWSY8KHXzDBYkQjRwIBw&url=https%3A%2F%2Fwww.pexels.com%2Fsearch%2Fnature%2F&psig=AOvVaw1vf-r36jaC7O-X0nRm2Sc0&ust=1507645110946954]]>
                    </value>
                </rawdata>
            </column>
            
            <!-- external attachment type column -->
            <column attributecolumnid="107">
                 <rawdata>
                        <attachments>
                            <attachment>
                                <id>101</id>
                            </attachment>
                            <attachment>
                                <id>102</id>
                            </attachment>
                        </attachments>
                </rawdata>
            </column>
            
            <!-- document link type column -->
            <column attributecolumnid="110">
                <rawdata>
                    <documents>
                        <document>
                            <docid>{document id}</docid>
                        </document>
                        <document>
                            <docid>{document id}</docid>
                        </document>
                    </documents>
                </rawdata>
            </column>
            
            <!-- folder link type column -->
            <column attributecolumnid="111">
                <rawdata>
                    <folders>
                        <folder>
                            <folderid>{folder's id}</folderid>
                        </folder>
                        <folder>
                            <folderid>{folder's id}</folderid>
                        </folder>
                    </folders>
                </rawdata>
            </column>
            
            <!-- isheet link type column -->
            <column attributecolumnid="112">
                <rawdata>
                    <isheetitems>
                        <isheetitem>
                            <recordid>{isheet record id}</recordid>
                        </isheetitem>
                        <isheetitem>
                            <recordid>{isheet record id}</recordid>
                        </isheetitem>
                    </isheetitems>
                </rawdata>
            </column>
           
            <!-- Lookup type column -->
            <column attributecolumnid="113">
                <rawdata>
                    <isheetitems>
                        <isheetitem>
                            <recordid>{isheet record id}</recordid>
                        </isheetitem>
                        <isheetitem>
                            <recordid>{isheet record id}</recordid>
                        </isheetitem>
                    </isheetitems>
                </rawdata>
            </column>
            
            <!-- join type column -->
            Doesn't support while Add/Edit isheet item
            
            <!-- calculate type column -->
            Doesn't support while Add/Edit isheet item
            
            <!-- Auto increment type column -->
            Doesn't support while Add/Edit isheet item
        </item>
        <item itemsequence="2" externalid="4" alerteroption="1">
        .
        .
        .
        </item>
        <item itemsequence="3" externalid="4" alerteroption="1">
        .
        .
        .
        </item>
    </data>
</isheet>

    So if we were wanting to update a choice and date/time column, then in JSON, something like:

    {
  "data": {
    "item": [
      {
        "itemsequence": 1,
        "alerteroption": 1,
        "column": [
          {
            "attributecolumnid": 9000,
            "rawdata": {
              "choices": {
                "choice": [{
                    "id": 1000
                }, {
                    "id": 1001
                }]
              }
            }
          },
          {
            "attributecolumnid": 9001,
            "rawdata": {
                "date": "03 Apr 2024",
                "time": "09:00"
            }
          }
        ]
      }
    ]
  }
}

    Where I've shown how you'd set multiple choices for say a checkbox Choice field, and for setting both the date and time for a DateTime field with the date format DD MMM YYYY.

    Hope this helps.

  • 0 in reply to Andrew Quinn

    Thanks for the response.

    I was able to get it to work using the body example below.

    {
  "data": {
    "item": [
      {
        "column": [
          {
            "attributecolumnid": "id value",
            "rawdata": {
              "value": "string value"
            }
          }
        ]
      }
    ]
  }
}

Reply
  • 0 in reply to Andrew Quinn

    Thanks for the response.

    I was able to get it to work using the body example below.

    {
  "data": {
    "item": [
      {
        "column": [
          {
            "attributecolumnid": "id value",
            "rawdata": {
              "value": "string value"
            }
          }
        ]
      }
    ]
  }
}

Children
No Data