Smartsheet is adding a leading apostrophe when writing numeric data.

Devi

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!

Jason Albrecht

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 and object_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 use value to set the cell’s content1. When you use value, 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 use object_value2. When you use object_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:

1. https://smartsheet.redoc.ly/
2. https://community.smartsheet.com/discussion/77505/updating-rows-with-multi-contact-cell
3. https://developers.smartsheet.com/blog/demystifying-query-parameters-in-the-smartsheet-api.html
4. https://stackoverflow.com/questions/22925389/smartsheet-api-populating-data-and-formulas-in-a-sheet
5. https://stackoverflow.com/questions/46287904/what-is-the-difference-between-values-and-objects