Error in Gviz Sum pulled from Google Spreadsheet, extra numbers in the result

[Alisson_Silva]

Friend

I have this error in this code

Can someone help?

I’m doing it right

he follows

gviz/tq?tqx=out:html&gid=0&tq=Select SUM(B) where A>=date’2025-03-29’and A<=date’2025-04-04’

[Taifun]

Try the format clause

Query Language Reference (Version 0.7)  |  Charts  | ...

Taifun

[Alisson_Silva]

Sorry

I don’t understand…

any examples…

please

[Taifun]

@Alisson_Silva

Select SUM(B) where A>=date’2025-03-29’and A<=date’2025-04-04’

Select SUM(B)
where A>=date’2025-03-29’and A<=date’2025-04-04’
format SUM(B) ‘#,##0.00’

Format

The format clause is used to specify a formatted value for cells in one or more columns. The returned data should include both an actual value and a formatted value for each cell in a formatted column. Many visualizations use the unformatted value for calculations, but the formatted value for display. The patterns that you specify in this clause are usually returned in the pattern property of the corresponding columns.

Pattern Syntax:

number, date, timeofday, datetime
The date and number patterns defined by the ICU.
boolean
Pattern is a string in the format ‘value-if-true:value-if-false’.

Example:

format salary ‘#,##0.00’, hireDate ‘dd-MMM-yyyy’, isSenior ‘Yes!:Not yet’

Taifun

[Alisson_Silva]

just error

{“version”:“0.6”,“reqId”:“0”,“status”:“error”,“errors”:[{“reason”:“invalid_query”,“message”:“INVALID_QUERY”,“detailed_message”:"Consulta inválida: PARSE_ERROR: Encontrado \u0022 \u003cUNEXPECTED_CHAR\u003e \u0022\u2018 \u0022\u0022 na linha 1, coluna 77.\nEstava esperando um dos seguintes:\n \u003cSTRING_LITERAL\u003e …\n \u0022*\u0022 …\n \u0022+\u0022 …\n \u0022-\u0022 …\n \u0022/\u0022 …\n \u0022%\u0022 …\n "}]}

answer should be like this

126.3

[Taifun]

Try the UriEncode block from the web component to encode the query and show us a screenshot of the query including Do it result

Taifun

[Taifun]

Alternatively you also can use your existing query and the format decimal block from the math drawer

Taifun

[Alisson_Silva]

Sorry

nothing more relevant to show

everything went wrong

still trying to fix this query

thinking…

[Taifun]

@Alisson_Silva

thinking…

just keep on thinking and trying
you also can

@Taifun

use your existing query and the format decimal block from the math drawer

and if you want help, then

@Taifun

Try the UriEncode block from the web component to encode the query and show us a screenshot of the query including Do it result

Taifun

[Still-learning (1-2-3-4...)]

This is known one in gviz sum(), though your values are in proper decimal

Anyhow try this method

SELECT%20TO_TEXT(ROUND(SUM(B)%2C%201))%20WHERE%20A%20>=%20date%20'2025-03-29'%20AND%20A%20<=%20date%20'2025-04-04'

This method may work in both html as well as in csv method of calling in the gviz.

[Alisson_Silva]

Nothing done

researching…

out:html&gid=0&tq=Select to_text(ROUND(SUM(B),1)) WHERE A>=date’2025-03-29’AND A<=date’2025-04-04’

[Still-learning (1-2-3-4...)]

Well i have tested with my ghseet in just SUM (B) and it throws proper value

https://docs.google.com/spreadsheets/d/1dfS8p0H0DyXyde0hItfu1EfJXJrdmBo1duTOL3UC5Qw/gviz/tq?tqx=out:html&sheet=Sheet1&tq=SELECT%20SUM(B)%20WHERE%20A%20%3E=%20date%20%272025-03-01%27%20AND%20A%20%3C=%20date%20%272025-04-04%27

and teh gsheet is

else you set the col B to double digit fraction

https://docs.google.com/spreadsheets/d/1dfS8p0H0DyXyde0hItfu1EfJXJrdmBo1duTOL3UC5Qw/gviz/tq?tqx=out:html&sheet=Sheet1&tq=SELECT SUM(B) WHERE A >= date ‘2025-03-01’ AND A <= date ‘2025-04-04’

[Alisson_Silva]

That’s right
with just one number with , it works
but when there is more than one number the error appears

add another number with decimals
13.4

[Still-learning (1-2-3-4...)]

click

[Alisson_Silva]

@Alisson_Silva

That’s right
with just one number with , it works
but when there is more than one number the error appears

add another number with decimals

In this case it is a sum of the number of kilometers traveled by a truck
With this there are more numbers with decimals that I need to have to make the payment calculation

tqx=out:html&gid=0&tq=Select SUM(B) where A>=date’2025-03-29’and A<=date’2025-04-04’

[Still-learning (1-2-3-4...)]

ok in such canse instead of calling them as html , pls call them as csv format where you can get proper value

see this

HTML

csv Query

however you will be working in kodular with csv format only they why should worry about? in json ,method also we are getting floating value

[Alisson_Silva]

I think the error is in the formatting of the column in Google Sheet

I created another column and everything came out fine…

I’ll do some more research…

[Taifun]

See also here

Floating point iterations fail

Taifun

[Alisson_Silva]

Nothing more

no answer yet

the first one with a date that is correct your answer for me
the second one with another date is still wrong, why would it be

gviz/tq?tqx=out:html&sheet=Relatorio&tq=Select sum(B) where A>=date ‘2025-03-29’ and A<=date ‘2025-04-07’

gviz/tq?tqx=out:html&sheet=Relatorio&tq=Select sum(B) where A>=date ‘2025-04-01’ and A<=date ‘2025-04-07’

[Alisson_Silva]

I think I got it

I followed some steps and it worked

Here is the result

include a decimal in your result

that’s what I was hoping for

testing now

@Still-learning
@Taifun