google apps script - Update named range automatically

Question

I've been having a hard time trying to figure this out. I realize this is perhaps more basic than usual for those who follow the GAS tag, however any help much appreciated.

If I'm breaking up my bigger task into component parts, my goal right now with this question is to update several named ranges automatically.

There is a tab on the spreadsheet called "DataImport". DataImport has 10 columns all 1000 rows long. There is a named range for each column e.g. cabbages (A2:A1000), dogs (B2:B1000) etc etc.

There is a script attached to a new menu item "Update Data" that when selected imports a csv file into DataImport tab meaning that the length of the data set will grow.

How can I tell the sheet to update each named range to be the length of data? So if the original named range "cabbages" was A2:A1000 and following an update the data now actually goes as long as A2:A1500, how would I tell the sheet to update the range cabbages?

I found a snippet of code online and started to fiddle with it:

function testNamedRange() {
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var range = ss.getRange('DataImport!A:A');
   var data_len = range.length;
   SpreadsheetApp.getUi().alert(data_len); // "alert just gives "undefined"
   ss.setNamedRange('TestRange', range);
   var rangeCheck = ss.getRangeByName('TestRange');
   var rangeCheckName = rangeCheck.getA1Notation();
}

My thinking was if I could just get the length of data following an update using the custom menu function, I could then use setNamedRange() to update cabbages range.

I'm really lost and I imagine this is simpler than I'm making it out to be.

How can I update the named range cabbages to be the length of data in UpdateData column A?

Answer 1

Edit: IMPORTANT

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. This should be an unnecessary work around. if you agree please star and the issue tracker at: https://code.google.com/p/google-apps-script-issues/issues/detail?id=5048

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

Emulates open ended named ranges by checking to see that the last row in the named range is the same as the last row in the sheet. If not, adjusts the named range so the last row in the named range is the same as the last row in the sheet.

should probably be used with on open and on change events.

function updateOpenEndedNamedRanges() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // names of open-ended ranges
  var openEndedRangeNames = ["test1", "test2", "test3", "s2test1" ];

  for(i in openEndedRangeNames) {
    var rName = openEndedRangeNames[i];
    try{
      var r = ss.getRangeByName(rName);
      }
    catch(err) {
      GmailApp.sendEmail("[email protected]",
                         rName + " -- Cannot find",
         "Trying to update open-ended ranges after rows added. 
"
         + "Unable to find range name-- "+ rName 
         + " -- in ss ( " + ss.getName() + " ) "
         + "
 If it is not needed please remove it " 
         + "from array 
 openEndedRangeNames[] 
 in the function 
"
         + "updateOpenEndedNamedRanges()");
      continue;
      }
    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(rName);
    ss.setNamedRange( rName, s.getRange(rfr, rfc, rnr, rnc ));
    }
}


function ssChangeEvent(change) {
 // changeType (EDIT, INSERT_ROW, INSERT_COLUMN, REMOVE_ROW, 
 //      REMOVE_COLUMN, INSERT_GRID, REMOVE_GRID, or OTHER)
  switch(change.changeType) {
    case "INSERT_ROW":
      updateOpenEndedNamedRanges();
      break;
    default:
      Logger.log(change.changeType + " detected. No action taken ");
      }
  }

Setup ssChangeEvent(change) to run when rows are added

Resources>this projects triggers