Hello and good day.
I have a list of client names and sales across multiple boards I need to update, the data for which is in an Excel sheet. I have access to the API and passing knowledge on how to code with python.
What I wanted to do was a code that would read the name on the excel, look for it in the text field of the board, and then update the columns with the information needed. I will, shamefully, admit I used ChatGPT to help me bridge the gaps in my coding knowledge to try and get it done. Been working on this list for weeks and it just kept getting longer, so I took a shortcut hoping it’d help.
The resulting script just tells me it can’t find any of the names on the board, and querying the board gives me every column except the first one/item column. I kept trying to troubleshoot it but now I just have a syntax error of a script on my hands and a migraine.
Is what I’m trying to do even possible?
Adding the code as I’m using it, it’s set to print out what columns it was checking
I added Fuzzy search thinking the issue was that maybe there were some case differences or typing errors
import pandas as pd
import requests
import json
from rapidfuzz import process, fuzz
Constants
API_TOKEN = ApiTokenGoesHere
BOARD_ID = [TheNumbersMason]
EXCEL_FILE = r"Excel/Sheet/Path/Goes/Here"
DROPDOWN_COLUMN_ID = ‘ColumnID’
CLIENT_COLUMN_ID = ‘Client Name’
MONTO_FACTURADO_COLUMN_ID = ‘ColumnWhereTheInvoiceAmountGoes’
FUZZY_THRESHOLD = 80
Headers for API requests
headers = {
‘Authorization’: API_TOKEN,
‘Content-Type’: ‘application/json’
}
Read data from Excel
df = pd.read_excel(EXCEL_FILE)
def get_columns(board_id):
query = f’‘’
{{
boards(ids: [{board_id}]) {{
columns {{
id
title
}}
}}
}}
‘’’
response = requests.post(
url=‘https://api.monday.com/v2’,
headers=headers,
json={‘query’: query}
)
data = response.json()
columns = data.get(‘data’, {}).get(‘boards’, [{}])[0].get(‘columns’, )
print(f"Columns for board {board_id}: {columns}") # Debug output
return {col[‘title’]: col[‘id’] for col in columns}
def get_items(board_id):
query = f’‘’
{{
boards(ids: [{board_id}]) {{
items {{
id
column_values {{
id
text
value
}}
}}
}}
}}
‘’’
response = requests.post(
url=‘https://api.monday.com/v2’,
headers=headers,
json={‘query’: query}
)
data = response.json()
items = data.get(‘data’, {}).get(‘boards’, [{}])[0].get(‘items’, )
print(f"Items for board {board_id}: {items}") # Debug output
return items
def find_closest_item(items, client_name):
item_names = [(item[‘id’], {cv[‘id’]: cv[‘text’].lower() for cv in item[‘column_values’]}) for item in items]
item_names_lower = [(item_id, item_dict) for item_id, item_dict in item_names]
for item_id, item_dict in item_names_lower:
for text in item_dict.values():
closest_match = process.extractOne(client_name.lower(), [text], scorer=fuzz.ratio, score_cutoff=FUZZY_THRESHOLD)
if closest_match:
print(f"Match found: {client_name} -> {text}") # Debug output
return item_id
return None
def update_item(board_id, item_id, monto_facturado):
dropdown_options = get_dropdown_options([board_id], DROPDOWN_COLUMN_ID)
cliente_facturado_option_id = None
for key, value in dropdown_options.items():
if value == “Listo”:
cliente_facturado_option_id = key
break
if cliente_facturado_option_id is None:
raise ValueError("Dropdown option 'Listo' not found.")
mutation = f'''
mutation {{
change_column_value(
board_id: {board_id},
item_id: {item_id},
column_id: "{DROPDOWN_COLUMN_ID}",
value: "{{"labels": [{cliente_facturado_option_id}]}}"
) {{
id
}}
change_column_value(
board_id: {board_id},
item_id: {item_id},
column_id: "{MONTO_FACTURADO_COLUMN_ID}",
value: "{{"number": {monto_facturado}}}"
) {{
id
}}
}}
'''
response = requests.post(
url='https://api.monday.com/v2',
headers=headers,
json={'query': mutation}
)
return response.json()
Process each row in the Excel file
for index, row in df.iterrows():
client_name = row[‘Nombre Cliente’]
monto_facturado = row[‘Monto IVA incl.’]
for board_id in BOARD_ID:
print(f"Processing board {board_id} for client {client_name}") # Debug output
columns = get_columns(board_id)
items = get_items(board_id)
item_id = find_closest_item(items, client_name)
if item_id:
item = next(item for item in items if item['id'] == item_id)
for column_value in item['column_values']:
if column_value['id'] == MONTO_FACTURADO_COLUMN_ID and column_value['value']:
print(f"Skipping update for item ID {item_id} because Monto Facturado is not empty.")
break
else:
update_item(board_id, item_id, monto_facturado)
print(f"Updated item {item_id} for client {client_name} in board {board_id}")
else:
print(f"Client {client_name} not found in board {board_id}")
print(“Done!”)