cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Google Sheets - Formatting & Apostrophe in front of numbers/formula

I have data coming in from Microsoft Flow into google sheets from CognitoForms. Is there a workaround to delete the apostophe or format the cell to a number? It wont calculate formulas or identify numbers. 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

Re: Google Sheets - Formatting & Apostrophe in front of numbers/formula

Hi @tiestogene,

 

Could you please share a screenshot of your flow’s configuration?

Further, could you please show a bit more about the numbers/formula?

 

I suppose that the numbers/formula that you want to format is a string and you want to format it as a number, is it true? The data structure of my Cognito form as below:2.JPG

I have made a test on my side and please take a try with the following workaround:

  • Add a “When a new entry is created” trigger.
  • Add a “Compose” action, Inputs set to following formula:

 

concat('a',triggerBody()?['Bonus'],'b')

 

  • Add a “Compose 2” action, Inputs set to following formula:

 

add(indexOf(outputs('Compose'),'a'),2)

 

  • Add a “Compose 3” action, Inputs set to following formula:

 

add(indexOf(outputs('Compose'),'b'),-2)

 

  • Add a “Compose 4” action, Inputs set to following formula:
substring(outputs('Compose'),outputs('Compose_2'),add(sub(outputs('Compose_3'),outputs('Compose_2')),1))
  • Add a “Insert row” action, Bonus field set to Output dynamic content of “Compose 4” action.

Image reference:3.jpg

The flow works successfully as below:4.jpg

 

5.jpg

 

Best regards,

Kris

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

View solution in original post

5 REPLIES 5
Community Support
Community Support

Re: Google Sheets - Formatting & Apostrophe in front of numbers/formula

Hi @tiestogene,

 

Could you please share a screenshot of your flow’s configuration?

Further, could you please show a bit more about the numbers/formula?

 

I suppose that the numbers/formula that you want to format is a string and you want to format it as a number, is it true? The data structure of my Cognito form as below:2.JPG

I have made a test on my side and please take a try with the following workaround:

  • Add a “When a new entry is created” trigger.
  • Add a “Compose” action, Inputs set to following formula:

 

concat('a',triggerBody()?['Bonus'],'b')

 

  • Add a “Compose 2” action, Inputs set to following formula:

 

add(indexOf(outputs('Compose'),'a'),2)

 

  • Add a “Compose 3” action, Inputs set to following formula:

 

add(indexOf(outputs('Compose'),'b'),-2)

 

  • Add a “Compose 4” action, Inputs set to following formula:
substring(outputs('Compose'),outputs('Compose_2'),add(sub(outputs('Compose_3'),outputs('Compose_2')),1))
  • Add a “Insert row” action, Bonus field set to Output dynamic content of “Compose 4” action.

Image reference:3.jpg

The flow works successfully as below:4.jpg

 

5.jpg

 

Best regards,

Kris

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

View solution in original post

anhthuch4ng Post Prodigy
Post Prodigy

Re: Google Sheets - Formatting & Apostrophe in front of numbers/formula

Hi @Anonymous,

 

Have you had a chance to apply @v-xida-msft's suggestions to adapt your Flow?

If yes and you find that solution satisfactory, please go ahead and mark this thread as "Solved". 🙂

 

Happy Flowing,

-AT (Community Admin)

jazzwall
New Member

Re: Google Sheets - Formatting & Apostrophe in front of numbers/formula

Hello, it turns out that the above post is not the solution.  There seems to be a bug in the connector where it is sending numbers with an apostrophe in the beginning despite there not being apostrophe on Microsoft's side.  No amount of data formatting or composing on the Flow (or Logic App) side helps with this.  Please help!

Screen Shot 2018-12-15 at 1.16.03 PM.pngScreen Shot 2018-12-15 at 1.17.21 PM.png

Ollyjb
New Member

Re: Google Sheets - Formatting & Apostrophe in front of numbers/formula

Hello.

I've just hit this same problem with a Cognito to Google Sheet flow row insert. Was driving me mad until I found this post and discovered it seems to be a connector issue not logic I've done.

I have a survey which I'm trying to export in way to match migrated legacy data which then continues with a date.

I'm converting the cognito UTC date to a simplified date format and then inserting into a google sheet. It is always including the preceding apostrophe that I've yet to work out how to post-process and remove in sheets, but ideally it wouldn't be there in the first place.

 

 

flow coginto convert.JPGFlow

 google sheets cognito insert apostrophe.JPGSheets

 

JasonHolt
New Member

Re: Google Sheets - Formatting & Apostrophe in front of numbers/formula

Just started hitting this myself this week. Happens from both a Microsoft Form "Survey" as well as the "Quiz" in Forms Pro when sent using Flow to Google Sheets and Smartsheet. It's something with they way Forms-to-Flow is formatting the numeric fields. I've tried to change the forms to be just a text field; but if the recipient enters only numbers, it gets a leading apostrophe. It doesn't happen with pure text fields, mixed text fields, or date fields...just the numeric fields in the Form. It's breaking a lot of our workflows and financial calculations that depend on the numeric data submitted in the form. We've migrated the forms over to Formstack (which we use for Webmerge anyway) and it doesn't have that problem. Clearly an issue with Microsoft Flow/Forms.

Helpful resources

Announcements
MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

firstImage

New Ranks and Rank Icons released on April 21!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power Automate Community!

firstImage

Now Live: Power Virtual Agents Community!

We are excited to announce the launch of Power Virtual Agents Community. Check it out now!

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Community Summit North America

The top training and networking event across the globe for Microsoft Business Applications

Users online (4,964)