CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Carly Bateman
Carly Bateman

Reputation: 25

How to get a google sheets script to find the last row

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: 7416

Answers (2)

ale13
ale13

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.');
}

Reference

Upvotes: 2

Elchanan shuky Shukrun
Elchanan shuky Shukrun

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

Related Questions