cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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 Team
Community Support Team

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 Team
Community Support Team

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
Level 8

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
Level: Power Up

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
Level: Power Up

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
Level: Power Up

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
thirdimage

Power Automate Community User Group Member Badge

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

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Kudoed Authors (Last 30 Days)
Users online (6,025)