Add a link to an existing cell that has validation turned on -- want to override the validation

Kevin Scott

I have the following code that has been working properly for quite sometime. It adds links to multiple sheets and cells. It is run from an account that has ADMIN access (and I can perform the desired functions in the UI successfully).

#############ORIGINAL CODE#################

def set_sheet_link(ss_client, source_sheet_id, source_row_id, source_column_id, dest_sheet_id, dest_row_id, dest_column_id):

  cell_link = ss_client.models.CellLink()

  cell_link.sheet_id = source_sheet_id

  cell_link.row_id = source_row_id

  cell_link.column_id = source_column_id

  cell = ss_client.models.Cell()

  cell.column_id = dest_column_id

  cell.value = ss_client.models.ExplicitNull()  # Must use the SmartSheet "null" value

  cell.link_in_from_cell = cell_link

  row = ss_client.models.Row()

  row.id = dest_row_id

  row.cells.append(cell)

  ss_client.Sheets.update_rows(dest_sheet_id, [row])

  return

##############################

I have a new requirement to add a link to another sheet. This other sheet tracks presales efforts. The value of the cell to be assigned a link (in) is from a pick list (validation is on). At the time that presales turns a project over to the delivery team, this Python script (the function above is part of that larger script) is used to set up a new project instance. The value of the cell will move from a selection from a pick list to being a link from the project delivery side; thus, I need to override the validation when I assign the link. I updated the code function from above to below (mods bolded):

#############UPDATED CODE#################

def set_sheet_link(ss_client, source_sheet_id, source_row_id, source_column_id, dest_sheet_id, dest_row_id, dest_column_id):

  cell_link = ss_client.models.CellLink()

  cell_link.sheet_id = source_sheet_id

  cell_link.row_id = source_row_id

  cell_link.column_id = source_column_id

  cell = ss_client.models.Cell()

  cell.column_id = dest_column_id

  cell.overrideValidation = True  # Override the data validation on the cell/column

  cell.strict = False  # False value required to support override of validation

  cell.value = ss_client.models.ExplicitNull()  # Must use the SmartSheet "null" value

  cell.link_in_from_cell = cell_link

  row = ss_client.models.Row()

  row.id = dest_row_id

  row.cells.append(cell)

  ss_client.Sheets.update_rows(dest_sheet_id, [row])

  return

##############################

The updated code gets the following error:

{"response": {"statusCode": 400, "reason": "Bad Request", "content": {"detail": {"attribute": "cell.strict", "index": 0, "rowId": 4986636077623172}, "errorCode": 1115, "message": "Row must contain either cell link updates or row/cell value updates; mixing of both update types in one API call is not supported.", "refId": "khyxyh"}}}

If I omit the line: cell.value = ss_client.models.ExplicitNull(), I get an error indicating that cell.value must be set to null.

Any help would be greatly appreciated!

-Kevin

Isaac Jose

Hi @Kevin Scott - I think you need to use cell.override_validation instead of cell.overrideValidation. It looks like the general API documentation shows the attribute labeled as overrideValidation, but the Python SDK docs indicate that the attribute uses override_validation. Hopefully this helps.

Genevieve P.

Hey @Kevin Scott

I was able to use the API to update a cell in a restricted dropdown list with a cell-link value, using

 cell.overrideValidation = True

However I did not use cell.strict = False. I didn't specify strict at all, even though the documentation notes that if you're overriding validation you should set this to False.

Can you test it with just the overrideValidation? If this works for you, I'll submit a request to have our documentation updated.

Cheers,

Genevieve

Kevin Scott

Hi @Genevieve P.

My apologies for the long delay, as I have been traveling over the past couple weeks.

This did not work for me... I only added the cell.overrideValidation = True without the cell.strict = False

I received the following:

#####################

{"response": {"statusCode": 400, "reason": "Bad Request", "content": {"errorCode": 5539, "message": "A cell link could not be saved in column \"Status\". This column is restricted to PICKLIST values only.", "refId": "5v925"}}}

