DREW CSILLAG: Hi. Wow, that's loud. I'm Drew Csillag, and welcome
to storing data in Google Apps Script. If everything goes to plan and
everything works, we're going to be going on a tour of the
four main storage systems available within Apps Script. Three of them you're probably
already aware of if you've used Apps Script
at all before. The other one, if you've been
to any of the other Apps Script talks so far, they've
totally leaked it. And so it won't be
a big surprise. However, they were at least nice
enough to plug this talk. A lot of useful scripts need
storage in some form or other. And in most usages of script
today, the storage is often related to the spreadsheet
that you embedded the script in. Since we now have scripts
in Google Drive and at script.google.com, it's
important to know what storage systems you have available to
you so you know which one is most appropriate for
your use case. But before we start, it always
helps to have a use case to help illustrate the concepts
that I'm going to show you. Google has loved LEGOs since
way back in the beginning, when Larry and Sergey decided
that they needed to build a hard drive enclosure for the
servers they were building. One of these originals
is actually on display at Stanford. Since then, some offices,
notably the New York City office, have LEGO areas, where
we can experiment or play or blow off work or that kind of
thing, to help us with our creativity. Or at least, that's
what we're told. I myself have a number of
bins of LEGOs at home. Actually, that's not
entirely true. My daughter has a number of
bins of LEGOs at home. She just lets me play with
them, because she's cool. Anyway, there have been a number
of times where we've been building something,
and you run short of the number of LEGOs. You're, like, 12 bricks shy of
being able to finish the thing that you wanted to build. And I thought that having an
inventory of the bricks that I had would be really handy, so
I can either design around what I have or at least know
that I'm going to run into trouble before I'm done. So I decided to start cataloging
the bricks. And I figured, hey, I work
on the Apps Script team. It might not be a
bad tool to use. So my plan was to start and
keep things really simple because I don't want to
have to do any more work than I have to. And so I decided to start
working with the simplest of storage systems we have
in Apps Script. And that's ScriptProperties. ScriptProperties is a simple,
string-based key value store that's tied to your Apps
Script project. Now, what exactly
does that mean? This means that a given script
project can only see the properties that belong to it. It cannot directly see any of
the script properties that belong to any scripts that you
might reference through the Script Libraries feature
that we have. Because you might store things
like JDBC connect strings or URLs or things that have
authorization credentials, and you don't want those going
all over the place. If you want to share
them with the other ones, you can do that. It's just a manual affair. It's not going to
be automatic. ScriptProperties works best
when you're dealing with smallish amounts of data that
the script needs that don't change terribly often. When I said that
ScriptProperties is the simplest of all storage systems
in Apps Script, I wasn't kidding. It's got exactly two methods,
one to get the string value by key, and the other
one to set it. One thing that's really nice
about ScriptProperties is that if you don't want to write
code-- because I prefer to write as little code
as possible-- that if you don't want to write
code and have to fill the store up with the values
or build a UI to do it, you don't have to. In fact, it's completely
reasonable to never call that second method, because in the
File, Project Properties menu, there's a Project Properties
tab, where you can edit, list, add key value pairs. And they're immediately
accessible through the UI. You may notice that the API is
called ScriptProperties, but the dialogue says Project
Properties. This is mostly a historical
artifact. Originally, because Apps Script
didn't originally have the notion of a project, there
was the spreadsheet and the script that you embedded
in it. And that's all there was. Fast forward a couple of years
now, and between being able to have multiple scripts, and now
we've got HTML resources and other things, it evolved into
the project as it has now. But we didn't change the name of
the API, because we didn't want to break people's scripts
unnecessarily for something that was basically a
cosmetic change. For my LEGO brick inventory,
I made my keys to be very simple, the shorter side, x, and
the longer side, and the value was the number of
bricks that I had. And it was really simple to
find out for a given brick what I had. Basically, you get the property
with two-by-four if I want two-by-four bricks,
convert it to a number, and I'm done. Easy-peasy, lemon-squeezy. Well, ScriptProperties has some
really nice advantages. Again, the API couldn't
be simpler. There's two methods, one of
which you may never have to use because of the
built-in UI. Another great advantage is
that, in order to use ScriptProperties, you need
to do exactly bupkis to set this thing up. You just start using it. Lastly, and it's best when it's
used for its intended purpose, for settings
related to a script. That is, in other development
environments, you might have a ,ini file or an XML
configuration file or something like that. For Apps Script,
ScriptProperties is how you do that. There are some things that
ScriptProperties is not a particularly good choice for. If your data is going to change
frequently, like more than once per second or so, then
ScriptProperties is not your best bet. Also, as I said earlier,
ScriptProperties is for smallish amounts of data. And this is more specifically
what I meant by here. And so if your data's expected
to grow to a decent size, you're going to run into that
ceiling before very long. And now that we have Script as
a web app, where we can use the user's credentials, if
you're going to store user preferences in there, and your
web app becomes extremely popular, you're more than likely
to run into that size limitation, as well. And while in this LEGO example
I'm kind of doing this, and we've seen a number of cases
where you guys and even my boss has done this,
ScriptProperties is not a generalized database. It doesn't provide any built-in
search facilities. And from what we've seen when
we've seen people do this, is that they have to write and roll
up their own search and query facilities. And lastly, structured values
don't do particularly well when you're using
ScriptProperties. You can use JSON and CSV in the
cases where you need it. And for one-off here and
there, that works OK. But if you're going to be
storing lots of data that way, it doesn't work especially
well. So now that I had my bricks
cataloged by size, I decided that I also wanted to track how
many of each color I had. So I had a couple of choices
of how I wanted to do this. First, I could try putting the
color into the key, like this. But then it becomes extremely
difficult to find out how many two-by-four blocks overall,
because I have to know every color that I might
possibly have. Alternatively, I could keep the
color out of the key and make the value a CSV of the
color in the count. But if I want to find out how
many blue bricks I have, obviously I have to know all the
brick sizes I ever have. Ultimately, what I wanted to
do has now outgrown what ScriptProperties is
really good for. And the next obvious one
is spreadsheets. Spreadsheets can be used for all
the kinds of things that ScriptProperties can, but
handles things like structured data with ease. Like ScriptProperties, it has
a high-functioning user interface, the spreadsheet
editor. For doing data entry, you've
got the spreadsheet editor, which allows you to do all
sorts of things, like including formulas and such. But we also have Google Forms
to do data entry, which is really, really handy. Also, if you need to copy or
export and convert your data to different formats, with
spreadsheets you can do that very, very easily. You can copy them, as well as
import and export the data. For the LEGO brick inventory
sheet, you can set it up something like this. And as you can see, we get the
nice colors, we get fonts. I used formulas to compute
the totals. And it was actually
pretty simple. And if I get another color
brick, it's just adding another column. If I get another brick size,
it's just adding another row. It's really simple. However, this flexibility
comes at a cost. The API for spreadsheets is
necessarily more complicated. If we look at the sample, the
function getBrickRow takes in a description of the brick,
like two-by-four or one-by-two, and returns the
row in the sheet for that brick type. So first, we get the
active spreadsheet. And then I always get the sheet
by name that I want, because who knows what people do
with these things, because I've messed up my spreadsheet
before. So if I get the one by name, I
make sure I always get the right sheet in the
spreadsheet. From there, I get a range of
the second row, one column wide, down to the bottom
of the sheet. And then we get the values. The data array that we get back
is a list of rows within the range that we chose. The elements in that array are
going to be single-element arrays, each containing
one-by-two, two-by-three, that kind of stuff. Well, here, then we go and
iterate over that array, looking for the brick
that they asked for. And then when we find it, we
return the index in the array that we found it at. You'll notice it says I+2. One of those two comes from the
fact that we started from the second row in the
spreadsheet, so we have to have one to compensate
for that. The other thing is also arrays
in JavaScript are zero-based. Spreadsheets, the rows
are one-based. So we had to add an extra one
to compensate for that. And if all else fails, we just
say, sorry, too bad, so sad. We couldn't find what
you're looking for. Now, for a moment, pretend that
we have a getColorColumn that does basically the same
thing, but returns the column for the color. With this, we can find out the
number of bricks of the given type and color with the
getBricksByColor function that we have here. Here, we get the sheet the
same way we did before. And then we get a range of the
row and the column from those two functions that we called
with the color and the description. And we get the value from it. This allows us to answer
questions very simply to say, how many blue bricks
do I have total? Since we've got the total
columns, we can use those column headings to find those
rows and columns. So we can get all two-by-two
bricks, all blue bricks, or all two-by-two blue bricks. So as you can see, spreadsheets
have some of their own advantages compared
to ScriptProperties. As I said, it has a built-in,
high-functioning UI and handles structured data much
more easily than you can in ScriptProperties. However, again, this
comes at a cost. Specifically, if you're fetching
lots of cell values from spreadsheets, you'll notice
it's a whole lot slower than ScriptProperties is. When you're dealing with
fetching data from a spreadsheet, you want
to try to fetch a rectangle of it at a time. That'll be much faster. If you want to illustrate this
to yourself, what you can do is you can write a script that
fills in 10 cells one by one, and then one that writes
10 cells all at once. And you'll notice how-- you can watch it happen. And it's very much-- so the upshot of this is that
you want to try to get and manipulate data in
large chunks. There's also some limits that
you need to be aware of. There's at most 50,000 rows in a
sheet and at most 200 sheets in a spreadsheet. If it gets anywhere near this
size, you'll probably experience lots of
pain anyway. They do get slower to load as
the more data you put in them. So in any case, for what we
were doing so far, the spreadsheet handles what
we're doing OK. But how do we expand it when
we start adding things like LEGO plates? LEGO plates, they look like LEGO
bricks, but they're about a third as thick. Well, in this case, we've
literally added a third dimension to the data. And while we could put it in a
spreadsheet, we've already had to write a bunch of search code
to be able to do what we're doing so far. And I didn't want to have to
write any more search code, because I've done enough. Well, what else could we do? Next one was JDBC. Using JDBC allows us to do
all this quite nicely. And our search code just becomes
comparatively brief SQL statements. We can now add the
height attribute. And the table would look
something like this. Since the plates are one third
as thick, I figure I'll keep the standard height brick
as now three. Well, how do we use this
from Apps Script? Well, here's an example. To get anywhere, you need to
get a database connection. And here, we're using a standard
JDBC connect string to connect to a MySQL
database. Apps Script supports MySQL,
SQL Server, and Oracle. And since database connection
strings contain authentication credentials, you might not want
this in your code, plus the fact that database servers
do move from time to time as things happen. So here, I used ScriptProperties
exactly for its intended purpose. And so we keep the JDBC URL in
a ScriptProperties and just reference it here. Once we've got the connection,
we create a statement, give our SQL statement over to it,
and get the result set out. And then we iterate over
the results set. Results, they have the getObject
method here. But they also have a bunch
of get and then type. They have getFloat, getDouble,
those kinds of things. So if you want to try to do type
coercion on the fly as you're extracting these from the
result cursor, you can do that there. If you notice, there's
numbers and comments after these lines. You can call them either with
the column number of the result or with the name of
the column in the result. Generally speaking, I've found
it's a good best practice to always use the name, for
a couple of reasons. First, the name makes it much
more obvious to the reader of this thing what column you're
referring to, because that's a whole lot easier to
read than this. Second, if you're doing things
like SELECT *, and somebody decides to add another column
to your table and then drop another one, then all
your numbers change. And then this loop
stops working. Now, once we've extracted all
these things, we build the result object, throw it in
an array, and return it. But getting all the bricks is
not a terribly useful thing all by itself. Let's say we want to find
all the blue bricks. Something that I've seen over
the course of my career is code that looks something
like this. All I will tell you right now,
and I will explain why, is never do this. The potential problem is
actually with every SQL database API in every
platform everywhere. This is not at all unique
to Google Apps Script. What's the big deal? It works, right? If you substitute in blue, you
do the query, you get all the blue bricks. And it will return exactly
what you want. And that's good. But if some user of the function
can pass in some nefarious value for color,
bad things can happen. There's actually a cartoon
that covers this. If you plunk in the term "Bobby
Tables" into your favorite search engine,
you will find it. It's very funny. But I'll illustrate the
problem to explain it. So if you call the function this
way, yes, you will get no rows from this when you
do execute this query. However, you will no longer
have a table with which to query after you're done, because
when you take that statement, and you add that
argument to it, the SQL you get there is what you
wind up with. So now that I've told you
exactly how not to do this-- actually, let me back
up a second. If you learn nothing else from
this whole session-- and as much as I want you to
remember the rest of it, if you remember nothing else,
remember this, because you never, ever, ever want
to do this-- so now that I've told you how to
do it wrong, here's how you do it right. All the databases that Apps
Script supports support the notion of something called
bind variables. And here, we're using
question mark. And this is basically a
placeholder for the value you actually want to pass in. And much like the get methods
that were on the result object, on the statement object,
there are a bunch of set methods, which allow you to
set the value of what those placeholders are. And they are numbered
from one. In this case, we've got just
the one, so we're good. And then the loop follows
as it was before. JDBC has some nice advantages. First, it's SQL. And pretty much, these days,
a lot of people know SQL. So it's not that hard. Second, if you use reporting
software in an existing database you have, you can
continue to use that. Apps Script is just
another client. And third, you've got the
transaction and consistency guarantees and all those kinds
of things that you're used to. However, it's not all rainbows
and unicorns. Unlike ScriptProperties and
spreadsheets, you have to have this database hosted someplace,
with all the provisioning and setup headaches
that that implies. It can also be slow. And actually, this delay,
depending upon where things are, can actually be built in. The problem here is that, say,
if the Apps Script server that happens to go to try to talk to
your database lives in New York, and your database is in
LA, even if everything was traveling at the speed of
light, it would take 24 milliseconds to make
the round-trip. And that's before you've routed
any packets and oh, yeah, even executed
your query. So talk about the importance
of data locality. And also, as I said before, if
you remember nothing else, be careful about SQL injection,
or you will have a really bad day. Another thing also that we
noticed, if you looked at the code sample, is that you spend
a lot of time shoving things in and out of SQL. And it's kind of a
pain in the neck. A lot of other languages, Java
and C# and those, they have all these frameworks that
basically handle the marshaling aspect, using either
data access objects or object relational mappings. A lot of what they're doing is
really just handling the data conversion between them. And again, you have to
plan a bit up front. You can't just kind
of do it ad hoc. You can't really kind of wing
it, because the expression I've heard before is that data
comes and goes, but schemas live forever. So to illustrate, as we move
to cataloging more of these LEGO parts that I have, I had
a bit of a dilemma, because not all LEGO parts are
easily describable the way we did bricks. Not all of them are
rectangular. They have the ones where the
number of studs on the top is different than the number of
little holes on the bottom. We have LEGO minifigs, which
are the little people-- and they look nothing at all like a
brick-- and satellite dishes and everything else. So how do we represent these
sanely in the SQL database? You've got a few choices, none
of them I particularly like. But you can have one
table that's got, like, 5 million columns. And each item has some subset
of those filled in. And I hope you know what-- you'd better be careful which
ones you fill in. You can try to partition the
parts into things that sort of look the same and then
try to basically do the same thing there. But you have a granularity
challenge, like how narrowly do you do these. And then another option that
I've seen before is you compose an attribute
value table. But those tend to be a bit
of a bear to query. Actually, in some cases-- I think I was talking to
somebody who said they wound up basically implementing their
own query optimizer to be able to do it. So for this LEGO example, it
turned out that the SQL database is probably not
our best choice. Well, today, I'm not really
announcing it because it's already been announced, but-- ta-da! We're launching something
that's even easier. And it simplifies the way that
you can manage data within Apps Script. It's sort of like if I do
everything right, it'll kind of change the way that
you think about ways that data is organized. We're calling it ScriptDb. And this is kind of a big
deal, because it's a JavaScript object database. You're working in Apps Script. You're working with JavaScript
objects and data all the time. Why not just deal with all that
marshaling stuff and make it so that you don't have to
deal with it, you just save what you want? And the other nice thing is that
the query syntax is very, very simple. The basic premise is what we
call a query by example. Sometimes, we refer
to it as QBE. The idea is that when you call a
query, you give it an object that looks sort of like what
you want to come back. And we go ahead and figure out
what that means and return you the objects that make sense. ScriptDb is really convenient
because it comes ready to use, which means that there's nothing
to host, there's nothing to set up, there's
nothing to configure, there's no installation. All you've got to do
is use the API, and you're good to go. All right. So how do we do that? Well, let's start simple,
and we'll go from there. All access to the database
is via a database object. And in ScriptDb, you get it by
calling ScriptDb.getMyDb. Like the way ScriptProperties
works, this database object is tied to your script project. That means that other projects
that may use yours as a library don't have direct
access to it. However, you can pass this
database object to other libraries that you may be using,
and they can access it the same way. But a good best practice is to
locate all of your database code in one library and then
export functions that manipulate it through the
libraries feature. Saving objects in ScriptDb is
actually kind of simple. Here, we make a part that's just
a two-by-three standard height brick. We've got 52 of them. And they're blue. And we say save. The object that's returned
by save is almost exactly identical to the argument to
save, except that it's got a getId method on it. It's the internal ID that allows
us to be able to access this thing later. And one of the things that's not
illustrated on the slide, but when you save things in
ScriptDb, like we have, it's a fairly flat object. It's just got attribute at
value, attribute, value, attribute, value. But the values themselves can be
objects themselves, whether it be arrays or nulls or
Booleans, numbers, strings. The only main JavaScript type
we don't currently support directly is Date objects. But what you can do is you can
call getTime on it, get the milliseconds as epoch value,
and then save that instead. So the other thing, also, that
the saved object allows us to know is whether or not this
is an existing object in ScriptDb or not. And this allows us
to do updates. And here, we're taking the
object we just saved. We say we're setting a
Boolean flag on it. We're updating the count. And we just save it again. The idea that we had is actually
useful because you can just say, I want
to load it. Or if you have an array of IDs,
you could just load the whole array. Now, if the object with that
ID doesn't exist, you get nulls back. The array of parts that you get
back is going to be the same length as the
array of IDs. For any one of those IDs that
doesn't exist, there's a null in that spot instead. So this way, you can identify
which ones are which. We can also delete
things by ID. Or if we already have the object
in hand, we can just say, delete the part. But the interesting thing is
when we get to queries. Now that the data is there, how
do we find what we want? Well, we'll start with
a basic query. First, we get the database. And this is how we do queries. In this case, we're saying, give
me all the objects in the database where the short side
attribute is equal to two. That's pretty simple. And then the result
object looks a lot like a Java iterator. If you've seen Java iterators
before, it's the same pattern. Basically, while the result
still has a next item, we get the next item out of
it, we process it, do whatever we like. Now, if we want to find all
two-by-four bricks, we just add another attribute into the
query object to do it. It's pretty simple. We can also query sub-objects
the same way that we query the top-level items. So this case, say I've got-- my LEGOs, actually, I keep track
as to which ones are in which bins in a bag. You can query these
sub-attributes of these objects, as well. We also support doing
alternation or OR. In this case, we're saying,
give me all the LEGOs that I've got where the short side is
two and the color is either green or blue. So far, so good. But what if I want the
ones where the long side isn't three? We also have NOT. Now we get a little trickier. I've got more than
just LEGO bricks. I've got those little LEGO
men, the minifigs. And they have various parts and
colors and decorations of their own, like heads and
torsos and legs and accessories and equipment. So what I want to do is, how do
I format these things so I can find all black
minifig parts? So if we have minifig parts laid
out, like up there on the top, you can use any value
just to say that this attribute exists at
all in the object. We also support things like, as
your database gets bigger, you might not want to deal
with them all at once. We allow you to say, like a
limit query, this is just like the way it would work in SQL . You just say, I want 20. Limits on their own are not
terribly useful until you can do ordering. By default, when you sort, it'll
sort them lexically, like it does on the left. However, when you're sorting
numbers, you'd probably prefer that 2 sorts before 10 instead
of afterwards, so you can pass the DB numeric argument as
the second argument. And that'll take care
of sorting it the way that you want. Now, in the course of my career,
when I'm dealing with databases and building a UI,
oftentimes, you're building some kind of paginated
display. And so here, you've got your
ordering criteria. You're saying, start at
page number times page size, limit page size. So like in our case, our
page size is 20. So give me the second
page of 20 results. And every time I've done this
ever in my life, I always seem to manage to screw it up. And so I decided when I was
doing this that I was finally going to capture the pattern
so I never have to get it wrong ever again. And so I added the
paginate option. You still can you "start at"
and "limit" if you choose. But you don't have to. Like JavaScript double equals,
we also support the same kinds of type equivalences
in our queries. So if you have saved something
as numeric 3, you can query it with string 3 or Boolean true. I won't read the slide. I assume you all can read
because you're here. But as another example, if you
save an item with a string value of 2, you can find with
querying for the numeric value of 2 or true. If you query it with the Boolean
true, that'll match any true value. So you'll obviously get things
other than 2 as a value. Now, this is the question that
almost always gets asked when I get this far. When you're using data stores,
you need to partition your data into logical subsets
of related data. For example, if you're in a file
system, they have, then, this notion of directories
and sub-directories. So you don't have to deal with
all your data at once. You can just deal with, say, the
photos from the last time you went to Chicago or a bunch
of salary letters or anything like that. In database land, you have silos
like all the HR data or all the data that corresponds to
some subsidiary corporation that you have. In a finer grained data silo,
it might be the table of current employee compensation
or the data for SKUs in department 29. The mechanism for siloing data
in database land is either by the server, the cluster it's in,
the database instance, the database within the instance,
the tables, and even to a certain extent, the columns. Data silos come in a couple
different flavors-- actually, exactly two-- disjoint and conjoined. Disjoint silos are silos where
a record can be in, at most, one silo of the given type. An example of disjoint silos are
things that are cars and things that are people. There's obviously no overlap
between those two. Or people that are 20 years of
age or people that are 21 years of age. In traditional databases, these
are often implemented with tables, since while a given
row, you may have copies of it in other tables, a given
row is actually only in one table at a time. Conjoined silos, on the other
hand, are silos where things can be in more than one
silo at a time. An example is things that have
email addresses and things that have postal addresses. You can have any of the four
different combinations of having these or not, and
they're all valid. And in traditional databases,
you can represent these using views. So let's apply these
to ScriptDb. With ScriptDb, you could silo by
the script using the Script Libraries feature, and then by
different combinations of attribute presence or value. A simple way to do disjoints
sets is like we do up top in what I call a table attribute. Since an attribute can have,
at most, one value at any given time, that kind of fits
the definition of a disjoint silo quite nicely. One point to make here is that
there's nothing magical about the name "table." We could have
called it "tableau" or "mesa" or "Fred." ScriptDb
doesn't know what "table" means. So you can use whatever
you want. In a way, the silo and conjoined
sets is what I call an implicit or an inferred one,
in this case, something like minifig type, just checking
for the presence of an attribute. But sometimes, there's no way
to after the fact infer a particular conjoined data silo
that you're looking for. In such cases, you can add
explicit flags to the data, like we do on the third case. If you have the mental model of
how Gmail labels work, this is very, very similar to that. When you're choosing how to
lay out your objects in ScriptDb, something that's a
really good idea is to name things consistently within
the objects. Within a silo that you've
preplanned ahead of time, it's kind of obvious. If we're storing LEGOs, and we
want to know colors, we don't want to store it sometimes as
color, sometimes as brick color, and sometimes as color
brick, because it makes the queries kind of ugly. However, even across your data,
it's a good idea to always call the same thing
by the same attribute. For example, in the examples in
this session, the color is always the attribute
simply named color. If we stick to that, when we add
things like axles or moon plates or roofing bricks or
whatever other LEGO parts we can come up with, we can always
pull all things of a particular color just by
querying a color attribute. How about another example? Say you have a database
containing your organizational information, like the org
chart Expense Reporting. If you always call the
department ID department_ID in your data, you can, in one
query, pull all of the stuff relating to department ID,
whichever one you choose, all in one pull. Now, eventually, it's going
to happen where you don't actually follow through with
this, and things don't go right, and things weren't
named consistently. After all, to err is human. Are you doomed? Fortunately not. One query feature that I haven't
mentioned yet is what we call attribute OR. Much like we have db.anyOf for
values, we also have a syntax to represent the query where
this or that attribute is equal to some value. Instead of having an unquoted
attribute, like I've had in all the examples so far, you
have the attribute be a quoted, comma delimited
list of attributes. So in this case, it would return
all the ones where the color was black, irrespective of
whether or not you spelled "color" with a U or not,
or which side of the Atlantic you live on. If you noticed earlier, I used
the term "preplanned silos." Some silos are very intentional,
like the table attributes or the flag-based
silos I mentioned earlier. But if you name things
consistently, often useful silos can be discovered in
your data after the fact. I've already been using a few
of them already in the talk. The one about color I've
already mentioned. But there are a few
others, as well. One silo is the bricks siloed by
one of their sides, whether the long side or
the short side. And if you look across your
data, you'll find that useful silos turn up in a bunch
of different places. Even if you're using SQL
databases, it's actually a good idea to do this, too, for
a lot of the same reasons. Well, so far, so good. The only thing left over that
I can think of now is joins. Well, what about joins? Well, first, joins are needed
less often, because if you name things consistently, you
can avoid a lot of them. In a traditional database, you
can't really cut across your data in quite the same way that
you can here because if you're crossing multiple tables,
you either need to do a union query or a
join for that. Second, multiple queries
in ScriptDb are not that big a deal. ScriptDb queries are
comparatively cheap, often in the neighborhood of 30 to 50
milliseconds, a little bit more expensive than the speed
of light round-trip from New York to LA and back. Your first database operation
in the script will take a little bit longer, because
we do have to load some information about your database before we can proceed. Traditionally, when working with
SQL databases, I've seen people go to somewhat extreme
lengths to formulate the query so that they can get the
data in one pull. But in ScriptDb, multiple
queries are actually just fine because they are, again,
fairly inexpensive. So say we want to find what
color of standard height brick we have, that we have at least
50 of them, and they're two-by-two bricks, and then to
make sure that we have a torso that's the same color. So first, what we do is we query
our two-by-two bricks, standard height. And we say, give me the ones
where we have at least 50 or more of them. We'd loop through the results
here, storing the colors in the color variable. And then, secondly, we just
query for minifig torsos, where the color is any of the
colors that we found. So it's actually not
that difficult. We are looking for common
patterns that we can make these kinds of things
simpler for. There are some limits
that you need to be aware of in ScriptDb. Query results can be at
most 50,000 rows. Though the size of your
actual data set can be much larger than that. It's only governed
by the quota. And the quota limits
are given here. So hopefully, you've got now the
information that you need when developing your next Apps
Script program as to choose which storage system
is best for you. To summarize, ScriptProperties
is a simple key value based store with a built-in UI,
spreadsheets when you need more structure and a more
advanced built-in UI, JDBC for interacting with existing legacy
databases you have or when SQL is important to you,
and ScriptDb for a zero setup JavaScript object store. If you want for more information
about Google Apps Script, ScriptDb, you can
go to the respective URLs in this slide. Yes, we've now got some time
for questions if you want. Please go up to either one of
the microphones so that the people on YouTube
can hear you. Great, thanks. AUDIENCE: Hi. Is the ScriptDb one instance
per script, or we can have different instances
for each user that's running the script? DREW CSILLAG: It's tied to the
script project itself. So there is one per script. Did I answer your
question right? AUDIENCE: OK, but the quota then
stays script owner then? DREW CSILLAG: Yes, the quota's
charged to the script owner. AUDIENCE: All right, thanks. AUDIENCE: You mentioned a best
practice around making a store available to multiple scripts. Would you mind just
reviewing that? DREW CSILLAG: Yes, sure. Actually, if you watched
Saurabh's talk the other day, you can kind of get an
idea of what he did. But basically, the idea is you
put all the database access that you need for your ScriptDb
database into one script project. And then the projects that need
it can then reference it as a library. And you could do it that way. Or again, you can pass the
database object between the libraries as well, if you
want to do it that way. But the best practice is to
co-locate it all in one spot. Hi, go ahead. AUDIENCE: So you talked about
doing a query with a comma separator for two different
words, where effectively, they mean the same thing. So you have some limitations,
it seems, on what the-- DREW CSILLAG: What
the keys can be. AUDIENCE: --what the
keys can be. DREW CSILLAG: Yes. Currently, they are limited to
what you can put into a normal JavaScript identifier. AUDIENCE: Identifier. OK. Not an object key. Because you could have the
comma in an object key. DREW CSILLAG: Right, yeah. But these are-- I'm drawing a blank on
what the thing-- it's a JavaScript identifier is
what the allowed characters set for a key. But the values can
contain whatever. AUDIENCE: OK. And the other question is, when
you're returning multiple values, you return null
to indicate that the value wasn't there. DREW CSILLAG: Yes, when you're
loading with an array. Yes. AUDIENCE: But that means
that you can't-- is there a way to distinguish
whether the value was there, but its value was null? DREW CSILLAG: OK. Yeah, something I didn't mention
is that when you're storing objects in Apps Script,
they have to be regular, full-on-- like an object with attributes
and values. I can't just store the
number 3 directly. So all the things that come
back from a load will be JavaScript objects with key
value pairs in them. If I'm explaining that-- AUDIENCE: OK. DREW CSILLAG: That make sense? AUDIENCE: Thank you. DREW CSILLAG: OK. AUDIENCE: What is the limit,
how many records or-- what can I store? Can I store millions
of records? Or is there a limit? DREW CSILLAG: It's limited
by the size of the stuff you put in there. It's not based upon a row
limit or a row count. AUDIENCE: You mean limited
by the total size of what I store? DREW CSILLAG: Yeah, by the
total size of the store. It's a size limitation. It's not the number of rows. AUDIENCE: And what's the size
limit for the store? For a paid customer? DREW CSILLAG: Consumers,
50 meg. Google Apps customers
are 100 megs. And-- AUDIENCE: Apps for
business, 200. OK. And is this still in beta,
or is it production? DREW CSILLAG: I think it's still
tagged as experimental. AUDIENCE: Experimental. OK. DREW CSILLAG: Yeah. AUDIENCE: This was
asked earlier. But yesterday, you talked about
standalone scripts that run with user quota. And somebody asked a few minutes
ago about the storage. You said it's per-- can you review again? Are all the database structures
per script, or are there any that are per user? DREW CSILLAG: Things like
ScriptProperties and ScriptDb, those are charged to
the script owner. But all the other quotas I can
think of are all charged to the user accessing
the thing if you publish it as a web app. AUDIENCE: I have another
question about the quota. Is the quota tied to the
script or to the owner? DREW CSILLAG: To the owner. AUDIENCE: OK. AUDIENCE: Hi. Is there a way that two users
with two different scripts can use the same database? DREW CSILLAG: Yes. Using the script libraries
feature that we talked about earlier, what you can do is you
can make it so that, say, the included library, its sole
function may be returning the database object that's
associated with that common script project that they're
both using. AUDIENCE: So I'm
still grappling with the quota issue. Is there a way to-- if I create a library that
accesses ScriptDb, can I now have individual users who want
to have their own separate databases use that library and
have three users, three separate databases, three
separate quotas? DREW CSILLAG: Yes. Basically, what you would do
is, in the three separate scripts, what they would do is
they would pass their database instance into the common library
that manipulates them. That make sense? AUDIENCE: Yep. DREW CSILLAG: OK. AUDIENCE: I've just got
a quick question. You said there's a
50k [INAUDIBLE] limit. I think it's returned. DREW CSILLAG: That's just
on the query size, yeah. AUDIENCE: So how did-- DREW CSILLAG: If you need more
than 50,000 rows, you'd have to find some way of breaking
it into chunks. AUDIENCE: OK. Is there a way to see how
many you would get? Because what if I want
all bricks, and there's 70,000 blue? I'd have to know what
IDs are in there. Like I didn't know if there was
a way to get first 2,000 on the query or whatever. DREW CSILLAG: I'm going to say
talk to me afterwards. Off the top of my head,
I don't remember. I'd have to look it up. Pagination might do it. Like I said, I'll have
to double-check. AUDIENCE: Is there
a count feature? In other words, can I count
how many bricks I have? DREW CSILLAG: Yes. Actually, there is a method
in the DB object that I didn't mention. It's db.Count. And it takes the same
kind of query object that the query does. Anything else? Well, thanks for coming. Great. [APPLAUSE]
