更新命名范围而不破坏公式 [英] Update named ranges without breaking formula

问题描述

电子表格在自定义菜单中有一个与它关联的脚本，可以导入更新的数据，从而增加长度的每个范围所需的数据。一旦数据被导入，脚本会将命名范围更新为新数据的长度。

以下是导入新数据然后更新命名范围的代码块：

  // //添加CSV菜单。可能会改变这是一个自动更新的基础日期
函数onOpen（）{
 var ss = SpreadsheetApp.getActiveSpreadsheet（）; 
 var csvMenuEntries = [{name：Update Data，functionName：importFromCSV}]; 
 ss.addMenu（Update，csvMenuEntries）; 
 
 $ b函数importFromCSV（）{
 var file = DriveApp.getFilesByName（double_leads_data.csv）; //获取文件对象
 var csvFile = file .next（）。getBlob（）。getDataAsString（）; //获取字符串内容
 var csvData = Utilities.parseCsv（csvFile）; 
 var ss = SpreadsheetApp.getActiveSpreadsheet（）; 
 var sheet = ss.getSheetByName（'DataImport'）; //仅将数据添加到选项卡DataImport以防止覆盖电子表格的其他部分
 sheet.getRange（2,1，csvData.length，csvData [0] .length）.setValues（csvData）; //写入在一个单一的工作表。从第2行开始（getRange（2 ...）
 SpreadsheetApp.getUi（）。alert（'Data Updated'）; 
 
 //现在更新已命名范围（如果它们已更改）长度
 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; //按原样跳过 - 跳到下一个名称
 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 它们之前引用了相应的命名范围。 阅读一些文档这里有句子

< blockquote>

当您删除一个命名范围时，任何引用该命名的
范围的公式都将不再起作用。但是，引用
命名范围的受保护范围会自动替换单元格值
的命名范围并继续工作。

我不确定这意味着什么。如果我使用保护范围，而不是所有的工作？我试过编辑上面的代码？我阅读了有关getProtections（）此处的内容，所以尝试了一下小编辑：

var openEnded = [businessunit2，date2，leads2，medium2，region2，saleschannel2];

  var openEnded = ss.getProtections（SpreadsheetApp.ProtectionType.RANGE）
  

我并没有真正期望这样做，但值得一试。

有没有在这里解决？如何在不破坏引用这些范围的现有公式的情况下使用脚本更新命名范围？将使用getProtections（）导致一个解决方案，或者只是一个转换？

解决方案

在公式中使用INDIRECT（rangeName）而不仅仅是rangeName。以编程方式扩展范围的唯一方法是删除它，然后将其添加回新的定义。

  = sum（间接（test1），间接（test3））
  

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

I have a spreadsheet with many formula referencing named ranges.

The spreadsheet has a script associated with it in a custom menu that imports updated data which can increase the length of the data needed in each range. Once the data are imported the script updates the named ranges to be the length of the new data.

Here is the code block that imports new data and then updates the named ranges:

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

All works well - the data import and the named ranges update. However, after the update all the formula referencing the named ranges break and show #REF where they previously referenced the corresponding named range.

Reading some documentation here there is a sentence

When you delete a named range, any formulas referencing this named range will no longer work. However, protected ranges that reference a named range will swap out the named range for the cell values themselves and continue to work.

I'm not really sure what that means. If I use a protected range instead will it all work? I tried editing the code above? I read about getProtections() here so tried making a small edit:

var openEnded = ["businessunit2", "date2", "leads2", "medium2", "region2", "saleschannel2" ];

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

I didn't really expect this to work but was worth a try.

Is there a solution here? How can I update a named range with a script without breaking existing formula which references those ranges? Will using getProtections() lead to a solution or is that just a diversion?

解决方案

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.

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

This is a messy and should be unnecessary workaround. If you agree please star the item in the issue tracker. https://code.google.com/p/google-apps-script-issues/issues/detail?id=5048

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