Use Apps Script to create data validation that uses a named range

1
votes

Here's the code:

  var sheet = spreadsheet.getSheetByName("Timesheet");
  sheet.getRange('B27').setDataValidation(SpreadsheetApp.newDataValidation()
  .setAllowInvalid(false)
  .requireValueInRange(spreadsheet.getRange('TaskItems'), true)
  .build());

The above code successfully creates a data validation drop-down menu in cell B27 that matches the named range "TaskItems". However, the data validation rule that apps script creates uses the actual address of "TaskItems" which is C2:1300, rather than "TaskItems" itself. So if I update the address of named range "TaskItems" to D2:1300, then my data validation rules no longer work because they are still using C2:1300.

I can set the data validation rules manually on each cell to the named range "TaskItems", and everything works great even when "TaskItems" changes. However, I can't get apps script to use the actual named range in the rule rather than the address of the named range when it the rule was created.

I tried switching out the range object with a string like so:

var sheet = spreadsheet.getSheetByName("Timesheet");
  sheet.getRange('B27').setDataValidation(SpreadsheetApp.newDataValidation()
  .setAllowInvalid(false)
  .requireValueInRange('TaskItems', true)
  .build());

but I get an error stating that requireValueInRange does not accept a string.

Does anyone know how to get apps script to use the actual named range in the data validation rule?

2
validationgoogle-apps-scriptgoogle-sheetsnamed-ranges
You might want to change this .requireValueInRange(spreadsheet.getRange('TaskItems'), true) to this .requireValueInRange(spreadsheet.getRangeByName('TaskItems'), true) – Cooper
I sometimes make my named ranges a little larger than needed so I can add to them programmatically without having to change the range size. – Cooper
Stack Snippets should be used only for executable HTML/CSS/JavaScript – Rubén
@Cooper, that was a good thought that I didn't consider. I tried it this morning, but unfortunately it's producing the same results - rather than using the actual named range in the rule, it applies the address. I see what you mean about making named ranges larger so that they can be expanded programmatically. I sometimes do this by using apps script to insert rows in the range so it expands the references automatically. Here however I was hoping to use the the named range. – Alex Libengood
You could try clearing the old validation and setting up a new one every time you change the “TaskItems” address – Cooper

2 Answers

1
votes

I had exactly the same issue and was disappointed to not find an answer here. It's very odd that something you can do manually isn't possible via a script.

But I just figures out a work-around.

Add the validation manually to a cell somewhere which refers to your named range. Then in the script, COPY the validation from that cell to wherever you want it. The copied validation rule uses the name of the named range - just as required.

Here the script I used for testing this.

function setvalidation() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("TEST");

  var vrule = sheet.getRange(1,1).getDataValidation();  
                               // Previously, you would have set up the validation in cell A1
  sheet.getRange(1,2).setDataValidation(vrule);         // Copy the validation rule to cell A2

}
0
votes

Data Validation, as well as other Google Sheets features like Conditional Formatting, doesn't work with named ranges.

You are trying to solve problem X, in this case handling the data validation's value in range reference, and you think solution Y, in this case using a named range would work, but instead of asking about X when you run into trouble, you ask about Y.

If you are using the named range solely to handle the value in range reference, then rather than updating the named range reference consider to update the data validation rule.

Reference