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

3 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.

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.