How to set Custom data validation in Google Sheets (A really simple step-by-step guide needed)

Question

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:

1. Tools > Script Editor ...
2. Paste the code inside a function, e.g. validate()
3. 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:

1. Tools > Script Editor ...
2. Paste the code directly in the editor

Answer 1

Overview

From Extend Google Docs, Sheets, and Forms with Apps Script - Docs editors Help

If you’d like to learn Apps Script but you’re new to programming, Codecademy offers a great online JavaScript course for beginners. (Note that this course was not developed by and is not associated with Google.) Once you feel like you have a handle on JavaScript, try either of these Apps Script quickstart tutorials: Macros, menus, and custom functions or Managing form responses.

Broad steps

1. Design the automated procedure (similar to a macro) aka script.

2. Create a Google Apps Script project. A bound script is the easiest way for custom validations for Google spreadsheets.

3. Create the main script.

4. Set the trigger method (optional):

   1. Events object.
   2. Installable trigger.
   3. Custom menu.

5. Execute the script. Repeat as it be necessary.

Note: The script could be called from the Script editor.

References

Docs editor Help
Google Apps Script - Google Developers
Data validation - Extending Google Sheets - Google Developers