Read Data from a Group and parse the JSON into a CSV

monday Apps & Developers
, , ,
1

Hey all!

I have a question that maybe you can help.
Im creating a code in python that basically uploads / updates some data in Monday.

In short, i have 2 separate folders with CSV files. My python code accesses those files and populates my board. one folder’s CSVs have what populates the values of one group and the other folder’s CSVs have what populates the sub items of one value
(e.g: Folder1 > toyota.csv, nissan.csv, bmw.csv.
Folder2> toyota-specs.csv, nissan-specs.csv, bmw-specs.csv).

my board groups names are : Toyota, Nissan, BMW.
in the toyota.csv column0 are several models of toyota cars.

everyday this toyota.csv is renewed automatically with same or less or new models.

i need my code to list all the items of the toyota group (or any other that is being checked), and compare column0 entries with the api json response of what the group already has as values. If theres something new i need to add it on monday and take the new id in order to use it for future updates. if something on monday is not there anymore on the csv i need to delete it. Whatever is the same i need to update it.

The way i tried to do it is to put the r.json() into a csv but i get multiple errors when i tried to use pandas.


columnsQuery = f"""query {{
                         boards (ids: {BOARD_ID}) {{
                            groups(ids: {groupIDToyota}){{
                                items {{
                                    id
                                    name
                                    column_values {{
                                        id
                                        title
                                        value
                                    }}
                                }}
                            }}
                        }}
                     }}"""                  

    data = {"query": columnsQuery}
    r = requests.post(url=BASE_URL, json=data, headers=HEADERS)
    columnsResults = r.json()
    print("columns", columnsResults)

this is the query i use to get the results. i specify both the board and group id. however when i tried a json parser website it showed me that the response is completely wrong and couldnt get any result. and when i tried pandas it didnt work.

would appreciate any ideas. thanks

"Preformatted text

2

Hello there @Avas and welcome to the community!

I hope you like it here.

I am not experienced with Python but as for the query, I recommend using something like this:

{
  boards(ids: 1234567890) {
    groups(ids: "topics") {
      items_page(limit: 10) {
        cursor
        items {
          id
          name
          column_values {
            column {
              title
            }
            id
            value
          }
        }
      }
    }
  }
}

Please note in this case the board ID is a number and the group ID is a string.

Here it is in Python:

import http.client
import json

conn = http.client.HTTPSConnection("api.monday.com")
payload = "{\"query\":\"{\\n  boards(ids: 1234567890) {\\n    groups(ids: \\\"topics\\\") {\\n      items_page(limit: 10) {\\n        cursor\\n        items {\\n          id\\n          name\\n          column_values {\\n            column {\\n              title\\n            }\\n            id\\n            value\\n          }\\n        }\\n      }\\n    }\\n  }\\n}\",\"variables\":{}}"
headers = {
  'API-Version': '2023-10',
  'Authorization': 'APITOKENHERE',
  'Content-Type': 'application/json',
}
conn.request("POST", "/v2/", payload, headers)
res = conn.getresponse()
data = res.read()
print(data.decode("utf-8"))

I hope that helps!

Cheers,
Matias

1 Like
3

Hello Matias ,

Thank you for your time to reply to me.
I think what you wrote me is just to see/read/list the items on the specified group?
I have already achieved that, it is no problem. What i need is to put the information from

data = {“query”: columnsQuery} (query being the part i already posted)
r = (url=BASE_URL, json=data, headers=HEADERS) (r = response 200)
temp = r.json() (<= data in json)

to put the data in temp into a CSV file.

1 Like
4

Hello again,

Oh! So you are succeeding in the use of our API and now you want to take that data and save it into a CSV file. Thank you for the confirmation!

I am afraid I am not experienced with CSV so I am not sure how that is done.

Let’s hope someone else in the community has experience with this :grin:

Cheers,
Matias

1 Like