cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

Data type getting changed in Google Sheets insert row action

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:

 

run history.png

8 REPLIES 8
Highlighted
Community Support
Community Support

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:
4.JPG

 

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

 

7.JPG

 

 9.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.
Highlighted

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.

Highlighted

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

Highlighted

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?

Highlighted
New Member

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

Highlighted
New Member

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

Highlighted

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:

Screenshot 2020-06-15 at 10.44.09.png

The row in Google Sheets:

Screenshot 2020-06-15 at 10.44.45.png

Highlighted

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.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

Users online (9,077)