Formatting of number columns may misrepresent values

Report a Bug
1

My App/Pages support link:

  • (private app)

Describe the bug:

  • If my source data is formatted as currency (e.g. $#,##0), Glide seems to interpret numbers as thousands (because of the comma? Markdown?). I was okay with this until my Google Sheets data mixed thousands of dollars with hundreds of dollars. The first, say $5,000, shown on the chart as “5”, but the second, say $193 showed as “193”, a taller bar not a shorter bar. So the values represented were off by an order of magnitude.

The workaround I found was formatting in Google Sheets as $0,000, so $193 is displayed as $0,193, then my data charts correctly.

Alternatively if I force everything to 0 formatting, my chart has Y axis labels as 5000 and 10000 which looks less elegant.

image

Expected behavior:

  • Glide should ignore source data number formatting, OR provide display formatting natively (adding complexity).

How to replicate:

  • Try and chart the following values:
    Apples 2,500 (formatting)
    Oranges 2500
    Pears 500

“Apples” bar will appear as 2.5, not 2500

2

This is a similar thread. Essentially numbers with currency are getting converted to text at some point, which treats the comma as a decimal. I think it’s specific to using the currency symbol. Maybe check to see if your column is set as a number column instead of text. In reality, you shouldn’t apply formatting in the google sheet and instead apply it in glide. If your column is a numeric column, then you can set precision, thousands separator, and currency symbol directly in the column configuration.

2 Likes
3

Thanks Jeff.

I format a lot in Sheets, for example 0.0," MW" when I have a value in kW like 285,000 kW I want to display as 285 MW. Takes the units guesswork out of seeing a plain number.

Now that I know, I think I can work around it. I’m just surprised, since Sheets considers the cell as a value no matter what the formatting is. It’s not like I’ve turned into text such as TEXT(a2, “$#,##0”) or anything. Perhaps it’s the ‘feature’ of Glide supporting Markdown syntax.

Thanks!

1 Like
4

Yeah, I’m not sure if it’s a recent change or something that’s just randomly come up twice now. Some countries do use comma’s as their decimal point, so to some degree I can understand what’s happening, but it’s still weird when it happens in countries that use periods as decimal points. Something getting lost in translation between column types.

2 Likes
5

I think as a general rule, it’s a bad idea to apply formatting to your data in a connected Google Sheet, and instead just treat it as a raw data source (in the same way you would with say a PSQL database driving a Rails front end).

Glide has plenty of options for display formatting, so it’s better to use these instead.

My understanding is that Glide does not see any formatting in your sheet anyway, and only looks at the raw data. Then makes a best guess at the data type based on the data. It doesn’t always get it right, so it’s always a good idea to check and set the column types in the Glide Data Editor after adding any new Sheets/Columns.

2 Likes
6

Thanks Darren.

Two things:

  • Glide absolutely looks at the formatting of data in Sheets, even if it’s a value and the formatting is an overlay. That’s why I’m having this issue.
  • As for keeping all my data in Sheets raw, that’s good for Glide’s use, not for the other uses I have for the data in Sheets. If I wanted the data only in Glide, I’d use native storage.

So I say again, this is a bug. Glide should ignore source formatting. It’s saved in Sheets as a value (e.g. 2500.13, but displayed as $2,500.13 in English Canada), use the value.

And it’s not just currencies. My 2,500 KW DC was displayed as 2.5 KW DC, whereas 800 KW DC is displayed as 800 KW DC. The comma is messing with the order of magnitude even though it’s not saved in the cell value.

7

Since we don’t work for for glide, I can only provide suggestions to get you up and running, but since you will view it as a workaround, it’s up to you if you want to go that route. I’m usually more focused on the end result no matter what it takes, as opposed to waiting for what may or may not be a bug to be fixed. If things change down the road, then I can always revert as needed.

In general, databases are designed to store raw data, and work best when data is stored as raw data with the appropriate data type. When you start mixing formatting within that data, then things get hairy because the data no longer matches the data type. In google sheets you are applying formatting on top of the raw data at the sheet level, before it even makes it to Glide’s copy of the database. Glide only sees that formatted data, so it no longer knows what the underlying data type is and only sees it as a string of text. It’s that extra hop from database to database that is non-standard in normal database driven applications.

As a programmer by trade, I can say that it can be a bit of a nightmare to convert between different data types. Formatting is normally applied AFTER the data has been retrieved from the database. Converting between text strings, numeric, dates, boolean, binary blobs, and other data types isn’t as straight forward as you may think. Add to that, some systems store numbers in different and multiple ways, such as zoned, packed, float depending on how you want to utilize memory storage. The fact that you have a mix of alpha characters mixed with numbers makes it hard for the code to decipher that text properly as a true number.

Again, I really don’t know if this is a new development, but I can partially understand what’s happening. It could be a nuance of the underlying code base, but on the other hand, maybe it is some of Glide’s own code that’s handling the conversion. I just don’t know. I haven’t taken adequate time to research if it’s expected behavior or not. Data type conversions are usually core to the underlying code base, so it shouldn’t be too reliant on Glide’s code. I’m just purely guessing based on my own experience.

If I were to guess, I would say it’s not a straightforward fix on Glide’s behalf. :man_shrugging: However it should be relatively easy to work around it with a couple of extra computed columns.

2 Likes
8

Yes and no.
Please observe the following:

This is why I said:

2 Likes
9

Thanks for going to the trouble of that Darren. Please read everything I wrote. Try a) using a mix of dozens and thousands formatted - the latter of which, in US / Canada, is a comma. Then b) try and use that column with mixed orders of magnitude, in a chart. The items under 1000 will show as they are. The items over 1000, such as 25,500, will show as 25.5. Try it.