#INVALID OPERATION Caused By Data Import Adding an Apostrophe

Laura McDonald

I have a Nintex Automation Workflow set up in which users fill in a form with numerical data and submit it - the form is basically an end of day inventory count. The form pre-fills all fields to 0s, and users can change the totals if there is more than 0 of any item. Smartsheet doesn't like data importing with blank cells which was originally causing issues, which is why all the cells pre-fill to 0. After the form is submitted, the workflow adds it as a new row to a Smartsheet.

This entire process has been working seamlessly for 5+ months, however on Monday, we started receiving #INVALID OPERATION on all formulas within the Smartsheet. Upon investigation, Smartsheet is now suddenly adding an apostrophe in front of all 0s, to display '0. It only adds an apostrophe when the value is 0, if it's any other number, it flows in fine, no apostrophe. I did test on the form removing the pre-filled 0 and re-entering 0 where the inventory total is 0, but the problem persists.

If we manually delete the apostrophe in the Smartsheet, the formulas work again. However, this is a workflow that runs daily on 29 sheets with approximately 25 columns of data per sheet. It is not efficient to manually remove the apostrophe each day.

I have been investigating and it appears that a helper formula column could solve the issue, but my concern is that this has been working fine for 5+ months and now it's suddenly adding the apostrophe. Does anyone know why Smartsheet would suddenly start adding an apostrophe, when it hasn't previously? It would be a large amount of rebuild to have to add a helper column to each of the 25+ columns, in 29 separate sheets. We did not make any changes to the Nintex form or the Smartsheet. I have confirmed with Nintex consultants that it is not an issue on their end, the fields are 'Integer' in Nintex, and Smartsheet is adding the apostrophe.

Any ideas?

Bassam Khalil

Hi @Laura McDonald,

Potential Reasons for Sudden Change

1. Smartsheet Update or Bug: Smartsheet periodically updates its platform, which can sometimes introduce new behaviors or bugs affecting how data is processed or displayed. This could have inadvertently changed how numeric values, especially 0s, are handled when imported from external sources.
2. Change in Data Handling: Even without explicit updates on your end, subtle changes in the backend processing of either Smartsheet or Nintex could alter how data is recognized upon import. For example, an update to how Smartsheet interprets data formats could cause it to mistakenly identify '0' as text.
3. Integration Behavior Change: If there has been any update or modification in the Nintex to Smartsheet integration (even something not directly changed by your team), it could result in data being passed differently, leading Smartsheet to interpret 0s as text.

Solutions

1. Contact Smartsheet Support: Given the sudden nature of the issue without any changes on your part, reaching out to Smartsheet support would be a good first step. They might be aware of any recent updates or bugs that could have led to this issue.
2. Helper Column Workaround: While not ideal due to the scale of your operation, using a helper column to convert text '0's back to numeric 0s can be a temporary fix. For instance, if your problematic column is A, you could set up a helper column B with a formula like =VALUE(SUBSTITUTE(A1, "'", "")). This would remove the apostrophe and convert the text to a number, which your formulas can then reference. Unfortunately, this does require adding a helper column for each of the original columns, which is labor-intensive but might be necessary as a short-term solution.
3. Automation Scripting: Depending on your access to developer resources, a script could be developed to run through the API to automatically remove apostrophes from new rows added to your sheets. This would be more efficient than manually adjusting each sheet every day.
4. Review Nintex Workflow: Although the Nintex consultants indicated the issue isn't on their end, it might be worth revisiting the workflow settings to ensure that the data type being passed to Smartsheet hasn't been inadvertently altered or is being interpreted differently due to changes on Smartsheet's side.
5. Monitor for Updates: If this issue is due to a recent update from Smartsheet, it's possible they might roll out a fix in a near future update, especially if this is affecting multiple users.

Bassam Khalil

Hi @Laura McDonald,

Potential Reasons for Sudden Change

