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

[Anonymous]

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. 

[v-xida-msft · Accepted solution]

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:

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:

The flow works successfully as below:

Best regards,

Kris

[anhthuch4ng]

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]

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!

[Ollyjb]

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

 Sheets

[JasonHolt]

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]

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");

[davist]

This issue was solved by message 2, but the issue identified by @jazzwall and @Ollyjb in messages 4 & 5 and not related to the solution. The issue persists at the time of this posting. Possibly a connector issue? Has this been solved elsewhere? Has this been submitted as an issue to Google or Microsoft?