在GAS中删除无效的命名范围 [英] Removing invalid named ranges in GAS

问题描述

我在工作表中定义了一些命名范围，以后将其删除.之后，范围保留在侧栏数据->命名范围..."中，范围为"#REF".我想删除它们，因为我不希望它们累积.

I have defined some named ranges in a sheet that I later delete. Afterwards, the ranges remain in the sidebar "Data->Named ranges...", with the range "#REF". I would like to delete them because I don't want them to accumulate.

SpreadsheetApp.GetActiveSpreadsheet.getNamedRanges()中未列出它们.

如何通过编程方式将其删除?

How can I delete them programatically?

另一种解决方案是如何定义在删除工作表时删除的命名范围.如果您在工作表中有一个重复的命名范围，则会发生这种情况-命名范围的名称类似于'Sheet1Copy'！RangeName"，但无法定义这样的名称.

An alternative solution would be how to define a named range that is removed when a sheet is deleted. This happens if you have a named range in a sheet that is duplicated - the named range has a name like "'Sheet1Copy'!RangeName", but it's not possible to define a name like this.

推荐答案

使用 removeNamedRange(name)删除命名范围.即使具有#REF的命名范围也可以使用！范围，而不由 SpreadsheetApp.getActiveSpreadsheet().getNamedRanges()返回.

Use removeNamedRange(name) to remove a Named Range. It will work even with Named Ranges that has #REF! as range and are not returned by SpreadsheetApp.getActiveSpreadsheet().getNamedRanges().

为了更轻松地使用#REF来维护电子表格不受命名范围的影响！作为范围，请保留您的命名范围"列表.您可以为此使用辅助电子表格.

In order to make easier to maintain your spreadsheets free of Named Ranges with #REF! as range, keep a list of your Named Ranges. You could use an auxiliary spreadsheet for that.

这篇关于在GAS中删除无效的命名范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！