getRange with named range google spreadsheet using scripts

Question

Can the getRange be used to have a named range instead of an area?
When I seem to do it, it says the argument must be a range. For example,

Instead of:

     getRange("A4:E7");

The area of A4:E7 has been made into a named range called 'Names' in sheet1.

Could you perhaps use:

var tableRange = SpreadsheetApp.getActiveSpreadsheet();.getRangeByName("Names");
getRange(tableRange);

Or is there any other way of doing it. The full code is:

function onEdit(event){
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var editedCell = ss.getActiveCell();

    var columnToSortBy = 1;
    var tableRange = ss.getRangeByName("Names");

    if(editedCell.getColumn() == columnToSortBy){ 
        var range = ss.getRange(tableRange); 
        range.sort( { column : columnToSortBy } );
    }
}

Calgary

Still stuck on this - I'm not sure why the google script is not allowing the defined name to be used – Calgary

Not an answer to your problem but just a suggestion: You don't need getActiveCell(). The edit event passes the edited range to the script so you can just use event.range.getColumn(). – Hussite

Answer 1

https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet#getrangebynamename

Custom function returning A1 address of named range:

function myGetRangeByName(n) {  // just a wrapper
  return SpreadsheetApp.getActiveSpreadsheet().getRangeByName(n).getA1Notation();
}

Then, in a cell on the spreadsheet:

=myGetRangeByName("Names")

This would put whatever "Names" is defined as into the cell. It will NOT update when you redefine "Names," because of GAS's aggressive caching. You can, however, force GAS to update it on every sheet calculation.

=myGetRangeByName("Names",now())

The javascript will ignore the unused parameter.

The following code does what I think you intend. When the first column of the sheet is edited, it sorts the range based on that column.

function onEdit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var editedCell = ss.getActiveCell();
  var columnToSortBy = 1;
  var tableRange = ss.getRangeByName("Names");
  if ( editedCell.getColumn() == columnToSortBy ) {
    tableRange.sort(columnToSortBy);
  }
}

This will NOT work if you move the list off column A, because getColumn() returns the absolute column, not the location of the cell in the range. You would have to add code to adjust for that.

Jipijapa

What's with that now()? I put it into a sheet and it errored. – Silurian

support.google.com/drive/bin/… – Jipijapa