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 } );
}
}
You can name ranges in Google Sheets to keep better track of them and create cleaner formulas. For example, instead of using "A1:B2" to describe a range of cells, you could name the range "budget_total." This way, a formula like "=SUM(A1:B2, D4:E6)" could be written as "=SUM(budget_total, quarter2)."
You can use the Range class to select specific sets of cells within a sheet. Instances of this class represent a range—a group of one or more adjacent cells in a sheet. You can specify ranges by their row and column numbers, or by using A1 notation.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With