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
v-xida-msft
Community Support
Community Support

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

6 REPLIES 6
v-xida-msft
Community Support
Community Support

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

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)

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

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.

 

 

FlowFlow

 SheetsSheets

 

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.

karanshah
Regular Visitor

After spending hours on this, the issues seems to be on Google Sheets' end. I ended up writing a google apps script (script.google.com) which triggers on edit to basically format the cells as number values.

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[1]; //get the sheet number

// Get the cells range
var cell = sheet.getRange("A2:A50");

// Convert them to integer format (removes the leading apostrophe)
cell.setNumberFormat("0");

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (1,327)