SmartSheet number column

[Tjeremiah]

Hi,

I have a Smartsheet I am trying to link to a SharePoint list during the migration process.

I have been able to use 'Get Sheet Data (dynamic content)' and a select to extract my data to dynamic content, however i am struggling to convert a sequential number column to an INT type.

The numbers are stored in the Smartsheet in 4 char format.

0001, 0002, 0003, etc. 

However when I bring these into flow, they are of type STRING. I have tried inside the select card, in a compose card and in a set var card to use the expression int(['my number']). I always return the error below.

I cannot change the format of the number within the Smartsheet, and i need the value to be an int in SharePoint so i can do a mathematical function with it and the list's 'ID' field.

Thanks for the help!

[Expiscornovus]

Hi @Tjeremiah,

Just double checking, but do you have any empty rows and/or empty Seq N columns in your sheet results of the Get Sheet Data action?

I was testing a int conversion solution and I was also able to reproduce it with the Get Sheet Data action. That action returned a couple of empty task rows for me (17,18 & 19). Maybe you have a similar issue?

Normally this approach should work.

[Tjeremiah]

Yes I do have empty rows in that column. I was expecting them to be passed as null values, which is still acceptable in the int type?

[Expiscornovus · Accepted solution]

Hi @Tjeremiah,

As far as I can see in my test with the Smartsheet connector is that whenever a text field is empty it will not be shown at all in the response.

You could check for empty within your select action for the mapping of Unique ID and assign a default number to it. When you do that you should be able to convert all items to an integer in your set variable action. Below is an example of such an expression.

if(empty(item()?['SEQNText']), '0000', item()?['SEQNText'])

Another option would be to filter the items out of the array via an filter array action. Imo that makes more sense because those tasks don't have a unique id.

[Tjeremiah]

I got this working with the filter action. I could then iterate through the array of SEQ No and convert them to int.

The Smartsheet connector was too limited to achieve what I needed, so I ended up exporting the sheet, formatting in Excel and importing as a new list.

There will be no "soft" transition unfortunately....