[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, googleappsdeveloper.blogspot.com
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.