Is there a way to edit crosssheet references using the api?

Question

Developing a workflow in which everyday a new spreadsheet is uploaded to the workspace. A second tracking sheet uses a cross sheet reference to populate several columns. I am attempting to automate the upload process and if I am able to edit an existing cross sheet reference I can just have it point to the new sheet as opposed repopulating the cells with a new reference. Given that the reference editor in the app is capable of changing which sheet and what range is referenced, it makes sense to me that there would be some way for the api to do this as well. I have read through the documentation here: http://smartsheet-platform.github.io/api-docs/ and found nothing that would allow me to edit the reference. I am hoping that I am missing something obvious.

I am able to get the list of references and retrieve specific ones using the code provided in the documentation. I also am able to change the cells in the given rows if necessary, but that adds a level of complexity that I would like to avoid.

Here is the code I use to retrieve and add a cross sheet reference

Wrksps = get_workspace_list()

for space in Wrksps:
    if space.name == 'QC':
        qc_space = space

QC_sheets = get_sheet_list(qc_space.id,'w')

for sheet in QC_sheets:
    if sheet.name == 'QC Active Issues':
        active_sheet = sheet

active_sheet = get_object(active_sheet.id, 's')

issue_sheet = get_object(Referenced sheet ID, 's')

for col in issue_sheet.columns:
    if col.title == 'Work Order ID':
        WO_col = col
    if col.title == 'Unstartable':
        last_col = col

xref = smartsheet.models.CrossSheetReference({
    'name': 'Sample Time Log Sheet Range 1',
    'source_sheet_id': Referenced sheet ID,
    'start_row_id': ROW ID,
    'end_row_id': ROW ID
    'start_column_id': start_col.id,
    'end_column_id': last_col.id
})

result = smart_sheet_client.Sheets.create_cross_sheet_reference(active_sheet.id, xref)

print(result)

result = smart_sheet_client.Sheets.list_cross_sheet_references(active_sheet.id)

Silly me... This is being done in python3.x, specifically 3.7. — Tom, Jul 03 '19 at 16:15

score 1 · Accepted Answer · answered Jul 03 '19 at 17:10

1

That's awesome you are creating this automation for your work! We don't yet have the capability to edit cross sheet references via the Smartsheet API. Right now it is only possible to create them and get the current cross sheet references for a given sheet.
To work around this you could create a new cross sheet reference on the sheet in the same cell. Unused cross sheet references on a sheet are automatically deleted from the sheet after two hours. Or you could update the cells on the sheet being referenced with new data.
I can pass along your use case and how you are looking to edit existing references.

answered Jul 03 '19 at 17:10

daveskull81

627
4
7

I ended up just having the reference sheet updated with the most recent data and storing the old data elsewhere. This is pretty much the answer I was expecting. Thanks for the response – Tom Jul 10 '19 at 18:03
@Tom Sounds good.That approach should keep the data flowing out to the other sheet. Happy to help! – daveskull81 Jul 10 '19 at 18:51

Is there a way to edit crosssheet references using the api?

1 Answers1