Using Power Automate to import data into an existing SharePoint list is fairly straightforward, except when the data isn’t very clean. For instance, if a number column contains blank values or some non-numerical garbage, SharePoint connector will flip out.
OpenApiOperationParameterTypeConversionFailed
The ‘inputs.parameters’ of workflow operation ‘Create_item’ of type ‘OpenApiConnection’ is not valid. Error details: Input parameter ‘item/Interest’ is required to be of type ‘Number/double’. The runtime value ‘””‘ to be converted doesn’t have the expected format ‘Number/double’.
OpenApiOperationParameterValidationFailed
The ‘inputs.parameters’ of workflow operation ‘Create_item’ of type ‘OpenApiConnection’ is not valid. Error details: The API operation ‘PostItem’ requires the property ‘item/Interest’ to be greater than or equal to ‘-1.7976931348623157E+308’ but is ‘NaN’.
— SharePoint Connector
The first instinct might be to wrap your data in if(empty()) construct or use clever error handling for invalid data. This approach works unless you’re dealing with a large number of columns of similar “quality”. A workaround is letting SharePoint handle the rogue data by changing the column type to a single line of text, importing the data, then changing it back to a number, which cleans out all invalid entries, leaving only valid numbers.
Cover image courtesy of AI. Ignoring “Denitent Cemmnieagl Datter”, not bad.
