使用脚本获取具有命名范围谷歌电子表格的 getRange [英] getRange with named range google spreadsheet using scripts

问题描述

可以使用 getRange 来命名范围而不是区域吗?
当我似乎这样做时，它说参数必须是一个范围.例如，

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,

代替:

     getRange("A4:E7");

A4:E7 的区域已经在 sheet1 中变成了一个名为Names"的命名范围.

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

你能不能用:

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

推荐答案

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

返回命名范围的 A1 地址的自定义函数:

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")

这会将任何名称"放入被定义为进入单元格.当您重新定义名称"时，它不会更新.因为 GAS 的积极缓存.但是，您可以强制 GAS 在每次工作表计算时更新它.

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())

javascript 将忽略未使用的参数.

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

如果您将列表移出 A 列，这将不起作用，因为 getColumn() 返回绝对列，而不是区域中单元格的位置.您必须添加代码来对此进行调整.

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.

这篇关于使用脚本获取具有命名范围谷歌电子表格的 getRange的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！