Changing subitems column values

[jsararas (Jeff)]

In the API Playground I can change a text column value for an item using:
mutation{change_simple_column_value (board_id: XXXXXXXXXX, item_id: YYYYYYYYYY, column_id: "text", value: "NEW" ){id}}
But when I replace the item_id with the id of a subitem I get:

{
  "error_code": "ResourceNotFoundException",
  "status_code": 404,
  "error_message": "Item not found in board",
  "error_data": {
    "resource_type": "item",
...
    "error_reason": null
  }
}

I can’t find any reference to changing subitem values in the documentation, only (parent) item values. Do I need to pass a parent_id as well or…?

[basdebruin]

Hi @jsararas

The subitems actually lives on another board. You have to change the boardId to the id of the board holding the subitems. You can query the subitems column to retrieve this.

[jsararas (Jeff)]

Hi @basdebruin

@basdebruin

You can query the subitems column to retrieve this.

Are you referring to what is described on https://api.developer.monday.com/docs/subitems? I tried to run the sample query in the Playground against my board id, and this is the response:

{
  "errors": [
    {
      "message": "Field 'column_values' doesn't accept argument 'id'",
      "locations": [
        {
          "line": 6,
          "column": 28
        }
      ],
      "fields": [
        "query",
        "boards",
        "items",
        "column_values",
        "id"
      ]
    }
  ],
  "account_id": 1618989
}

Not sure if the sample code is wrong or I’m doing something wrong but I’m unable to get at the boardID for the subitems.

[basdebruin]

hi @jsararas

When I copy/paste the example code in the API playground and change “yourBoardId” to the boardId of the board holding the main items it returns data correctly. Don’t understand why you get this error.

Anyhow, with this you can query the subitems but you can’t change them. What I meant was to query all columns on your board with this query:

{
  boards(ids: 123456789) {
    columns  {
      id
      type
      settings_str
    }
  }
}

Then iterate through the results and look for column_type “subtasks”. The setting_str of ths column will look like this:

{
  "data": {
    "boards": [
      {
        "columns": [
          {
            "id": "subitems8",
            "type": "subtasks",
            "settings_str": "{\"allowMultipleItems\":true,\"itemTypeName\":\"column.subtasks.title\",\"displayType\":\"BOARD_INLINE\",\"boardIds\":[9999999]}"
          },

The array boardIDS (in the example with value 9999999) is the boardId of the board holding your subitems.

[TheWes (Wes Myers)]

alternatively… if you don’t have a ton of different boards and you’re not trying to deal with several boards at once, it may be worth it to you to just get the subitem board id manually…

if you go into your monday board, you’ll notice that the URL has your board id… if you click on an item in your board you’ll see the board id and the item id in the url (board id first, then item id after “/pulses/”).
https://yourdomain.monday.com/boards/xxxxxxxxxx/pulses/xxxxxxxxxx
…now if you click on a subitem in that board, you’ll see that first number change (because subitems are just items on a different board and are only nested under parent items visually in your ‘parent’ board). The second number is your subitem id… Now you can just grab that subitem board id (first number) and use that as your ‘board_id’ instead of the board id for your parent items. Plug it in and it should work for you.

[jsararas (Jeff)]

Thanks @TheWes ! Now it’s clicking how this is all being stored in the background. I’m able to change_multiple_column_values for a subitem successfully in the Playground. I also saw your post on GAS - that will also come in handy at some point! Right now I’m trying to create these subitems in VBA and it’s kicking my ass. I don’t suppose you’ve achieved this in VBA? I’m trying to mimic the Python Quickstart code to pass the column values as variables upon creating the subitem. Here’s attempt #643:

query_msub = "{""query"": ""mutation ($StockCode: String!, $columnVals: JSON!) { create_subitem (parent_item_id: " & ItemID & ", item_name:$StockCode, column_values:$columnVals) { id board { id } } }"" "
vars = "{\""StockCode\"": \""300.TEST1\"", ""columnVals"": ""{\""text\"": \""Things\""}"" } }"
mutate = "" & query_msub & ", ""variables"":" & vars & ""

[TheWes (Wes Myers)]

Hey @jsararas!

Ever since I started working with JavaScript I swore off VBA, so it’s been a good while since I’ve done anything in VBA.

However… I’ll take a stab at it… but take this response worth a grain of salt because I’ve got no clue if this will be right or not.

I see what you’re doing there… at first I thought you had bracket problems (an extra ‘{’ in ‘query_msub’ and then I saw an extra ‘}’ in ‘vars’… makes it a little difficult to read… but I’m guessing from here you put…
"payload" : mutate
…in your request?

I think maybe you might need some backslashes for ‘query’ and ‘variables’… it’s always the little things… I’d recommend trying to keep your code as close to monday’s examples as you can… makes things easier to compare and translate so you can figure it out.

What I mean is… instead of trying to build your payload string while defining your query and variables (I’m talking about those extra brackets) , just define your query and variables on their own and then put them together in the payload string… might help you see where your going wrong.

…see if this works:

query_msub = "mutation ($StockCode: String!, $columnVals: JSON!) { create_subitem (parent_item_id: " & ItemID & ", item_name:$StockCode, column_values:$columnVals) { id board { id } } }"

vars = "{\""StockCode\"": \""300.TEST1\"", ""columnVals"": ""{\""text\"": \""Things\""}"" }"

and then in your request put:

"payload" : "{\""query\"" :" & query_msub & ", \""variables\"" :" & vars & "}"

…and then let me know if luck is on my side!!!

[jsararas (Jeff)]

Thanks for taking a crack at it @TheWes! No luck I’m afraid. What I do is build up that query string and then pass it to another function as queryStr. The query request is structured as:

    Dim monday As New WinHttpRequest
    With monday
        .Open "POST", apiURL, False
        .SetRequestHeader "Content-Type", "application/json"
        .SetRequestHeader "Accept", "application/json"
        .SetRequestHeader "Authorization", apiKey
        .Send queryStr
        response = .ResponseText
        status = .status & " | " & .StatusText
    End With

I’m curious about your "payload" : mutate suggestion. I haven’t seen “payload” used before- were you suggesting I change:

.Send queryStr to .Send "payload" : queryStr? That gives me a syntax error- i imagine it needs to be enclosed?

It feels like the component that Monday is choking on are the quotes around the columnVals “{ … }”. In Python that’s wrapped with a single quote ‘{ … }’ but I’ve tried both in VBA with no luck. The reason I’m thinking that is because I can do change_simple_column_values using this:

query_msub = " { ""query"" : ""mutation{change_simple_column_value (board_id: XXXXXXXXX, item_id: XXXXXXXXXX, column_id: \""text\"", value: \""NOFUN\"" ){id}}""}"

