Google sheet auto sort script code needed!

[mustafaalbasel]

i tried auto sort function, on edit and on change and i asked ai for the help but still no auto changes when i send the data from mit app inventor to google sheet
let me show you
am collecting ordersa from customers on the app and then the data is being sent on googlesheet
so i have multiple columns on google sheet needs to be auto sorted, date column, time column so on
so when the new customer send a new order the data should be auto sorted on google sheet so when i back orders to drivers, the drivers can eliminate the similar orders to collect the similar order by numbers to make thier orders completed

[mustafaalbasel]

if you have any other better ideas for sorting the data coimng from google sheet so i will be happy to listen and learn

[mustafaalbasel]

it i couldn't sort the google sheet by the script code
so can you help me to make an equation like this by blocks for the list view elements
if the date is similar, time is similar, location, arrive place, travel type are similar then sum the people num chosen?

[mustafaalbasel]

here is my blocks for the data when get back from the google sheet

so how can use make a new sort for these items

and then after sorting collecting that num

[TIMAI2]

I have built up a simple apps script web app to demonstrate sorting of a sheet, then to return values.

SCRIPT

function doGet(e) {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  var lr = sheet.getLastRow() -1;
  var range = sheet.getRange(2,1,lr,5);
  var fn = e.parameter.FN;

if (fn == "i") {
  range.sort(1);
}
else if (fn =="d") {
  range.sort(2);
}
else if (fn =="n") {
  range.sort(3);
}

SpreadsheetApp.flush();

var vals = range.getDisplayValues();
return ContentService.createTextOutput(JSON.stringify(vals));

}

note the use of SpreadsheetApp.flush(), which ensures all operations on the spreadsheet are completed before running the next command in the script.

SHEET

You supply the script with a single parameter value for FN, either i to sort by the id column, d to sort by the date column, or n to sort by the name column. The url you would send should look like this:

https://script.google.com/macros/s/AKfycbzqR37kVh...AUC_Cliaim_vjA_w1/exec?FN=i

The script returns a stringifed json of values from the spreadsheet, which you need to convert back into an AI2 list using the JsonTextDecode block from the web component

You can, of course, modify this script to return a sort of your choosing...

[Taifun]

@mustafaalbasel

sorting the data coimng from google sheet

Use Gviz, which can sort by column easily, fot example

ORDER BY C

sorts your data by column C

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...

Taifun

[mustafaalbasel]

thanks so much for your effort but lets show me more, do you mean the data will be sorted on the sheet first before i get it back to the list view or the verse
let me show you in the video of my sheet when adding a new order

if you noticed that the new order didnt be sorted after adding the equation

the translated columns that i wanna sort sequence and after that wanna get the sum for the num people for the similar orders after sorting,
i hope to understand me so i wanna know will it be sorted by that or should get the data to the listview first to sort !

[Taifun]

@mustafaalbasel

the translated columns that i wanna sort sequence and after that wanna get the sum for the num people for the similar orders after sorting,

do you need

1. the spreeadsheet itself sorted, or
2. the data you get from the spreadsheet into your app sorted

for 2) use gviz as already mentioned earlier, it offers easy filter and sort features as well as data aggregation

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

Taifun

[mustafaalbasel]

i wanna the sheet sorted itself to calculate the similar no people from and then get data with being sorted so its better for me

[mustafaalbasel]

same as the video so when i send a new order it should be sorted not added below

[TIMAI2]

then add the new order first (append to last row), then sort the spreadsheet.

[mustafaalbasel]

yes but i dont wanna sort it manually everytime i wanna be automated sorting

[TIMAI2]

So use the google apps script I provided

[Taifun]

@mustafaalbasel

i wanna the sheet sorted itself to calculate the similar no people from

are you aware that for your app you do not need the Google Spreadsheet itself sorted, the Spreadsheet is only a database and for the database it does not really matter if the data is sorted of not...
what you need is to get the data sorted from the spreadsheet into your app

to sort a Google Spreadsheet actually is out of scope for this community

Taifun

[mustafaalbasel]

Thanks Taifun and TIMAI2, Alright will try to sort from the app and let you know

[mustafaalbasel]

have you any other ideas please

i cant arrange data from it any written text then the already written in the pic isnt working
let me show you my request and tell me what can i do with your creativity
there are about 10 columns from the 12 columns in the sheet needs to be arranged with the similar cells

similar date, time, so on as you see in the screenshot
and after arranging the data i need to get the sum of num people column for the similar arranged
for example
1 order 1 jan 8am from egypt to usa
2 order 20dec 11am from egypt to mexico
3 order 15feb 8pm from mexico to egypt
so it needs to be arranged like that
1 order 1 jan 8am from egypt to usa
3 order 15feb 8pm from mexico to egypt
2 order 20dec 11am from egypt to mexico

i dont care about where can i arrange them inside the app or outside, the only important thing is to arrange multiple columns with the similar so if it can be by block and then show them arranged in the listview directly it will be great

[Taifun]

@mustafaalbasel

I cant arrange data from it any written text then the already written in the pic isnt working

???
Not understandable, sorry

@mustafaalbasel

sheet needs to be arranged with the similar cells

Make yourself familiar with the sql language, read again Query Language Reference (Version 0.7)  |  Charts  |  Google for Developers

It looks like you want to sort by date... find the date column, from the spreadsheet it seems to be column A, i.e.

ORDER BY A

@mustafaalbasel

to get the sum of num people column

So which column is it? Sorry, I can't read your language... if you want help, then let me suggest you to make it as easy as possible for others to help...

Taifun

[mustafaalbasel]

i tried the run query by adding arranging columns but didnt work, the only thing working is the text he wrote which is select a,b,c ,,,,
lets show me how to arrange listview elements blocks something like that but by blocks

when ListView.BeforeSorting
sort list by column "Date"
sort list by column "Time"
sort list by column "Trip Type"
sort list by column "Car Type"

when ListView.AfterSorting
foreach item in list
if item = previous item
sum += item.NumberOfPassengers
else
add to list sum
previous item = item

[mustafaalbasel]

that way will be more better for me than the query solution or the google sheet scripts

[ABG]

Search for same ingredient and add them

This type of problem works better with tables (lists of lists) and a group by sum procedure similar to the SQL SELECT SUM GROUP By construct. This sample app groups and sums expenses, as opposed to grouping and summing ingredients, but the base concept is the same. group_sum.aia (7.6 KB) Here is a raw data Comma Separated Values (CSV) file... [details table structure] [when Screen1 Initialize] [when FileSampleDetails GotText] When the file arrives from the Media folder, it gets scrubbed…

Maybe you need a grouped sum procedure?