3

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
1
  • I'm just using the code provided as an example, it's not the actual validation I want to use.
    – Morgoth
    Dec 11, 2014 at 21:39

1 Answer 1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.