How to prevent Smartsheet API from converting certain strings to integers

Vani Narayanan

Hi all,

I am not sure if Smartsheet recently changed how strings and integers are formatted, but my API script has seemingly randomly began converting certain string values to integers automatically. For example, a string that leads with 0s such as "0450" is now being put into the smartsheet as just 450 and it shows up as a numerical value.

Any ideas on why this might be the case?

I attached a text file with my updateRowToSheet method as well as an example of a string value that is showing up as an integer in smartsheet. I looked at the API documentation https://smartsheet.redoc.ly/tag/cellsObjects and tried adding in columnType and displayValue as types string, but that did not work. All the code in the text file is in python.

updateRowToSheet.txt

Thanks!

Lee Joramo

I have not had to deal with this issue via the API, but here are a few suggestions from best to worse:

• Try forcing the value to be a string in the python code: 'value': str(rowToAppend[x])
• Try prefixing the number with a single quote '. For example: 'value': "'"+str(rowToAppend[x]) The single quote should force the value to be interpreted as a string, and should be otherwise ignored
• Try using a "Contact Column" instead of a "Text/Number" Column, this will preserve leading zeros although it can cause some confusion about the data type.
• Try adding a space before or after the string (this will of course change the actual value, but in some cases that is ok.

Also, are you actually setting "displayValue" to be "string". That should be set to what you actually want to show. So try: 'displayValue': str(rowToAppend[x]),

rschene

Smartsheet Support also notified me on Feb 12, 2024 that this had been corrected. Leading zeros are no longer being dropped.

Specifically, the problem was a Power Automate flow that was inserting data to Smartsheet. The flow showed a value such as 0123 but the value appeared in Smartsheet as 123 in a Text/Number column type. I assume the Smartsheet API is being called between Power Automate and Smartsheet. Thankfully this has been corrected by Smartsheet and 0123 is now appearing.

Maybe the change will take some time to replicate to the others that are still having issues.

Lee Joramo

I have not had to deal with this issue via the API, but here are a few suggestions from best to worse:

• Try forcing the value to be a string in the python code: 'value': str(rowToAppend[x])
• Try prefixing the number with a single quote '. For example: 'value': "'"+str(rowToAppend[x]) The single quote should force the value to be interpreted as a string, and should be otherwise ignored
• Try using a "Contact Column" instead of a "Text/Number" Column, this will preserve leading zeros although it can cause some confusion about the data type.
• Try adding a space before or after the string (this will of course change the actual value, but in some cases that is ok.

Also, are you actually setting "displayValue" to be "string". That should be set to what you actually want to show. So try: 'displayValue': str(rowToAppend[x]),

Lee Joramo

You said that this issue was recent change that affected a previously working API, and I mentioned the adding an single quote (or apostrophe) in front of the number. I just read another form post that indicates there has recently been a change in how the API deals with this. Or it is a new bug. See the reply by @Lindsay Whitbread

https://community.smartsheet.com/discussion/comment/412589/#Comment_412589

Also @Genevieve P. of Smartsheet replied in a discussion that the API distinguishes between a number 000123 and a string "000123"

https://community.smartsheet.com/discussion/comment/348141/#Comment_348141

Lindsay Whitbread

Around the 18th of January, the final fix was put through to reverse the behaviour, however, the behaviour around strings and numbers was not returned back to 100% the same as before the issue was introduced on the 4th of January.

Try using an apostrophe at the start of the string, but we've had variable results when doing that.

Vani Narayanan

Hi @Lee Joramo @Lindsay Whitbread thanks for the replies

So originally @Lee Joramo had the suggestion of just simply doing forcing the rowToAppend to be a string with the str() command to get str(rowToAppend[x]). That worked for a few weeks, but yesterday Smartsheet must have decided to put through more changes because it is failing again. I tried doing 'value': "'"+str(rowToAppend[x]) but every cell now has a ' character in front of it changing the value. The apostrophe was there when it was working but only when you actually clicked on the cell, now it is just apart of the value straight up. Strange Smartsheet keeps messing with the API and cell logic

Lindsay Whitbread

We've had the same experience regarding the apostrophe actually displaying. What's happening is that the data is being written as a double apostrophe.

I agree it's frustrating, I highly recommend putting a support case in to create visibility that the changes that are being made by Smartsheet are not quite hitting the mark.

Obviously Smartsheet are wanting to create a good user experience so it helps them to raise issues through support (we can't be sure they are able to keep on top of the community discussions).

Martin Wijaya Hermanto

Hi Smartsheet Support,

I have the same issue. Can you please revert back to the original behaviour because with the current behaviour there is no way to write something simple like 0010 as text into smartsheet using API without apostrophe.

Adding apostrophe as suggested does not resolve the issue as the apostrophe itself will be part of the value and it will be considered different values. This have broken many of my cross reference formulas in other sheets

Can you please help rectify this issue urgently.

Thank you so much

Henry O

Hello, we are currently struggling with the same issue. Unable to get something as simple as a NJ zip code into a Smartsheet using the API. either the leading 0 is cut off completely, or we get a double apostrophe and data looks like '07710 in the Smartsheet cell.

Dina K.

We are experiencing the same issue and has left us with over 100 solution sheets impacted and dead in the water due to erroneous indexing. We use Site # with preceding zeros and we have the double apostrophes as well. I put in a support case but no word yet.

Did ANYONE see any communications this API update was happening? We did not get any notifications, emails or anything???

dchirst0

We also have the same issue here. We implemented the leading apostrophe solution for the time being, but would really like a non-hacky solution where we can keep an integer as a string. Interestingly, for us the integer was really long and so caused an overflow error since it was larger than 2^32

Etienne Mermillod

Hi,

Same issue here...

Currently the best way for to force is the issue is to set a cell by its ObjectValue passing a StringObjectValue.

Debbie Sawyer

ooh! I observed and logged a support call today where a code has been transferring to Smartsheet successfully as 0123 since Oct 23 until 24th Jan 2024. Then between 24th Jan and 5th Feb 2024, something has happened to have the text "convert" to a number on landing in Smartsheet. so the Reference number 0123 (which had been handling fine) is now being converted to a number 123 which is now not acting as a reference to the system which passed the data to Smartsheet.

So a change between 24th Jan and 5th Feb has occurred which has caused us an issue here.

Smartsheet have responded to my support call saying they are investigating it, there is no resolution timeframe.

I'll watch this space.

jrummell

This is affecting an api integration I did for a client. They have tons of "codes" that start with zeros that are being changed and breaks their smartsheet workflow.

Jeanne G.

I am struggling with the same issue.

row: {"columnId":4857304538998660,"value":"001868"

is being written to a smartsheet as 1868. This code has been in place for years without any issues. None of my work arounds seem to be working out for me.

alex_m_data

I am having the same issue and have submitted a ticket to Smartsheet support. Hopefully they fix this soon and I'll update everyone here.

apfautz

I'm having a related issue where I have some values for invoice #s or purchase order #s that appear to be auto converted to numbers resulting in generic api (400) Bad Request failures. Example values are "4281544000070234005" and "23e01989". First issue was possibly Jan 11th. Continuous failures since Feb 3rd. Submitted a ticket as well.