自动更新命名范围 [英] Update named range automatically

问题描述

我一直在努力解决这个问题.我意识到对于那些遵循 GAS 标签的人来说，这可能比平时更基本，但是非常感谢任何帮助.

I've been having a hard time trying to figure this out. I realize this is perhaps more basic than usual for those who follow the GAS tag, however any help much appreciated.

如果我将更大的任务分解为多个组成部分，那么我现在这个问题的目标是自动更新多个命名范围.

If I'm breaking up my bigger task into component parts, my goal right now with this question is to update several named ranges automatically.

电子表格上有一个名为DataImport"的选项卡.DataImport 有 10 列，所有 1000 行.每列都有一个命名范围，例如卷心菜 (A2:A1000)、狗 (B2:B1000) 等

There is a tab on the spreadsheet called "DataImport". DataImport has 10 columns all 1000 rows long. There is a named range for each column e.g. cabbages (A2:A1000), dogs (B2:B1000) etc etc.

There is a script attached to a new menu item "Update Data" that when selected imports a csv file into DataImport tab meaning that the length of the data set will grow.

There is a script attached to a new menu item "Update Data" that when selected imports a csv file into DataImport tab meaning that the length of the data set will grow.

如何告诉工作表将每个命名范围更新为数据长度?因此，如果原始命名范围卷心菜"是 A2:A1000 并且更新后数据现在实际上与 A2:A1500 一样长，我该如何告诉工作表更新范围卷心菜?

How can I tell the sheet to update each named range to be the length of data? So if the original named range "cabbages" was A2:A1000 and following an update the data now actually goes as long as A2:A1500, how would I tell the sheet to update the range cabbages?

我在网上找到了一段代码并开始摆弄它:

I found a snippet of code online and started to fiddle with it:

function testNamedRange() {
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var range = ss.getRange('DataImport!A:A');
   var data_len = range.length;
   SpreadsheetApp.getUi().alert(data_len); // "alert just gives "undefined"
   ss.setNamedRange('TestRange', range);
   var rangeCheck = ss.getRangeByName('TestRange');
   var rangeCheckName = rangeCheck.getA1Notation();
}

我的想法是，如果我可以使用自定义菜单功能在更新后获取数据的长度，那么我可以使用 setNamedRange() 来更新卷心菜范围.

My thinking was if I could just get the length of data following an update using the custom menu function, I could then use setNamedRange() to update cabbages range.

我真的很迷茫，我想这比我想象的要简单.

I'm really lost and I imagine this is simpler than I'm making it out to be.

如何将命名范围白菜更新为 UpdateData 列 A 中的数据长度?

How can I update the named range cabbages to be the length of data in UpdateData column A?

推荐答案

重要

在公式中使用 INDIRECT("rangeName") 而不仅仅是 rangeName.以编程方式扩展范围的唯一方法是删除它，然后使用新定义重新添加它.此过程会破坏公式并返回 #ref 而不是范围名称.这应该是一个不必要的解决方法.如果您同意，请在以下位置加注星标和问题跟踪器:https://code.google.com/p/google-apps-script-issues/issues/detail?id=5048

Use INDIRECT("rangeName") in formulas instead of just rangeName. The only way to extend the range programmatically is by removing it and then adding it back with a new definition. This process breaks the formula and returns #ref instead of the range name. This should be an unnecessary work around. if you agree please star and the issue tracker at: https://code.google.com/p/google-apps-script-issues/issues/detail?id=5048

=sum(indirect("test1"),indirect("test3"))

模拟开放式命名范围，方法是检查命名范围中的最后一行是否与工作表中的最后一行相同.如果不是，则调整命名范围，使命名范围中的最后一行与工作表中的最后一行相同.

Emulates open ended named ranges by checking to see that the last row in the named range is the same as the last row in the sheet. If not, adjusts the named range so the last row in the named range is the same as the last row in the sheet.

可能应该与 on open 和 on change 事件一起使用.

should probably be used with on open and on change events.

function updateOpenEndedNamedRanges() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // names of open-ended ranges
  var openEndedRangeNames = ["test1", "test2", "test3", "s2test1" ];

  for(i in openEndedRangeNames) {
    var rName = openEndedRangeNames[i];
    try{
      var r = ss.getRangeByName(rName);
      }
    catch(err) {
      GmailApp.sendEmail("[e-mail]",
                         rName + " -- Cannot find",
         "Trying to update open-ended ranges after rows added. 
"
         + "Unable to find range name-- "+ rName 
         + " -- in ss ( " + ss.getName() + " ) "
         + "
 If it is not needed please remove it " 
         + "from array 
 openEndedRangeNames[] 
 in the function 
"
         + "updateOpenEndedNamedRanges()");
      continue;
      }
    var rlr = r.getLastRow();
    var s = r.getSheet();
    var slr = s.getMaxRows();
    if(rlr==slr ) continue;
    var rfr = r.getRow();
    var rfc = r.getColumn();
    var rnc = r.getNumColumns();
    var rnr = slr - rfr + 1;
    ss.removeNamedRange(rName);
    ss.setNamedRange( rName, s.getRange(rfr, rfc, rnr, rnc ));
    }
}


function ssChangeEvent(change) {
 // changeType (EDIT, INSERT_ROW, INSERT_COLUMN, REMOVE_ROW, 
 //      REMOVE_COLUMN, INSERT_GRID, REMOVE_GRID, or OTHER)
  switch(change.changeType) {
    case "INSERT_ROW":
      updateOpenEndedNamedRanges();
      break;
    default:
      Logger.log(change.changeType + " detected. No action taken ");
      }
  }

设置 ssChangeEvent(change) 在添加行时运行

Setup ssChangeEvent(change) to run when rows are added

资源>这个项目触发

这篇关于自动更新命名范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！