How to reference a dynamic range in Google Sheets Script?

Question

I have a sheet where the filter originally started on row 2, so my range was A2:J. As I've added and deleted rows/columns, my range has changed and it's still changing as I edit the sheet.

Is there a way to name a dynamic range so that if I add/delete columns, my functions will work properly? Kind of like how Sheets/Excel will update formulas if a cell reference is edited.

I have sort by and set cursor at cell functions that run onOpen. However, if I don't manually update the range, then everything gets screwed up every time I open the document. For instance, a few days ago my range was "A6:J" + lr; my filter was on row 6. I added another header row, making my filter row 7, but the script referenced A6 as the range to sort. This screwed up my data. It's driving me nuts!

Would appreciate some insight on how to reference a dynamic range. Not sure if INDIRECT or ADDRESS would work?? Not sure if this answer makes sense?

 function onOpen(){
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("DATA");
  var lastRow = ss.getLastRow();
  var lastColumn = ss.getLastColumn();
  var lastCell = ss.getRange(lastRow, lastColumn);
  var range = ss.getRange("A7:" + lastRow);
  Logger.log(ss.getRange(lastRow, lastColumn).getDataRegion().getA1Notation());
  var columnToSortBy = 2;
  var range = ss.getRange("A7:J" + lr);
  range.sort( { column : columnToSortBy, ascending: true } );
  var r = Math.max.apply(Math, ss.getRange('B:B').getValues()
        .map(function (d, i) {
            if (d[0]) return i + 1;
        }).filter(function (d) {
            return d;
        })) + 1;
    ss.setActiveRange(ss.getRange(r, 2))
}

---

EDIT: I figured it out! Answer below

Combine .getFrozenRows()+1 with lastRow and lastColumn to get a dynamic range that begins after a header row. If a column/row is added/deleted, the range referenced in the function will automatically update. This only works for ranges that begin after a header/filter row, and end with the last filled column.

You can run a log to see if this matches the target range. Run the log below, view log. Then add a row or column, re-run log and compare:

var range = ss.getRange(ss.getFrozenRows()+1, 1, ss.getLastRow(), ss.getLastColumn());
Logger.log("Sorting Range: %s", range.getA1Notation());

StackExchange has provided so many answers for me, so I hope this helps somebody else :) Full explanation with more test logs below.

Answer 1

I think I figured it out! Using getFrozenRows() gives me all rows up to the filter. If I add getFrozenRows()+1 then I get the first cell of the range I want to sort (excludes the header rows). I can combine that with lastRow to get the dynamic range.

I changed var range to var range = ss.getRange(ss.getFrozenRows()+1, 1, ss.getLastRow(), ss.getLastColumn()); Only issue is that it gives the last filled row + 6. Example, lastRow is M318, but the log shows M324. I've tried it on other sheets, for some reason it always adds 6 rows. I've tried it on a test sheet that i filled and using the new range works great.

A workaround is to run a function to remove any rows without data. My function also includes deleting empty columns. Caveat: sheet cannot be empty. Must have at least one cell with data

function removeEmptyRowsColumns(){ var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var maxRows = ss.getMaxRows(); var lastRow = ss.getLastRow(); var maxColumns = ss.getMaxColumns(); var lastColumn = ss.getLastColumn(); ss.deleteRows(lastRow+1, maxRows-lastRow); ss.deleteColumns(lastColumn+1, maxColumns-lastColumn); }

Here's what the final function looks like:

function sortColumn(){ var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var range = ss.getRange(ss.getFrozenRows()+1, 1, ss.getLastRow(), ss.getLastColumn()); Logger.log(ss.getRange(lastRow, lastColumn).getDataRegion().getA1Notation()); var columnToSortBy = 2; range.sort( { column : columnToSortBy, ascending: true } ); var r = Math.max.apply(Math, ss.getRange('B:B').getValues() .map(function (d, i) { if (d[0]) return i + 1; }).filter(function (d) { return d; })) + 1; ss.setActiveRange(ss.getRange(r, 2)) }

I had to go through a ton of trial and error to figure out the dynamic range. I found it helpful to run some logs. You can run a test logs function, or add the Logger.log() to your function to see if the result matches your target range. I compiled some of the logs I ran into a function to make it easier for me to compare

function testLogs(){ var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("ENTER SHEET NAME"); var lastRow = ss.getLastRow()+1; var lastColumn = ss.getLastColumn(); var lastCell = ss.getRange(lastRow, lastColumn); var maxRows = ss.getMaxRows(); var maxColumns = ss.getMaxColumns() var range = ss.getActiveRange(); var dataRange = ss.getRange(ss.getFrozenRows()+1, 1, ss.getMaxRows(), ss.getMaxColumns()); var newRange = ss.getRange(ss.getFrozenRows()+1, 1, ss.getLastRow(), ss.getLastColumn()); Logger.log("ACTUAL Sorting Range: %s", newRange.getA1Notation()); // result: full range, starting after filter row Logger.log("# frozen rows: %s", ss.getFrozenRows()); // result: # frozen rows (row # = header/filter row) Logger.log("sorted data end cell: %s", lastCell.getA1Notation()); // result: range end cell, using last filled column/row (in A1 notation) Logger.log("Last Row #: %s", lastRow); // result: last filled row # Logger.log("Last Column #: %s", lastColumn); // result: last filled column # Logger.log("last cell's value: %s", lastCell.getValue()); // result: last row's, last cell's VALUE Logger.log("Sorting Range, incl empty: %s", dataRange.getA1Notation()); // result: sorting range, starting after filter row Logger.log("last empty row #: %s", maxRows); // result: last empty row # Logger.log("last empty column #: %s", maxColumns); // result: last empty column # Logger.log("cursor end cell: %s", range.getA1Notation()); // result: range end cell, depending on cursor locale }