1. Smartsheet Update or Bug: Smartsheet periodically updates its platform, which can sometimes introduce new behaviors or bugs affecting how data is processed or displayed. This could have inadvertently changed how numeric values, especially 0s, are handled when imported from external sources.
2. Change in Data Handling: Even without explicit updates on your end, subtle changes in the backend processing of either Smartsheet or Nintex could alter how data is recognized upon import. For example, an update to how Smartsheet interprets data formats could cause it to mistakenly identify '0' as text.
3. Integration Behavior Change: If there has been any update or modification in the Nintex to Smartsheet integration (even something not directly changed by your team), it could result in data being passed differently, leading Smartsheet to interpret 0s as text.

Solutions

1. Contact Smartsheet Support: Given the sudden nature of the issue without any changes on your part, reaching out to Smartsheet support would be a good first step. They might be aware of any recent updates or bugs that could have led to this issue.
2. Helper Column Workaround: While not ideal due to the scale of your operation, using a helper column to convert text '0's back to numeric 0s can be a temporary fix. For instance, if your problematic column is A, you could set up a helper column B with a formula like =VALUE(SUBSTITUTE(A1, "'", "")). This would remove the apostrophe and convert the text to a number, which your formulas can then reference. Unfortunately, this does require adding a helper column for each of the original columns, which is labor-intensive but might be necessary as a short-term solution.
3. Automation Scripting: Depending on your access to developer resources, a script could be developed to run through the API to automatically remove apostrophes from new rows added to your sheets. This would be more efficient than manually adjusting each sheet every day.
4. Review Nintex Workflow: Although the Nintex consultants indicated the issue isn't on their end, it might be worth revisiting the workflow settings to ensure that the data type being passed to Smartsheet hasn't been inadvertently altered or is being interpreted differently due to changes on Smartsheet's side.
5. Monitor for Updates: If this issue is due to a recent update from Smartsheet, it's possible they might roll out a fix in a near future update, especially if this is affecting multiple users.

Andrée Starå

Hi @Laura McDonald

I hope you're well and safe!

The apostrophe is always added when it starts with a 0 because it's interpreted as text.

Did you have it working with leading a 0 before?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

SergeantPup

@Andrée Starå My numbers don't start with zeros and they're still adding the apostrophe at the beginning.

I use power Automate and I can confirm that no matter how I send the value, I get an apostrophe preceding it. I have also parsed it to an integer before sending it yielding the same results.

Here is confirmation of how it's being sent:

Here's what it looks like when it gets in Smartsheet:

and so we're clear that I'm sending this as an integer, here's how it's parsed:

I have burned an entire day looking at this issue following 100 Smartsheet threads and I've been unable to find a solution except a helper column. I shouldn't need a helper column if I'm passing it through as an integer without a preceding zero already. The issue happens also when I pass just a zero through, it's entered in smartsheet as '0.

The root cause of mine is the same as everyone else's, formulas have broken because of the apostrophes being added by the Smartsheet API.

Are there any solutions that don't require a helper column/formula?

Matt Kirby

@Andrée Starå is there any advice on how to overcome this? would changing the column format help at all? Do you know how others have overcome the " ' " when using leading zeros?

Peter Hill

@Andrée Starå This is also an issue for me and started appearing as in the OP earlier this year. I found a few threads online going back years as they did, but my issues are exactly as described by SargeantPup where I have a datamesh set up to pull over 5-digit employee IDs from one sheet to another and it worked flawlessly in formulas for the first year or two until a few months ago when I noticed it adding the apostrophes causing other workflows to break (that was a fun one finding the issue originally). I have not added the helper column in my own case because the datamesh only needs to run the first time so it isn't as large an issue for me with my two sheets as compared to 25+ sheets, so I just have resigned myself to manually entering the cell and deleting the apostrophe but it is an issue I would love to be solved as well. Seems like an issue that shouldn't have ever been a thing in the first place.