Power Automate & Smartsheet: Requires 'String' But Is 'Float'
Has anyone who uses Power Automate with Smartsheet run into this issue before? I'm trying to set up a flow where when a sheet is updated, a row in SQL Server is updated. The error I'm getting is:
"ResponseSwaggerSchemaValidationFailure. The API 'smartsheet' returned an invalid response for workflow operation 'Get_a_sheet_data_(dynamic_schema)' of type 'OpenApiConnection'. Error details: 'The API operation 'GetSheetData' requires the property 'body/value/0/Amount' to be of type 'String' but is of type 'Float'.'"
The column in question consists of whole numbers. In Smartsheet, the column type is Text/Number, there doesn't seem to be a text-only type.
Would appreciate any suggestions
Answers
-
Do you have some rows in that column that are numbers and some rows that have text?
That would generally be the issue
-
Thanks for the suggestion. The column consists of whole numbers or blanks, nothing else. I can try and populate the blanks with 0 and see if that makes a difference.
-
@Slowpoke Hi did you find an answer to this? im having the same issue
-
For others with this issue, this is how I solved it. For me, the error was for the "Row ID" column which I have to have for every smartsheet because they don't pass the Row ID through the dynamic content function so you can actually update rows (but I digress). It was giving me specific row number errors and I recopied the row ID's one by one into the field and the errors resolved one by one. But when I replaced them all, it still said the Row ID in row 0 was not formatted correctly. Well I have no way to fix row zero. I also deleted any excess 'blank' rows at the bottom as I know this can sometimes cause issues. Finally, I copied the contents of the column into another blank sheet, deleted the Row ID column, saved, then re-added the Row ID column and recopied the values back in and it immediately started working. I have no explanation, I just know how I overcame it and I hope it helps others that come searching for it.
-
Another way to overcome this limit is to force the value to be a text value - for myself, the column was one where some users entered a number, others were entering text. Because the Smartsheet column treats number values as numbers in the text/number type column, this confused Power Automate. So I left that column alone, added a new helper column and used a JOIN function (joining only the one column, not adding a delimiter) - this turned any value into text.
So the 2 in Parking Note turned into a text value of 2 in Parking INFO. I referenced Parking INFO in my Automate and hide that column in the view for my users.
