CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Doug Fir
Doug Fir

Reputation: 21302

Update named ranges without breaking formula

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.

enter image description here

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?

Upvotes: 1

Views: 1708

Answers (4)

ballenf
ballenf

Reputation: 934

The following code will update a named range without deleting it or create the range if it doesn't exist. 

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

Edited to employ .map method instead of iterating through array of named ranges.

Upvotes: 1

andyhelck
andyhelck

Reputation: 41

formulas that reference the NamedRange are being broken by the line

ss.removeNamedRange(openEnded[i]);

I believe you can simply omit this line, and go directly to

ss.setNamedRange( openEnded[i], s.getRange(rfr, rfc, rnr, rnc ));

this approach appears to be working for me in a GAS script that adds a column to a NamedRange in Google Sheets. Formulas in other cells reference this named range and are not broken when my script executes

I read the three issue tracker postings and I understand the concern is generating duplicate entries in the set of named ranges. So far I have not seen this behavior so perhaps this bug was fixed.

Upvotes: 1

ScampMichael
ScampMichael

Reputation: 3728

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

Upvotes: 1

Zig Mandel
Zig Mandel

Reputation: 19864

the detail you mention about protected ranges wont help you. That refers to the protected range definition in their details pane. You can define a protected range to be a named range and if the named range is deleted it won't break the protected range definition.
i also ran into this a while ago and consider it to be a serious bug in their named ranges api. Its ridiculous that their api doesnt have a way to modify them (instead of deleting and recreating). I mean obviously if we use named ranges is because we expect them to change. Sorry for the rant but this is a very old issue that is still broken.
edit: see
https://code.google.com/p/google-apps-script-issues/issues/detail?id=1040 (i'm #7 there from 1.5 years ago)
and
https://code.google.com/p/gdata-java-client/issues/detail?id=196 (im #4 and just added #5 there)
Please star both.

Upvotes: 1

Related Questions