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?
