Updating cell format using Smartsheet API (Python

2019-07-25 17:29发布

问题:

I'm trying to write both values and display formatting to a smartsheet. I access the sheet with:

import os
import re
import logging
import smartsheet
file_pssw = open('path2accesstoken/smsht.txt','r')
aux = file_pssw.readlines()
access_token = re.findall(r"'(.*?)'", aux[0].strip())[0]
#ID of your sheet to update
sheet_id = XXXXXXXXXXXXXXXX
# Initialize client
ss = smartsheet.Smartsheet(access_token)
# Load entire sheet
sheet = ss.Sheets.get_sheet(sheet_id, include=['format'],page_size=206)

I am then able to access the format of a particular cell with

In[45]: sheet.rows[48].cells[3].format
Out[45]: ',,,,,,,,,22,,,,,,'    

According to the Smartsheet API documentation and the Python SDK source code, it seems like the following code should successfully change the formatting of this cell (it at least makes a successful PUT request):

In[46]: sheet.rows[48].cells[3].format = ',,,,,,,,,22,,,,,3,'
        result = ss.Sheets.update_rows(sheet_id, [sheet.rows[48]])

However, if I then reload the sheet and check the formatting of this cell:

In[47]: sheet = ss.Sheets.get_sheet(sheet_id, include ['format'],page_size=206)
        sheet.rows[48].cells[3].format
Out[47]: ',,,,,,,,,22,,,,,,'

The cell formatting is unchanged (this is confirmed by checking the sheet through the UI).

Is this functionality not included in the Python SDK? If it is, how do I access it? If it isn't, is the best approach to use requests and the json package to build the request?

回答1:

This is adapted from the Update Rows example in the docs to include the format attribute. It is important to note that to update a cell's formatting you have to include the value attribute as well. You will want to use the current value of the cell if you don't want to change that.

Note that when building the cell object format is preceded by an underscore. I should also point out that in this process I am constructing a NEW cell object and a NEW row object for the request rather than modifying any existing objects.

# Build new cell value

new_cell = ss_client.models.Cell()
new_cell.column_id = <COLUMN_ID>
new_cell.value = 2
new_cell._format = ",,,,,,,,27,,,,,,,"

# Build the row to update

new_row = ss_client.models.Row()
new_row.id = <ROW_ID>
new_row.cells.append(new_cell)

# Update rows

updated_row = ss_client.Sheets.update_rows(
<SHEET_ID>, # sheet_id
[new_row])