Google Script How to Update Google Sheet data from a web App | Laurence Svekis | Skillshare

Google Script How to Update Google Sheet data from a web App

Laurence Svekis, Web technology Instructor

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
17 Lessons (1h 39m)
    • 1. Google Script Promo Video

    • 2. 1 Introduction to Google App Script and Google Sheets

    • 3. 2 setup Google Script

    • 4. 3 Write your first Google Script

    • 5. 4 Create dialog and webapp of Google script output

    • 6. 6 html output append and update styling with Bootstrap

    • 7. 7 Coding exercise add UI item render HTML

    • 8. 9 Output content from spreadsheet

    • 9. 11 Output Sheet content as Data

    • 10. 13 Loop data output array as HTML

    • 11. 14 Sheet data in HTML table with inputs

    • 12. 15 data passing using jQuery from element

    • 13. 16 Class google

    • 14. 17 Update Google Spreadsheet fron HTML

    • 15. 18 Launch Webapp

    • 16. 19 Styling Tweaks

    • 17. 20 Source Code review


About This Class

Explore how you can use Google Script to connect Google Apps and with web apps to update your Google Spreadsheet

Use Google Script to connect your Google Sheet data to a web app so users can update content within your sheet.   Connect a sheet without having to share the entire sheet with them.   Google Scripts provides an amazing opportunity to extend what you can do with Google Sheets.  Source Code is included!!!!

Did you know you can publish web apps that have web urls directly from Google, no hosting and only using your Google Account.   

Google Script is JavaScript with some added custom classes and methods.....  Using Google script allows you full control over what your Google Apps can do connecting them together and making them work smarter.

Learn how to output and update your Google Sheet content using Google Script to publish it as a web URL.   

JavaScript, HTML are prerequisites to this course.  jQuery and Bootstrap are used within the application so experience using these technologies is essential to learning the content of this course.

Google provides a powerful set of tools with Google Apps.   Google sheets is a great place to store data within a spreadsheet and then make use of that data in a number of ways.  This course demonstrate how to connect and google sheet content and output it to a web app, and then update the Google Sheet content directly from the web app without having to open Google sheets.

  • Share data within your Google Sheet
  • Provide a url to update data without providing access to the Google Sheet itself
  • Select and show content from a specific sheet
  • You will be amazed at how easy it is to work with Google Sheets

This course provides ways to use the data for web development and more.  Course covers outputting the data as a web app.  This will give you access to the data using a URL which you can then share online.   Select either one sheet or all its up to you no programming is needed.

Source code is included as well as how to update it to get the content from your own Google Sheet.  

Minimal programming is required for this course, but a basic understanding is needed for the more advanced web application functionality.

Google sheets provides a great way to collaborate and use data online within a nicely packaged spreadsheet.  Now you too can use that data.

I'm here to help you learn more about web applications and ready to answer any questions you may have.

Join now and start doing some amazing things online!


