Google sheet auto sort script code needed!

[mustafaalbasel]

i used many codes to sort the data coming from mit app inventor but still sorting when i enter values manually not sorting the values after getting from mit app inventor
here are the codes i used if you can help me to adjust any as i wanna sort the data due to multiple columns

function autosort() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const ws = ss.getSheetByName("Sheet1");
const range = ws.getRange(2,1,ws.getLastRow()-1,12);
range.sort([{column: 4, ascending: true}, {column: 5, ascending: true}, {column: 6, ascending: true}, {column: 7, ascending: true}, {column: 9, ascending: true}, {column: 10, ascending: true}, {column: 11, ascending: true}, {column: 12, ascending: true}])
}


  function onEdit(e){
  const row = e.range.getRow();
  const column = e.range.getColumn();
  if(column === 1 && row >=2) return autosort(); 
}


____________________________________________________________



function onChange(e) {
  if (e.changeType === "INSERT_ROW") {
    autoSort();
  }
}

function autoSort() {
  const ws = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const lastRow = ws.getLastRow();
  const range = ws.getRange(2, 1, lastRow - 1, 12);
  range.sort([
    { column: 4, ascending: true },
    { column: 5, ascending: true },
    { column: 6, ascending: true },
    { column: 7, ascending: true },
    { column: 9, ascending: true },
    { column: 10, ascending: true },
    { column: 11, ascending: true },
    { column: 12, ascending: true }
  ]);
}

[TIMAI2]

What do you need help with; the google apps script code, or doing the same in AppInventor ?

Apps Script:

Automatic Sorting on Sheets

[mustafaalbasel]

i tried many codes honesty even used the AI equations, but the data coming from the mit app inventor app to the google sheet isnt being sorted auto with the script equation, if i added data manually in the sheet is being sorted but any new additions from the app to the sheet isnt being sorted, so is there a way to fix that?

[TIMAI2]

You have your sort functions, but are you returning the data to your app after sorting it ?

You need to be clear about what you are try to achieve, are these sort functions called from a google apps script web app ?

[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