我正在嘗試清理我不再需要的大量命名范圍的電子表格，并留下我仍在使用的少數范圍。所有這些命名范圍都包含選項卡名稱，因為它們源自模板選項卡（名為tmp），其他選項卡從中復制。即使在我從作業表中洗掉所有衍生標簽并只留下tmp 之后，'tmp'！出現在范圍的名稱中，既顯示在命名范圍側邊欄中，也顯示在 getNamedRanges() 中。
當我嘗試有選擇地洗掉消除的命名范圍時，無論我如何指定范圍的名稱，我都會收到錯誤訊息，指出不存在此類命名范圍。基本上，我正在反饋 getNamedRanges() 和 getRange().getSheet().getSheetName() 給我的相同資訊，只是一路上出現亂碼。
該問題在以下測驗片段中被隔離，涉及在選項卡名稱周圍呈現單引號。我嘗試了幾種方法，包括用斜杠轉義單引號，并在代碼中添加了我在targetDoc.removeNamedRange(namedRange)行上得到的錯誤的注釋。
const analyzerDoc = '1pYgcX2dxzHd4cCofy0RFZTzEl36QesiakMGIqCC2QlY'
const openAnalyzerDoc = SpreadsheetApp.openById(analyzerDoc)
function testDeleteNamedRange (){
var docUrl = openAnalyzerDoc.getRangeByName('docUrl').getValue();
var targetDoc = SpreadsheetApp.openByUrl(docUrl);
// var namedRange = 'dyCl_MoodEntries' // The named range "dyCl_MoodEntries" does not exist.
// var namedRange = 'tmp!dyCl_MoodEntries' // The named range "tmp!dyCl_MoodEntries" does not exist.
// var namedRange = "'tmp'!dyCl_MoodEntries" // The named range "'tmp'!dyCl_MoodEntries" does not exist.
// var namedRange = "\'tmp\'!dyCl_MoodEntries" // The named range "'tmp'!dyCl_MoodEntries" does not exist.
targetDoc.removeNamedRange(namedRange);
}
此錯誤妨礙了更長的功能，該功能運行良好，但對于此測驗功能中隔離的部分。
更長的函式獲取要從此作業表中洗掉的范圍的名稱和選項卡： 
這樣做的正確方法是什么？謝謝！
uj5u.com熱心網友回復：
此函式將洗掉其作業表名稱在范圍名稱內的所有命名范圍。
function deleteAllNamedRange() {
const ss = SpreadsheetApp.getActive();
ss.getNamedRanges().filter(r => ~r.getName().indexOf(r.getRange().getSheet().getName())).forEach(r => r.remove());
}
uj5u.com熱心網友回復：
如果您只想洗掉特定的命名范圍，您可能會從嘗試以下代碼中受益：
function testDeleteNamedRange() {
var openAnalyzerDoc = SpreadsheetApp.openById('SPREADSHEET_ID');
var namedRanges = ['NamedRange1', 'NamedRange2', 'NamedRange3', 'NamedRange4'];
namedRanges.forEach(range => openAnalyzerDoc.removeNamedRange(range));
}
運行代碼前
執行testDeleteNamedRange函式后
解釋
您要洗掉的命名范圍已添加到陣列中；因此，通過回圈遍歷這個陣列，每個命名范圍都成功地從電子表格中洗掉。
該removeNamedRange方法需要一個型別的物件，String它是需要洗掉的范圍的名稱。
至于getNamedRanges您提到的，此方法最終以NamedRange物件陣列的形式從電子表格中檢索所有命名范圍- NamedRange[]。
參考
接下來，這是洗掉選定命名范圍的函式：
function deleteSelectedNamedRanges () { var namedRangeData = SpreadsheetApp.getActive().getSheetByName('NamedRanges').getDataRange().getValues(); namedRangeData.shift(); // Remove header row data. var rangesToDelete = namedRangeData.filter(function(nrDatum) {if (nrDatum[3]==true) return nrDatum}); // [3] equivalates to Column D, the checkboxes where I select which Named Ranges to delete. console.log (rangesToDelete.map(value => value[0])); // [ 'Sheet3\'!Moe', 'Sheet2\'!Curly', 'Sheet1\'!Moe' ] console.log (rangesToDelete.map(value => value[2])); // [ 0, 1, 5 ] // [2] is the index number of the Named Ranges. /* The order here derives from how values in Tab Named Ranges happen to be sorted. In this instance, I have not changed that order, so the Named Ranges To Delete are in ascending order. For one thing, this is the opposite of what we want; for another, I want to be able to sort the Named Range Tab freely before making my selections. So, we must sort this data in DESCENDING order. */ rangesToDelete.sort(function(value1,value2){if (value1[2]<value2[2]) return 1; if (value1[2]>value2[2]) return -1; return 0}); console.log (rangesToDelete.map(value => value[0])); // [ 'Sheet1\'!Moe', 'Sheet2\'!Curly', 'Sheet3\'!Moe' ] var rangesToDeleteIndexNumbers = rangesToDelete.map(value => value[2]) console.log (rangesToDeleteIndexNumbers); // [ 5, 1, 0 ] var namedRanges = SpreadsheetApp.getActive().getNamedRanges(); for (i=namedRanges.length-1; i>=0; i--) { /* We must loop in descending order because deleting Named Ranges will change the index numbers of all Named Ranges that come after each we delete. */ if (rangesToDeleteIndexNumbers.indexOf(i) !== -1) {namedRanges[i].remove(); console.log ('Removed NR # ' i)} // Delete Named Range if this iteration number can be found in rangesToDeleteIndexNumbers. } }運行此函式后，可以看到洗掉了 3 個 Names Ranges，留下了 6 個：
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/361499.html