How to Grow a Spreadsheet into an Application (Cloud Next ’19)

How to Grow a Spreadsheet into an Application (Cloud Next ’19)


[MUSIC PLAYING] CHARLES MAXSON: Good
afternoon, and welcome. I want to thank everybody
who’s joined us this afternoon. It’s great to see
such a full crowd. Also want to welcome everybody
on the livestream or those who are going to be
watching the recording. I thank everybody for your
time and for your attention. Super excited to be here. I’m Charles. ERIC KOLEDA: And I’m Eric. CHARLES MAXSON:
And together, we’re both part of the Google Cloud
developer relations team, and we focus on G Suite. And one of the neat things
about the work that we do is we get to see all the
interesting ways people try to use G Suite to
build applications. And our favorite
specialty, both because we find it really useful, but
also it excites us a lot, is the way people
use spreadsheets in their organizations. And so the idea behind
this session was, let’s talk about how do we
take spreadsheets and make them into applications? Now, real quick, a little
bit about this talk. We’re really not here to talk
about all the deep feature functionality of spreadsheets. A lot of folks know that. We’re really here to talk about
the journey and the lifecycle of evolving an idea from
innovation and creation right through its lifecycle
and beyond as well. So we’ll talk about
the different progress and the journey that a
spreadsheet can take. And so I’ll step back for
a second starting off. The first thing that’s
really super cool to note is nobody here really needs a
definition of a spreadsheet. Obviously, everybody knows
what a spreadsheet is. And that’s one of
the great things about targeting spreadsheets
as an application platform because, obviously,
you know how to use it and other users
know how to use it. However, one of the
interesting things, if I asked every single
person in this room to give me your definition
of a spreadsheet and what are the use cases you
use to build a spreadsheet, I’d hear a lot of
the same things. I’d also hear an awful
lot of different things. And that’s really the
magic behind a spreadsheet. The versatility of them really
kind of makes them magical, which is why I prefer this
definition of a spreadsheet. Now, a little history
flashback, Bob Frankston, when he was
co-founder of VisiCalc said, “the definition
of a spreadsheet is a magic sheet of paper
that can perform calculations and recalculations.” And it truly is magical. First of all, I want to make
a shout-out to VisiCalc. It’s now 40 years old, founded
in 1979, which by the way, is probably older than a
lot of folks in this room. Eric, I know it’s
older than you, right? [LAUGHTER] ERIC KOLEDA: Yeah, yeah, yeah. CHARLES MAXON: But
I actually used it when it was a brand
new application and it was the
first time I really saw the power and the
beauty and the magic of what a spreadsheet can do. And a couple of things about a
spreadsheet at that time was it was the first computer program
that really wasn’t just a top-down sequential
way of doing processing. It didn’t write instructions
and the computer just executes them off in order. It allows you to put content
anywhere within the spreadsheet and it updated it automatically. And that was kind
of neat, right? You didn’t have
to be a programmer to learn how to write
those instructions, which was the other really
super cool thing about it. It really kind of
democratized the way people could build solutions
using a spreadsheet. And again, I literally did
use these on the Apple IIe that you see sitting here,
which does kind of date myself. But I remember the
first time I saw this. I was like, well, what am I
going to do with this thing? And I’m a super huge baseball
fan, if anybody who knows me, and I used to keep all
the statistics of all the different baseball stats. And every single game, I would
log this on a piece of paper and do the hand calculations
and erase the old calculations. And then I discovered
VisiCalc and the rest was history for me. I was able to go in and
automatically make that work. And so that was my very
first application a very long time ago. And as you can say, obviously,
the rest is history. So since then,
we’ve had VisiCalc, we’ve had SuperCalc and
Symphony, and Quattro Pro and Excel, and Lotus 1-2-3. And now today, obviously with
Sheets and Sheets as a service, this makes us one of the most
ubiquitously, widely available platforms for building
applications in the world. And that’s the spreadsheet
using something like Sheets. So obviously, it became
a much better calculator than we had in years past. And really the prime use
for it at the beginning was, how do we make
a better calculator? But since then, as people do
when they build solutions, is they find new ideas and
new ways of using things. And I always say
a spreadsheet is become kind of the Swiss Army
knife of building applications. You can build a lot of
different things with it. Commonly, you see people
build database-based solutions in a spreadsheet, and sometimes
we think it’s a good idea. Sometimes we’re very afraid of
what people try to do with it. We kind of say misusing a
spreadsheet as an application. But again, there’s a
lot of great features and functionalities in there. Of course, people do
other things like analysis and ad hoc modeling and all
kinds of different things. Probably the most
common thing you see people do is use
a spreadsheet for list management, right? You come in, you got this
nice 2D grid of data. You start typing
things in there, whether you kick off a project
or you do something else. We do it all the
time here at Google. ERIC KOLEDA: Oh, yeah. You can see the history
of my thought process by looking at the history
of my spreadsheets. It’s a reflection of how
I’ve approached work at work. CHARLES MAXSON: Absolutely. And so obviously a ton of other
things you can do with it. And so that’s why our
argument today is spreadsheets make really good applications. And so you’re probably
like, what, applications? Sure, it’s kind of a broad word. I think apps, I think smartphone
app or something, whatever. But really, when you think
about, what is an application? It’s simply a piece
of software designed for a specific use
for a specific user or set of users, right? So it’s simply to make it work. And so by that, I say a
spreadsheet is a really good application platform. And again, if you’re looking
at it from a development standpoint, traditional
multi-three-tiered development, you’ve got the UI layer. You’ve got the logic layer. You’ve got the database layer. This spreadsheet gives
you all those things. I’ve got that 2D grid,
where I can quickly put in data by rows and columns. And I can sort and filter
and make it pretty, which is kind of my UI layer. Then, of course, I’ve
got a full logic layer, where I can use not just
the built-in functionality, but actually extend it and
customize on my own using things like Apps
Script, which we’ll talk a little bit about today. So it really is a great
application platform, and that’s why our argument
today is that spreadsheets make a really good application. Really fast, easy
to get started. I can go up to the formula
bar and go, Sheets.new, and I’m already creating
an application, an idea. How many times a day do
you do something like that? ERIC KOLEDA: I lose count. CHARLES MAXSON: Absolutely. And so the other thing,
just besides being fast and easy and accessible, is
the fact that so feature-rich. A lot of folks are like,
oh, I want to build my own application my way. And my answer is always, if
you think of all the built-in functionality in a spreadsheet,
if you were to create the ability to store data– to analyze data and format
it, the ability to do things like pivot tables and charts– if you were to build all that
plumbing and infrastructure yourself, you’d still
be working on it. With a spreadsheet, you can
be done with an application way often, way faster than
anything else you can build on. And so for that reason,
I think it makes a super-great application. I think the other thing that I
find special about spreadsheets is, unlike a lot of
development paradigms, where you have to go out and
gather requirements and then go back and build a
solution as a developer, then go back to the user
and see if it works, spreadsheets can be
built using teamwork. As easy I can share
a spreadsheet, just like I share a
file, I can go out and I can bring others
involved in the process in, whether they’re
technical or not. And they can actually help me
build my application, which, again, is very unique. So it’s very
iterative, very trial. Bring the end user in
who knows the business process likely as good as
you do or maybe better, and you can come up
with a better solution. ERIC KOLEDA: Yeah,
and this is something we see at Google all the time. I mean, we’ve only just
been speakers here at Next. We haven’t organized
the whole thing. The number of spreadsheets I’ve
seen that powered this event is dizzying, right? It was the right
choice for building out a whole bunch of
mini applications that make this come to life. And I feel like, especially
coming from a developer background, sometimes you get
a bit of spreadsheet shaming. You send out a spreadsheet. People are like, really? Another spreadsheet? It is the right choice for
a large amount of problems. And I think, of the points
you highlighted here, exactly double down on why
that’s the case. CHARLES MAXSON: However, we
don’t want to overemphasize and say you should build every
type of spreadsheet application in the world you can, because
there are some times where it doesn’t make sense. Now what one of the things that
we were talking about earlier, before we came on,
was how great it is to use a
spreadsheet, where you can kind of use trial and error
and see if the solution fits? You can know pretty
quickly whether or not it’s a good place
to put a spreadsheet to build a solution, right? There’s certain kind
of tells that there is. Also, one of the
neat things about it is you can use it for
a while and actually outgrow the boundaries
of the spreadsheet, but get a lot of use out
of it and maybe not waste all that development time on
an application that could stay right inside of a spreadsheet. So it’s a great
place to fit that in, which I think is kind of neat. So when does a spreadsheet
not make a good application? Well, there’s some
kind of obvious things that you may see. For example, if you have a very
extensive logic layer, where there’s a lot of
complex functionality, and you may need extensive
UIs, or a lot of handholding of the user, or
protection of the data, then that may not be a good
place to put in a spreadsheet. You’ve probably seen this, Eric. Let me ask the
folks in the room. How many people have
ever seen a spreadsheet that just grew out of control? It went from mission critical
to mission impossible? So nearly everybody in the room. Hey, keep your hands up
if you’re the person that built that spreadsheet. [LAUGHTER] Now, also keep your
hands up if you’re sorry. We see this quite a bit. ERIC KOLEDA: A phrase I saw
you have once in our notes– spreadsheets happen. They just happen. And I think it’s not
even a bad thing. The fact that you
got to that point means that your idea was right. It was the right thing to build. The fact that everyone
started using it and it grew out of control
meant that it had value. So I think, in a
way, it’s a sign of its popularity
and its success that it grew to that point. CHARLES MAXSON: Absolutely. But don’t overdo it. Don’t create an application
in a spreadsheet just because you can. For example, there’s a lot
of great third-party tools out there that I see people try
to recreate in a spreadsheet. Just because you can create
an expense-reporting tool in a spreadsheet doesn’t
mean you should do it. You want to make a
CRM in a spreadsheet? You could. Is that the best thing to do? Maybe not. So you have to figure out the
buy versus build and do it. But the great thing, again, is
spreadsheets lets you evolve. It’s really about the journey
and the process of using, how the tool allows you
to iteratively create the best working prototype you
can before you go somewhere else. ERIC KOLEDA: Yeah,
this screenshot here is an example of one of
the many spreadsheets that I’ve managed over time. It’s the case that
within Google, we have many, many teams working
on many things simultaneously. It’s just difficult
to keep track who owns what, where
is it in the lifecycle, where are the different
resources associated with it. And as a part of developer
relations, for me, its APIs. Where are all our
APIs’ teams at? What’s the latest release? Where are the docs? I built a spreadsheet for,
first, my own purposes, then my team’s purposes. Before I know it, actual
product teams, PMs, they were coming to me
looking for this information. CHARLES MAXSON: Well,
I used his spreadsheet, and it was this wide. And by the way, for the
folks not in the room, it was really wide. You went way out past Z. ERIC KOLEDA: Yeah,
if you go past Z– CHARLES MAXSON: Once you go
past Z, you’ve gone too far. ERIC KOLEDA: It is a
little bit too far. It was time to hand it over. And luckily, I had
proved out the concept. I had proved that this was data
that needed to be captured. People found it valuable. But it was better maybe to
move it to another platform. So there was a new team that
was looking to kind of help organize all this information. So they took my spreadsheet
as raw material, and they turned around and
built an App Maker app, which is the second screenshot. I had nothing to do with it. A few weeks later,
it was live, running. We’ve used it ever since. It’s way better at kind of
providing cross-linked data. Obviously, you don’t have to
worry about people stamping over each other’s information. The scrolling problem is gone. So this was a case, where
I don’t think I could’ve skipped the spreadsheet step. That step of proving the
idea, getting the raw material was required to get
to this destination. And that part of the
journey is really what we’re here to
talk about today. CHARLES MAXSON: Awesome. Let’s do it. ERIC KOLEDA: Yeah, so we’re
going to jump over now to a little bit of a live demo. And Charles, I do need
you to sign in briefly. So we’re going to talk about
the lifecycle of a spreadsheet. Not every spreadsheet goes
through this exact path, but I’m hoping this is a
little emblematic of how things are going to run. So you’re starting a project. First thing you want to
do, open your browser. And what I do and what Charles
mentioned is sheets.new. If you don’t use
that entry point, it’s a way you can
actually, with just typing in your browser, create
a brand new Google Sheet. So like I said, any
time I start a project, here I am in Google Sheets. So the hypothetical world
we’re going to be in here is one where you’re approving
requests from your team to travel for various reasons. So I have been
doing it via email and I’m getting
tired of that, so I’m going to create a Travel
Requests spreadsheet. Now I’m going to have a
little helper on my side here leading us
through this journey. But this stage of the lifecycle,
I’m calling the Infancy. I have some kids
of my own and I’ve been thinking a lot of
how children develop. So I see this as infants,
where infants come out and they don’t even
know they have hands. They end up
scratching their face, because they don’t even know the
own boundaries of their ideas. And that’s kind of what
I see this is like. You have a blank
slate and you’re trying to figure out,
what is in my head? What are the pieces of
data that matter to me? How do I organize them? How do I think
about the problem? And so in this case,
I’m just throwing some initial data in here. I dump it into a sheet. This is like my v0, v minus 1. This is great, but
not that great. The next thing is
I’m calling Toddler. This is where it’s
all about confirming. I got a toddler right now. As I drive him to
daycare every morning, it’s all about car,
truck, sign, tree. He repeats every single
word because that’s how he gets validation
that he understands and is speaking the same
common language as I am. And at this stage, this is where
you’re using the Share button, as Charles mentioned
earlier, to bring others into your development process. So you share with others. And collaboratively,
you help figure out, you missed some edge cases. You missed some
boundaries when you were thinking about this problem. So in this case, I share it
with some other managers, who are doing a similar
approval process. And they’re, you know what? We’ve got to capture
what state it’s in. There are different
cities with the same name. We’ve got to have a start
and end date to figure out how long they’re traveling. And we’ve got to break
out the flight and hotel costs so we can get
a better idea of, are they spending the
money on the right things? So Google Sheets has a long
track record of collaboration, commenting, sharing. This is where that
comes into play. But there’s more
phases to progress. The next, I’m
calling Grade School. This is where your child learns
how to sit up straight, follow directions, hold a pen. It’s about playing the
part of being an adult even before you’re
all the way there. And for me, this
is about putting on that kind of external face. So for me, formatting. I am a big spreadsheet
formatting hardcore person. I do not like spreadsheets
that even have extra columns that I’m not using. And I actually wrote an
add-on that does just that. So this, for me, is
about use your bold. Use your frozen
rows and headers, conditional formatting. I love doing the one where you
highlight the whole row based upon just the yes or
no here at the end. That gives me a really
clear validation as I’m looking at the content. And then data validation,
where you can add a drop-down, so that helps give
users signals about how to use this application. This UI isn’t just
surface level. It’s not just because
you like the looks of it. It is giving hints to users
about what the data means and how to use the application. And those hints are important. CHARLES MAXSON: I
can’t agree more. When you see that
black-and-white spreadsheet with all these numbers
on it, it really doesn’t help the user understand
what the meaning of the data is. And using some subtle color– I don’t mean bright red, yellow,
and green, which blares at you, and blinks, and does
those crazy things. But making it look so I can draw
that user’s eye to something like you’ve got here,
I think, really helps the spreadsheet kind of pop. ERIC KOLEDA: Totally. At this point, this is where
a lot of my spreadsheets stop. The point of this
lifecycle idea is that you don’t have to move all
the way to the end of the time. This could be a very good
spreadsheet for doing your job. This is what that
earlier spreadsheet I had looked like a lot, just
with a lot more data in it. But there is more room
to kind of progress through this lifecycle. So the next phase, I’m
calling Middle School here. In middle school,
it’s a little bit about realizing maybe
your parents need to have some distance between
you and your personal life. Maybe you start kind of
keeping a private diary. It’s about, not
everyone needs access to all the same information. And so in this
phase, it’s really about thinking about access. So before, the nice
thing about a spreadsheet is you can find anyone in. The kind of bad side is
it’s very democratized. Everyone can see and
modify everything, and that’s not always
the right way to go. So what we do here is we
actually apply some data protection to our ranges. Saying that approved
column, you shouldn’t be able to enter
your own request and approve it at the same. Let’s send so that only
the travel approvals group at our corporation can
actually make that change. A simple thing, but one that
now takes this application and adds a richness
around separating data. The other thing that you
can do is kind of use a private spreadsheet. So maybe you use
this main spreadsheet for entering the
requests and keeping track of what’s going on. But if you use an import
range, you can kind of suck in a copy of it. And then maybe
here in the notes, I’ll list out why I denied
a certain piece of travel, just so that I can have it for
a record without maybe exposing every piece of my inner
thoughts to the people doing the requests. So once again,
we’re still staying in the world of spreadsheets. We haven’t broken
out, yet we’re already adding complex things
like access control into our application. The next phase, I
call High School. This is where you
get your first job. You learn about how to check
in and check out on time, how to work the register. It’s about process. This is what I call
building out the process. Having everyone in
a spreadsheet just start adding rows and
being on top of each other, and maybe going back
and modifying something they should have before. That process is
a little chaotic, and there’s a lot of
times where you really want to have a more streamlined
process so that people are channeled into
the right destination. So what we’ve done here is
upgraded this spreadsheet to use Google Forms. Google Forms has long-been
a part of spreadsheets. That’s built right in. And it makes it so that
we can now maybe unshare this spreadsheet with the
whole organization and just the manager group. And then direct
everyone who needs to make a request to the form. So I’ve got a little link here. So the same information
we’re capturing before– why are you going? Where? It’s all captured in the form,
and they just get this one view into the process. They don’t have to see where
everyone else on their team is traveling,
who’s been approved and who hasn’t, further
kind of separating out the different
parties responsible. And you can see, we’ve
even got time stamps. It’s a bit of a
nicer flow for users. And that just helps,
once again, narrow who can access what at what time. The last phase of
this Sheets lifecycle, I’m calling the College Student. This is where you’re
really bringing in the professional aspects,
but you still go home for break and sleep in your old bed. So you’re still in
the comfort of Sheets. You haven’t left. But you really want
to start bringing in app-like functionality
that people come to expect. And this is where we
turn to Apps Script. As Charles mentioned before,
Apps Script is our cloud, JavaScript scripting language,
baked deeply into G Suite, including Google
Sheets, that allows you to add a whole lot
of extra functionality that’s not built into
Sheets with relatively simple amount of programming. So what we’re doing right
now, behind the scenes, is creating a new
Apps Script attached to the spreadsheet that’s
going to add a whole bunch of extra functionality. So you can get to
Apps Scripts by going to Tools, Script Editor. It was there the whole
time if you never tried it. There’s a lot going
on in this Apps Script and we’re not going to dig
through every line of code. What I’m going to do is
just run it to show you what the end result is. In total here, there’s a
little over 100 lines of code. And so you can actually
accomplish a lot without a lot of coding. We use the same
OAuth that you would use in any sort of
fully-built application to access your G Suite data. It’s going through the
exact same channels. Admins have the exact same
visibility into data access. So as I head back to
the spreadsheet now, you’ll see there’s a brand
new menu item up there called Travel Requests. That is definitely
not built into Sheets. With only a little code, I
can add in my own custom UI elements. And if we’re talking about
guiding users to the right part of your application, there’s
nothing like custom UI to really point them
in the right direction. In this case, I’m just
showing a simple dialogue with some calculations. This is not
interesting, but you can imagine all of the different
extra companion functionality you could build in with dialogs
and with sidebars and menu items. That’s interesting,
but I think what’s more interesting is doing
things automatically. Having someone click
a menu item is nice. I think the real
savings come from when you take away a manual step. So what we’re going to do
is submit another request. This time, we’re headed
off to Google I/O. I submit that request and it
shows up here in the sheet. Now before, maybe if
I was the manager, I’d have to remember to come in
and open this sheet every once in a while. But with Apps Script,
I can actually make it so that every time a
new form entry is submitted, it takes an action. In this case, I’m
having it send me an email with every time a
new request has been sent. I can open it up. And in this case, I’ve created
just a simple summary of what’s been requested, as well as I can
link right back into the sheet so that I can easily
take action on it. There’s way more ways you
can get advanced here, but this is a simple
way to get started. CHARLES MAXSON: So less
than 100 lines of code. Users don’t have to do anything. And you create a
workflow application. ERIC KOLEDA: Yeah,
we’re definitely getting into the realm
of workflow right here. And not only that, once
I am in that spreadsheet and I say you know what? Google I/O, Eric,
you are approved, I can also react to that change. So I just change the
cell from blank to yes. And in this case, the
person making the request and approving it is the same. It’s me. But the requester
can also get an email saying that their travel
request was approved. What this unlocks in your
organization can be huge. The ability to take all of that
manual work– remembering– just takes it all off the
table and builds it directly into Apps Scripts. And then I’ll just
give one last Apps Scripts demo here, which is
that you can extend the Google Sheets in other ways as well. So let’s say I wanted
to figure out– if they were going to
drive to this location, could they do it? Do they really need to fly? And so what I’ve added in
here is a new custom function called Driving Distance. This is one that automatically
knows where our office is located, and then
calculates how far it would take to drive there in miles. It’s just like
every other formula. You can copy and paste. You can drag down. And all of a sudden, now,
I’m getting a whole bunch of contextual information
that’s built into this sheet and adds a lot more
richness to the data. CHARLES MAXSON: Awesome. ERIC KOLEDA: So we’re going
to cut back to the slides now. That’s the lifecycle up to here. But when you’ve
reached that point, you’ve got a very functional
application in a spreadsheet. But as Charles said, there’s
times when you’ll even outgrow those bounds. CHARLES MAXSON:
Yeah, absolutely. So you saw that application did
feel like a full application. And I think it does a
great job and the effort was pretty limited. But what happens if
maybe you realize that folks are in there
trampling over the data, or the UI isn’t
intuitive enough? Or you just simply want
a streamlined application that is very specific
to that solution so the user doesn’t get lost? You don’t need any documentation
or worry about support. You want to make
it seem like it’s a full-blown solution without
the spreadsheet in the way. So it’s evolved to a point
where you’ve got the business requirements down. You know it works well
and people like it. The app is still needed. Why not build a full-blown
app on top of it? Now, of course, you could
go up and you can create a full stack application. You can go out and start
spinning Java code or Python code or whatever, build your
own UIs and do all that stuff. But before you do
all that, wouldn’t it be nice if you had an
interim step, where you didn’t have to go out
and do all that full work? And maybe a lot of folks
don’t even have the ability as a developer to do that. Well, G Suite has
an alternative, and it’s a tool that’s
called App Maker. A real quick show of hands
for folks in the room. How many folks have
seen App Maker already or understand App Maker? So a good amount of folks have. So that’s awesome. More than half, which
is really good news. For those that don’t, App Maker
is a Google family member. It’s a product that’s available
with Google Business Edition and Enterprise Edition, which
allows you to quickly build a rapid application. So the beauty of that is, with a
limited amount of code– which, by the way, is all based upon
Apps Script that Eric was talking about a second ago– you can go off and you can
create that point solution using App Maker. So a couple things about it that
make it real simple and easy– instead of spending
a lot of time going out and
creating your own UI, you have the ability to come
and drag and drop this user interface and not waste
time fixing it all up. You can do it pretty
quickly and uniquely. So I think that’s one of
the big things about it. You know what? Enough of me talking about
it since a lot of folks have already seen it. I’m going to flip
over back to the thing and give you a quick show of
how we take this application and we make it into
an App Maker app. All right, so when
it gets up there– OK. So picture this
spreadsheet again. It’s got a little more colorful. We’ve got some more
data, and it gets large. But you want to take
it into App Maker. One of the good things
about this journey and the way these
products work super-well together is I can you use this
spreadsheet– both the logic of the data within it
and the data itself– to build my first application. So let’s do that. So to get started,
I can come in. And getting started
is real super-easy. I can say New. And if you come
under here, you’ll notice next to App
Scripts and between Sites, I can see App Maker. So it’s really easy
to get started. It’s built right into my
environment, of course, as long as it’s been turned
on inside of your environment by your administrator. If it has, you can
come here real fast and simply say,
appmaker.google.com if you want even a
faster way to get there, and create a new project. Before you do, again, if
you’re new to the product, you’ll notice there’s a
lot of ways to get started. There’s some tutorials, code
labs, even some great samples to actually use. And you can even put
them into production or use them as a learning
tool to get started. So let’s create a brand
new app from scratch. You’ll notice there’s a lot
of templates out of the box. So you can see here a bunch
of different templates that are available. There’s even a neat one called
Travel Approval already, where I’m going to show
you, at the end, what it looks like if you use
the template out of the box. But we’re going to
build ours from scratch and have a brand
new application. So here it comes. It’ll go off. It’ll create a ready
solution for us. We see you’ve got the
blank design surface. Let’s come in here and name
this thing Travel Request. We’ll keep the same theme going. And the first thing I need to do
is go out and bring my data in or create my data model. So I’m going to come in
here and say Add Data Model. And you’ll notice, there’s a few
different ways I can do that. For example, I can bring
in a Cloud SQL database or create one, which
we’ll do here in a second. But also, I could
use a calculated one, where I could bring data
in from other places and actually create it in
memory and work with it there, as well as I also could bring
in directory information. So if I wanted to work with
my user profile data or stuff like that, I could
do that, as well, which is great in a request
application like this. But let us take the
generic Cloud SQL version and we’ll name it
Travel Request. And instead of building
it from scratch, we’re going to
say Import Fields. Or I can go out and I can
take that Google spreadsheet and select it from Drive. You can see, here is that
colorful spreadsheet. Pull it in. And notice, it’ll
go out– and look at the different
Sheets within there– and offer me to build
off of that model. So I can come in here. Let’s say Next. It’ll bring in all the fields. It’ll bring in the column names
for me and even the data types. This is super-neat. I don’t have to go out and
define my data in grave detail. I can come in and see
the different data types. So when I actually
do data validation, or I use some of the
controls and widgets available inside of
App Maker, they’ll automatically come out
based upon that data type as a preference. So we’ll say Create here. It’ll go off and it’ll
create that first data model, and it’s that simple
to get started. Now, notice I can do a bunch
of other things with the data. For example, I can create
views on it or relationships so I have a more
complex data model. It’s kind of similar back
on a spreadsheet, where I would do v look-ups or use
some validation handling on it. Again, I can build it all
in here to my data model. I have the ability to add
events, as well as security. This is one of the things, I
think, App Maker does really, really well. We were talking about
it in spreadsheet, how people can trample over
your code, or your formulas, or your data. Here, I can make the models
based upon user permissions and profiles. For example, in this case,
I could have approvers only see certain data that,
obviously, my users couldn’t inside the model. So I can do that as well. But other than that,
my data model’s done that quickly and
that fast in this case. So the next thing I need
is the user interface to take a look at it. So I can come in here, and
I can create a new page. There’s one already
stubbed in here for me, which I’ll
say Rename This. And we’ll say Add Request. I say, OK. And now I’ve got a blank
design canvas up here, where I can come in and start
adding widgets on top of it. So again, if you see all these
different widgets in the panel here, I can put things like
date boxes and star ratings, and text controls, and all
these type of things in here. But one of the neat
things is I can come in real quickly and
add one without having to do a lot of work. I can create a
form-based one and add it to my design service. And just like this, I can
choose my data source, say Next. Pick the type, in
this case, Insert. Pick the fields I want
out of that data model. I don’t want the approved
item on there, obviously. Again, I don’t want people
approving them either, like you didn’t. And then I say Finish,
and there you go. That’s how fast I
can come up and I can build my first UI screen. And again, so far,
to date, I’ve written zero lines of code getting this
done, which is kind of neat. Now, one thing
you’ll notice, it’s kind of a large
design surface here. So what I’d like to do is
also make this mobile-ready. So it’s really a good
tool for building mobile-based
applications as well. And I can have a little guidance
up here, where I can say, you know what? Let’s make this for larger
phones since most folks that I want to build this
for have a large phone. And let’s make it portrait
so they hold it this way when they’re walking down the
hall creating travel requests. I can just manage it that
way and snap it to the grid. So you can see how the
responsive design makes it kind of neat there, where,
again, I haven’t done any work, but now I’ve got the
beginnings of a mobile app. OK, other things I can do. I’ve got a Property
window over here, which I can change
any of these controls, do things like change
the look and feel, change the names, et cetera. In this case, I want to
make these buttons look a little more mobile-friendly
and use our material design. So I’m going to take the Submit
button and the Clear button, multi-select that, and
apply those styles. So for example, I
want to come in here and find something that looks
a little better, a little more intuitive. Look, these are good. I’ll use these fab ones. And also, I need to come in
here and change that text on this one button because
there’s no submit material design name, but
there is an Add one. I’ll put that in there. And real quickly,
you can see, again, no code, but just one change. And I’ve got my
form ready to go. So let’s take a look at how
this thing runs real fast. I can preview this. So again, another
thing about App Maker is it’s really good
for integrative design. I can come in here,
see how the app works. I’ve got a preview mode. Once I go through my
required approvals here, which I only have to do
the first time as the developer of this, you’ll see I
can come through and pick these real quick. You got the authentication. In here, in a second, I’ll have
a preview of my application. So this is the Preview window,
where we’ve got a little debug tool available here. I kind of makes it smaller, but
notice again, out of the box, I can come in here and
say, you know what? Let’s put in a
request for Charles. He’s going to Next,
which is in London. And that start date, I believe,
it’s sometime out in October. You see we’ve got
nice, rich tools. And again, I haven’t done
anything to model these. It automatically
picked the right type of ones based upon the
format that it read when it built that data model. So again, Next is
going to end there. And flight cost to London. So from here on the
west coast to London, kind of expensive, so I’m going
to say that flight is a lot. And notice how it knew. Again, because I’m
looking for a number, it’s automatically
validated the data. So again, without
even doing anything, I can make sure I get the right
data in my application, which, again, in a spreadsheet
is kind of hard. People can put the
wrong thing in. Unless you wrote a lot of code
and did some validation rules around it, it’s kind of hard. So in this case, I can come
up and say, you know what? Flight’s going to
costs about $2,000. And it’s probably about $2,500
for the hotel in London. And I add the record. So that’s how fast you can start
collecting data and building an App Maker app. Let’s quickly create
a form that allows you to now do the approval process. So let’s go back to
the design surface, and I’m just going
to create a new page. And again, a couple of
different types I can create. The first is kind
of a full page, but I also can do
a page fragment, so I can reuse that across
my entire application, as well as create some little
dialog boxes that pop up, which is kind of neat. In this case, I just
want a full page. And I’ll just call
this Approve Request. And let’s pick our
data source again, so we don’t do a lot of work. All that binding will
be done automatically behind the scenes. I say Create. I’ll come back
over to my widgets. I’ll pick that table, then
I’ll put it out there. Again, just take the default.
I’ll make it editable, but only for the approval
because I only want people to be able to approve this. There you go. And I’ve got a few other options
about sorting or deleting and adding records in
here, which I’ll just accept all the defaults. And you’ll notice, I’m still
on the big phone portrait. This a little wide. I can either shrink
it or go back to custom, where, in this case,
I just want to make it wider and bring that over. Got a little design
surface issue here. Normally, you’re
developing this on a larger screen, which makes it easier. I am a little bit
out of real estate. I don’t know if I can grab
the thumb and pull it over. Let’s preview this and
see what it looks like. Wait. Before I do that, I want
to bring my data in. And you’ll notice the data comes
in separately on this thing, and I have a good idea here. Before I even bring
the data in, let’s make this so people can see the
location of this user request. Remember what I said earlier
about making data very visual? The neat thing here
is I can come through and there’s a lot of
other widgets I can add. For example, if I wanted
to add a location map, I could see without having to
read where someone’s going. So if I see it somewhere
far or luxurious, like Hawaii or
something, I would know and I could make a
decision pretty quickly. So in this case,
I want to come in, and what I need to do is simply
bind up that map by saying, you know what? Let’s take the
address and change its binding to the location
of that data model. So as I scroll through
those, I can see real quickly all of those. There’s a little copy
and paste of my API key, so it’ll actually run
and I can use that model. Oh, I didn’t bring
the data in again. Let’s try that one more time. I keep jumping the page here. So you recognize, when you
bring the data model in, it doesn’t actually
bring the data as well. It just helps you
build the model. So you have to specifically come
out here into the data settings and say, you know what? I want to import
that data as well. ERIC KOLEDA: Yeah, I think
it’s often the case, actually, that you’ll have even more than
one spreadsheet, like copies and copies of spreadsheets,
used in different departments. You can actually now unify all
those spreadsheets together into your data model, bringing
those separate processes into this one application. CHARLES MAXSON: Absolutely. So I can come in here, and I
can say select that and bring it in. Choose my model. It’ll go out and look
through Drive again. It’ll take a second. There it is. We say Select. We say Import. OK, so there, perfect. Now we’ve got our
30 rows in here. Now if I go back and I run
this and preview it and pick that import form, you’ll
notice, as I go through, all the data is in there. As I pick different
ones, I can quickly see how the map updates. So again, I’ve got live-bound
data in my application and did very little
once I realized I got the right sheet
to bring into the data. So pretty cool. And again, you saw how, just
with a few drag-and-drops and a couple clicks, I was able
to bring a mini application in. If I was the fast
forward and, for example, start working with
a template, this is the built-in template
of what it looks like. And you can see, either
starting with a template or with a little more
effort, you can come in and you could build quite
a bit of an application with very little effort. Now people always say, is App
Maker a low code, or no code, or a lot of code? What is it really? If you look at this
application, you can see I’ve got
a few data models. You can see how the
data’s been normalized, like lodging and transportation
is out, but again, pretty straightforward. You can see I’ve got a
bunch of different pages, for example, that manage
things like the requests and the dashboards. But to be honest, if
you look at the code, you can see there’s a little
bit of code behind it. So for example, there’s
some client items, add a page request
or approve the page. But it’s not an
awful lot of code. So I always kind of say,
it’s kind of an in between. It’s middle code. ERIC KOLEDA: Yeah, I think
the code, in this case, it’s letting you customize. Because what you
built right there worked right out of the box. But sometimes you need
to continually customize, optimize. That’s where the code comes in. I feel like it’s more
like an additive layer. CHARLES MAXSON: Absolutely. OK, cool. So that’s App Maker. Let’s go back to the slides. ERIC KOLEDA: Sure. Looking at App
Maker, if I wanted to go back to my
lifecycle analogy, I kind of think of it a little
bit like renting an apartment. You can paint the walls. You can decorate. But at the end of the
day, if the boiler breaks, that’s not your problem. We’re taking a lot
of the hard parts around managing data, building
models, building UIs, making sure they work on
multiple browsers. Google is taking
that off your plate, allowing you to have a
customized existence, but still a simple one. CHARLES MAXON: Absolutely. ERIC KOLEDA: I think the last
stage here is the homeowner. This is where I want
to buy the whole thing. If I want to tear out every
wall, that’s up to me. But also, if something goes
wrong, it’s also on me, too. And that’s when you really
want to go beyond G Suite to build out your own
top-to-bottom custom application. It’s great when you
get to this point. Not every application
gets to this point. Very many exist as spreadsheets
or as App Maker apps. But there are times when it’s
so mission-critical, you want to have deeper integrations
into internal systems, that you need to
hand it over to IT or work in coordination
to build out something completely custom. And this is the legacy. This is building something
that lasts for a long time. The nice thing is, if you’ve
gone through that App Maker stage, App Maker is
backed by Cloud SQL. So you can, right away,
start working on that data. There’s no importing
it into another system or figuring out how
to move it around. I think you could actually
even have both applications– the App Maker and maybe the
new one you’re working on– at the same time, operate
on the same data model. Because it’s Cloud
SQL, it’s very easy to share that data layer
between where you came from and where you’re going. The other thing I want
to just put out there is that users still like Sheets. Even if you are
evolving beyond a Sheet. You’re kind of making this
thing production-ready, users still like
working in Sheets. You don’t have to actually
give up on Sheets. I think what you can
do is say, Sheets is going to be a view
into our application. It’s going to be a subset. It’s going to be a window
into the data and process. And so one of the tools
that’s in the tool box here is the Google Sheets API. It allows you to, from your
professional application, from Node, Java,
.NET– you name it– take data and move it
into and out of Sheets, behind the scenes, for users. These screenshots here come
from one of our code labs we have up that say, look,
you have a custom system? Push data into
Sheets for reporting. As Charles was saying,
there’s so many things you don’t want to rebuild. It’s not fun to
rebuild charting. It’s not fun to rebuild
pivoting, right? Don’t do that. Don’t waste your time building
out those Sheets features. Instead, think of Sheets
as a reporting tool that you could instantly
use for whatever application you’ve built. So take subsets of your
data, views of your data, push it to Sheets, and
then give it to users in a form that
they’re familiar with, that they like to work with,
that they know how to mash up. And start that cycle
all over again. If you want to get
really advanced, you could even start using
Sheets and the Sheets API as a bulk editing platform. As nice as your custom UI is,
it’s probably always designed with a edit, save, edit, save. And a lot of times,
in certain use cases, people want to do bulk editing. Sheets is a great
tool for that as well. Another way you can kind
of stay relevant in Sheets is with an add-on. That sidebar that guided
us through the lifecycle was a custom add-on I built
in this demo domain called Lifecycle of a Spreadsheet. And you can do the
exact same thing. You can have an
internal application, and you can build a companion
add-on that kind of starts adding that relevant contextual
information into Sheets, bringing in data, kicking
off processes, bridging the gap between that other URL
that your users might forget and the Sheets that they are
working in day in and day out. CHARLES MAXSON: One
thing a lot of folks understand in the
lifecycle is, I’ve written a great application
for my spreadsheet. How do I use it for
other spreadsheets? And that’s when the
add-on comes in. ERIC KOLEDA: Yeah, the nice
thing about an add-on is that, although you can build one
and distribute it to the whole world– which we have a
large library of them– you can also just keep it
within your organization. So you can make it that
some custom functionality is now available instantaneously
to anyone in your organization who opens up Sheets. And that can be a really
powerful way to kind of not have to have a
whole other discovery process for this capability. I’d also be remiss
if I didn’t give a shout-out to Data Studio,
a close cousin of the G Suite family, one you’ve probably
heard about here already a bit at Next. It’s yet another
very powerful way to take data that’s in
Sheets or in Cloud SQL, build visualizations,
build charting. I’ve created some really
nice dashboards in it that my whole team uses. And it’s another
way you can take what is a process that’s
moved out of G Suite, but still make it relevant using
the tools that G Suite has. And then one more thing is
just the G Suite Marketplace. I wanted to call this out
that although we talked about some tactics
here today that you can use to grow spreadsheets
into applications, we have a lot of great partners,
who are also building solutions in this space. The G Suite Marketplace
is a great place to go to both find those
kind of custom solutions for turning spreadsheets
into applications, as well as some point solutions,
that Charles mentioned. There are a lot of
times in which you may reach that point where your
spreadsheet’s grown too large, and maybe it’s time to think
about that buy-versus-build decision. And the G Suite
Marketplace is a good place to discover apps that instantly
work with G Suite, that connect up to users’ identity
and their data in a way that’s pretty seamless. So let’s recap a little bit
of where we’ve been today. Spreadsheets, we think,
are a great place to start. They’re great for prototyping,
incubating, validating, showing that this idea that
I have is worthy and right, and it has a purpose and a
need within the organization. CHARLES MAXSON: Absolutely. And also, you saw how we can
do things like add forms, and use Apps Script to guide
the user through a better experience by
adding custom logic, functionality that we can use
for getting input from them, and then taking Workflow
processes on top of that. ERIC KOLEDA: Yeah. And then I think, App
Maker is a great pit stop on the road to building your
own fully custom application. It allows you to focus on the
functionality and not so much the piping and the architecture. You get to add value
immediately, and then not have to worry about some
of those harder bits. CHARLES MAXSON: And
then finally, we recognize that even though
your solution may not be centered in Sheets, Sheets
are definitely a good component within your larger solutions. So if you want to use that data
without ad-hoc functionality, or like Eric mentioned, you
don’t want to recreate pivot tables and charts, build
your solution somewhere else, but dump your data where
your users really want it– back inside of Sheets– and let them work with it there. And you can use anything
else in the GCP family to work with your Sheets. ERIC KOLEDA: That’s right. So for learning more, a great
place to start is our G Suite developer site at
developers.google.com/gsuite. We have linked up there how
to find more information about Apps Script, add-ons,
App Maker, the Marketplace, as well as we have
a lot of getting started materials– code
labs, quick starts, reference documentation, you name it. CHARLES MAXSON:
So yeah, remember, it’s
developers.google.com/gsuite, and you can get started. ERIC KOLEDA: That’s it. So we want to thank
everyone for coming. We would love and
crave your feedback. So please, if you can,
leave us a star rating. Tell us what you think. And thank you so
much for coming. [MUSIC PLAYING]