1. Google Script Promo Video: use Google script to connect your Google sheet data toe Web app so users can update content without your sheet. Connect a sheet without having to share that entire sheet with them. Google Scripts provides an amazing opportunity to extend what you could do with Google Sheets. Source code is also included. Did you know you can publish Web APS? Have you girls directly from Google know hosting accounts required? Onley need to have a Google account. Google script is like JavaScript, with some added custom classes and methods. Using Google script allows you to have full control over what your Google applications can do, and you can connect them together, making them works, murder and producing some really amazing results. Learned output and update your Google sheet content using Google script and publish it to a Web. Google provides a powerful set of tools within Google maps. Google Sheets is a great place to store data within a spreadsheet and then make that data available in a number of weeks. This course will demonstrate how to connect. This course demonstrates how to connect Google sheets as well as put a content into a web and then update that Google sheet directly from your Web without having to open Google Sheets. You can share your data on Google sheets providing you are Elton Up that that that data select and show content for a specific sheet. You'll be amazed at how easy it is to work with Google Sheets. I guarantee you, Google Sheets and Google script could do a whole lot more than you've ever expected. You're gonna be truly impressed with the outcome of the different applications and connections you can make using Google script. I'm here to help you learn war about building Web applications and ready to answer any questions you may have joined now and start learning how to do some incredible things on line using food. 2. 1 Introduction to Google App Script and Google Sheets: welcome to our Google script course. And in this course I'm going to be showing you how you can build Ah, Web application using Google script. And this application is gonna then connect to your Google spreadsheet, and I'm gonna show you how to do all of this from scratch. My name is Lawrence, and I've been a Web developer for over 18 years, and I'm so excited to have the opportunity to present this course to you today. It's gonna be truly an amazing project and based around Google script, which heavily is based around JavaScript. So if you're familiar with JavaScript, you're gonna be ready to jump right in and start writing some Google script in doing some amazing things with it. Welcome. And in this course, I'm gonna be showing you how you can connect your Google spreadsheet information to weather up to a Web. And basically, what we're doing is where giving the ability to update and change content. So you saw that as I'm updating the cold within the Web app. My Google sheet is actually also updating with that same information s So this is what we're gonna be showing you how to build this out and build it from scratch. So again you see that it was cells as they're changing on the front end web. They're also changing within the Google sheet. And I can update thes as well so you can see that everything will change. As I'm updating, I'm also going to show you how to do this within a pop up dialogue s Oh, this is gonna be really useful as we're developing out the code because this is going to give us stability. And I know you can't see that. So we're going to just we're just gonna make those changes. And you can see that those changes also will reflect once we refresh the page because that data is being loaded on on the fly into the page army that find the page loads, establishing all of the data and information that's being pulled out from the Google sheet . And the really nice thing about it is that if, for instance, I want to get rid of a particular column, that script is dynamic enough to in order to adjust for it. And if I wanted to add in that column So if I want to have something like rank and I go back in here, and if I refresh it, I've got that updated, updated information from my Google sheets and it's very flexible. And still I have updated this column, and as I'm adding up and adding in those data values, you can see that those values are also changing the Google seat. So this is gonna this course is gonna be all about Google Sheets. You're going to need a Google account, and I'm gonna show you how to set one of those up as well and how to create your Google spreadsheet and then ultimately link it and access the Googles script and build out this application. So all of this is included in the course, and I do encourage you Teoh work alongside of included a lot of the source code as well as I have included some exercises. Eso you too can take the source code, practice what you've learned in the lessons and try it out for yourself and see what you can make happen within Google, Google Maps and Google script 3. 2 setup Google Script: welcome. And for this course, you are gonna need tohave a Google account because the Google Google script is written right online rate within the cloud within your Google drive within your Google applications . So you do need to have a Google account. If you have what already, you can go ahead and log into it. And if you don't, then you need to get a set one up over at accounts dot google dot com. And over here, you can also use your own email address to set up your Google account so you don't necessarily need a Gmail account in order to get a Google drive. So once you've set that up, you can go ahead and you can pause the video and go ahead, log in to your Google account or go ahead and set one up and then look into your Google account. And once you've done that, you should be able to coat over to Dr Log into your account. Go to dr dot google dot com and from there you're gonna be able to access all of your files that are on your dr on Google. So these are all up in the cloud so you can access them anywhere from any browser logging into your Google account. And that's one of the nice thing about APP Scripts is that you don't need to bring it into your computer. You don't need any special tools where resources all you need to do is have a browser and be able to set up a Google account. So next, once you've set this up, you've got a number of options. You can go over to dr dot google dot com, or you can go, and you can click this. So chevron here under my drive. And because this project is gonna be based around Google Sheets, you're gonna need to create a Google sheet. And the way to do that there's a number of ways to do that s o the way that I typically do . It is I'm on a Windows machine so I can click over here. I can right click, and I can open up Google sheet. But it's the same thing that if you go over to here, select Google sheep and this will open up a brand you Google sheet. So we're just gonna call it projects up Datar s O that will be, uh, our basic Google sheet that we're gonna use, and we're gonna be updating this from a Web application. So we're going to create an established that connection where data can be sent to here and updated. So our content and our Google sheet is going to be able to be updated now a little bit more about APP scripts before we begin and we jump into the coding. So ab scripts. What it does is and allows you to increase the power of your favorite Google applications. So basically, what that means is that you can connect all of these applications and a bunch of functionality that's native within these applications together. And at the end of the day, you could do some really amazing stuff with just a few lines of code with Google sheets. There's a lot of information here about Google sheets and depending on what app you're looking at using. So they all have scripts, so even your Gmail will have a script capability, your calendar, your contact, your drive. Of course, you've got ability, connect, slights, forms, docks and pretty much anything here in between eso the reason the one that we're going to be using, of course, is the Google sheets, and that's if you want reference any of the built in classes or methods that are available within Google Sheets. They're all listed here. Developers dot google dot com for slash AB script for it slash reference for its brash spreadsheet. Eso Just like the spreadsheet. If you're looking to get some classes and some methods that available in document, you can click document contacts, calendars. So whatever application from the Google Sweet Whatever service you're looking to use, you can always get mawr information. And as you can see, there's a lot of built in stuff ready to go. And this is really the beauty of all of interacting and building of Google scripts, because essentially, this is like writing JavaScript code, and you've got a lot of these ready made methods that you can simply call to and do some really amazing stuff with so connecting everything together. So, using a combination of JavaScript and the Google script, methods were able to accomplish rating Google script. So the next lesson I'm gonna show you how you can create some Google script. And of course, there's a lot you can do eso we're gonna be full here seeing the scope of this, the upcoming lessons on building up that interactive Web application where we can connect what's happening within our Google sheet to a Web app. So show you how to do all of that, and I'll show you how to get started with that in the upcoming lesson. 4. 3 Write your first Google Script: welcome back. And I hope you've had an opportunity to set up your Google account and create a brain you sheet Because this is where our starting point to get into the script editor and the coding and everything starts at this point here, you can also develop scripts on their own so they don't necessarily need to be tied into one of the ops. You can go out on its own and link it into the ops, and I'm gonna be showing you a little bit of that linking into the application. So in case you aren't opening up a Google shooter, you want to tie your Google APP code to an existing Google sheet? I'll show you how to do that as well in the upcoming lesson. So first of all, we need a place to write our Google script and that is already pre done and ready to go within our Google sheet. So you can go under tools hit script editor, and that opens up a brand new Google script. Eso ready to go? And the nice thing about it is, that's already tied. So if you're doing it this way, you already connecting it within the Google sheet. And this is my preferred way to do it. Because if I've got a bunch of data here within my Google sheet, I also want to be able to access it and make use of it directly. When I open up that Google sheet, I want to be able to make use of the script. So this is a separate project. You can call it something different. I'm gonna call it update, er, just to be consistent with my Google Google sheet name. And just to show you when you go over to your Dr you're not going to see this one here because this is actually in order to get to the script file, you always need to go through your Google sheet. And if you create the script, file separately, then you're going to see them within your drive. So that file is sitting somewhere within being connected into this Project update or Google Sheet file that we've created. So now we are set up and we're ready to go with the Google sheet. So one of the first things that I usually like to do when I'm developing within Google sheets is to create some additional options here so I can run functions right from the Google sheets. So I don't know what have to open up a new page. And the reason for this is when you set up a Web app in Google scripts than if you make changes to it, you always have to republish it, and it does take a little bit more time than to be able to immediately update the code. So this is why when I'm developing the applications, I set this this pop up window function that runs within the dashboard. So the way that you can set that up. So this is all this is going to do. So we see that we've got help here on the end and what this is going to do Is this gonna add another button here that we can click and we can have a drop down with some options? So that's all this function is doing, and we're gonna use the on open and on open is a built in a method within Google sheets, which basically means when you open up the Google sheet, it runs dysfunction. That's all it's doing, and you can run the function separately as well, if you like. But this is the default one that when the Google sheet opens, it looks for on open. If on open exists, then it runs that sheet content and you got to see how this works in at the end of the lesson. So right now, let's just set up a variable. So just like javascript set up a variable. And here is where we start using some of the built in classes for Google. So we've got the spreadsheet up. So this is a class that exists within the Google sheet, where we can connect and weaken grab the spreadsheet eso. Basically, it allows the user to connect with the content within the spreadsheet. Eso were running the script file connecting to the class that holds the values and the information for the spreadsheet itself. That's where we get spreadsheet, and the part of the app that we want to interact with is the you. I sets the user interface, so we need to get that object and we're gonna place all of that information ready to go with him that you I So now we've set up a variable, given that some object to contain within the u I. And we're going to run another Google sheet method and this is is gonna be create menu eso . What this one does is this allows us to create a menu after the application initiates eso. I usually like to give it a name, like maybe options or something like that. So whatever makes sense and remember this is going to sit at the end of help. Here s Oh, that word options is going to sit as the name of the tab and then we can add a number of items here as dropped down functions that can be run and and executed directly from the user interface. So the first thing that we need to do is we need to add a caption. So I'm just gonna call this one pop up eso Basically, this is gonna be the same as whenever we're doing helps sheets help. So it will be in the sub menu there, and then the idea here is to connect it to a function and the function I'm gonna call show dialogue and then lastly, let's adul of this add to the u I. So there's no parameters that we can pass in, There s Oh, that's it. That's all we have to do and let me show you this part here. And so now we need to create a function called showed dialogue. So every time that button gets clicked, we're gonna execute the content within this function. And we also want something toe happen here. So when we click the pop up button for now, let's just set up a toast. So again, we're grabbing that spreadsheet app. So the same one that we grab over here and this is the class for the entire spreadsheet up and that gives us ability to get the active spreadsheet. And once we have the active spreadsheet than we can specify methods. And so I simply want to just have a simple pop up message. And this is wearing I use a toast and within the toast, I would just do button clicked. And we are going to get rid of this as we progress to the lessons eso This was just to illustrate that we're going to create this you I item within our menu and click it and have something happen. So now let's go back, Teoh here to our Google sheet and because we've made an update and this is live within the cloud. This function on Lee runs when we open up the Google sheet. But I did say that we can initiate the running of that function as well. There's a couple options here within the script file, so we compress debug eso debug is different than actually running it because we need toe except permissions. And this is actually something that you're going to probably have to do quite a bit off. They've added additional permissions now and now. You always have to type this continue every time you hit these permission screens. So go ahead and okay, that and not just basically says that it's giving the script the permission to manage the spreadsheet in our Google drive. So I'm gonna go ahead and allow that, and you do need to allow it in order for the script to run. If you don't allow it, your scripts not going to run. So now I've executed that, and I see that I've got this new top here within my menu and it says options and I can click pop up and this is the result here, where we get button clicked, we get this little toast that pops up. So every time I click it, we get a little message here for the user. So that's what we want to accomplish for this lesson. And next lesson I'm gonna show you with in more detail on as to what we can do. Because, of course, we can't do this within this little pop up. We can't have all the information. And also we need to add some information to this Google sheet because it's pretty empty right now. So that's all coming up in the next lesson, and we're going to get things started and get a whole lot more interesting. When you see what happens with Google sheets that's coming up. 5. 4 Create dialog and webapp of Google script output: we'll come back in and this? Listen, we're gonna make things a little bit more interesting. So previously we saw how we can have this toast motile pop up, coming up and showing up whenever we click our options button. And this doesn't actually give us an option to pass any other data over or to display anything in a nice format that we could actually used to update. So let's take care of that in this lesson. So I also want to add in some data, so maybe we can do first last age, and then I can list out just some random names here. We'll start with mine, and I believe you guessing at what my age is. So it's actually not 30 on, then I'm just gonna create some random people. Here s so that we've got some data, Teoh toe, Look at within our within our output here. Eso next do Mike their egos. We've got just a few people, and really, at the end of the day, it's not gonna matter how much data you have here. We're gonna be ableto output, all of it because we're gonna make it dynamic and we're going to look through it dynamically. So now we've got some data. Eso let's take the next step and update this dialog box. So what we want to do is we want to create a template from our HTML and the way that we're going to do that. So we need to create an HTML file s Oh, this is gonna be the actual output file that we're gonna take a look at eso. This could be called whatever you want, just going to call it. Or maybe I could just call it a sheep or something like that. So by default, it's always gonna go as she html and you see that it opens up in you tab. And now I've got a couple files here that I can access. So instead of having this little toast popping up, I want and put some of that information and set that up to be ready to render out within the Web page. So let's set those up. So first of all we need to do is grab our HTML template. Essentially, that's what this is gonna be. And we're gonna default to some or methods that are built in. So this one is HTML service, and what HTML service does is it lets you serve up what Web pages that can interact with service, side app, script functions. And this is exactly what we want to do. So you see here that it's a simple this here, they've got ability to serve up that file. And this is a really long method name here, so create html output from file. Remember, it's always gonna be lower case in the upper case as we've got new words. And then just within here is we've got the file information, so we're actually doing the same thing. But we've called the file something different. Eso let's take care of that and place this into a usable object and we've called our HTML file sheet. Now we've placed it within the sheet and we can output and we can we can return and we can look at the content there. So as long as at the end of the day I do a return off this HTML template, you're gonna be able to output and see the contents of this template. Eso let me open this one up and we are gonna require using G query So this is one of the things that I usually like to include right away, cause J query, especially within what pages, just makes things a whole lot easier. So I'm gonna grab the Cdn Fergie quarry, and this is the best way to go. If you're adding in external libraries, you cannot. An angular you can add in pretty much any library you want in order to create some more advanced functionality and capabilities within your application. Eso The next thing that we need to do is we need to maybe add something mawr into this page s So let's give it It's given and Div with an i d of output so you can output some information there and we can close that off. And then just below here is we're gonna add in some script tags, so just do script and close that off. And within the script here, it's a number of different things we can do. Maybe we could just do some simple G query and we'll grab that put and we'll add some HTML in there. So just say hello, so it looks pretty good to me. Let's try the same. So now when I click options. We got this pop up. So we get that one popping up and let me see. I think something might have gone wrong there. So just gonna save all of those. And one of the things that we're actually missing here is because we're trying to show a dialogue and you only need the return on the do get. So what I'm gonna do is I'm gonna do I can create that do get and the do get is for Web apps. So basically, it will put the content of the web app and I'm gonna show you how to update the dialogue as well. Eso We don't actually need to show to return the html template within the dialogue, but we do need to use to get you I because it's the you why that's actually gonna output the content. And from here, we can do actually a show dialogue, a modal dialog because that's the type of put that we want and the user interface that we wanted to So is the HTML template. And then all of the attention is a title here. So my get up and let's save that. So let's go back out here and now this time will try the pop up. Let's try that again and see, That s so we click the button. We see we've got the title. My data, We've got the value. Hello. So that's what's being rendered out here because as within our sheet dot html, we're actually doing an output html Hello. And we can see that that's exactly what's gets rendered aid. And now, to turn this into a web because you do need to do get within the web up. So just gonna call this one our first project version. We're gonna gloat and deploy it. And what this is going to do Is this going to give us a Web? You are all that we can visit and see the output here off our Google sheet. And when we render that we see, there we go. We've got hello being rendered out eso Basically it's two different ways and these are the only two real differences between the two output methods and this one over here. So this one is the one that he used for development. And then over here is when we launched the rial web app and then the code in the middle. All of this code here in the middle, This is gonna be the same between both of them. Eso the next lesson. We're going to dive deeper into this and I'm gonna show you how to build this out with a little bit more detail and to provide a richer user experience, So show you how to do that in the upcoming lesson. 6. 6 html output append and update styling with Bootstrap: welcome back. And in this lesson, we're gonna show you how you can append in additional information into your HTML output. Eso This goes the same for this pop up window if you want to add some more information and I am going to get rid of this toast here. So I'm gonna comment the suit for now and let's add some additional HTML into our output. Eso we saw that we could use a template to create html output. We can also create that html output on the fly and swell so we could just simply create that html html output instead of grabbing it from a file. But because our application is gonna be a little bit more complex and always like to include styling, we're going to keep it this way to creating that from that HTML file. Another thing that I usually like to add in is to add in some styling, and bootstrap is great for styling. If you haven't used boots drop, it's just a really easy way to bring in some typical classes within your HTML structure to basically make it look a whole lot nicer. And we are going to be using tables within boots from boot strap eso. That's the component that we're gonna be using is tables. And basically, what bootstrap can do is that bootstrap can style your table is really nice and neatly without having any additional CSS that I need to play around with. And I didn't want to keep the focus of this course are more on the coding as opposed to the styling. So that's we're gonna bring boots drop in and brought bootstrap into our HTML file. So just like a regular Web page, you have ability to bring in libraries, link libraries, type HTML code typescript, type CSS, whatever you want to do. It's just like any other Web page. So what we also wanted to look at is what else we can do with that html output class. So within the HTML itself, we've got a number of classes and methods that are available to us. We see here that we can add meta tag page. We can also append content, and this is the one that I wanted to look at because here is where we want to actually upend some of that content on. We've got a bunch more options here. We could even set our title if we want eso weaken, set the title of the output Eso this is Mawr. If we've actually rendering out our application eso I'm gonna put that in there. You know, I did say that there isn't gonna be that much of a whole much of a difference. But for now, let's just add that in and we just call it my sheet and save that they're So now if I was to publish the op, I could get rid of all of this within that tab and I could have a nice clean title. There s so a lot of stuff that you can do. And really, you can really play around with this. You can even have five icons and so on and so on. You can get the title. You can change the title. So whatever you want todo eso for this instance I want to use upend. So what upend is going to do is basically it's gonna penned additional content into you of my output. Or in my case, I've called it html template so I can upend that output and what I'm using the dialog box. We cannot put in the same way. But this way I can actually see that update happened right away. And I don't have to go through all the trouble of rendering of the web application. So basically, what this does is this gives us a choice. We can either render out the each to Mel over here and just simply output it within our HTML file. Or we can get gather the data, send it into our HTML file and build out what our output is gonna look like it. And from our HTML file using G. Corti. So whatever method you prefer, really, at the end of the day, this is gonna be javascript. And then I've got an option here to use J Query. So what I'm gonna do is I'm gonna actually pass the data into the HTML because this is actually going to give me the ability to pick up that information, and we can see it within the console. So this is gonna be a lot more of a usable format when we're developing it out. Eso Either way, you could do that content so you could build out here just dependent, just as regulated to Mel content and dump it into the HTML file. Just says we did the hello world. Or we could just build out and get the content and pass that through. So that's what I'm gonna do, and I'm gonna show you how to do that in the upcoming lesson. 7. 7 Coding exercise add UI item render HTML: welcome back, and in this lesson, I want to give you the opportunity to try out some of what we've looked at and what we've gone through in the earlier lessons. So what I want you to do is to create your own Google sheet. If you haven't already done so, log in to the script and I want you toe add a menu just as we've done over here. So I did not pop up menu that you can interact with, and you don't have to go to create the Web. Hoppsy can skip the do get for now and at the moment simply create an HTML file. So whatever you want that to be, create your own HTML page and have that render out and pop up within that pop up window. So once you've completed that, then we could jump into the next lesson, and we can focus more on the data. So you also do have a few options, and they do advise. Take a look and see what's available over at the Gu class output html output class and see what you can do where you can add in additional output and change some of the options for your window. You can even change the size of the window as well. Eso Try that out for yourself. Make some adjustments to the height and the width of this. Thats pop up window and pause the video trying for yourself, and I'll show you how to set the height in the with and the rest of it. We've already gone over, and I'm just going to do a quick review back on the other side. So go ahead, pause a video, open up Google sheets, tried it for yourself and see if you can set the height and the width of the window. So right now we're just have a default heightened with. So let's set this to be really wide. So maybe we could do 1200 pics or by 800 picks. So again, depending on what the size your screen has set a custom height and with from the window. So if you had the opportunity to pause the video and try that out, if you haven't, then go ahead at this moment. Try to try that out because I am going to be sharing the solution. So we already have gone over the code in order to show the dialog box toe add the you I menu or to update the you I menu within Google sheets. So this court is all that really the same thing you part of this particular challenge was to actually set ah, height and with so much like what we did with upend, we've got the html template object and we can use this object and weaken set with so we can also get with so we can get a height. We can get a with but we can also set a height and weaken set with. And it's the same thing that we did when we added an upended. We can just simply specify a pixel height or pixel with and in this case, we need to do both of them. So we've already set on upend into that html template object. So let's take this one step further and we're gonna add and we're gonna set ah, height. So we want to do set height and here so this is gonna be a pixel height. So let's set this height at 800 pics, and we can actually chain them together so we can also do set with within that same statement. So we just need to enter in an integer for with So you 1200. And what I'm gonna do is I'm gonna get rid of this part here, and I'm gonna simplify our statement, so that should do it. So now you heard of that second dot There s o that should do it and let's try to. So now our dialogue should be much wider and much higher. S o that was it. And hopefully you're able to achieve that within the solution. Even if you did it within separate lines of code, that's fine. This was just a more of a shorthand method because all we're doing is attaching all of these methods to that same object and weaken chain them together with the periods. So no, I didn't mention that earlier, but if you're able to accomplish that, that is great. You're already to go into the next section where we work with data where things they're going to get a little bit more complex because we're gonna be actually interacting with what is contained within the sheet, and we're going to be passing that all over into the HTML and then lastly, we're gonna loop through all of that information from the sheet and display it nice and neatly within that within the output. And then once we've completed within the dialogue than that's we're gonna move it into the Web app and render out and build it that Web application, So all of this is coming up. 8. 9 Output content from spreadsheet: welcome back. And I hope you had an opportunity to try a working with Google Sheets and even have some fun with it and see what type of HTML pages you can output within your Google sheet. So now let's look at the data. So we saw that we can create a sheet from the HTML template, and what we want to do within this lesson is grab all of our data from our Google sheet. So there's a number of ways to grab the sheet, and I'm just gonna call it F cess. I'm gonna create a brand, you another object here. We're going to contain the contents of the sheet, and now I'm gonna get over to where I've got my sheet classes. So go down over to here. That's under the G services and that spreadsheet eso. This is where we've got information that's contained within the spreadsheet up. So there's a number of ways to actually identify our spreadsheet and grab the information so you can grab it by the active sheet. We can also grab it by I d Weaken, grab the active spreadsheet. It should be actually spreadsheet and then or grab the spreadsheet by the i d. So I usually like to grab the spreadsheet by the I. D. Because we don't always necessarily need to have an open spreadsheet in order to run the code. So this way is more efficient. But if you are sharing the coder, reusing it, then every time you need to update 90 so benefits and drawbacks to either method. So what I'm gonna show you is how we can do that. Get spreadsheet by i. D. So they have an example here. I'm gonna copy and paste that in, and I'm going to use the same variable SS instead of ABC, which is not the idea of my sheet. I need to grab the idea of my sheet. And the idea of the sheet is located within the URL. So you can go up here within the spreadsheet. You are l and grab the idea. So it's whatever follows slash D slash and all those characters before we do slash at it. So before all of the characters of the next slash so you can go ahead and space that in there and basically this will grab the spreadsheet into an SS object and then next We need to specify the sheet that we want to get. So this is so we've First, we've got our spreadsheets. And then we need to specify because spreadsheets can have a number of sheets. So maybe we'll just call this one people, and we're gonna grab it by using the sheet name. And we also have a number of options so we can get the active sheet as well. So this is all dynamics or whatever is active and open. This is what the Google script will use, but in case we want to be more specific, which we do, we want to use the get active sheet or not things. Sorry not to get active sheet. We want to get sheet by by name. So that is, get that's another method. He that's just not seeing it right now. But this one is called Get Sheet by Name, which is located under spreadsheet, and there are a lot of different methods that are available here. So the one that we want to use is get sheet by name on. This gives us the ability to grab the sheet by name, and you could also combine them just that's what we showed you earlier, where you can link them together. Or you can set up separate variables for those eso we're gonna just We're gonna actually call this one sheet, and we're gonna do so we can do now because we've got the spreadsheet app in the SS object weaken do SS gets get sheet by name, and then here we just need to specify the name. So the name of our sheet is people, so that will pick up all of the sheet data into the object sheet. So now we need to actually get the data range on. When you're working with spreadsheets, you need to be really specific. So get range actually returns a specific range off content. So we need to isolate out the content. And then we need to specify what we want to do with it. So three steps here. First, we have to identify the sheet, which we've done identify where I identified the spreadsheet, which we've done identify the sheet within the spreadsheet, which we've done next. We want to get the information. We want to get the data into a range. So this is where we just use a spreadsheet and we get it into a range and we don't have a specific one. So we want to use get data range, so range of the data that's present So it will return back all of the data, and that's actually what we want to do. So I want to do get data range, and then we want to get the values of that. So let me just copy this because this is exactly what we want to do. And we can see here that we've actually got the ability toe loop out through all of that content. So maybe I'm gonna cheat a little bit here, and I'm gonna use that source code toe Lou Pote, and we can build out the content that's available within our sheet. But then I'm also going to show you how you could pass it in as data because ultimately that's the way that we wanna build that out. So now if I do show a dialogue, we don't have anything on the output, but we do have it within the log. So we see that the content from our sheet has now been logged out, and basically that's being done here in Logar log so able toe loop through all of the rows of data and were able to make use of it within our within our Google sheet. So the same idea is what we're gonna do, coming up where we're actually gonna instead of just getting the role information and getting the value from those rose, we're gonna pass all of this as data, and then we're gonna loop through it and build it on the other end within our HTML. So that's what we're gonna try to do in the upcoming lessons. So that's and for now you can try the soup for yourself. You can try this source code this looping of the source code and just see if you can log out the contents of your spreadsheet into the log. And this will prepare you for the upcoming lesson where we were, where we take the data and we do even more with the data 9. 11 Output Sheet content as Data: welcome back. And this lesson, we're going to do more with the data. We're actually gonna pass it over into the sheet. So we saw that we can loop through it and we can make use of it. But we also want to pass it through into the sheet into the console and then render it out that way. So there are two ways that we could render the same and this was one of them. So I hope you had the opportunity to try it for yourself and a quick reminder when you are using the source code that I'm providing, make sure that you're updating this with your own I D. Because after I finish up this project, more than likely, I'm gonna be deleting this Google sheet and as well, you don't have access to it Anyway, eso you're not gonna be able to read the data and throw some errors there. Eso make sure that you've set this Google sheet up on your own and you're rendering at the data on your your own sheet. I d. So now we saw it, we could loop through, and that's pretty cool, because we've got all of the data sitting within the values and we can loop through it because we see that this is just a simple array of values. Eso Now all we need to do is actually pass those values through to the other end into into our HTML s. Oh, this is gonna be the same thing that we've done over here with the penned and so on where we're able to pass some data. So we're gonna use that each to melt template. And this time we're gonna use we're gonna pass data. And basically what's happening here is that we've got options for content we can pass through, so we can just simply pops it through as data. So maybe what we'll do is we'll just passed through the values as data and I'll show you how you can pick that information up into the HTML eso this information is gonna be passed through. So within the HTML sheet, we need a way to actually pick up our information on we're gonna pass this into javascript . We're going to use script, and so this is just regular javascript, and we need to actually pass the data information that we've got in RGs file into the sheet . Eso let's set up a variable and we'll call it Data GS. So this is going to contain our information and this actually is gonna look similar to the way that PHP looks. Well, the syntax look similar to start off, but we are gonna This is how we can pass information from our back end, which is a GS code into our front end, which is our HTML. So the content contained within here that put contained within here this is coming directly from that value within the back end that we've called data eso. What we also need to do is we need to make it into a usable format going to string if I it using Jason String. If I since this regular JavaScript and we need to string a fly, that data object and what else I'm gonna do is an A console log, that information so that we can take a closer look once it renders a constant log data gs. And now we can go back to our GS and finish up that code within our Google. She part One other thing that we do need to update on this particular application is that we need to place the each to Mel template into another object. We're gonna police it into HTML as an object because we actually need to evaluate out that HTML template code and we can't evaluate it out as well as rendered in the dialogue. I'm going to get rid of the upend, and I'm gonna update that to evaluate. And there's no parameters that need to get past into evaluate this, a method that runs within within the Google script that gives you the ability to take a closer look at that. And then I'm gonna remove at the template part because now we've got a new object that actually has the evaluated code from our HTML, a template object. So let's go back out and try this one out. So go ahead and click the options the pop up. And now you see that we can go into the consul because we're actually out putting that information into the console. So it's nicely neatly presented into Honore and within the array, we've got a number of different, another ary within that array, and we've got them all nice and neatly numbered. So this actually gives us that Bell ability to identify the coordinates off where each particular value is located within our spreadsheet, and we're gonna use this within the upcoming lessons in order to identify which cell within our Google sheet cash needs to be updated when we make changes within our HTML output. So it actually had to do that in the upcoming lesson and do try the suit for yourself. Try out the evaluate part of it because whenever you're passing data, you're gonna make sure that you do evaluate that data so that you can ace immediately posit into your Google sheet. And then I'm just simply logging that out. So I'm picking it up as string ified data, and I'm logging it out and making use of it within my console. Try it for yourself. In the next lesson, we're gonna jump into some G query and make use of our output content in order to build out what our content is within the sheet and display it for the users that are coming into our Web application 10. 13 Loop data output array as HTML: Welcome back in we saw how we can send our data information from our Google sheet into our pop up window and then also eventually into our Web app That's gonna work the same way. And we see that we're passing this information through. So this is actually coming from the sheet so could make any changes. It will actually update that information once this op is loading eso. It's not gonna update it now if I make changes and I can't actually get to it cause I'm opening up a dialogue window eso Of course, it would have to refresh him. The same thing would have to be on the web app. So we are ready to you jump back into the sheet because we've successfully passed that information over and we're ready to actually do some more with our Google with our G query . So let's ah, let's get rid of this one here. And we just set up the J query Rapper. So one side, the Dom loads and once G query is ready to go, we want to do some type of action, and this is where we want to build it. The content for output eso we can loop through the data because it's being passed through. Let's take a closer look at that again and because it's within the consul now. So this is just your typical front end code eso We've got an array, a bunch of information couple honoree within an array s We need to loop through those rays . We've got a length because our arrays and we can easily loop through that information and output and make use of it. Eso first thing that we need to do is set up a loop. So do Lupe and was due for I loop, starting it out. I had zero and loop while I is less than and this is where we're going to use that new data GFC that we created s let's got a length. So you know that just looking at it previously that it's got a bunch of items in there. So we need to look through that length and we need to increment I so increment I plus one, that's our loop. That's an initial you loop. And then within that object, we also have some additional objects as well. So much like what we saw earlier were actually a loop within a loop. We need to do another one of those. Some actually gonna update this. Do far. I just for formatting See. So now let's do var row and will set the value of role to be nothing. And we'll add in our other loop. And so this is gonna loop through the array that's within that main array that mean J s ar e g s r e s o this one. We're gonna use g just like what we did in our earlier example. Because the Dina really is just gonna be the same thing. Eso loop through the values. Or so we've got data and I length. So for each one, we're going to contain a length as well, certain loop through it while it's less than that length and we're gonna increase. We don't actually to keep the equal sign either. And now let's increments G plus one. So +12 j and we'll finish up that loop there, and I'm gonna invent this where I can't indented. So I gotta use the arrow, the space bar eso Next. We've got this loop and we need to check to see if there's contents in there. So if data G s I rocket J So if content exists in there, then we're gonna you make use of that and we're going to do something with that. And this is where we're gonna use row. So Row is gonna equal road plus data GS, I and G, whatever value is being contained within their so it's simply going to just put that and then over here, we'll just do ro equals row plus and we're gonna calm a separate. Those were just comma separated out, and we're gonna fic fix that as we progress Eso Now what I want to do is after we've finished our two loops, I want to output whatever content is held within within role. I want output that to our elements output. So html and we just call it Rose. We're gonna pass all of the information that's been built from road. And I just realized that we probably need because we're setting roll up here. Let's take that out of the loop. So that s we're building it out. We can We can see all that information being generated there because this way it was be overriding it for each additional generation off the array information. So we see here, we've got first last age. We've got the next one, the next one and the next one. And over here we can also We can also add in a line break if we wanted to. So we could do something like this that just for now so that we represent that we're actually moving to the next item in the array within that loops. Let's try that and see, that looks a little bit more clear. So there we go. So now we've got all of the information from our from our Google sheet, and we're just opening it comma separated out within our sheet. So next lesson, we're gonna have Teoh actually create inputs for this so we can update that content. So that's coming up, and we're gonna build a table around it as well, so show you to do that in the upcoming lesson. 11. 14 Sheet data in HTML table with inputs: welcome back in. Previously we saw how we can output are content from our Google sheet nascent neatly into our HTML. So this lesson let's take this one step further and we're gonna add some additional styling to this. So what I want to do is only put it as a table. And I've already touched Bootstrap. So I want to add a class in as well, So basically, just going to start this out here and create that first table and I'm gonna use single quotes, So I'm gonna update them all with single courts now as well, to be consistent. So first of all, we started out with the table and here we need to specify that for the role. So before we enter into our next loop, we need to specify that row will equal or not full its role Well equal Ro ro so equal itself essentially adding a new table Row two, that html output. So that's were building the suits. Then when next we need toe loop through it. And then what we need to do is we need toe output, that content, and then over here, instead of the Kama, we know that Actually, it's over here, this one. So instead of that comma, we know that this is the end of the table rope we just need to insert are there. And so here. This is where we're actually building out all of that information. So we're not gonna comma separated it any more. And we know that we've got several pieces of data. So it's just quickly output that and go into the inspector. They're going to the consul because we're still able to see it here. So you see that we've got all the different items in the array. We've got all of the different names. So we know that the first the 1st 1 is going to be the column, the call of names so we don't actually have to output where we can open those differently. So we know that because that's the 1st 1 We can put that differently, and then the rest will just have values that we want to make use of eso. This is these are the values that we wanted to make use of and let's add in our HTML So this is an exercise in adding HTML. We need tohave our content there and then next to that, we need to have ourselves. I'm gonna take care of the head information afterwards. Let's just see how this looks right now and just make sure that we're actually putting it nice and neatly and that we are eso I used the table inverse. So maybe not the best one to use. Let me see if I've got another one that I can use. Basic table. And I know I didn't want to spend too much time on this Eso we want to maybe an inverse the head. We can get rid of this table inverse altogether and just give it a class of table eso for now. Let's just do that. And we can worry more about styling. Azzawi progress. So sometimes sometimes this happens as well. That it is does take a little bit of time to update. So now we see we've got all of our content inmates and neatly formatted. And the interesting thing here is that if we enter in city, we can planetary in just a few cities here we go back to the output and we can see that that automatically gets out of him. So we've got a very dynamic application where we can always take care of all of this information really easily. Eso going back into our HTML. So we need to actually create some ideas for the cells in order to identify them. And we also need to create some inputs for this content. Eso Basically what I want to do is I want to do a condition here and I want to check to see if the value of I is equal to zero or we'll just do it like this. Would you if it's not equal to zero than we want to do this and if it is equal to zero or we could just do it else so that we know that this is the header part so we can do this a little bit different where we can do row and we know that this is ahead or sell eso not as much that we got. We can do a th instead of TD because this is header and we should also include that within over here as well So instead off. So know that if I equals zero that we do that else what we want to do is we wanna do Instead of a table role, we want to do a table head and then we actually want to do the same thing down here at the bottom. So we're adding in the ends there, and just so everything is nice and neat. Let's see how this looks right now. So you see that we've got that table headings and something has happened there. So we can always look into our HTML and see what's happened there. So it looks like our first item there s we've got our th and we've got something blank being passed there. Uh, let's go back into our code and see what happened there. Eso What's happened is that we're actually need I actually forgot that I should actually do a th that was my mistake there that it should be a th and we're still always adding in that t r and I did want to close off the th and the T R. So that was my mistake there that I was not doing the tables properly, So it's not a t h. It's a T head that we need to close off. So we're opening up that he had and closing that off. That was where we got that issue. This is one of those things where you it's always good to keep checking back eso next. We need to make these ones these inputs something that we can adjust. And it's actually good that we've broken these up because these ones are now gonna be inputs with values. So instead of just putting the value, we're gonna do it as an input and we'll do type and will equal that type to text and then simply just do a value here and then add this as the value of the input. And I believe that's all we have to do it just close that off to get that as the value of the input and let's go back, try do it again. And so now we've got these as creditable fields. So the next thing that we need to do is we need to identify what value it is, and then pick that up in the G query so we can add some geek worry where we've got maybe an on change action or something like that, that whenever any with these fields get changed, then we can automatically send the command to update the Google sheet. So I show you how to do that in the upcoming lesson. 12. 15 data passing using jQuery from element: welcome back in this lesson. I want to pick up how we can identify that these particular elements are actually changing . Eso I've done a little that cleaning up the code, making it more readable. And we know that we need to set some classes in J coury. So let's set that one up first. And this is our basically our selector classes is called this DT value and this will give us stability using Jake Great to make our selection and one of the things that we do need to keep in mind because this content is dynamically generated. If we try to register the all the different classes initially, we don't have any available as the contents being built so we could throw it. We could up come up with some errors s. So this is why if you're generating dynamic content using bakery to select the change event listener, you need to do it this way. So we're specifying first going to specify the containing element that's actually existing and hard coded. And then we specify the on and we're using that change event. And then now is where we need to specify the class that we're listening to. And then afterwards we've got our function that we can pass through, and then we could make some type of action on this. Uh, let's, uh, let's Consul log out the contents here. So just do a consul log and let's just type something like changed for now. And then, Of course, we're gonna update that as well. So let's go back. It's and refresh it, do a pop up. And now, whenever I changed the content and if I click off of it, then it's actually changing it, and it should be saving it. So now whenever we make a change, what I want to happen is I want this information. So first I wanna pick up the information in the cell and I want to identify where it fits in on the grid because we've got columns, we've got rows, so we need to know which call him in which role this particular out, this particular change is taking effect. And in that way we need to send that back to the GS code to actually update the Google script. So let's ah, let's go back into our code and we'll see what kind of information we can pick up. So you see if we can pick up anything in this so make some change and we can see the value there that's been changed. So if we do something like this value, we should be able Teoh, pick up the value of the information. So now when we go in and we click it, we make some changes, were picking up the value. But we still don't know what role in which call them not sin. And the secret to that is within When we're building out our HTML, we actually do know what's rolling which call them. It is because we've got I and G. So now we need to be able to pass that information over and pick that up. Eso We've got a number of options here, and what we could do is we could pass the data information so we could do something like this where we do data role and equal that to a value and we could do data column and equal that to a value. So maybe that's probably the best way to go for now. And what we can do is set this up as a value of I because this is the rose. And then next we need to set it up as a value of columns. And let's, uh, let's just out our double quotes around there. So instead of I let's do t J. And then what we want to do is we want to pick up those data values as, where picking it up within this and also I'm going to use the J query this so the dollar sign this so we can get the full object information. Let's go back out here. Try this out. Do a pop up window. So now, as we update something here, let's, uh, quite a bit of stuff going on here within the console. Eso Now let's just see if we've got some updates. So as we're updating, So we just updated that cell with Smith in it, We see that we've got the value there, and under data set, we've got our columns and our rows so we can actually access this information. So just as we're accessing value, we can also access that data set information, so know that the value is there, and this time let's grab data set and we've got row and column so we can put that information as well. So I'm just going back to that and called a data set. So let's open up our our editor. And now we can get rid of this J crew. And this was just so I could get more information on this object. So let's do data set and we've got role. And we also have a column. So we called it C well, for column. And then lastly, we have a value as well. So let's just make sure we're picking everything up going back here and what I'm gonna do, actually, just save this one here just to make sure that it saved. And let's pop that up open. So now we've got one and zeros, we've got those ones. But we're actually not picking up the value properly, so we see that we've got the coordinates properly. Uh oh. Because I've got data set there instead. Just going to value that was the problem there and popped up back up again. And this time everything should be good. We've got the coordinates of the cell that we wanted to update. We've got the information that we wanted to update, and we've got everything ready to go. The next lesson all we have to do is sink it to our content within our Google sheet. I'll show you how to do that coming up. 13. 16 Class google: in this lesson. I wanted to show you how we can update the contents of a cell via our Google script. So now that we're able to pick up the data that we want to make use off, we will need to pass this information back to our Google sheet. Let me show you how to do that. And this is Google script run. And next we need to do with. And this is how the client side AP, I so with successful handler and then we run the function s. So this is what is contained over here. And then we just need to specify the function that we want to run. So this one is on success and run the function, get unready e mails, and then we can pass some parameters in there as well. S o, this is the callback function. Sorry, s o. It's actually running on success function, which is over here. So this is all contained on the front end. This callback function, you don't need to specify one, but it's always good to have something whenever the script runs. Just to make sure that we're completing the cycle whenever we've completed the successful run of it. Let's go back into our code. Instead of typing it out, I'm copying and pasting. I now need to create a function called on success. And then here is next. We need to specify the function that we want to run. Eso. The function that I wanted to run actually is going to be called. Let's give it a name instead of that. Let's just call it update. We're call it sheet update is going to run the function, and I want to pass some parameters. So these are actually the parameters that I wanted to pass through into the sheet update function. It's no need Teoh. Link it all together. Create a couple functions here. Eso next, what's to function sheet update and we're passing some parameters. So let's take a closer look. So we've got roll call, um, and value. So just do R, C and V passing that information over eyes within the Google sheet and we also need to create an additional function here to this function is what runs whatever we've made a successful update, so we just call it on success and it can actually retrieve parameters back from what we're doing within the G, the Google sheets. Eso just call it data because we're actually retrieving back some information. And maybe what we want to do is due on alert. Just keep something simple like this that we've got data info or something like that is we're gonna pass value back called info from our Google sheets, and I will go over this again afterwards. I know it's probably getting a little bit complex here, and I am trying to run through this quickly. A swell As try Teoh, be able Teoh, address any issues or any information that you need to find out. S o What I want to do over here is I wanna return back, so I'm picking it up as data. So maybe we just return it as data so we can return back information on Let's set up that variable. So we'll set up a variable called data and they've got to go back again and we've got one called in force. Were passing within the data object were passing in full. So let's set that up and we just call it success for now. And you can really you can build this out as well, attaching additional information here within these parameters. So we're just gonna pass back success. So basically, what's happening now is that whenever we've got an update, it's going to run this Google script and passed the values of the parameters that we're picking up from that change object. And then once it finishes, it's going to run the on success function. So let's try it out and see if it works. So let's go into options. Pop up s so far, so good. It's making update and change it and we see that we've got success. So we're able to successfully pass the primers and information back. And next step is to finish up the sheet update function, where we're actually going to update the content of the sheet. So that's coming up in the next lesson. 14. 17 Update Google Spreadsheet fron HTML: welcome back. And I know I've been going through things rather quickly, but let's get everything working first and then we'll do a no overall review off what we did and why we did. It s so we know that we're able to pass some data from our front end to our back and Google script code. So the next part of it is the same thing that we did over here, where we identify the sheet and we get we identify the spreadsheet, then identify the sheep that we want to use, and we want to update some of the values. And we can literally do pretty much the same coat that we had here, where we're getting all of the data and all of that information. And now So we've got the same object information that we had up here. But now what we want to do is we want to set values. So let's set up where we're gonna identify the cell that we want to use. So this again, this is identifying the cell that we want to use. We've got it all within the values object, and we can use the information that we're picking up and we're passing through here that we've got the roll off, whatever the value is, and we know that we've got the column of whatever value this one is. And come to think of it, I think I do have them reverse. But we'll just keep it as this right now. I think I'm calling the columns as Rose and the Rose That's columns S. So I think that was a mistake that I think I made here right in the beginning where I should have been identifying them differently. But for now, let's just keep it as is, and we've got the rose in the columns that we've passed through here, rows and columns and it might be actually okay, so lets us see make sure that this works out and we're setting the value of this object located at are row value calling value and we need to set the value of it. And that's where we're picking up the arguments here that we've passed from our front end to our back end and we're setting that information and then the next thing to do is to use the range object, and we need to instead of get values we need to set values and notice that this is plural and the values that we're setting We're gonna set all of the values of the entire object so we don't need to be very specific. Were just updating everything to the new values that we've picked up and hopefully, let's go try the suit and see if this works so the goal and open up options. And now, whenever I'm making an update there, we can see that we've got success and we're actually updating, so you can see kind of in the background there that the Google sheet is updating. So all of the information that where picking up there and that alert is getting kind of annoying. So maybe we can get rid of that. And instead, because we are using boots, drop bootstrap has much better alerts. So let's sigh at these alerts in instead of having and a copy this again, instead of having for success on, also, instead of the output here are gonna put, I have put another div and this div is gonna be my flash message. Dan closed off. And then within this flash message, this is wearing an update, not information. So I called it flash message. And I do need to keep that hash because this is Jake worry. We do have everything working. So right now it's just making it look better. Essentially, eso What I want to do is I want to add in some information there within another dibs would do alert, alert, success. And then here's where I can add in the message information. And then, lastly, let's close that dip off. That's all I need to do s so we could do something. There's a lot of room to play around and really customize this information. So going back here. So we had called up data info. So let's try the suit one more time. So options pop up. And now let's make some updates and we've got success will fix those ones up, and we see that we're getting the success message popping up, and it's it's actually it's not changing, but maybe we can even pass some additional information additional parameters back so we can do something like R C. The So let me ah, success. And we'll just do changed. Changed our and call him And this, uh, this Of course, this is just customizing Teoh. I'm just picking up that information that I have here at the at the top to the It's close that off. So basically we changed road and we changed role. We changed rope. We changed the column. Value success. We don't maybe don't need the change. May that's gonna make a little bit. Let's try the suit now. So now this time we should see different messages within that success. So we see that we've changed role one column one and now we're changing row to call them one. This is called him zero. So let's call him zero. So actually, that was correct that these are the role is in These are the columns. So everything is good there. So we don't need to do any trouble shooting or bug fixing species success. And the road that we change the column that we changed in the value that we changed it to s Oh, this is it. We've connected, successfully connected our Google sheet to our to our HTML code. And the next step is to output this information into the web app and get that working properly. So that's coming up and then we're gonna do a review back again. Teoh, do a quick overview of what we've actually worked on within this course. So all of that is coming up. 15. 18 Launch Webapp: Welcome back in. We're coming up to the completion off our project. So we're almost ready and just go over it. Usually at this point, it's a good idea to Dover and do some final testing. I just received that and I'm going to refresh it. So it cleans out the consul and we can see that we've got the changes. They've already taken place, going under options, the pop up information. We've got all of that array object information being passed and we can do updates to the content in the Google sheet and everything is passing properly. And as far as I can tell, everything looks to be working. Greet s. So now we're ready to actually put it as an app and to build it as a web app. Eso This will update what we have here before, because until we launch a new web app or until we update it, we're not going to see the new code. Eso we do need to take into consideration that we need to evaluate out that content which we didn't have initially set. Eso do keep that in mind that we do need to do and evaluate out off that content when we do the do get s Oh, this is gonna be look a little bit different Eso everything else should be the same. We get the spreadsheet information and where you build it all that so all of this we can just simply copy and paste. And the only part that's gonna be different now is how we're actually out putting the contents we've got in the data and we need to do an evaluation of the data so much like what we did over here, where we did the HTML template and we did the evaluate. We need to actually set this to sandbox mode in order for it to work. So let me just piece that in there. And now instead of rendering up that Htoo template just like what we did over here, we're going to render that eight as html. So let's go over, publish it doing you version and updated and update that and we've got the Ural will still stay the same. So when I refresh the page and something went wrong there, So let's see what happened here with evaluate so might be similar to what we did earlier. Where we're having a problem with the evaluate out of that content. And one of the problems is we need to use that same format where we're getting HTML service from sheet. So this coat is gonna be the same. So we're using the template and we're not generating that. HTML That was probably the error there. So try that again. So just do a new version to version three and update it to see that. And now let's try the Senate again. Eso that time it works. So that was the problem. The way that we're rendering out that html object. Eso Now let's, uh, minimize this screen to make it smaller, and it's not re sizing. Great. So that's something that we needed to take care of. Uh, let's, uh, let's pop this one out and will resize this one as well so that we can do a nice side by side comparison and just to make sure that everything is working. So you see, as we're updating some content, it's updating within the Google sheet, and that's exactly what we want to happen. The only thing is, it's not responsive s So this is something that we can take care of. So there's always whenever you're launching applications, always gonna run in. Nothing is ever works right off the bat, and this is one of the things that you'll find that usually there is quite a bit of this kind of back and forth and just making sure everything works properly. And that's because we did bring bootstrap in, but we didn't add in the heading information, so I need to take care of that. That's one of the bug fixes that we need to do. But we know that our code is working well, and that's the important part of this lesson and this course to make sure that our code is working properly. So next lesson, I'll do some bug fixes and get this part working and output that Web app, and then we'll do a full review of the court. So all of that is coming up 16. 19 Styling Tweaks: welcome back. And in the previous lesson we saw how he can output, are content to our boots dropped table. And we saw that it's actually really, really large. We can go back down to 100% eso it does fit in fairly neatly, but it's not really responsive. And the whole idea for boots drop is to make it responsive. So this isn't gonna be really as much of it, Ah, Google script code as it is about bootstrap and what we can do to fix this table s Let's open up our editor and take a closer look at what we have to work with. So I have made some updates to the court. I've got it in the view port. I've added in different size of thought for the sheets. Eso I've added made those changes. And within the Google script, we can see that I've also added in a different size. So this is gives us the ability to basically to try it out first and see what it looks like before we actually implement it into our Web application. So now when I goto options pop up, it's a little bit better. It's not great, but it's made the sells a little bit easier to read. And, of course, if you're not using bootstrap, you won't have all this potting and spacing. And this might not even be a problem for you. If you're not using boots, drop eso. Everything is working within the code. So let's just do a quick update to this. Eso tried it out, and we see that anything under about 800 800 pixel with just doesn't look right. It's better to have a bigger screen in order to interact with this content for a bootstrap . I've also added in the Container Div. They're a swell within boots drop. So let's let's just publish this up, knock out to our Web app and hopefully it looks a little bit better and going back out here , and we can go and refresh the page and we can see that in fact, it we can see all that the cells. So this is good. We can interact with um, and also we could also even go back down to three columns as well to make it more formatted . We can also remove some of the padding from boots drops of quite a few options, but we're not going to concern ourselves about this, because objective with the course once again was about the Google script. So we've got that up and running. Everything is working. We're updating to the new cells the new values for the cells, and we're doing it via a Web application. Eso the next lesson. We're gonna do a quick overview of all the code that we've gone through in the previous lessons so that you're ready. Teoh, take the source code for yourself, try it out and get it working within your own applications. 17. 20 Source Code review: Welcome back in and this application. We set out to build out a Google script where we could create a Web U R L and interact with their content that's available within our Scougall spreadsheet so that actually having to give someone access to our Google spreadsheet, we can see that we can really nice and neatly interact with our content and have it reload and update our content within our Google spreadsheet. So we're able to do this using Google scripts and also some J query. So let's open up our Google script. Editor s o. First of all, let's go through the Google script. So we created a number of functions, the 1st 1 over here. The open on open is a function, a default function that fires off whenever you open up Google sheets. So that's all it does, and we're grabbing the spreadsheet, you ice or grabbing the whole you. I here and we're updating the menu. So we're adding this new item within the menu called options, and we've called it pop up and basically all it does when you click pop up. It runs the function, show dialogue and then so dialogue is over here. So we've got our sheet. So essentially, we're building a template from our HTML sheet, which is here, and we are going to go over that shortly. Next, we're grabbing the I D off our Google sheet. Eso That's this one over here. So essentially, we're selecting what content we want to use because we might have a number of different sheets. And we want to make sure that we're grabbing the information from the correct spreadsheet as well as from the correct sheet. And that's where we're identifying at us. People. Next get data range so essentially that select all of the content that's available, and then we get values and we place that into our values object, and we're passing that values object into our HTML template data. Eso were just simply creating another container for it to pass it to our front end, and then here were doing and evaluate because we're passing data and as soon as you pass that data, you need to include evaluate. We're setting the height Inuit. So these are again optional fields on just how it's being displayed here. The dialogue is being displayed, and then what we're doing is we're popping up that dialogue box that contains this HTML file. So building out all the data and out putting that HTML box and the next function that do get s So this is the default function that gets run whenever the user visits the published web app URL So it just fires off the same way show dialogue fires off and pretty much the same code. So all of this is the same, And we could short not by placing that within a function if we wanted to again. It depends how we wanted Teoh to use this or we could do something different. Here s so there's a number of options, but we're just going to leave it as this because this way it's a lot more flexible. If you want to try to edit it and work with, it s so next. We need to evaluate it just as we did over here. And we need to put it in a sandbox mode. I frame as this is standard way of putting your content within the HTML Web app You, Earl, it's the world putting that each to metal object that we built eso That's it for this part , and then we've got one other function down here, and this function is actually fired off by the front end Quote by the HTML. And what it's doing is it's doing pretty much the same thing we do up here, where we identify and we make our selection, we get the data, we get all the values and then we overwrite one of the values, and then we simply take that whole object of values and rumor. Over here, we've over written it with the new value that got sent through, and we set the values of that particular range. So we set the values of all of the content within the Google sheet. Now you could be more specific. You could update one particular role if you wanted to, so those are options as well. But for now, we're just gonna leave it as set values. And then here were just simply returning a message back to the user as to what happened and what was done and what was changed. I know I've got spaces in there and I don't have spaces in there, so let's let's just keep it's consistent and get rid of some of the spacing here. Eso basically just tells us what we did. So we have something different toe look at as we've made our updates over here on the sheet . So over here we're actually pulling in that content from the GS code. And this is how you do it with the bracket question mark Explanation Mark equal. And we're string defying this content. So essentially we can run the GS code on the front end. Basically, this is what that's doing, and this gives us the ability to run that GS code on the front end. And all we're doing is we're taking the value of data that's been passed in and we're string defying it, and that basically gives us the ability to pick it up in our JavaScript. And we're out putting it in the console. We see it, it's Honore within an array, and it contains all of the information from our Google sheet over here. This is just styling. We've connected boots drop so that we don't have to worry too much about styling. And we saw that in the last lesson we did have to make some updates, the styling, so it didn't really work that seamlessly as I wanted to, as I really didn't want to focus. Hardly anything on styling Eso down here again. This is just the output. So we've got an area for a flash message. So this is that message that gets sent back, gives us a place to deposit that, and then our output is gives us a location to build our input fields. Next, we've got a link to G Query because we love using G query because it just makes things so much easier and quicker to write. And again, I didn't want to focus too much on the front end code. I did want to focus on the GS, but of course, they go hand in hand, and we did end up working a bit with the foreign in code. And this is where we selected Jake worry, because it just makes it easier to interact with the content within the the dawn, as opposed to javascript. We need a few extra lines of code. Eso. What we did is we looked through the length of data. G s s O. This is just all street javascript. We're picking up the lengths were getting that passed. So essentially, actually, this was all javascript. All of this looping here. And the only part that was J Query was where were out putting it to the output. And we're updating html for that on the next. Another part of that was J query was where we're actually adding our event listener eso This one does get a little bit complex within JavaScript because we need to identify the elements first. And, of course, we're building them out dynamically s. So this is why we had to select output and we had to register all the DT values that would contain all of this function on change. So it to register at first, and then we're as we're building it out. So this is the way that you could do it with G query, where you can build out some dynamic content and still register those event listeners. Then first we tried it within the consul to make sure that we're picking up our data properly. Next, we ran our Google script because we picked up this data and we ran our Google script. So this is a default method and Google script to connect back to the Google sheet and essentially run this function. So all it's doing is running the function on the back end and passing some information to the back end. And it's got a callback function, which is again back on the front end. So once we do the round trip to the back end code, once that's completed, we end up back on the front end and we have ability to pass some data again once at this point. So this is what we've done. We've passed the data back to the back end, sent up to here. And of course, we've already reviewed this where we're picking up this data and making use of it, updating our Google sheet on the back end and then just sending a message that we were successfully able to update it. So there's a lot of variations you can do with this code, and there's quite a bit that you can extend this code. So I'm gonna leave this to you. We've got all of the basics covered. How to do all of the core functionality that we've set out within the scope of this course eso. Now I'm gonna post this source cord so that you, too, can try their for yourself and take a closer look at Google scripts and see all the really cool and amazing things you can do. Thanks again for taking the course, and I do suggest Try out Google script because really, it's truly amazing and it is actually fun to work with, and it's great practice. Even if you're looking to practice some of your JavaScript, it's a great place to you, really let loose and let your creativity flow, creating some amazing things using the Google script.