Smartsheet is adding a leading apostrophe when writing numeric data.
I am using Python to copy excel data to the Smartsheet. I have an excel column that can be either a number or text/string, and I want to ensure that numeric values are not automatically formatted with a leading apostrophe in Smartsheet.
I noticed that when using the value attribute instead of object_value, the leading apostrophe is not added to numeric data. However, I have specific date columns that require specific formats.
Could someone brief on these attributes (value and object_value) and guide me on when to use each, especially concerning date formatting, number/text?
new_cell = ss_client.models.Cell({'column_id': {column id here},'value': {value here},'strict': False})
new_cell = ss_client.models.Cell({'column_id': {column id here},'object_value': {value here},'strict': False})
Thank You!
Answers
-
Hi @Devi
I notice your question has been here for a little while, so I thought I'd try to help.
I don't know Python but the way you laid out your question, it made me wonder if Bing (ChatGPT) could help.
Here's what it returned as a reply:
In the Smartsheet API,
value
andobject_value
are two different ways to set the value of a cell1.value
: This is the actual value of the cell. If you’re dealing with a number or a string, you can usevalue
to set the cell’s content1. When you usevalue
, Smartsheet will try to guess the type of the value (number, string, etc.)1.object_value
: This is used when you need to specify the type of the value explicitly2. For example, if you have a date or a multi-contact cell, you would useobject_value
2. When you useobject_value
, you also need to specify the type of the object (DATE, MULTI_CONTACT, etc.)2.
In your case, since you’re dealing with a column that can be either a number or a string, using
value
might be causing Smartsheet to misinterpret your numbers as strings, hence the leading apostrophe.If you want to ensure that numeric values are not automatically formatted with a leading apostrophe in Smartsheet, you could try using
object_value
and specifying the type as NUMBER. This should tell Smartsheet to treat the value as a number, not a string.For your date columns that require specific formats, you should use
object_value
with the type DATE. This will allow you to specify the date format.Here’s how you might modify your code:
new_cell = ss_client.models.Cell({ 'column_id': {column id here}, 'object_value': ss_client.models.ObjectValue({ 'objectType': 'NUMBER', # or 'DATE' for date columns 'value': {value here} }), 'strict': False })
Learn more:
- https://smartsheet.redoc.ly/
- https://community.smartsheet.com/discussion/77505/updating-rows-with-multi-contact-cell
- https://developers.smartsheet.com/blog/demystifying-query-parameters-in-the-smartsheet-api.html
- https://stackoverflow.com/questions/22925389/smartsheet-api-populating-data-and-formulas-in-a-sheet
- https://stackoverflow.com/questions/46287904/what-is-the-difference-between-values-and-objects
Hope this helps and that you have a great day,
Jason Albrecht MBA, MBus(AppFin), DipFinMgt
LinkedIn profile - Open to work
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives