Dynamic Data Validation with Apps Script

My coworker, Kat, needed to set up meetings with our 13 elementary schools. Appointment slots would have been a lot of clicking back and forth on calendar events plus her dates were variable and subject to change. She set up a spreadsheet with each school on a row. The challenge was to set up data validation rules that would automatically update after a school made a selection.

cracks knuckles

The first thing to do was to list out all the possible dates. Updating a spreadsheet is easier than changing a validation object in Apps Script. I can also loop the sheet and make structural adjustments on the fly.

Once that list was done, we wrote a function to set validation on the sheet using the date.

Now that validation is set, we needed to listen for changes to the spreadsheet. The onOpen function has an event object which holds information about where and how the sheet was edited. This let us limit the function to changes in columns two or three only. If a cell was edited (ie the validation dropdown was changed by the user) the function gathers event data and passes it along to the workhorse.

Now we can update the validation range. Loop through the entire range of dates backwards. Once a matching row is found, delete the row. Looping backwards prevents your index from changing. We also remove validation from the edited cell at this point so it doesn’t show an error for having a value outside the validation list.

Because validation is pulled from a range, deleting the row (and the value) removes it as an option in all of the other cells! Once the sheet is finished and everyone has a date, you could even add a short loop to push dates to your Google calendar as all day events.

Here’s the full script if you want to add it to your sheet.


Featuredimage is Array of Rivets flickr photo by jim.choate59 shared under a Creative Commons (BY-NC-ND) license

5 thoughts on “Dynamic Data Validation with Apps Script

  1. Ali says:

    Very nice article, I’ve really enjoyed reading it. Dear, is it possible to set validation rules in Google forms for short text question type by mathematical logical equation e.g. Input should satisfy input % 2 ==0

    • Brian says:

      No, you can’t use logic operations in form validation. The closest would be to use a regular expression (which you can do) to check for a valid input. If you want logic expressions, you’d have to either validate on the sheet and conditional format or host a web app form that saves data to a Google Sheet. Martin Hawksey has a blog post on a simple implementation.

      • Ali says:

        Thanks sir for your prompt reply, I do agree with you that validation using regular expressions (regex) provide great way to do several rules that would be otherwise nearly impossible using the other available predefined rules. Despite that the given example above (input % 2 == 0) can be easily validated using regex, someone may sometimes need to validate using some logic by formulas similar to what can be done easily in Google sheets. Also using Google apps scripts (GAS) wouldn’t help as validation script methods provided by Google for forms makes also impossible to deviate from the above preset rules. I really appreciate your help for providing your suggested post, and if available please provide other basic sources of information for work-around tricks.

  2. Mehul Gandhi says:

    Hello Can you help me out for the below case?

    I am working on one google sheet, where I have applied data validation on date (i.e. user (i.e. to whom I have shared the file) can enter / select only today’s / current date for data entry.)

    Now the case is, sometime due to some reason, user was not able to do entry on same day, so I (i.e. Owner / admin of file) can violate that data validation criteria and can enter / select previous date.

    I want app script for same.

    • Brian says:

      When you set the validation, there are options in the object to either reject the change or to show a warning. In the script linked, change line 8 of `setValidation.gs` to:

      let validation = SpreadsheetApp.newDataValidation().requireValueInRange(rules).setAllowInvalid(true).build();

      Setting `setAllowInvalid` to true allows an invalid input, false disallows the input. As far as I know, there is no way to allow a single user to follow one rule and another use follow others. You could achieve the same without using data validation by checking the onEdit object trigger and seeing who made the change before accepting/rejecting the edit.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.