Data Validation – Apps Script Crash Course

Data Validation – Apps Script Crash Course

[SWISHING] KALYAN REDDY: Hi, everyone. Welcome to Apps Script
Crash Course. Today, we’re going to learn all
about data validation in Apps Script. Now, data validation, as a lot
of you know, is our top requested feature or has
been for a while. We recently implemented
this, in August. And there’s a blog post
announcing the functionality on our blog, You’ll want to check that out. What we’ll do in this crash
course is run through the basics of what data validation
is and how I can do it, programmatically, just to
get you up and running. So what I have here, if I can
switch the example here– so we have just a
new spreadsheet. All I did was rename it. What is data validation? So data validation is the act of
putting rules to a cell so that you can restrict
the contents of it. So in a regular spreadsheet, you
can type whatever you want in any of the cells, and
it accepts the input. But let’s say I want to restrict
what’s in B1, here. I can go to data validation in
the UI, and we can specify a criteria for this range, here. So we can say that we want
to have a list of items. Let’s just do cat, hat, and
bat as the allowed items. And on invalid data, we’ll
show a warning. We’re not going to reject input,
because we don’t want to be that mean. And let’s just go ahead and
save that validation. Now what you’ll see here
is these regular cells are just blank. But this validation cell has a
drop down that shows you the different allowed
inputs for that. So we can either go press
in and select these. Or if it’s just a blank cell,
you can type in something, and it’s selected, as well. Now just to prove that it works,
let me just type in some random nonsense here. And then you’ll see that there’s
a red mark here saying that the cell contents
are invalid. Now let me reset all of this,
here, and we’ll run through a more useful example. Let’s see. Now, in order to get rid of data
validation rules in the UI, you have to go back to data
validation and remove validation. So now we’re back. This is just a normal
spreadsheet. And what we’re going to do is
programmatically create the same thing in a slightly
more useful example. So let’s say you’re inviting
some people to come to your house for a party, and you want
an easy way to find out what someone’s response is. Now this is in a world where
Google Calendar doesn’t exist, and there’s no other mechanisms
in order to do this more efficiently. So we’re going to use a basic
spreadsheet for this. So now, we’ll just set up
quickly two columns here. One will be Name, and one
will be Response. Let me just bold that. Now we can have our
invite list here. Let’s invite Bob. Let’s invite Tim. Let’s invite Jane and
then Joe, also. Now what we want is, we don’t
want to send this list to them, and then they can say,
“fo sho” or anything. We want to restrict it so that
maybe we can send emails automatically and take actions
programmatically based on what they wrote. So what we want to do is ideally
provide them choices saying, yes, no, or maybe
in this Response column. In order to do that, we
can use Apps Script. Go to Tools Script Editor
to pull that up. We need to get a reference
to this range. And then we need to create a
validation object, and then we join those two together in
order to apply it on the spreadsheet. I’ll show you how that works. So let’s get that range
first, not range. [LAUGHTER] Equals. Now SpreadsheetApp.getactive
gets you the active spreadsheet. And then you can further say get
range and give it the A1 notation of B2 to B. Now, some of you are familiar
with this, but I’ll just explain for everyone else. Now when I just say B here, it
means that start B2 and then go all the way down in that
column and apply to every single cell there. So now that I have the range,
let’s create the validation object Now, data validation, the object
itself, lives within the spreadsheet app service. So we’ll start by going into
Spreadsheet App and then New Data Validation. As you can see, this
returns a data validation builder object. And a builder object– it’s a common paradigm that we
use in other places in Apps Script, as well. What it allows you to do is
apply operations over and over to this object when you’re
constructing it. So you’re the builder,
and you’re making it. And so one thing we want to do
with data validation is we want to give it the type of data
validation, and we want to give it the validation list
or the validation source. In this example, the type of
data validation would be we want to provide it a list
of immediate values. And we want it to tell the user
if it’s valid or not. So let’s go select
the type first. Now we can say a dot and then
use autocomplete to see all the validation rules. And we’ll say, Require
Value in List. And we’ll select this overloaded
method with two parameters that I’ll explain
in a second. The first one is an array, and
you can provide, in B, acceptable values. Here, there will be yes, no,
if I can type, and maybe. Now what we want in the second
column is we control if we’re going to show the drop down
that these are valid [? assertions ?] or not. So now, let’s just
make that true. So now, what do we do here? This method shows that we want
a list-based validation. We provided it the list, and we
set the dropdown to true. Now, what we need to do is
finally build the validation object, and we do that
by doing build. Now that we have the range and
then we have the validation, we can clump them together. Now, in the range object, you’ll
find a method called Set Data Validation. Now, you can provide it the
validation that we just created, Save, and Run. You’ll notice that it’s going
to ask for authorization. This is because you need to
access the spreadsheet in order to make changes to
the validation rules. So now let’s just go ahead and
continue and accept the authorization in the nice,
new authorization panel. Now when you go back to the
spreadsheet, these cells were blank before, and now they have
this little drop down in them with the list of
yes, no, and maybe. So when you share the
spreadsheet out through the power of Google Docs
collaboration, your invitees can say, no, Bob’s
not going to go. Or Tim’s going to say,
yes, I’ll go, or Jane can type in, maybe. And we’ll wait for Joe to
actually fill out this form. So this is the way that Joe can
later go in and say, cat, here, and screw up your
data analysis portion. Because cat is not valid. And it’ll have this red
mark next to it. So it’s a very simple example of
how to do data validation. If you take a look at our blog
post announcing this feature, we have a more complicated
example that shows you just how powerful it is too
programmatically be able to change all these validation
rules. And let me quickly show you the
power in an example, here. So now, I have a copy
of a spreadsheet from that blog post. And this spreadsheet applies
a validation rule to all of the cells. And right now, these
cells, in this row, are flagged as invalid. And that’s because these
dates are in 2014. What these validation
rules are saying– the validation rules are
enforcing that the dates are in 2013. So now, with the data validation
object and the various methods within, you can
parse this entire document to get all of the old validation
rules, update them all so that instead of 2013,
they’re looking for 2014, and then apply them again to the
spreadsheet, all in a very short number of steps. Now, let’s see. I can go to this menu and
say, change the 2013 validations to 2014. And there you go. Instantly, all of the 2014 dates
are now valid, and the 2013 dates are invalid. Now, if you want to find out how
to do this, there’s some great sample code attached
to this document. And once again, you can find
this by going to our blog post on the Apps Developer blog. We’ll put up a link for that
so you can get into that. I want to thank you for
joining me on this. I hope you found it informative,
and I can’t wait to see what you guys
come up with. Thanks. My name is Kalyan Reddy. Goodbye.

8 thoughts on “Data Validation – Apps Script Crash Course

  1. Kalyan you are a natural instructor! Thank you so much! The way you break down these complex issues into practical steps is invaluable!

  2. Hi Kalyan When I try to follow as the Codes you mentioned i Facing i Problem While Executing : Range.Setdatavalidation in my window the list of Command which will pop-up by usually Ctrl+Space key it will not coming out where am i making i mistake i don't know. Can u Pls help me with this… Is there any problem in my range.setdatavalidation(validation)- I have to write down manually this code..

  3. Hi Kalyan, great useful video. Do you have an example about using data validation with custom formula?

  4. Thanks for your video. I need different lists for each cell in the validation rule and I know how to create it. When I add the validation to each cell one by one inside for loop, some cells are skipped. I think this is due to time require to take effect on each cell while for loop is running fast. How can i control the for loop to go next only after task to current cell is completed.

  5. Thanks for this amazing video, just wondering is any way to get the data from another spreadsheet and make it a dropdown list?

Leave a Reply

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