cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Tjeremiah
Helper I
Helper I

SmartSheet number column

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!

Tjeremiah_0-1614591231316.png

Tjeremiah_2-1614591403783.pngTjeremiah_3-1614591502996.png

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Expiscornovus
Super User II
Super User II

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.

View solution in original post

4 REPLIES 4
Expiscornovus
Super User II
Super User II

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?

 

emptyrows_smartsheet.png

 

 

Normally this approach should work.

intconversion_smartsheet.png

Tjeremiah
Helper I
Helper I

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
Super User II
Super User II

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.

View solution in original post

Tjeremiah
Helper I
Helper I

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....

Helpful resources

Announcements
MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MSFTBizAppsLaunchEvent

Experience what’s next for Power Virtual Agents

See the latest Power Automate innovations, updates, and demos from the Microsoft Business Applications Launch Event.

New Super Users

Meet the Power Automate Super Users!

Many congratulations to the Season 1 2021 Flownaut Crew!

Power Platform ISV STudio

Power Platform ISV Studio

ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.

Top Solution Authors
Users online (37,219)