decimals and thousands separators

[Albert C]

Hello!
I have managed to extract data from a spreadsheet with this query,
which works great.

http://spreadsheets.google.com/tq?tqx=out:html&tq=select O,C, sum (H)
group by O,C order by O desc limit 20 label sum (H)
"Ventas"&key=tZrsLGPo9h-dPJeCPXs4_ww&pub=1

HOWEVER, I have an issue with localization. Depending on the browser I
use (IE in Spanish, Firefox in English), it swaps dots and commas for
decimal and thousands separators. Is there a way to override the
browser language and always use the English localized settings? (Dot
for decimals, Comma for thousands)

Thank you in advance,

Albert C

[VizGuy]

If you change the output type from html (as you set it here, probably for the example only) back to the default json, you will notice that in the response there are only real values, and no formatted values.

It means that localization is done on the client side, based on your browser settings.

There are two ways to change it:

1. You can change the response from the spreadsheet (or from any other data source that is using the open source java library) by adding the format clause to the tq parameter like in the bold part at:

http://spreadsheets.google.com/tq?tq=select%20O,C,%20sum%20(H)group%20by%20O,C%20order%20by%20O%20desc%20limit%2020%20label%20sum%20(H)"Ventas"%20format%20sum(H)%20'0.00'&key=tZrsLGPo9h-dPJeCPXs4_ww&pub=1

Note that you can set the format to be any valid formatting pattern (see more details in the query language docs).

2. If you use javascript, you can set the formatted values on the client itself, by manually set them in the data table or by using any one of the formatters described at http://code.google.com/apis/visualization/documentation/reference.html#formatters (see specifically NumberFormatter in your case).

Both of these are ways to control the formatted values. Note that some charts and visualizations ignore the formatted values and format the values internally. Usually these ones have ways to control the formatting specifically.

Regards,

VizGuy

--

You received this message because you are subscribed to the Google Groups "Google Visualization API" group.
To post to this group, send email to google-visua...@googlegroups.com.
To unsubscribe from this group, send email to google-visualizati...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/google-visualization-api?hl=en.

[Albert C]

Hi VizGuy... Thank you so much for your prompt reply!

I used Option 1 mentioned above, since I am not using Javascript. I'm
actualy using output type CSV, as this query is used to feed an excel
workbook. I used the formatting clause you mention, and cool as it is
(I had not been able to use it previously), it still does not specify
the decimal separator to be the dot, which is what I need for the CSV
file to work correctly. Any other advice you could give me? Perhaps
some way to specifically get the decimal separator to be dot?
(specified within the URL)

Thanks in advance,

Albert C.

On Dec 7, 7:43 am, VizGuy <viz...@google.com> wrote:
> If you change the output type from html (as you set it here, probably for
> the example only) back to the default json, you will notice that in the
> response there are only real values, and no formatted values.
> It means that localization is done on the client side, based on your browser
> settings.
>
> There are two ways to change it:
>
> 1. You can change the response from the spreadsheet (or from any other data
> source that is using the open source java library) by adding the format

> clause to the tq parameter like in the bold part at:http://spreadsheets.google.com/tq?tq=select%20O,C,%20sum%20(H)group%2...)
> "Ventas"%20*format%20sum(H)%20'0.00'*&key=tZrsLGPo9h-dPJeCPXs4_ww&pub=1

>
> Note that you can set the format to be any valid formatting pattern (see
> more details in the query language docs).
>
> 2. If you use javascript, you can set the formatted values on the client
> itself, by manually set them in the data table or by using any one of the

> formatters described athttp://code.google.com/apis/visualization/documentation/reference.htm...

> specifically NumberFormatter in your case).
>
> Both of these are ways to control the formatted values. Note that some
> charts and visualizations ignore the formatted values and format the values
> internally. Usually these ones have ways to control the formatting
> specifically.
>
> Regards,
> VizGuy
>

> On Mon, Dec 7, 2009 at 7:17 AM, Albert C <alberto_cat...@yahoo.com> wrote:
> > Hello!
> > I have managed to extract data from a spreadsheet with this query,
> > which works great.
>

> >http://spreadsheets.google.com/tq?tqx=out:html&tq=selectO,C, sum (H)

> > group by O,C order by O desc limit 20 label sum (H)
> > "Ventas"&key=tZrsLGPo9h-dPJeCPXs4_ww&pub=1
>
> > HOWEVER, I have an issue with localization. Depending on the browser I
> > use (IE in Spanish, Firefox in English), it swaps dots and commas for
> > decimal and thousands separators. Is there a way to override the
> > browser language and always use the English localized settings? (Dot
> > for decimals, Comma for thousands)
>
> > Thank you in advance,
>
> > Albert C
>
> > --
>
> > You received this message because you are subscribed to the Google Groups
> > "Google Visualization API" group.
> > To post to this group, send email to
> > google-visua...@googlegroups.com.
> > To unsubscribe from this group, send email to

> > google-visualizati...@googlegroups.com<google-visualization-api%2Bunsu...@googlegroups.com>
> > .

[Wynand Lens]

Hi Guys

Isn't there a way to read "1 000" as "1000" in Google sheets?

I've got hundreds of data entries, and Google sheets gives me the error:

"Function ADD parameter 1 expects number values. But '22 166.19' is a text and cannot be coerced to a number."

CERTAINLY it can be coerced to a number if you just ignore the thousand separator.

So is there a way I can get sheets to ignore the space for the thousand separator?

Also, why is there so little traffic about this problem online?