Sorting in Google Sheets with JavaScript macro across multiple columns
infm
Posted messages
35
Status
Membre
-
infm Posted messages 35 Status Membre -
infm Posted messages 35 Status Membre -
Hello,
Hello,
I just read the information on the neighboring subject.
I can do what is indicated.
However, I cannot create the script (JavaScript) for sorting across multiple columns.
I mean:
sorting the entire sheet (see further!)
sorting starting from column D
followed by sorting from column A
so that for rows with the same value in D, they are in order in A
For example, sorting by department then by names, thus the alphabetical order of names within the same department.
I specify "sheet" and not "range".
Because for a range, it can be done manually (i.e., by doing it on the keyboard)
But I need to define the range, and it is dynamic.
I can't find how to indicate the second part of the range with a variable corresponding to the number of rows (COUNTA). Always a syntax error!
(Maybe define it at maximum, for example, 3:65000? What is the maximum?)
For sorting the sheet, you cannot (I don’t know how!) indicate more than one column.
So if sorting the sheet, I cannot handle two columns
if sorting a range, I cannot define the sorting area with a variable (number of rows)
Thanks for any help
BM
Configuration: Windows / Chrome 80.0.3987.100
Hello,
I just read the information on the neighboring subject.
I can do what is indicated.
However, I cannot create the script (JavaScript) for sorting across multiple columns.
I mean:
sorting the entire sheet (see further!)
sorting starting from column D
followed by sorting from column A
so that for rows with the same value in D, they are in order in A
For example, sorting by department then by names, thus the alphabetical order of names within the same department.
I specify "sheet" and not "range".
Because for a range, it can be done manually (i.e., by doing it on the keyboard)
But I need to define the range, and it is dynamic.
I can't find how to indicate the second part of the range with a variable corresponding to the number of rows (COUNTA). Always a syntax error!
(Maybe define it at maximum, for example, 3:65000? What is the maximum?)
For sorting the sheet, you cannot (I don’t know how!) indicate more than one column.
So if sorting the sheet, I cannot handle two columns
if sorting a range, I cannot define the sorting area with a variable (number of rows)
Thanks for any help
BM
Configuration: Windows / Chrome 80.0.3987.100
3 réponses
Hello,
If we can sort multiple columns on the entire sheet without a macro; procedure:
• Ctrl+A to select the entire sheet (or click in the box to the left of A and above 1)
• Tab "Data" >> Sort range
• Check if row 1 of the sheet contains headers (if not, you'll need to select from the first row of the table)
• Sort by column (choice) A>Z
• Add a column for sorting
• then by column (choice) A>Z.....
Best regards
If we can sort multiple columns on the entire sheet without a macro; procedure:
• Ctrl+A to select the entire sheet (or click in the box to the left of A and above 1)
• Tab "Data" >> Sort range
• Check if row 1 of the sheet contains headers (if not, you'll need to select from the first row of the table)
• Sort by column (choice) A>Z
• Add a column for sorting
• then by column (choice) A>Z.....
Best regards
Thank you, but that's not what I'm looking for.
I know how to do that.
But not all users will know how to do it.
So I'm creating buttons that I link to the various useful sorts.
And now I need to prepare some macros.
These macros are in JavaScript
I’ve created them for sorting ranges like the following:
function SortDateAndName() {
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getRange('3:1000').activate()
.sort([{column: 4, ascending: true}, {column: 1, ascending: true}]);
};
In this macro, my problem is that the 1000 (last row) should be replaced by a variable because the number of rows is variable, and I haven't managed to do that.
I chose to sort a range because for sorting a sheet, I couldn’t find the syntax for sorting on multiple columns like above, column 4 followed by column 1.
So: find the script (in JavaScript) to replace what can be done easily manually.
I have constructed scripts on sheets to sort similar to the one above but they always return an error; something is missing and I don’t see what it is?
To be continued then.
I know how to do that.
But not all users will know how to do it.
So I'm creating buttons that I link to the various useful sorts.
And now I need to prepare some macros.
These macros are in JavaScript
I’ve created them for sorting ranges like the following:
function SortDateAndName() {
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getRange('3:1000').activate()
.sort([{column: 4, ascending: true}, {column: 1, ascending: true}]);
};
In this macro, my problem is that the 1000 (last row) should be replaced by a variable because the number of rows is variable, and I haven't managed to do that.
I chose to sort a range because for sorting a sheet, I couldn’t find the syntax for sorting on multiple columns like above, column 4 followed by column 1.
So: find the script (in JavaScript) to replace what can be done easily manually.
I have constructed scripts on sheets to sort similar to the one above but they always return an error; something is missing and I don’t see what it is?
To be continued then.
Hello,
I'm not going to be much help with this macro; I don't know anything about it.
If it helps, for a personal Excel spreadsheet, I recorded a sorting macro that ended with selecting the last cell containing a date in column A.
After sorting, I selected the last cell at the bottom of the table column and then pressed Ctrl + up arrow to go back to the last cell with a value.
Here is the code that resulted from it:
I haven't checked if it's possible to do that on Google Sheets.
Best regards.
I'm not going to be much help with this macro; I don't know anything about it.
If it helps, for a personal Excel spreadsheet, I recorded a sorting macro that ended with selecting the last cell containing a date in column A.
After sorting, I selected the last cell at the bottom of the table column and then pressed Ctrl + up arrow to go back to the last cell with a value.
Here is the code that resulted from it:
Sub Button()
ActiveSheet.Unprotect
Range("A7:R800").Sort key1:=Range("A7"), Order1:=xlAscending
ActiveWindow.SmallScroll Down:=585
Range("A800").Select
Selection.End(xlUp).Select
ActiveSheet.Protect
End Sub
I haven't checked if it's possible to do that on Google Sheets.
Best regards.
RE:
I did an experiment on Sheets with the same actions as above except for removing protection at the beginning and adding protection at the end, and here is the resulting script
sorting a range A3:D100 starting with column D, then column A; selecting cell A100 and using Ctrl+arrow up to return to the last non-empty cell in column A
Hope this helps you
Best regards
I did an experiment on Sheets with the same actions as above except for removing protection at the beginning and adding protection at the end, and here is the resulting script
sorting a range A3:D100 starting with column D, then column A; selecting cell A100 and using Ctrl+arrow up to return to the last non-empty cell in column A
/** @OnlyCurrentDoc */
function Sort() {
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getRange('A3:D100').activate()
.sort([{column: 4, ascending: true}, {column: 1, ascending: true}]);
spreadsheet.getRange('A100').activate();
spreadsheet.getCurrentCell().getNextDataCell(SpreadsheetApp.Direction.UP).activate();
};
Hope this helps you
Best regards