Adding numbers to a row in SmartSheets

[Anonymous]

Hi,

   I'm very new to Power Automate/Flows and I'm trying to build a Flow that takes a table from an Excel workbook and add it as new rows to a SmartSheets sheet. the issues I'm finding it the numbers in the table have a leading apostrophe added when copied in to SmartSheets. This means that SmartSheets can't use the information to create a chart as they are 'seen' as text.

the numbers are formatted as numbers in the Excel workbook and if I copy and paste the values manually, they are pasted as numbers.

I've tried to use the Float expression (the numbers have decimal points) when adding the values to SmartSheets but that gives me the same result. I did look at the data operation but I'm not really sure where to start.

Any advice on how to get the numbers to copy as numbers without the leading apostrophe will be greatly appreciated

[v-litu-msft]

Hi @Anonymous,

I created a simple Flow to reproduce your issue, but not find the same problem, there is no leading apostrophe created in the SmartSheet:

Flow:

 Run result:

Could you please share an instance of Excel table and screenshots of Flow? They could help us assist you better.

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-litu-msft 

   Thank you for replying and i'm sorry it's taken a while for me to get back to you. The information below is dummy data but is in the same format. I've tried to upload some images but I can't seem to get it to work so I'll have to try and explain. The top table below shows how the table in Excel Looks with the top row being set a table Headers, Column 1 formatted as text and the rest formatted as numbers

Column1	APR	MAY	JUN	JUL	AUG	SEP	OCT	NOV	DEC	JAN	FEB	MAR
ACTUAL	-0.30	0.09	0.30	0.09	0.08	0.25
BUDGET	0.02	0.20	0.25	0.35	0.50	0.60	0.75	0.80	1.20	0.90	1.30	1.50

The table below shows how the numbers look in Smartsheets after the flow has copied them across. When looking at the SmartSheet normally the leading apostrophe (') is hidden but shows when you click to edit the cell contents.

Column1

APR

MAY

JUN

JUL

AUG

SEP

OCT

NOV

DEC

JAN

FEB

MAR

ACTUAL

'-0.30

'0.09

'0.30

'0.09

'0.08

'0.25

BUDGET

'0.02

'0.20

'0.25

'0.35

'0.50

'0.60

'0.75

'0.80

'1.20

'0.90

'1.30

'1.50

the issue is that with the apostrophe SmartSheets see's the numbers as text and so won;t use them to generate charts which is the end point of this process. I've seen this before to maintain a leading zero for things like phone numbers but these numbers have a decimal so that shouldn't cause the apostrophe to appear. I've tried formatting the smartsheet incase it is seeing the decimal point as a thousand seperator but that isn;t it either. Aside from this there aren't any more options for formatting in SmartSheets.

in Power Automate I have tried to format the data as XML and JSON incase that was the issue but it was not. I've also tried using the float() expression to force the numbers as floating point numbers as they are all decimals but that didn't work either.

Any thoughts or suggestions would be greatly appricated.

Many thanks

Lewis

[v-litu-msft · Accepted solution]

Hi @Anonymous,

Yes, the leading apostrophe (') is hidden, but if you using Get a sheet data in after insert the data into SmartSheet, the apostrophe won't appear, they just show without any apostrophe.

In the SmartSheet, you could create helper columns and use a VALUE(SUBSTITUTE(........, "'", "")) to remove the apostrophe and convert it into a numerical value.

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-litu-msft ,

   From the image you've shared I'm not sure how to implement that as the data is coming from an Excel spreadsheet and adding the data to a SmartSheets sheet.  I have implemented the second half of your solution with using the extra columns in SmartSheets to remove the leading apostrophe which has sorted out the issue. Thank you for your help with this.