Traceback (most recent call last):

 File "/Users/kscott/Documents/Arista PMO Utilities/new_project_v2_10.py", line 2209, in <module>

  set_sheet_link(smartsheet_client, project_delivery_sid, project_delivery_rowid, dest_proj_status_col_id,

 File "/Users/kscott/Documents/Arista PMO Utilities/new_project_v2_10.py", line 226, in set_sheet_link

  ss_client.Sheets.update_rows(dest_sheet_id, [row])

 File "/Library/Frameworks/Python.framework/Versions/3.10/lib/python3.10/site-packages/smartsheet/sheets.py", line 1040, in update_rows

  response = self._base.request(prepped_request, expected, _op)

 File "/Library/Frameworks/Python.framework/Versions/3.10/lib/python3.10/site-packages/smartsheet/smartsheet.py", line 250, in request

  raise the_ex(native, str(native.result.code) + ': ' + native.result.message)

smartsheet.exceptions.ApiError: {"result": {"code": 5539, "errorCode": 5539, "message": "A cell link could not be saved in column \"Status\". This column is restricted to PICKLIST values only.", "name": "ApiError", "recommendation": "Do not retry without fixing the problem. ", "refId": "5v925", "shouldRetry": false, "statusCode": 400}}

#####################

Please let me know if there could be something else that I am overlooking.

Thank you very much!

Kevin

Genevieve P.

Hey @Kevin Scott

Would you be able to post a new code snippet of your latest attempt? And can you confirm the sdk version you're using?

As long as you're an Admin on the sheet you should be able to perform this action - I'd like to troubleshoot further.

Thanks!

Genevieve

Kevin Scott

Thanks for working with me @Genevieve P. !!

I have Admin access to the target sheet:

I have been using this version of the sdk: smartsheet-python-sdk 2.177.1

I am now (just updated) using smartsheet-python-sdk 3.0.0 and same error received.

Here is the call:

set_sheet_link(smartsheet_client, project_delivery_sid, project_delivery_rowid, dest_proj_status_col_id,  presales_sid, resourcing_row_id[resourcing_row_selected], presales_status_col_id)

Here is the function:

##################

# Set sheet link

# Value from source will be visible in dest

def set_sheet_link(ss_client, source_sheet_id, source_row_id, source_column_id, dest_sheet_id, dest_row_id, dest_column_id):

  cell_link = ss_client.models.CellLink()

  cell_link.sheet_id = source_sheet_id

  cell_link.row_id = source_row_id

  cell_link.column_id = source_column_id

  cell = ss_client.models.Cell()

  cell.column_id = dest_column_id

  cell.overrideValidation = True   # Override the data validation on the cell/column

  cell.value = ss_client.models.ExplicitNull()  # Must use the SmartSheet "null" value

  cell.link_in_from_cell = cell_link

  row = ss_client.models.Row()

  row.id = dest_row_id

  row.cells.append(cell)

  ss_client.Sheets.update_rows(dest_sheet_id, [row])

  return

###############

By the way, this function is successfully used for multiple other link creations...but fails in this particular sheet (this is the only link to be created in this sheet).

Thanks again for any help!

Kevin

Genevieve P.

Thank you for this additional information!

I can't see anything directly from here which would be causing your error. I am very curious about the fact that it's only happening on one sheet; would you mind testing by creating a copy of the sheet with Save as New to see if you can successfully update the copy?

Then the next step at this point would be to open a ticket with Smartsheet Support with all of this data/information including a screen recording and a link to the sheet.

Cheers,

Genevieve

Kevin Scott

Thanks, @Genevieve P. !

I created a copy of the sheet that exhibits the issue related to inserting a link in a column that has a pick list associated. I received the same error results.

I will open a ticket, as you suggested.

Best regards,

Kevin

Kevin Scott

I have created case #06109824, as @Genevieve P. suggested.

Thanks,

Kevin

Isaac Jose

Hi @Kevin Scott - I think you need to use cell.override_validation instead of cell.overrideValidation. It looks like the general API documentation shows the attribute labeled as overrideValidation, but the Python SDK docs indicate that the attribute uses override_validation. Hopefully this helps.

Kevin Scott

Hello @Isaac Jose!

That solved it...thank you so very much!!!

I am surprised that nothing gave me an error when I used an attribute that did not exist??

In any case, all resolved...thanks again!

Genevieve P.

@Isaac Jose for the win!

I’m so glad to hear this resolved the issue.