Creating Items Unexpected token

monday Apps & Developers
,
1

I am having trouble creating items. I have read through the api documentation and have tried setting up my post request exactly shown in the docs. I unfortunately cannot share my code one for one since some of the information is sensitive. Though the problem seems to be coming from how the “column_values” are formatted.

Code
The purpose of the update_payload variable is to fix the column values. I created some code that parses data and correctly formats the forward slashes to how create_items mutation example looks like.

# python 3.10
import json
import requests

apiKey = 'xxx'
apiUrl = "https://api.monday.com/v2"
headers = {"Authorization" : apiKey,
           "Content-Type" : 'application/json',
           "API-Version" : '2023-10'}

def create_item(board_id, group_id, item_name, column_values):
    payload = f"""
    mutation {{
        create_item (board_id: {board_id} group_id: {json.dumps(group_id)} item_name: {json.dumps(item_name)} column_values: {json.dumps(column_values)}) {{
            id
        }}
    }}
    """
    update_payload = payload.replace("\\\\\"", "\"")
    print(update_payload)
    data = {'query' : update_payload}
    r_boards = requests.post(url=apiUrl, headers=headers, data=json.dumps(data)) # make request

    return r_boards


# Iterate through each group
for idx, (g_name, g_id) in enumerate(gid_dict.items()):
    if g_name == "Group Title":
        continue
    df_filter_project = df.loc[df['Project'] == g_name]
    item_name_list = df_filter_project['Task'].tolist()
    item_vals_df = df_filter_project.drop(['Project', 'Task'], axis=1)

    # Iterate through each 

    # Iterate through each row and construct the string
    formatted_strings = []
    for _, row in item_vals_df.iterrows():
        data_dict = row.to_dict()
        
        # Not ideal to have nested for loops (shouldn't be too slow since it's looking at columns and not records)
        replaced_dict = {}
        for key1 in data_dict.keys():
            for key2 in cid_dict.keys():
                if key1 == key2:
                    replaced_dict[cid_dict[key2]] = data_dict[key1]

        # Construct the string for each row
        formatted_string = "{"
        for col, value in replaced_dict.items():
            formatted_string += "\\\"{}\\\":{{\\\"text\\\":\\\"{}\\\"}},".format(col, value)
        formatted_string = formatted_string[:-2]  # Remove the trailing comma and space
        formatted_string += "}"

        print(create_item(created_board_id, g_id, item_name, formatted_string).json())
        sys.exit()

Print Outputs
Those “field#” are the ids of the related columns

    mutation {
        create_item (board_id: xxx group_id: "xxx" item_name: "xxx" column_values: "{\"field1\":{\"text\":\"xxx\"},\"field2\":{\"text\":\"xxx\"},\"field3\":{\"text\":\"xxx\"},\"field4\":{\"text\":\"xxx\"},\"field5\":{\"text\":\"xxx\"},\"field6\":{\"text\":\"xxx\"},\"field7\":{\"text\":\"xxx\"},\"field8\":{\"text\":\"xxx\"},\"field9\":{\"text\":\"xxx\"},\"field10\":{\"text\":\"xxx\"},\"field11\":{\"text\":\"xxx\"},\"field12\":{\"text\":\"xxx\"},\"field13\":{\"text\":\"xxx\"},\"field14\":{\"text\":\"xxx\"},\"field15\":{\"text\":\"xxx\"},\"fiel16\":{\"text\":\"xxx\"},\"fiel17\":{\"text\":\"xxx\"},\"field18\":{\"text\":\"xxx\"}") {
            id
        }
    }

{'error_message': '809: unexpected token at \'{"field1":{"text":"xxx"},"field2":{"text":"English, John"},"field3":{"text":"xxx"},"field4":{"text":"xxx"},"field5":{"text":"xxx"},"field6":{"text":"xxx"},"field7":{"text":"xxx"},"field8":{"text":"xxx"},"field9":{"text":"xxx"},"field10":{"text":"xxx"},"field11":{"text":"xxx"},"field12":{"text":"xxx"},"field13":{"text":"xxx"},"field14":{"text":"xxx"},"field15":{"text":"xxx"},"field16":{"text":"xxx"},"field17":{"text":"xxx"},"field18":{"text":"xxx"}\'', 'status_code': 500}
2

