Number values containing 'Apostrophe after being imported from Excel

Greetings All !

looking for some assistance regarding an integration issue I've picked up when importing data from Excel to Smartsheet's using MS FLow , i.e. Excel->MS Flow-> Smartsheet

I am inserting an integer but the value on Smartsheet contains an apostrophe before the number which is not ideal as i am using these values in formulas in the sheet

"host": {

"connectionReferenceName": "",

"operationId": "InsertRow"

"parameters": {

"sheetId": "947...",

"row/Order": 4008274673,

"row/Main": "M3A",

"row/Operation": "10",

"row/Key": "7IN7",

"row/text": "INSPECT",

"row/Description": "EQUIPMENT CHK",

"row/Plan": "2885"

}

Any assistance on this would greatly be appreciated.. Thanks !!

Find more posts tagged with

Accepted answers

Paul Newcome

I can't answer how to get rid of the apostrophe at the beginning of the number when importing the data, but I can say that the VALUE function in Smartsheet will ignore that apostrophe and output a numerical value.

All comments

Paul Newcome

Shiv

Thank you @Paul Newcome , i managed to use the VALUE Function as a work around, i feel this might be an issue on the Power Automate function although we passing an integer into that field and not a string.

jshrum

I had this problem also a month or two ago. We download an HR report every morning and put it in a Smartsheet for a Data Shuttle upload. All of the sudden, I had a ton of formulas "break." Tracking it down, it was because Smartsheet started putting an apostrophe in front of the person's salary amount. I read all the reasons as Smartshees was interpreting the information in a way that it put the apostrophe in there. This process has been in place for over a year, but all of the sudden stopped working. What I found was that my excel file was a .csv as that's what we use in Smartsheets. When I changed the format to .xlsx, it stopped putting the apostrophe in the cells. I can't say whether or not this was fixed, as I just keep using the .xlsx, but it caused a lot of problems. Another example that if something that has been working fine for a long time, and all of the sudden breaks for no reason, it might be because Smartsheets changed something. I had something similar happen earlier this week when my data shuttle workflows all broke. It was because the date and drop down columns had the "Restrict to list values only," or "Restrict to date values only." I had to turn all of them off to get it to work. It was just recognized as being a bug and Smartsheets is working on it. Here's the topic... How to address column restriction error and value does not conform to req for PICKLIST? Both of these situations are good examples to post to the community, and put in a ticket, to list the problem.

PeggyLang

@Paul Newcome
JUST the answer I needed this morning! THANKS!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.