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. \n"
         + "Unable to find range name-- "+ rName 
         + " -- in ss ( " + ss.getName() + " ) "
         + "\n If it is not needed please remove it " 
         + "from array \n openEndedRangeNames[] \n in the function \n"
         + "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

Answer 2

Offering this function I wrote to handle dynamic resize of named ranges:

function resizeNamedRange(rangeName, addRows, addCols) {
/* expands (or decreases) a range of a named range. 
rows and columns to add can be negative (to decrease range of name). Params:
rangeName - name of range to resize.
addRows - number of rows to add (subtract) from current range.
addCols - number of columns to add (subtract) from current range.
Call example: resizeNamedRange("Products",1,0);
   */

  var sh = SpreadsheetApp.getActiveSpreadsheet();

  try {
    var oldRange = sh.getRangeByName(rangeName);
    var numRows = oldRange.getNumRows() + addRows;
    var numCols = oldRange.getNumColumns() + addCols;
    if (numRows < 1 || numCols <1) {
      Logger.log("Can't resize a named range: minimum range size is 1x1.");
      return;
    }
    sh.setNamedRange(rangeName, oldRange.offset(0,0,numRows, numCols));

  } catch (e) {
    Logger.log ("Failed resizing named range: %s. Make sure range name exists.", rangeName); 
  }
}

Answer 3

Maybe I'm missing something, but the function below takes a rangename and the range that it should contain. If the rangename already exists it updates the range to the passed value. If the rangename doesn't exist, it creates it with the passed range values.

Also with regard to the "#REF!" problem in the sheet. You can do a find and replace and tick the box for "find in formulas". Put "#REF!" in find and the named range name in the replace box. This assumes only one named range was deleted and that there were no other unrelated #REF! errors. This approach helped me fix a spreadsheet with the error spread over 8 different sheets and 20+ formulas in just a few minutes.

/**
 * Corrects a named range to reflect the passed range or creates it if it doesn't exist.
 *
 * @param {string} String name of the Named Range
 * @param {range} Range (not string, but range type from Sheet class)
 * @return {void || range} returns void if Named Range had to be created, returns NamedRange class if just updated. This needs improvement.
 * @customfunction
 */
function fixNamedRange (name, range) {
  var i = 0;
  var ss = SpreadsheetApp
           .getActiveSpreadsheet();
  var ssNamedRanges = ss.getNamedRanges();

  for (i = 0; i<ssNamedRanges.length && ssNamedRanges[i].getName() != name; i++) {};

  if (i == ssNamedRanges.length) {
    return (ss.setNamedRange(name, range));
  } else {
    return (ssNamedRanges[i].setRange(range));
  }
}

Answer 4

I found the solution!

I have a cell with a drop down list with all the clients that the company has registered on the system, if the name we enter does not appear on the list, then function newClient executes. Basically we use the SpreadsheetApp.getUi() in order to save the new information. Once we have introduced the client data, function creates a new row on the client's sheet and enters the information from the prompts on the last row. Once done, updates the drop down list automatically.

The real function is inside of a big function that calls newClient if it's needed so the real one would be newClient(client, clients), on the example I put the variables in order to make it easier.

I hope it works!

function newClient() {
  var ss = SpreadsheetApp.getActive().getSheetByName('Clients'); // Sheet with all the client information, name, city, country...
  var client = 'New company';
  var clients = ss.getRange('A2:A').getValues();
  var ui = SpreadsheetApp.getUi();
  ui.alert('Client '+client+' does not exist, enter the next information.');
  var city = ui.prompt('Enter city').getResponseText();
  var country = ui.prompt('Enter country').getResponseText();
  client = client.toUpperCase();
  city = city.toUpperCase();
  country = country.toUpperCase();
  ui.alert('Here is the information you entered about '+client+':'+'\n\n'+'City: '+city+'\n\n'+'Country: '+country)
  ss.insertRowAfter(ss.getLastRow()); // Insert a row after the last client
  ss.getRange('A'+(clients.length+2)).setValue(client); // Let's suppose we have 150 clients, on the first row we have the titles Client, City, Country, then we have the 150 clients so the last client is on row 151, that's why we enter the new one on the 152
  ss.getRange('B'+(clients.length+2)).setValue(city);
  ss.getRange('C'+(clients.length+2)).setValue(country);
  var namedRanges = SpreadsheetApp.getActive().getNamedRanges(); // We get all the named ranges in an array
  for (var i = 0; i < namedRanges.length; i++) {
    var name = namedRanges[0].getName();
    if (name == 'Clients') { // All the clients are stored on 'Clients' named range
      var range = ss.getRange('A2:A'); // Update the range of the 'Clients' named range
      namedRanges[i].setRange(range);
    }
  }
  ui.alert('Client created, you can find it on the drop down list.');
}