Smartsheet API Python - extract data from external excel file by cell and update into a smartsheet using API

Question

So I am trying to copy all the data from an excel file with dynamic row length - can range from 100 to 500 rows , which I then want to copy the contents from each cell iterating by column and updating rows to the last row

now my current code updates by Row when I specify the column ID, I am storing a primary column and non primary column[] , I am not sure how do I iterate a update through cells in each column in my row first , so If I lose my interenet connection for any reason I know till where it got last updated.

Yes this is slow process

The second part is I can open an excel file with openpyxl read the cell value and store it in a variable but I am struggling to pass it to the smart sheet code ....

MySheet     =     smartsheet.Sheets.get_sheet(SHEET_ID, PrimaryCol)
for MyRow in MySheet.rows:
    for MyCell in MyRow.cells:
        print (MyRow.id, MyCell.value)
        row_a = smartsheet.Sheets.get_row(SHEET_ID,MyRow.id)
        cell_a = row_a.get_column(PrimaryCol)
        cell_a.value = 'new value'
        row_a.set_column(cell_a.column_id, cell_a)
        smartsheet.Sheets.update_rows(SHEET_ID, [row_a])

Any help would get great thanks

Answer 1

I think these links (Add Rows, Update Rows) will be helpful in achieving the functionality you're looking for.

Ultimately, when ripping through an excel or CSV file, you're going to want to generate the entire row update (and updates for all of the rows) before submitting the update call to Smartsheet.

It appears in your code that you're making an update call for each cell in your sheet. So on a high-level, you might try first getting all of the columnIDs for your sheet and then for each row in your excel file, generating an update/add call for that new row.

Your last step should be a single call to the sheet that contains all of the row updates you're looking for. The last call should very seriously look something like:

smartsheet.Sheets.update_rows(SHEET_ID, ROW_UPDATES)

Where ROW_UPDATES is a list of all the row objects you're adding/updating.