and it works. But when I try and re-jig it as a change_multiple_column_values:

query_msub = " { ""query"" : ""mutation {change_multiple_column_values (item_id: XXXXXXXXXX, board_id: XXXXXXXXXX, column_values: ""{\""text\"": \""FUN\"",\""text_1\"": \""TIMES\""}"" ){id}}""}"

then I’m back to the 500 error. The only real difference - unless you can spot something else- is the multiple column values. The logs on Monday’s end say Invalid Token.

[TheWes (Wes Myers)]

Hey @jsararas!
Sorry… forget about the payload thing… I’m trying to translate things from GAS… my requests look like:

var query = "mutation($item:Int!, $board:Int!, $columnVals:JSON!){change_multiple_column_values(item_id:$item, board_id:$board, column_values:$columnVals, create_labels_if_missing: true){id}}";
    var variables = {
      "item": parseInt(itemID),
      "board": parseInt(boardID),
      "columnVals": '{' + mondayRow.toString() + '}'
    };
    var url = "https://api.monday.com/v2";
    var options = {
      "method": "post",
      "headers": {
        "Authorization": key,
      },
      "payload": JSON.stringify({
        "query": query,
        "variables": variables
      }),
      "contentType": "application/json"
    };
    var response = UrlFetchApp.fetch(url, options);

Looking at your multiple columns… I’ve never seen an underscore in any of my column ids… maybe “text_1” should be “text1”??? …maybe try your multiple_column_values with only the “text” column, and without the second “text_1” column… have you tried “text_1” on simple_column_value?

[jsararas (Jeff)]

Hey @TheWes, interesting that you have double quotes on your variable keys, then single quotes on the values. I might try some more variations on that.

Yes the column (and group!) auto-naming conventions in Monday don’t seem to be very conventional - my column is indeed text_1. I tried your suggestions anyway, but didn’t find any different results. Appreciate the help.

[JCorrell (Jim - The Monday Man)]

@jsararas

I replied to your email. But not sure where you are most active (and for others watching)…

I’m pretty sure the issue is not due items v. subitems; but the syntax difference between change_simple_column_value() and change_multiple_column_values() and some “escaping”.

query_msub = " { ““query”” : ""mutation {change_multiple_column_values (item_id: XXXXXXXXXX, board_id: XXXXXXXXXX, column_values: “”{"“text"”: "“FUN"”,"“text_1"”: "“TIMES"”}“” ){id}}“”}"

The BOLD part is “inside” the italicized part and will need “more escaping”… something like:

query_msub = " { ""query"" : ""mutation {change_multiple_column_values (item_id: XXXXXXXXXX, board_id: XXXXXXXXXX, column_values: \""{\\\""text\\\"": \\\""FUN\\\"",\\\""text_1\\\"": \\\""TIMES\\\""}\"" ){id}}""}"

Also, both column ID’s and group ID’s can (and often do) have underscores. Every time there is an invalid character to be used in an ID it is replaced with an underscore.

---

Jim - The Monday Man (YouTube Channel)
Watch Our Latest Video: Should I convert my Integromat account to Make? - YouTube
Contact me directly here: Contact – The Monday Man

[jsararas (Jeff)]

@JCorrell, that worked, thank you so much! That particular layout was not in the 743 combinations I had tried I had tried the \\\" escaping on the columns, but I think I see now, I was treating the column_values block the same as ""query"" and ""mutate"", so all quotes were disappearing by the time the query hit, but I needed to preserve one set around the column_values block. Have I got that right?

[JCorrell (Jim - The Monday Man)]

@jsararas

Glad it worked.

It can get a little tricky when you’re trying to figure it from scratch. Basically, at each level, for quotes within quotes, within quotes you have to add an additional “escape” level. Honestly, I don’t know the correct terminology.

The way I ended up constructing my VBA routines was to basically create my own “stringify” sub. So, that when I wanted to add another API call/command string I could keep it simple and let the program figure it out.

[jsararas (Jeff)]

@JCorrell For sure. I do have the VBA-JSON package installed, so now that I know what ‘good’ looks like, I may bend that to my will in order to stringify/serialize the values- or build my own as you suggest. Honestly, I can’t believe this isn’t a more common need. At least a dozen people use Excel, no? Either way- many thanks again for your help!