Data Validation from script but using a named range

Question

I'm trying to figure a way to add DataValidation with named range name as "Range" value. Using getRangeByName in requireValueInRange provides me with just "A1:A50" while i need to find a way to put "SampleRange" (which is range name) in it's place. It is possible to do manually but i have 300 rows to update one by one and i need a way to automate it.

function ValidationNamedRange() {
    var NameSpace = "SampleRange";
    var rule = SpreadsheetApp.newDataValidation().requireValueInRange(NameSpace).build();
    range.setDataValidation(rule);
  }
}

Thanks for the help!

In Google Apps Script syntax casing is important, GetRangeByName and RequireValueInRange will throw syntax errors. — Wicket
– Wicket, Commented Apr 20, 2018 at 12:03
The code in the question doesn't show the problem. Add a minimal reproducible example — Wicket
– Wicket, Commented Apr 20, 2018 at 12:14
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

Wicket · Accepted Answer · 2018-04-20 01:33:50Z

0

GetRangeByName outputs the range as A1 notation

This is wrong. Besides the correct syntax is getRangeByName(), this method returns a Range object.

Example

The following will add a data validation to the specified named range.

First create a named range, and set its name to NamedRange1

Execute the following script

function myFunction() {
  var name = 'NamedRange1';
  var rng = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(name);
  var rule = SpreadsheetApp.newDataValidation().requireNumberBetween(5, 10).build();
  rng.setDataValidation(rule);
}

answered Apr 20, 2018 at 1:33

Wicket

39.4k9 gold badges81 silver badges201 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Tomasz Borkowski Over a year ago

Thank you very much for answer. I guess i wasn't clear enough. I need to find a way to add a Named Range name as range value in validation. When i use "GetRangeByName(SampleRange)" I get data validation with "A3:B400" and I need a way to have "SampleRange" instead. Is there a way to create DataValidation rule that will work like this?

Sander Over a year ago

this adds dataValidation on a range, but the question is actually, how to add datavalidation that adds a dropdown verifying the content 'against' a named range it's easy enough to generate a range, but it will always translate to the A1 notation within the datavalidator as far as i know, I have not been able to do this either as of yet.

Collectives™ on Stack Overflow

Data Validation from script but using a named range

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related