更新命名范围没有打破公式(Update named ranges without breaking

我有很多命名范围的公式引用的电子表格。

电子表格具有与它在定制菜单进口更新后的数据，可以增加在每个范围所需要的数据的长度相关联的脚本。 一旦数据被导入的脚本更新的命名范围是新数据的长度。

这里是导入新的数据，然后更新的命名范围内的代码块：

// add the CSV menu. Might change this to be an automatic update base don date
function onOpen() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
  var csvMenuEntries = [{name: "Update Data", functionName: "importFromCSV"}];
  ss.addMenu("Update", csvMenuEntries);
}

function importFromCSV() {
      var file = DriveApp.getFilesByName("double_leads_data.csv");// get the file object
      var csvFile = file.next().getBlob().getDataAsString();// get string content
      var csvData = Utilities.parseCsv(csvFile);
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = ss.getSheetByName('DataImport'); // only add data to the tab DataImport to prevent overwriting other parts of the spreadsheet
      sheet.getRange(2,1, csvData.length, csvData[0].length).setValues(csvData);// write to sheet in one single step. Start at row 2 (getRange(2... )
      SpreadsheetApp.getUi().alert('Data Updated');

      //now update the named ranges if they have changed in length
       var openEnded = ["business_unit", "channel", "date", "leads", "medium", "region" ];

      for(i in openEnded) {
        var r = ss.getRangeByName(openEnded[i]);
        var rlr = r.getLastRow();
        var s = r.getSheet();
        var slr = s.getMaxRows();
        if(rlr==slr ) continue; // ok as is-skip to next name
        var rfr = r.getRow();
        var rfc = r.getColumn();
        var rnc = r.getNumColumns();
        var rnr = slr - rfr + 1;
        ss.removeNamedRange(openEnded[i]);
        ss.setNamedRange( openEnded[i], s.getRange(rfr, rfc, rnr, rnc ));
      }
}

一切运作良好 - 数据导入和指定范围的更新。 然而，在更新后的所有公式引用的命名范围突破并显示#REF他们前面引用相应的命名范围。

阅读一些文件在这里有一个句子

当你删除一个命名范围，引用此命名范围的任何公式将不再工作。 然而，引用了一个名为范围的保护范围将换出的命名范围的单元值本身，继续工作。

我真的不知道这意味着什么。 如果我使用一个受保护的范围，而不是将它所有的工作？ 我试图编辑上面的代码？ 我读到getProtections（） 在这里所以试图使一个小编辑：

VAR式开口= [ “businessunit2”， “DATE2”， “leads2”， “中2”， “区域2”， “saleschannel2”];

var openEnded = ss.getProtections(SpreadsheetApp.ProtectionType.RANGE)

我真的不希望这个工作，但值得一试。

是否有解决方案吗？ 我怎么可以更新脚本中的命名范围不会破坏现有的公式，引用的范围是多少？ 将使用getProtections（）导致的解决方案或者是说只是一个消遣？

Answer 1:

在公式中使用，而不只是rangeName INDIRECT（“rangeName”）。 以编程延伸的范围内的唯一方法是通过去除它，然后用一个新的定义添加回来。 这个过程会破坏公式，并返回#REF而不是区域名称。

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

这是一个混乱的，应该是不必要的解决方法。 如果你同意，请在问题跟踪明星的项目。 https://code.google.com/p/google-apps-script-issues/issues/detail?id=5048

Answer 2:

你提到的有关保护范围的细节不会帮助你。 这指的是在细节窗格中的保护范围定义。 您可以定义一个受保护的范围是一个命名的范围，如果指定的范围内被删除也不会断裂的保护范围定义。
我也遇到了这样的一段时间了，认为这是在他们的命名区域的API一个严重的错误。 它的可笑的是他们的API可是没有办法对其进行修改（而不是删除并重新创建）。 我的意思很明显，如果我们使用的命名范围，是因为我们希望他们改变。 很抱歉的咆哮，但是这是一个很老的问题是仍然打破。
编辑：见
https://code.google.com/p/google-apps-script-issues/issues/detail?id=1040 （我是从1.5年＃7有前）
和
https://code.google.com/p/gdata-java-client/issues/detail?id=196 （IM＃4和刚添加＃5存在）
请明星都。

Answer 3:

引用该NamedRange公式由线断

ss.removeNamedRange（式开口[I]）;

我相信你可以简单地忽略此行，并直接进入

ss.setNamedRange（式开口[I]，s.getRange（RFR，RFC，RNR，RNC））;

这种方法似乎在气体脚本添加一列，在谷歌表根据NamedRange要为我工作。 在其他单元格的公式引用此命名范围和我的脚本执行时不破

我读了三个问题跟踪器的帖子，我明白值得关注的是在一系列命名区域产生重复的条目。 到目前为止，我还没有看到这种行为也许这错误是固定的。

Answer 4:

下面的代码将更新指定的范围，而不将其删除或创建的范围，如果它不存在。

function fixNamedRange (ss, name, range) {
  var ssNamedRanges = ss.getNamedRanges();
  var ssRangeNames = ssNamedRanges.map (function (ssRange) { 
      return ssRange.getName(); 
    }
  );
  var myRange = ssNamedRanges[ssRangeNames.indexOf(name)];

  if (myRange) {
    return myRange.setRange(range);
  } else {
    ss.setNamedRange(name, range);
    return -1;
  }
}

编辑通过命名范围的阵列采用.MAP法代替迭代。

文章来源: Update named ranges without breaking formula