cannot update row using Smartsheet API

Question

I'm using the Python example at http://smartsheet-platform.github.io/api-docs/?python#update-row(s) nearly verbatim. The difference is I'm updating only one cell in one row. I can see the value change in the variable row_a but the row does not get updated in the Smartsheet itself.

Here is my code, which is nearly identical to the code posted in the API guide:

row_a = smartsheet.Sheets.get_row(sheetId, 916467282667396)
cell_a = row_a.get_column(5937660066850692)
cell_a.value = 'new value'
row_a.set_column(cell_a.column_id, cell_a)
smartsheet.Sheets.update_rows(sheetId, [row_a])

Seeing that the text 'new value' does not appear in the Smartsheet after running this code, I added the word print in front of the last line to see what is returned by the API call and this is the result (I added indentation for readability):

{
    "requestResponse": null, 
    "result": {
        "code": 1062, 
        "name": "InvalidRowLocationError", 
        "recommendation": "Do not retry without fixing the problem.", 
        "shouldRetry": false, 
        "message": "Invalid row location.", 
        "statusCode": 400
    }
}

How can I fix the InvalidRowLocationError and get my row update to be sent to the Smartsheet?

Answer 1

There are actually two bugs associated with update_rows in the smartsheet-python-sdk version 1.0.1 but there is also a workaround. The error InvalidRowLocationError is encountered if you try to update a row where a cell is indented (see bug description at https://github.com/smartsheet-platform/smartsheet-python-sdk/issues/44). The error NotEditableViaApiError is encountered if you try to update a row where a cell contains formulas, links to other cells, system values, or Gantt values (see bug description at https://github.com/smartsheet-platform/smartsheet-python-sdk/issues/42).

These errors occur no matter which cell in the row you try to update because the smartsheet-python-sdk updates an entire row. It's important to note that the API works. So the workaround is to use the Python requests module to perform the actual update like this:

import requests
url = "https://api.smartsheet.com/2.0/sheets/SHEETID/rows"
payload = "{\"id\": 6436521654937476, \"cells\": [{\"columnId\": 8276294740797316,\"value\": \"new value\"}]}" 
# headers omitted from here for privacy
headers = { YYYYYYYYYYYYYYYYYYYYY }
response = requests.request("PUT", url, data=payload, headers=headers)
print(response.text)

In the sample above, the payload contains only the one cell that I wanted to update rather than the entire row. A more readable version of the payload is (a row object with only one cell):

{
  "id": 6436521654937476,
  "cells": [
    {
      "columnId": 8276294740797316,
      "value": "new value"
    }
  ]
}

Answer 2

I've found a way to still use the sdk while only updating certain cells in a row where a given row has formulas and things blocking api updates which is related to the NotEditableViaApiError.

for row in sheet.rows:
    cell = row.get_column(column_id)
    cell.value = 'modified cell value'  # edit the cell locally

    # gather more cells and add to list of cells for row...

    # remove all cells in row and just add back the single cell or list of cells
    row.cells = [cell]
    print my_sheets.update_rows(sheet_id, [row])  # update sheet

There's still an api call per row for me being able to get things working, but at least there's a way around the issue of only updating certain cells for a given row. This call will only update cells in the row's list and will leave others untouched.

Answer 3

The actual issue here is that the request requires you to use PUT instead of POST. See: http://smartsheet-platform.github.io/api-docs/?shell#update-row(s)