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

Video Statistics and Information

Video
Captions Word Cloud
Reddit Comments
Captions
[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]
Info
Channel: Google Workspace
Views: 98,337
Rating: 4.8841753 out of 5
Keywords: type: Conference Talk (Full production);, pr_pr: Google Cloud Next, purpose: Educate
Id: vnm6ViI06MM
Channel Id: undefined
Length: 43min 31sec (2611 seconds)
Published: Thu Apr 11 2019
Related Videos
Note
Please note that this website is currently a work in progress! Lots of interesting data and statistics to come.