Data type getting changed in Google Sheets insert row action

[AndyCoder]

I built a flow that receives an http request with a JSON body and the following schema:

{
  "type": "object",
  "properties": {
    "Name": {
      "type": "string"
    },
    "Timestamp": {
      "type": "string"
    },
    "Temperature": {
      "type": "number"
    },
    "LightIntensity": {
      "type": "number"
    },
    "SoilMoisture": {
      "type": "number"
    },
    "SoilFertility": {
      "type": "number"
    }
  }
}

The numbers are really parsed as numbers, since they work in conditions.

But when I use this data to insert a row in Google Sheets the numbers are converted to strings (starting with an apostrophe) and they have to be converted to numbers manually in Google Sheets afterwards.

{
  "Timestamp": "06.05.2018 12:02:18",
  "Name": "Palme",
  "Temperature": "23",
  "LightIntensity": "375",
  "SoilMoisture": "14",
  "SoilFertility": "124"
}

Why don't numbers get passed as numbers into Google Sheets?

Here is the history of a run:

[v-xida-msft]

Hi @AndyCoder,

Could you please share a bit more about your issue?

Could you please show a bit clear about your flow's run history?

I have made a test on my side and don' t have the issue that you mentioned. Based on these Number type property (Temperature, LightIntensity, etc.), no matter what data (String data or Number data) you passed for these properties, the "Insert row" action would insert these data into your Google sheet as text data.

I have made a test on my side and my flow's configuration as below:

When you passed Number value for these Number type property, the "Insert row" action works successfully as below:When you passed String value for these String type property, the "Insert row" action works successfully as below:

 

Best regards,

Kris 

[AndyCoder]

Thanks for you reply.

It seems to me that you are experiencing exactly the same as I did.

Whatever data type is used, it ends up as string in Google Sheets. At least from your Google Sheets screenshots the numbers look like strings, as they are left aligned. Or is it possible in your sheet to use the average formula to calculate the average of column?

I had to format those columns as numbers before Google Sheets allowed some calculation for them.

So I would like that numbers are inserted as numbers in Google Sheets.

[chieffish]

Andys totally correct, from the screen shots it looks like you have the same issue. Double click the cell and you will be able to see the apostraphe

Exactly the same issue here. Doesn't matter if the number is formatted as an Integer or Float in MSFlow, the result in Google Sheets is as a text

[Supergeek]

I am facing the issue and really dont know what to do. My number field from Cognito Form is flowing in the google sheets as text and I don't know what is the solution!

Can someone help?

[degan]

Same problem here, getting a single quote in front of all data in Google Sheets

[ffarrell]

I solved this by adding a script to https://script.google.com/home to change the column format to Number every time there was a change on the sheet. The funtion I used was

/** @OnlyCurrentDoc */

function MakeColBnumeric() {
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getRange('B:B').activate();
spreadsheet.getActiveRangeList().setNumberFormat('#,##0.00');
};

You should be able to modify this for your own purposes

[BartJR]

Hello everyone,

has anybody been able to find a solution for this? I get the same issue, whenever I try to add a number to a cell in Google Sheets, an apostroph is put before the actual number.

The insert row action in Power Automate:

The row in Google Sheets:

[BartJR]

I wanted to share how I solved this. 

In Google sheets, I created a new sheet, in which Sheet2!A:A = Sheet1!A:A * 1. This automatically changes the format of the cell in sheet 2 to a number. 

I then used the numbers in sheet 2 to work with.