Hello there @Binx96 and welcome to th community!

I hope you like it here :muscle:

For me to take a look into what might be failing, I need to check the type of column of each column and the data you are trying to pass. Each column type uses a specific syntax so depending on which column types we are talking about, different formats will be needed. With the information you sent, I can not tell which column types you have or what kind of data you are sending in each case.

You can find examples on how to pass data for each specific column type in our Postman examples here.

You can also find it in the documentation here.

Let me know how that goes!

Cheers,
Matias

1 Like
3

All columns are “text” type.

Here is an some better data:

mutation {
        create_item (board_id: 123456789 group_id: "123456_r6456yhgt" item_name: "01 - Office Land Survey" column_values: "{\"phase\":{\"text\":\"3213 - 3213 Data Archiving\"},\"pm\":{\"text\":\"Doe, John\"},\"client\":{\"text\":\"U.S.A - Company\"},\"org\":{\"text\":\"0.01.45.123.00\"},\"status\":{\"text\":\"A\"},\"compensation\":{\"text\":\"0\"},\"first_hours\":{\"text\":\"1.0\"},\"first_second_labor\":{\"text\":\"256.123\"},\"text\":{\"text\":\"0\"},\"first_cost\":{\"text\":\"0\"},\"first_expenses\":{\"text\":\"0\"},\"first_second_cost\":{\"text\":\"253.854\"},\"second_hours\":{\"text\":\"0.0\"},\"second_first_labor\":{\"text\":\"0.0\"},\"second_consultants\":{\"text\":\"0\"},\"second_cost\":{\"text\":\"0\"},\"second_expenses\":{\"text\":\"0.0\"},\"second_first_cost\":{\"text\":\"0.0\"}") {
            id
        }
    }
4

The error is because you’re manipulating strings, passing through json.dumps() multiple times and its adding extra \ characters that are causing the graphql query parser to error.

The problem I see is there is no reason to be building and manipulating strings. The column values is a JSON string of a regular object - build the object first, then turn it into a JSON string rather than trying to build the JSON from strings.

I am a javascript guy, so bear with me. I am also leaving out a lot that isn’t about creating the request body to send to the server

Build your columnValues as an object, I will leave that up to you as to how you do it (because I am not a python guy). If you’re using column values you retrieved with the API, the value property of a column_value is a JSON string. Use JSON.parse() or json.loads() to get an actual value object you can work with rather than strings that you need to manipulate. (Or use fragments in column_value v2 in the new API to bypass the JSON string all together.)

This is an example of the structure you’re creating, which you’re doing through string manipulation, but instead just make this object. Keys are the column ID and their values is the value object for the type of column it is. In this case all are text.

const columnValuesObject = {
   columnId: {text: "string"},
   columnId2: {text: "string2"},
   columnId3: {text: "string3"},
}

Look into is GraphQL variables. https://graphql.org/learn/queries/#variables so that you aren’t trying to do string manipulation on the query itself. Create a working query in the playground and copy it with the copy button and use it. :slight_smile: Using variables lets the server substitute values after parsing the query.

const query = `mutation ($boardId: ID!, $itemName: String!, $groupId: String, $columnValues: JSON) {
  create_item(
    board_id: $boardId
    group_id: $groupId
    item_name: $itemName
    column_values: $columnValues
  ) {
    id
  }
}`

// yes the above query is correct, you can steal it.

In the playground you can create the variables for testing, its below the query section. In the playground - you type JSON in. In your code though, its just an object for the variables:

const variables = {
     boardId: 1235423432,
     itemName: "item name is this",
     columnValues: JSON.stringify(columnValuesObject), //this is where we turn the columnValuesObject into a string.
     groupId: "groupid_string"
}

// in your code you will have variables for the boardId, itemName, groupId 
// that are used in this object, I'm skipping writing that out here.

now to put it together:

