I am trying to remove the formula from a column in a existing sheet with python.
I tryed to set my formula to None using the column object (column.formula = None)
It does not work and my column object remains unchanged. Anyone have inputs to solve this issue ? Thank you !
This took me a bit to figure out, but seems like I've found a solution. Turns out that this is a 2-step process:
Update the column object to remove the formula (by setting column.formula to an empty string).
For each row in the sheet, update the cell within that column to remove the formula (set cell.value to an empty string and cell.formula to None).
Completing the STEP 1 will remove the formula from the column object -- but that cell in each row will still contain the formula. That's why STEP 2 is needed -- STEP 2 will remove the formula from the individual cell in each row.
Here's some example code in Python that does what I've described. (Be sure to update the id values to correspond to your sheet.)
STEP 1: Remove formula from the Column
column_spec = smartsheet.models.Column({
'formula': ''
})
# Update column
sheetId = 3932034054809476
columnId = 4793116511233924
result = smartsheet_client.Sheets.update_column(sheetId, columnId, column_spec)
STEP 2: Remove the formula from that cell in each row
Note: This sample code updates only one specific row -- in your case, you'll need to update every row in the sheet. Just build a row object for each row in the sheet (like shown below), then call smartsheet_client.Sheets.update_rows once, passing in the array of row objects that you've built corresponding to all rows in the sheet. By doing things this way, you're only calling the API once, which is the most efficient way of doing things.
# Build new cell value
new_cell = smartsheet.models.Cell()
new_cell.column_id = 4793116511233924
new_cell.value = ''
new_cell.formula = None
# Build the row to update
row_to_update = smartsheet.models.Row()
row_to_update.id = 5225480965908356
row_to_update.cells.append(new_cell)
# Update row
sheetId = 3932034054809476
result = smartsheet_client.Sheets.update_rows(sheetId, [row_to_update])
