CODEWITHSUNDEEP
validationgoogle-apps-scriptgoogle-sheetsnamed-ranges
Alex Libengood
Alex Libengood

Reputation: 553

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

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?

Upvotes: 4

Views: 2496

Answers (1)

Alex Goodall
Alex Goodall

Reputation: 53

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

}

Upvotes: 3

Related Questions