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

Question

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?

You might want to change this .requireValueInRange(spreadsheet.getRange('TaskItems'), true) to this .requireValueInRange(spreadsheet.getRangeByName('TaskItems'), true) — Cooper
– Cooper, Commented Dec 3, 2018 at 20:50
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
– Cooper, Commented Dec 3, 2018 at 21:19
@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
– Alex Libengood, Commented Dec 4, 2018 at 14:36
You could try clearing the old validation and setting up a new one every time you change the “TaskItems” address — Cooper
– Cooper, Commented Dec 4, 2018 at 15:50
Does this answer your question? How to set a named range for a data validation programmatically (in Google apps script) in a Google spreadsheet? — Alex Tullenhoff
– Alex Tullenhoff, Commented Aug 4, 2022 at 20:32

Alex Goodall · Accepted Answer · 2019-01-19 20:23:30Z

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

}

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related