Reputation: 25
First time trying to use scripts here so please help!
I have a sort script set up, however, I have data automatically moving to this spreadsheet based on cells values on different workbooks, which is constantly creating new rows. The current script I am using must have a range in order to sort, but that range is ever changing, please help me so every time a new row is added it will add this row to the range.
Current script:
SHEET_NAME = "History";
SORT_DATA_RANGE = "A2:V36";
SORT_ORDER = [
{column: 25, ascending: true},
{column: 17, ascending: true},
{column: 24, ascending: true}
];
function HistorySort(e){
multiSortColumns();
}
function multiSortColumns(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName(SHEET_NAME);
var range = sheet.getRange(SORT_DATA_RANGE);
range.sort(SORT_ORDER);
ss.toast('Sort complete.');
}
Upvotes: 1
Views: 7397
Reputation: 6072
Depending on the exact end result you are expecting, you can do any of the following:
1. Adjust the SORT_DATA_RANGE
such that you don't set the end row of the range.
SORT_DATA_RANGE = "A2:V"
In this way, the entire range is being considered and even though new rows are added, these will still be included properly in the range.
2. Make use of the getDataRange
, getLastRow
and getLastColumn
methods.
If you don't specify the range you want to sort since it ends up changing constantly, you can easily retrieve it using getDataRange
. This method will end up returning a Range
corresponding to the dimensions in which data is present. As for getting its exact dimensions, the getLastRow
and getLastColumn
methods are used.
function multiSortColumns(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName(SHEET_NAME);
var dataRange = sheet.getDataRange();
var lastRow = dataRange.getLastRow();
var lastCol = dataRange.getLastColumn();
var range = sheet.getRange(2, 1, lastRow, lastCol);
range.sort(SORT_ORDER);
ss.toast('Sort complete.');
}
Upvotes: 2
Reputation: 337
You can use getLastRow
for that:
SHEET_NAME = "History";
SORT_DATA_RANGE = "A2:V"; // do not specify the row here
SORT_ORDER = [
{column: 25, ascending: true},
{column: 17, ascending: true},
{column: 24, ascending: true}
];
function HistorySort(e){
multiSortColumns();
}
function multiSortColumns(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName(SHEET_NAME);
var range = sheet.getRange(SORT_DATA_RANGE + sheet.getLastRow()); // change here
range.sort(SORT_ORDER);
ss.toast('Sort complete.');
}
Upvotes: 1