cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
AndyCoder
Level: Power Up

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

6 REPLIES 6
Community Support Team
Community Support Team

Re: Data type getting changed in Google Sheets insert row action

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.
AndyCoder
Level: Power Up

Re: Data type getting changed in Google Sheets insert row action

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
Level: Powered On

Re: Data type getting changed in Google Sheets insert row action

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
Level: Power Up

Re: Data type getting changed in Google Sheets insert row action

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
Level: Power Up

Re: Data type getting changed in Google Sheets insert row action

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

ffarrell
Level: Power Up

Re: Data type getting changed in Google Sheets insert row action

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

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Kudoed Authors (Last 30 Days)
Users online (5,445)