Google sheet auto sort script code needed!

[Taifun]

you are mixing 2 different things...
these blocks from your screenshot look like some blocks from @ABG, which are for doing sql stuff in lists, this is not what you need

your data is stored in a Google Spreadsheet, you access it using gviz as described in the tutorial here

Use gviz to get and query google sheet data

[gvizscreen1] Guide on how to use the google visualization API to get and query "public" google sheet data Use gviz to get and query google sheet data Note: this approach has been knocking around for years, e.g. https://puravidaapps.com/spreadsheet.php, but I have never seen it "properly" explained like this...

the documentation of the query language is here Query Language Reference (Version 0.7)  |  Charts  |  Google for Developers

something like this

select name, number, date, time, sum(oeoplenumber)

will not work... if you want to use aggregate functions like sum, then you have to group the data for all other columns, for example

select name, number, date, time, sum(peoplenumber) 
group by name, number, date, time

Taifun

[Taifun]

@mustafaalbasel

lets try to sort the similar data inside the columns
date
time
cartype
location
arrive
traveltype
insideoroutside
and then get the sum for the similar rows for the peoplenumber

select date, time, carype, location, arrive, traveltype, insideoroutside, sum(peoplenumber) group by date, time, carype, location, arrive, traveltype, insideoroutside

Taifun

[mustafaalbasel]

Hi, thanks all of you for your effores, just do me a favor please as am confused and its my last step in my project,
lets send me a sample aia file showing arranging the list view elements blocks for the tagreted rows i show you before as below
lets try to sort the similar data inside the columns
date
time
cartype
location
arrive
traveltype
insideoroutside
and then get the sum for the similar rows for the peoplenumber

Arranging the list view elements something like that not sure but close

thanks in advance for your help, really appreciate it

[Taifun]

Read again my last 2 answers and start following the gviz tutorial

Taifun

[mustafaalbasel]

yes i tried it let me show you
here is my aia file with my google sheet table

and here is my trying, i tried the same equation you sent

and the example above i didnt mean it but something like by blocks let me show you my point

am getting the data from the listview as below by items and number for items

so i thought if there is a way to sort rows with that blocks using the items and thier numbers
using a block like below

maybe i didn't show my point well but if you can try the aia file i sent and make the query sort working it will be nice from you

[Taifun]

It is cartype and not carype... check your column names

Get a simple query working like select *

See also How to show raw responseContent from your app when debugging

Taifun

[Taifun]

@mustafaalbasel

so i thought if there is a way to sort rows with that blocks using the items and thier numbers
using a block like below

There is no need to sort rows anymore...
You send the correct sql statement to the spreadsheet and already get the sorted and aggregated result back

Taifun

[S.V_Arul_Shiju]

[Free] Google SpreadSheet Extension Fastest Lite Version

This may be the alternative option. But, since you prefer to code, this may not help.

[mustafaalbasel]

thanks it worked when i added columns by as below
select D, E, G, I, J, K, L, sum(F) group by D, E, G, I, J, K, L
but it didn't arrange the date lets see below

[Spicy_Topics]

Test this url,

"A1:L"

[Taifun]

@mustafaalbasel

select D, E, G, I, J, K, L, sum(F) group by D, E, G, I, J, K, L
but it didn't arrange the date lets see below

Don't store the date in text like 6january or 19june use a sortable text format like YYYY-MM-DD which includes year month and day to get a reasonable result also at the end of the year

2024-02-06
2024-06-19

To sort your data add additionally an ORDER BY clause, for example

select D, E, G, I, J, K, L, sum(F) group by D, E, G, I, J, K, L order by D,E,G,I,J,K,L

Taifun

[mustafaalbasel]

thanks so much its arranging the date now but the time is still no so which format needs to be as well and what about the other texts if they are arabic, will it arrange the text with the similar texts so its familiar with English only?

[S.V_Arul_Shiju]

Please share the .aia file at the end. My project also need part of this. Thanks.

[Taifun]

@mustafaalbasel

thanks so much its arranging the date now but the time is still no so which format needs to be as well

A datetime format would be best, i.e. YYYY-MM-DD HH:MM

@mustafaalbasel

what about the other texts if they are arabic, will it arrange the text with the similar texts so its familiar with English only?

I do not know... sorting should also work for arabic texts... test it and let us know if it works

Taifun

[mustafaalbasel]

Sure will do and let you know, one last thing please how can we add these data in the listview
and make each row separated from the query after sorting same as I am getting the data from the googlesheet in the screenshot above?

[Taifun]

@mustafaalbasel

how can we add these data in the listview
and make each row separated from the query

Yes, you can do it like this
Taifun

[mustafaalbasel]

thanks , the listview is working
I am currently trying with both arabic and english to see if its working for both or english only and will let you know
i just wanna fix the equation
select B, C, D, E, F, G, H, I, J, K, L, sum(F) group by D, E, G, H, I, J, K, L order by D, E, G, I, J, K, L
i wanna show columns B, C, D, E, F, G, H, I, J, K, L but sort and aggregate by D, E, G, I, J, K, L

[Taifun]

@mustafaalbasel

but sort and aggregate by D, E, G, I, J, K, L

What is the content of columns B, C, H?
You have ro group all columns except of those you use aggregate functions
You have sum(F), so if you do not want to aggregate B, C and H, you have to use one of the aggregate functions for these columns, too

From the documentation Query Language Reference (Version 0.7)  |  Charts  |  Google for Developers

Group By

The group by clause is used to aggregate values across rows. A single row is created for each distinct combination of values in the group-by clause. The data is automatically sorted by the grouping columns, unless otherwise specified by an order by clause.

Note: If you use a group by clause, then every column listed in the select clause must either be listed in the group by clause, or be wrapped by an aggregation function.

Taifun

[mustafaalbasel]

B, C, F, H
Name, Number, People number and travel price
so they should be different to get the aggrerate people number by so i wanna show all columns
B,C,D,E,F,G,H,I,J,K,L sorting by D,E,G,I,J,K,L getting aggregate F

[Taifun]

Name, number (of what? ticket number?) and price do not matter to get the sum of people, who travel the same date and same route

One solution is to get the first name. number or price... for example

select min(B), min(C), D, E, F, G, min(H), I, J, K, L, sum(F) group by D, E, G, H, I, J, K, L order by D, E, G, I, J, K, L

Just prepare some example data and let me know, how you like to have the result so we can create the correct query

Taifun