const requestBody = JSON.stringify({
    query,
    variables,
})

// use your favorite tool to post the requestBody to the API server. 
// the exact method may vary since some clients can take an object
// and will stringify it for you so you''d skip the stringify above.
1 Like
5

Thank you @codyfrisch for the very detailed help!

2 Likes
6

Thanks @codyfrisch !! Still working out the kinks of converting/understanding JS to python. I was able to get it to work via the playground. I’ll update when I can get python to work with it.

1 Like
7

Good progress!

I wish I could provide more assistance with the building of the object in python. here is some rough JS to do this with text columns.

const columnValues = //get the column  values with GraphQL, this will be the column_values array of the item.
const columnValueObject = {};

for (const column of columnValues) {
   if (column.type !== "text") {
        console.warn(`unsupported column ${column.id} of ${column.type} encountered`);
        continue;
   }
   columnValueObject[column.id] = { text: column.text };
// you may want to consider creating a function for this loop, that has switch statements by
// column.type and the correct pattern for each type. If you may do this with more than just
// text columns.
}

const columnValueString = JSON.stringify(columnValueObject );

Obviously this only applies if its to the same column ID… you’d need to get fancier if you’re mixing things up and need to match one ID with another’s value. (maybe two boards).

1 Like
8

Thanks!

I believe that I have the correct python object, but I am running into a json decoder error. It looks like it has something to do with how the json object is formatted, but I’m not seeing why just yet.

My object:

{'Phase': 'phase_1', 'Client': 'Some Company 123', 'Org': '00.34.23.54', 'Consultants': 0}

The function:

def create_item(board_id, group_id, item_name, column_values):
    query = """
    mutation ($boardId: ID!, $itemName: String!, $groupId: String, $columnValues: JSON) {
        create_item(
            board_id: $boardId
            group_id: $groupId
            item_name: $itemName
            column_values: $columnValues
        ) {
            id
        }
        }
    """

    variables = {
        'boardId': board_id,
        'groupID': group_id,
        'itemName': item_name,
        'columnValues': json.dumps(column_values, separators=(',', ':'))
    }

    request_body = json.dumps({query : variables})
    data = {'query': request_body}
    print(data)
    r_boards = requests.post(url=apiUrl, headers=headers, data=data) # make request

    return r_boards

Data Output:

{'query': '{"\\n    mutation ($boardId: ID!, $itemName: String!, $groupId: String, $columnValues: JSON) {\\n        create_item(\\n            board_id: $boardId\\n            group_id: $groupId\\n            item_name: $itemName\\n            column_values: $columnValues\\n        ) {\\n            id\\n        }\\n        }\\n    ": {"boardId": "123456", "groupID": "654321", "itemName": "Office", "columnValues": "{\\"Phase\\":\\"phase_1\\",\\"Client\\":\\"Some Company 123\\",\\"Org\\":\\"00.34.23.54\\",\\"JTD Consultants\\":0}"}}'}

The Error:

Traceback (most recent call last):
  File "C:\Users\name\Anaconda3\envs\monday\lib\site-packages\requests\models.py", line 971, in json
    return complexjson.loads(self.text, **kwargs)
  File "C:\Users\name\Anaconda3\envs\monday\lib\json\__init__.py", line 346, in loads
    return _default_decoder.decode(s)
  File "C:\Users\name\Anaconda3\envs\monday\lib\json\decoder.py", line 340, in decode
    raise JSONDecodeError("Extra data", s, end)
json.decoder.JSONDecodeError: Extra data: line 1 column 5 (char 4)
1 Like
9

I believe the issue is with this:

request_body = json.dumps({query : variables})
data = {'query': request_body}

try this:

data = {
     'query': query, 
     'variables': variables
}

I’m presuming requests.post must do a json.dumps(data) internally to send JSON.

2 Likes
10

It does not. I took it out to try and trouble shoot the problem. I already called it on my “request_body” variable, so I wasn’t sure if I needed to call it again.

With you suggestion and adding data.dumps() back in, that worked! Thank you tons for you help!

