jsonpython-3.xrestsmartsheet-apismartsheet-api-2.0

How Can I Get a Row From a sheet and Use it to Add or Update a New Row on Another Sheet? (Python 3)

I have been looking for a solution to the situation I've briefly described in the title. I'm trying to use a smartsheet_client.Sheets.get_row() request from one sheet, take that data and either add it or update it to a new row on a sheet where I can use a location specifying attribute to place it in a spot that isn't just the bottom of the sheet. I know that I can copy rows from one sheet and paste them to another with code, but I am trying to bypass the "only copy at the end of the sheet" limitation. Is this even possible, or am I grasping at straws? Here is my code that I have been working with:

row_a = smartsheet_client.Sheets.get_row(
  2896510686914436,       # sheet_id
  6830091038549892,       # row_id
  include='discussions,attachments,columns,columnType'
)
row_a.sibling_id = 3539932199446404
#if row_a.isinstance('parent_id',int)
#row_a.created_at = new_time
#row_a.modified_at = new_time
row_a.above = False
#row_a.row_number = None
#row_a.parent_id = None
row_a.id = 7015416612448132 #id of new row

# Add rows to sheet
response = smartsheet_client.Sheets.update_rows_with_partial_success(
  731362710841220,        # sheet_id of sheets we are adding to
  [row_a]
  )

print("Done!")
print(row_b)

There's a lot going on right now, but my original plan was to get_row then append the sibling_id and .above of where I want my new row to go, and then simply add a row of the row object I just built. Instead, I received parsing errors:

`{"response": {"statusCode": 400, "reason": "Bad Request", "content": {"errorCode": 1008, "message": "Unable to parse request. The following error occurred: Field \"createdAt\" was of unexpected type.", "refId": "1eyronnlz32sw"}}}`

My next thoughts were to append the created_at attribute to be the same as real time, but then modified_at started becoming the error. So I did the same thing again, and then the new error became "invalid row location: specify above or below with siblingId You cannot use other location specifiers in the same request."

No matter what I seem to do from this point, nothing works. Even if I set "other location specifiers" like row_number and parent_id to None, I'm just told that "The attribute(s) row.id, row.createdAt, row.modifiedAt, row.columns[], row.sheetId, row.version, row.accessLevel are not allowed for this operation."

Nothing seems to be just quite right for this operation. If anyone can offer any insight relating to my situation or just helpful tips in general, I am all ears.

Thank you!

Solution

  • I would not recommend trying to use the response from a Get Row operation to create a new row (Add Row) in a sheet. Reason being -- you're likely to encounter issues (as you've described) caused by the fact that not all row attributes can be set.

    For example, createdAt and modifiedAt will be included in the Get Row response, but cannot be specified in an Add Row or Update Row request -- because they are read-only attributes that are set automatically by Smartsheet when a row is created or modified.

    That's exactly what the error message "The attribute(s) row.id, row.createdAt, row.modifiedAt, row.columns[], row.sheetId, row.version, row.accessLevel are not allowed for this operation." is trying to tell you -- those are all read-only row attributes that are set automatically by Smartsheet -- trying to set them via an Add Row or Update Row request will always result in this error.

    Copying a row from one sheet to a specified position in another sheet can be accomplished by a two-step process:

    1. Issue a Copy Rows to another sheet request to append a copy of the specified row to the bottom of the other sheet. (Note the id of the newly created row that's included in the response, as you'll use it in step #2.)

    2. Issue an Update Rows request -- containing attributes as described in Specify Row Location -- to move the newly created row to the desired location in the other sheet.

    Here's some sample code that implements the 2-step process I've described.

    # specify source info
source_sheet_id = 3932034054809476
source_row_id = 3812039265019780

# specify destination info
destination_sheet_id = 8428033158735748

'''
STEP 1: 
    Copy row from source sheet to (bottom of) destination sheet
'''

# copy row from source sheet to (bottom of) destination sheet
# (include everything -- i.e., attachments, children, and discussions)
response = smartsheet_client.Sheets.copy_rows(
    source_sheet_id,
    smartsheet.models.CopyOrMoveRowDirective({
        'row_ids': [source_row_id],
        'to': smartsheet.models.CopyOrMoveRowDestination({
            'sheet_id': destination_sheet_id
        })
    }), 
    'all'
    )

# get the id of the newly created row
destination_row_id = response.row_mappings[0].to

'''
STEP 2: 
    Move new row from the bottom of the destination sheet
    to the desired location within that sheet. This example moves the row 
    to directly below the specified sibling row.
'''

# specify id of row that should appear directly above the row I'm moving 
sibling_row_id = 3620387999115140

# build the row to update (move)
# 'id' specifies the id of the newly created row that I now want to move
# 'sibling' attribute specifies the id of the sibling row where the row should be moved
# relative position (to sibling row) is not specified, so it will default to 'below'
row_to_move = smartsheet.models.Row()
row_to_move.id = destination_row_id
row_to_move.sibling_id = sibling_row_id

# update the row to change its location
updated_row = smartsheet_client.Sheets.update_rows(
    destination_sheet_id,
    [row_to_move]
)

    This should get you headed in the right direction with things. If you have trouble with step 2 (successfully using location specifier attributes to move the row to the desired location), please post a new question here on Stack Overflow.