I'm trying to figure out how to create custom data validation in Google Sheets what are the steps I need to follow to achieve this?
For example if I wanted the following validation:
// Set the data validation for cell A1 to require a value from B1:B10.
var cell = SpreadsheetApp.getActive().getRange('A1');
var range = SpreadsheetApp.getActive().getRange('B1:B10');
var rule = SpreadsheetApp.newDataValidation().requireValueInRange(range).build();
cell.setDataValidation(rule);
I have tried the following:
- Tools > Script Editor ...
- Paste the code inside a function, e.g.
validate()
- pase the function in the cell I want to receive the validation,
=validate()
, but I received the following error "You do not have permission to call setDataValidation"
And the following:
- Tools > Script Editor ...
- Paste the code directly in the editor