Author:

23 thoughts on “How to Grow a Spreadsheet into an Application (Cloud Next ’19)”

  • When will app maker be made available to non-profit users? This has lots of potential to be extremely useful to us.

  • Is the add-on you used to show the Lifecycle of the spreadsheet cycle available as a standard add on ? It was a great way of talking someone through the spreadsheet or process. Thanks

  • Great video!! I wish they would more into detail about the app marker. I’ve reached the point of developing a big spreadsheet the works great but I need to take it to the next level. App maker is a great tool to move to.

  • This looks like a great way to rapidly prototype an application. Is there a way to create custom "renderers" for different types of columns. For example, you might have a String that contains a protein identifier of some sort, and you want to display a 3D rendering of the protein. Or you might have a String containing a SMILES string representing a drug-like compound that you're screening, and you want to display the structure of the compound. Also, are the widgets web components?

  • So – I love this, however, we have used countless 'free' scripts to do something as simple as e-mail notification of a new form submission. Can you embed this as part of the tool instead of requiring users to follow complicated steps? Can you publish this script so we can stop looking for third-party tools that do this? Thanks in advance.

  • I just tried to have a go at this and get AppMaker to work. But I wasted 2 hours trying to setup App Maker. I'm not a programmer. I wanted to just make apps in the simplest way possible. Maybe google should setup accounts for those of us who don't already have huge IT departments and the capabilities to make an App in the first place.

    Essentially, all it proved to me was that these tools are not ready for non-coders or small businesses. It's so excruciating to watch, as there are way more small businesses than huge corporations worldwide and a small subscription fee to make apps and websites and control all your business data would go completely viral if executed correctly.

    That's the game changer. F#$% the corporations who already have dedicated administrators and the budget to afford to build something however they want. You will never own that whole market. But you would own the whole small business market if you could make it simple and easy to setup. And there is way more revenue there. This is such a missed opportunity Microsof… wait, google. Yes you have become microsoft now. Chasing corporate not chasing the market needs.

  • I'm On Your Roof says:

    Where I work, we turned a spreadsheet forest into an app forest, but kept the spreadsheet forest for reconciliation purposes.

  • an application within an application… and worst case scenario, its a universal language that the data can be exported to some other application

Leave a Reply

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