def create_item(board_id, group_id, item_name, column_values):
    query = """
    mutation ($boardId: ID!, $itemName: String!, $groupId: String, $columnValues: JSON) {
        create_item(
            board_id: $boardId
            group_id: $groupId
            item_name: $itemName
            column_values: $columnValues
        ) {
            id
        }
        }
    """

    variables = {
        'boardId': board_id,
        'groupID': group_id,
        'itemName': item_name,
        'columnValues': json.dumps(column_values, separators=(',', ':'))
    }

    data = {
        'query': query,
        'variables': variables
    }

    r_boards = requests.post(url=apiUrl, headers=headers, data=json.dumps(data)) # make request

    return r_boards
2 Likes
11

surprised it worked as you expected - i think your variable is groupId not groupID

since in the query the $groupId: String doesn’t have !, it makes $groupId optional. Since your variables don’t have groupId it just ignores that its missing in variables. It also ignores keys in variables that aren’t defined in the query.

1 Like
12

Yeah, I got too excited and thought it was working. Thanks for catching that syntax error. I was going nuts trying to figure out why one of my groups was getting all the items when it should have been creating 5 items for both groups.

Another problem I am seeing is that the text fields (column values) are not filling in either. Hmmmmm…

13

Looking at your results, your column_values object is incorrect.

First the key needs to be the column ID (not the column title). Those appear to be column titles. Second the value of each key must be literally. {'text': textstring} in other words the value of each columnID key is an object with a text key, with the text value you want in the column. See the following JSON for what it should look like when done.

"{\n columnId1: {text: \"test text\"},\n columnId2: {text: \"more test\")\n}"

14

Strange, I swore the playground allowed me to use the title of the column to insert a value. Must be mistaken then. Okay, I updated the object with the column id and ensured that the values are all strings. Not sure how to get it to that JSON format that you have illustrated. Tried json.dumps().

{"phase": {"text": "123"}, "client": {"text": "Some Company 123"}, "org": {"text": "00.45.45.76"}, "cost": {"text": "0.0"}}
15

if that is the output of the object through json.dumps() printed, then you’re good.

The extra stuff you see in mine is whats there AFTER passing data through json.dumps() (a second pass) Since the string you have is in the columnValues variable - and that then gets put json.dumps as part of data it will pick up the extra \ to escape quotes so the JSON is valid. (and new lines).

Basically, take what you got and put it in the columnValue variable and you should be good.

1 Like
16

Yes that is the object after passing through the first json.dumps(). Ahh yes, I see the \ now after the second pass through json.dumps(). Thanks for clarifying that, I was getting confused why I needed to pass it twice through json.dumps(). Seemed a bit redundant.

I now am getting a new error :frowning: . It appears these are coming from the ‘phase’ column id. I understand the error. But I am not changing the column datatype, so not sure where this is coming from.

/docs/change-column-values', 'error_data': {'column_value': '{"text"=>"5000 - 5000 Project Management"}', 'column_type': 'TextColumn'}}
{'error_code': 'ColumnValueException', 'status_code': 200, 'error_message': 'invalid value, please check our API documentation for the correct data structure for this column. https://developer.monday.com/api-reference
17

OH my. I am so sorry! This is embarrassing. I was copying from my code for “long_text” columns which use {text:string} on accident the whole time.

it is as you had it before. Yes, stupid the two text columns have different formats. Here is hte object.

{columnId: textstring, columnId2: textstring}

1 Like
18

Ah yeah! That is producing! Can you please explain the two text columns? I wasn’t aware that there are two different formats.

19

text column type supports a short bit of visible text in the column.

long_text columns show a snippet and when clicked, drop down a text box to show the full longer length of text. Up to 2048 characters.

funny fact is a regular text column has a potentially higher maximum size (or did pre-mondaydb at least) into the megabytes of text. I haven’t tested its limits lately. But there is no way to easily see all of it!

You can create a long text column by creating a new column and choosing “more columns” and searching “long text”

Honestly, when they were created long ago, monday should have just made the display type a setting on a text column. rather than separate column types!

2 Likes
20

Thank you @codyfrisch for the help!!

2 Likes