Logic app - Azure adding apostrophes while inserting row into GoogleSheets

Question

Hello,

I am experiencing the issue with azure logic app connector to Google Sheet.

I am processing data in azure and when it is done I would like to insert those data into GoogleSheet. But anytime I insert the row to GoogleSheet the azure is automaticly adding the apostrphes to the data.

Here you can see as I am inserting the row with specific columns:

The column "Částka s DPH" is a number in format like this: 1889

But when the connector insert the data into GoogleSheet into column which is a automatic format as on picture bellow:

It always insert the number with apostrophe like this:

I think it is because of the format number and that my GoogleSheet is set to czech language, but I can not find how to fix the apostrophe and also let my setting in czech language.

Could you please help?

Answer 1

Hello @Azure - Thanks for getting back to us here and providing additional info. It looks like the addition of the leading apostrophe to number values is a known formatting feature with programs like Google Sheets and MS Excel. By adding an apostrophe in front of certain numeric values such as 000123 the value becomes a data type of string and users are then able to preserve the 000 which would normally be stripped out automatically. Here's an example of a blog post that discusses how the addition of apostrophes can be helpful: https://spreadsheetpoint.com/keep-leading-zeros-in-google-sheets/

Based on the screenshot you've provided showing the Input/Output data, the reason why your Logic Apps workflow is being affected by the formatting feature is, perhaps, the number values are wrapped in double quotes (""), for example:

{
    ...
    "Částka_x0029_s_x0029_DPH":"987",
    ...
}

Instead, you'll want to try to change it such that the integer values are not wrapped in double quotes (""):

{
    ...
    "Částka_x0029_s_x0029_DPH":987,
    ...
}

It looks like you're mapping each value as Dynamic Content so you'll just probably need a small inline conversion from string to an integer. You can do so by using the int() function: https://learn.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference#int

In addition to the above, and if the above doesn't do the trick, I would try enforcing custom formatting within the Google Sheets as well.

I hope this helps, please let me know if you need further assistance with this issue.