Apps Script Web App FUN API and JSON Data Spreadsheet | Laurence Svekis | Skillshare

Apps Script Web App FUN API and JSON Data Spreadsheet

Laurence Svekis, Web technology Instructor

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
14 Lessons (1h 10m)
    • 1. 0 Sheet Data as JSON API Introduction

    • 2. 1 Create Google Script

    • 3. 2 Use HtmlService createHtmlOutput

    • 4. 3 parameters in the URL

    • 5. 4 Client side Server Side Code

    • 6. 5 Output from a file

    • 7. 6 Spreadsheet Data

    • 8. 7 Spreadsheet Data 2

    • 9. 8 Data as JSON Output

    • 10. 9 SheetData as JSON Output

    • 11. 10 WebPage Connection Fetch

    • 12. 11 Request parameters

    • 13. 12 Request parameters 2

    • 14. 13 Sheet Data as JSON API Conclusion


About This Class


Output Spreadsheet data as a web app URL with JSON feed of contents of the Spreadsheet. Run code and customize endpoint
Bonus free PDF guide packed full of resources and code snippets to help you learn - follow the lessons using the guide!  Learning made easy :)

One of the most common questions I get is how to use Spreadsheet data in a web application.  This course will cover how to setup a web app and output sheet data as JSON to a web URL endpoint.  Which can then be used to display and use the data within any web application or web site.

Please note that the scope of this course is creating a WEB APP with Google Apps Script code, JavaScript and previous programming knowledge is an essential prerequisite to this course. 


Course covers

Use of HtmlService to output HTML page contents

Setup of web app for web URL Create a WebApp

WebApp Client side - Server Side Code

Client side Google Script option

Output from a file

Getting Spreadsheet Data

Data as JSON Output

SheetData as JSON Output

WebPage Connection Fetch

E Parameters in request URI

Conditions with parameters

Taught by an instructor with over 20 years of Web Development experience.

If you've been looking to get started with Google Web Apps -  THIS COURSE IS FOR YOU!!!!

Nothing to lose - Fast friendly support is always available to help if you need it.

Please note that the SCOPE OF THIS COURSE IS Creating a simple endpoint of JSON data and will not cover complex commands and everything about JavaScript, JSON its a simple HOW TO GET STARTED COURSE - if you are looking at more detailed Google Script content this is not for you.


1. 0 Sheet Data as JSON API Introduction: hi and welcome to the course. That's going to show you how you can write Google APP script, creating a Web app that you can use as an endpoint to connect as a data source from your spreadsheet data. Use it within any Web application. So using JavaScript in Ajax connection to your Web app. U R l make a request and return back spreadsheet data. My name is Lawrence, and I'm so excited to have the opportunity to present this course to today. I really enjoy working with Google. APP script concept of this course came up from a number of students that have been asking me for various ways that they can use the content contained within a spreadsheet within their own websites. And Web applications as a place to house data, also have a flexible place where they can update content and have it propagated to the web instantly. And this is the course to address those questions. There are some prerequisites to this course to be aware of. You should be familiar with JavaScript, have some programming experience prior to taking this course, as the scope of this course is specific to the topic of creating a Web app and connecting to the Google sheet data and out putting it in a Jason format. You should also be familiar with Jason and how Jason Works and JavaScript objects work and how you can output content into your Web application using JavaScript. So JavaScript is a crucial part of understanding the upcoming lesson. We're gonna be using Google Maps script, which is actually based on JavaScript in the cloud. And it's got a number of classes that you can utilize. And they all contain various methods that help you accomplish Ah, whole bunch of stuff really easily. First radio show. You can create a Web app. Create some client side code, sending content from your Google script to the client side. Also out putting content from, ah file so you can use a file as a template out putting again into the client's side and coming from the script site. And then how to access the spreadsheet data. Put the spreadsheet data Innogy Sohn format that that can be utilized within the Web. App you how you can output that within the client's side, using the Crete in the template from file method and then finally out putting it as Jason into the Web app and creating a Web. You are l that you can access the Google spreadsheet data that's being output as Jason and how you can use Fetch or Ajax. Request to your Web app you earl and retrieve the data that's contained within the spreadsheet and then use that data in your JavaScript code to update contents of your Web page and use the request parameters to customize some of the code and output customized content depending on what the request parameters contain. I've also included a handy PdF guide that you're looking at right now, a guide that's going to contain a bunch of girls that we're going to be covering an upcoming lessons, a swell a source code. And there's a number of exercises that I'm going to challenge you to get you coding in each one of the lessons. So, no, you're excited to get started. So go ahead, log in to your Google account and let's start righting some Google APP script 2. 1 Create Google Script: Hey there. In this lesson, we're gonna set up the up script that's going to be a standalone script, and then eventually we're going to connect that to a spreadsheet and output content from the spreadsheet. So if you've never used Google App script before, you need to have a Google account in order to log in, and this is all done within the cloud. The script is written, and it allows you to connect various G sweet products together to the main website to start scripting and to find out more information is Google script, Ford slash start, and from here you can start scripting and gives you some more information about what's available. There's also some example, scripts and developer reference area, and you can go over to that as well. To find out more about Google APP script is a sampling of some of the many Google maps that you can connect together. And there's all that information here where we've got reference information and we are going to be looking at this leader on a swell because we are going to be using various classes and methods that are available within this reference section and as you can see there's a lot you can do with Google ob script. So let's go back a few steps and open up. The online editor and Google AB script is based on JavaScript and writing it with in the cloud. So they provide you on online editor so you can just go ahead and click New project. And this will initiate and create the new projects and also at script dot google dot com for its not shown for it slash start. You can see all of your projects that you have projects that are shared with you. There's triggers and executions as well as help and additional information settings and just a bunch of really useful stuff about Google AB script. So go ahead and hit new project and that will open up a brand new script within the editor . I'm gonna minimize some of these two compact that controls. We do need to give it a file name, So let me un minimize that. And let's give this project Ah, file name. So, Jason, a P I and point course hit, okay. And this is what the project is gonna be referenced as so when you look into all the different scripts that you have open and available. It's gonna be referenced by the name, so now we can go ahead and compact that controls. I'm also going to shrink the left hand side. And this was the listing of all the files so that I've got a little bit more space to use within the view area or to 200 so that the code that I'm writing is a little bit more visible on the screen. So this is the default starting position within the Google APP script. They give you a default function, and there's a menu up here that provides all the functionality. And we are gonna be going through some of that as well as, of course, the deploying of the Web app. Since that's the core of the course, there's also help, and we can get more documentation tutorials, Support says a lot of useful stuff there. This menu is the quick launch menu, so you can click thes and do everything you can in the upper menu that you can do it a lot quicker so you don't have to open up the drop down. So once you've set up your Google APP script, given it a name and set up the project. And then, of course, if you ever want to access the projects, you can list it. All of your projects seriously can go to script at google dot com, and that's one way to get to your projects. You can also go into your Google drive, and this is going to show you that this is the new project that I just opened up that we're working on, and you can also open it from your Google drive. So either way, there's a number of different ways to get to your project whenever you're ready to work on it again and open it up. So remember again, it is shared within your Google drive so that you always have access to it, and it's shared there, as well as within the script dot google dot com site. So next you're ready to start setting up our first script. So let's coming up in the next lesson. 3. 2 Use HtmlService createHtmlOutput: hope you had an opportunity to log in to your Google script. As in this lesson, we're going to get right to it and start writing some code, creating a first Web app and this Web app is going out. Put some content and this is all done through a service called HTML Service. So a number of different methods that are available there there's more information about the service and as well as but the Google script in general, over at the developers dot google dot com, ford slash AP script for it slash guides for a slash Web execution More information about the Web APS. So essentially, What they're saying here is that it needs to contain default functions so either do yet or do post and either one of the uses a different method that is being connected to on the URL . And then you have an option to output content. So this is using HTML service, and you can output either text content or HTML object content. And there's also some information about the E parameters and the request parameters. We're going to be looking at that in a little bit later on in the lessons There's also some information directly about the HTML service and how you can use that. Seek an essentially use each to melt service in order to return. HTML sits usually user interface or some type of Web interface, that it puts this content and so we're going to be using out. And then you can see there's also a number of different methods that are attached. So what? The way, the way that it works is thes methods provide that added functionality. So we've got one where we consent a title, so we're out putting the content. We can set the title to the Web page as well as there's an option here to output Met Attack . So you cannot put some meta information. And also with the HTML service, there's the sandbox mode. So its client side, each to most service scripts. And then they have all of these different methods as well that are able to output that HTML content and then for the HTML class, the HTML output class. And this is the one that we're gonna be using. It's relatively straightforward, so we call the default function that is gonna be used by default within the Web app to output content and then return the content that we want to output to the user to make it visible to the user. So let's get to it and open up our up and create our first function. So there are some reserved function names, and one of the reserve functions was, Do get. And what this does is you're gonna need this function for the Web, as this is the function that it goes to and renders out the block code that's contained within this function. Whenever the Web U R L is accessed, so it's first create a variable, and this is gonna be containing output and using the HTML service. And once you've written the class and you do the the dot, you're going to see a drop down of all the different methods that are available, then there is quite a bit here, and it also tells you what parameters were being expected. The one that we're using in this lesson is going to be the create HTML output. I'm going to keep the parameters in there, so it's expecting some HTML to output. Let's create another variable and will call that html and this is going to contain just HTML code that we want to put on the page so you can put whatever you want in here. I'm gonna type in hello World says. There's just a typical way to put some HTML and weaken bold it. Or better yet, let's make it as a church ones, so it's bigger and stands it with more. So once we've got the HTML string content that we want output, we place it as a parameter in the create HTML output method and using the HTML service. This is going to construct the HTML content and then we're ready to return it back and the do get function is expecting a return of the value, and putting an output will return that content that was constructed back on to the Web page order to run this function. So if you just go hit, do you get and you run it, you're not going to see anything happening. And that's because that we have to publish it as a Web app. And then the returned output will be shown within the Web URL, and that can be done under the publish tab. So go over to publish and hit deploy as Web app, and then it asks you for the project version. I'm going to just call that first, it asks you to execute the app. So you do need to execute the APP. And for this type of functionality, you could just execute the app as your own account. And there's also an option to execute the account as the user accessing the Web app so this would log. This would ask anyone going to the URL to log themselves in eso. In this case, we don't wanna have that level of authentication because we want this AP I end point to be just available. Eso We could make a simple fetch request to it and pull the information back on the page, or we want anyone going to the Web. You are about to be able to see the content, and there's also another options. These air, the different security parameters. So who has access to the app eso By default, you can keep it on lee yourself, so your only the one That's I. As long as you've logged into your Google account and you're going to the Web, you are l. You're the only one that's gonna be able to see that Web Buehrle. So you have to be logged into Google in order to see it, and it has to be your account that's created the up. There's also an option of anyone. So this means that anyone can see the content on the Web, you earl. But they have to log into their Google account so they don't logged into their Google account. They're not able to see it. And the last option and the less secure one is just an open anyone, even anonymous. So this is perfect for creating are on open end point, as we can simply connect to that end point. Or we can go to the Web, you URL in the browser, and we're going to see the content being output. So let's deploy our Web app and you get to you or else so one is an execute herbal. You are also execute herbal at the end. This is the Execute Herbal Earl, So if you place that into your browser, what's gonna happen is you're going to see the content gets rendered out. Now, the difference between that and the deaf version is that the diversion is for developing the application so you can see content as you're developing it, and immediately there's no difference. But if you go into your code and if you update this, I'm gonna add a two and save. Then I refresh the diversion. The two was added, and if I refresh the execute herbal version, it stays the same. So this is going to stay the same until you re publish a new version of the APP, and it's not gonna change until you do that. So this could be considered your live version. And then this is your developer version where you can write your developer coat. So keep that in mind that there's two different you are else that being output. And you can also simplify this as well. So we don't have to have this complex of a structure. They're gonna just copy this and commented, It's doing an end Tunggal comment, and we can simplify this into one statement where the h two Mel is here and we just simply returned back the results of that string So you could really simplify the function just by doing that and going over to the dead version you're going to see that still renders out the same way. So I do have an exercise for you in this lesson, and that's to log in to your Google account and create the script. Do get script. I give you a project the name, of course. And if you haven't already and then add the code to output, some HTML so created HTML message out, put it and then publish it. Using the publish button, Deploy as a Web app and try the URL within your browser and make sure that you're working and up and running. It could be ready to move on to the next lesson where we look at how we can get parameters within the Web app and how that works within the URL. So it's coming up in the next lesson. 4. 3 parameters in the URL: Hey, this lesson we're gonna look at how we can check the different Earl parameters. So getting some content that's being passed into the U. R L some parameters and then using those and out putting those within our Web app and also how we can use logs the logger log method in order to debug our application and find out a little bit more about what's happening behind the scenes. And there's also going to be an exercise in this lesson where you gotta check the parameters of the Earl. I use the value of the parameters toe up boat, update the output and add the URL. So add to the euro that you're out putting an update, the message content. So it's gonna be the challenge in this lesson. So let's get to it and I'll show you how you can use the parameters. The challenge is gonna be to update and output that content dynamically, using the content contained within the parameters going back into the function. We're gonna toggle the comment for that one and then going into our web application, we're gonna use the do get function once again, and this time we're gonna get the request parameters passed in as a variable value of E and then use that value. We're gonna put it into a variable car parameters and then use Jason String if I so if you're familiar. JavaScript Google APP script has this as well, this option to string if I an object and this basically turns any object array into a readable format into a string value and then, using the logger log, put whatever is contained within parameters and let's go to the Web, see that we're not returning anything, and that's because we don't have the return. HTML. Create output yet, and this is why you do need to return something. So if you do get this error message in your do get function within your Web app, then you know that you haven't updated your not returning anything. But we should be able to see something within the logs because this function did run and always still runs in the background. Even if it's not returning and output, they're going into the logs content. You see the content of the logs, so we've got parameter and context past content, Ling's query string parameter, And that's because we don't have any parameters yet within the Web. You, Earl, I'm gonna put one in calling it message, and I'm gonna type hello world in the message and let's go back into view logs. And now you see that we've got content here. So we've got an object called parameter, and then within parameter, we've got message, and then the value of message is Hello, world, or we can go into parameters. And that also contains an object called message. And within there is Hello world. We're able to pick up that information, and that means that we can also use that information that's contained within their. So instead of string defying all of the parameters, let's just take that one object that we saw. So we did have an object called parameters, and we're gonna access just this one. Part of it is just one object, and we're gonna string if i that one and then log that out into the lug, so refresh and going into the logs. So So we've got message. Hello world. So that means that we can take the object information that we've got there and we can output the contents of that parameters So let's take e parameters and to message and run that one more time and see what happens and then going into logs. We've got an array format where we've got hello world and this the way that it works is in case you have another value here that has another message and you go into the logs. Now we're gonna have hello world and we've gotten a re here with two objects. So I only want the first item being returned back and try that one more time, a refresh and then view logs. So now we've got the string value off Hello World So ready to output that using that HTML service just update the HTML service and create output from an HTML string and that string will be hello for now, just make sure that we are out putting and returning content. So pause the video here and it put the contents of the parameter message and you also have to check to make sure that you do have that message in the URL and we do need to check to make sure that there is a value within the parameters and we also should set up a default message in case there is nothing sent. Then we've got nothing to show. So I'm gonna type in nothing sent. And this is just our default starter message. We're gonna still get the parameters as programs and we're gonna take that full e object in case we want to access any of the other values that are contained within their And we can log it, what's contained within parameters and refresh and then going into the logs. So now we see that we've got the message. So just like before, that's the ur That's the content that we're after. So it's do it condition and check to see if the value of message is contained within e parameters. If it's contained within their, then we're going to update the variable value of message taking e parameter and getting message object and then using the index value of zero, because that's gonna be the one that we're after, as we saw with the option to have more than one message. So if messages contained within the parameters, it should output the message. And if it's not contained in there than it should output, nothing sent. Let's also update that html gonna wrap it in each one tags side. Go back out to the developer version. Refresh. I was So we've got the hello world. And what happens if we get rid of message? So if we don't have a message that was get this nothing sent. Make sure that you're able to pick up the content within the U. R I and out put it on the peach. You could be ready to move on to the next lesson. We're going to do some server side coding and we're going to evaluate the code and send it to the client side, so it's coming up next. 5. 4 Client side Server Side Code: it was the HTML template. Cloths were able to dynamically construct HTML and also with evaluate. This gives us an opportunity to return. HTML. Output is an object format, and we can pass some values as well as run code on the client side. So let's check out what we mean by this. And as you can see in the example that we've set the template and we're creating a template that's using the variable value of food and you see these that looked like PHP brackets these air, actually to render it the content of the code, evaluate that we're able to get the content and evaluate that content and out putting it within the do get works the same way where we can render the template, evaluate the template and return the evaluated version off the template. So it's create our first test function, and this is just gonna run a test of how evaluate works and how the templates work and then afterwards will bring this into the do get. And we'll use that within the do get so you can use the HTML service and create a template and this number of options for creating template and in this case, we're gonna just use the string HTML and within the string HTML. So it's expecting a string value. That's where we've got the quotes and we're gonna output content and looking for a variable off food. And that means that we need to create a variable of food. And the idea is that the variable value is gonna sit within the Google script on the server side and is gonna allow us to pass it through on the client side. And template is already identified as the object. So we have to set a separate variable as Fu is gonna be an object sitting within the template mean template, object. And this is where we can pass a value off. Hello sues the longer log and output the evaluated version of template. So if we just do template itself and I need to update that Texas a template so it's saved that. And then if I go into view, we have an object, food is equal to hello, but we're not updating were not evaluating. So if we use the logger and we take template and we evaluate it now, we've got HTML output that were out putting, and that means that we can render it so this is the same thing that we were doing here. We're creating HTML output. So we're getting this as content, and if we get it as contact, it's gonna turn it back into a string. So it's evaluating as HTML content, and it's turning back into a string value. Now watch what happens now. When we go into the logs, you're going to see that it gets rendered out as just hello. That's the values being rendered. And that's all that we want to actually output because within the each to mount template, that's all we're out putting. We don't have any additional content so we can have things like that each one. And now when we run that, we're out putting it as just regular string HTML content. And that's all being done with evaluate. So essentially, what Fu is doing is it's allowing us to take that value and use it within the HTML template and get the whatever the values here that's contained within the script. So let's apply this into the do get function, and again, this is the default Web app, so just make sure you only have the one I've commented out the other ones from before that we were using. And it the way that the script will run is if you have another do get below that it will run that one. So run the last one that's available within the script. You have a challenge for you. The challenge is to run, evaluate code and output the value of into the client's side within the Web app. So glad pause the video and I shall walk you through the solution coming up. So we had a lot of the solution done. So where we created the template, we also had a value of four food and elliptic this to be hello world. So it is something. So it is slightly different and with do get when we refresh it, we're not returning anything, so always remember to return content back. And this is where we just take the template itself. And if we just return the template, what's gonna happen here is that we're not return of value. That's a supported type, and that's because when we initiate the evaluate, then that's gonna actually turn it into that HTML content that is the supported type. And now you can see that that value that's coming from the variable from the data that's Kane teamed within the script is being output on the client side. And if we do get content so you might be wondering, Well, what happens if I do get content? Because this is turning it back into a string. So once again, it's not a supported content. And the reason we were using get content up here is because we're out putting it into the lock so you don't need to get content unless you want. Turn the contents of your template back into a string format that you can read within the logger. So coming up next, we're gonna output to a file. So we're gonna create on each team l file put content into that html file in the Web app. So it's coming up next 6. 5 Output from a file: There's gonna be a fun lesson as we're going to create a file that we're gonna output the contents of that file into the Web app. And that's using that same creek each to male output from a file method from the HTML output and putting it as an object in returning it from the do get for the Web app. And there's also an exercise here where you have to create an index file and then use the code in order to evaluate content from the server side to the client's side and then output on the client site. And I'll walk you through how to do that as well. So first, let's go ahead and we're going to set up our HTML files are index dot html file that we're going to be using and then out putting that content. I gotta slide this over and go under the file top and hit you html file. And this is where you need to give it a name. I'm gonna just call it Index. You can call it anything you want. This is just how we're gonna reference the contents of this file and I'm gonna then minimize it and you can have any number of files can be contained here. And you can access all of these with the Google script. If you have other G s files, those are also going to run, so you could realistically create U. G s files for every function. If you wanted to, it's probably not a great way to do go about it, but you can segment the Google script as well. And there's also another option here where you can create a tire Lee new project. And this is one project that we're working on and this is creating a entirely new project. And there's also options to create new docks, spreadsheets, presentations, forms, drawings and some really cool stuff. So that's a lot of shortcuts within Google. So I gotta shrink this back down as we're not gonna be able Teoh needing to reference it and in here all type hello HT amount. Now the objective is to use the do get so using the do get method and putting the content in returning it as HTML. So first, let's create template and using the HTML service and then in the drop down we're using create template from file. So you've got a number of options I put from file and in this case, because we want to use the templates were doing template from file, and this is returned back. So the file name is going to be a string value, and the file name is just index. It's not index dot html. It's just index. That's the file that we're gonna be using and returning, and so immediately if we just do a return of temp. And if we refresh the Dev version, we don't have a supported version, and that's because it's a template and with templates you have to evaluate. So let's create variable called each to milk and take the template that we have and just run the evaluate because again, with the template and this gives us the option to pass data into the client side. So a quick refresher and we need to return back the html, not the temp. And this time we should be able to see the contents of the HTML file, which really need is this could be just as a regular HTML file. So if you had some styling properties there, you can have that as well and refresh the background will be changed to read so you can add in style. You can also run job script in here within script tax. So just like any other HTML page, you can do all of that front, front in code. So we did have a challenge for this lesson, and that was to get creative variable, so sign of variable value and just as we did before, in the earlier lesson to render it out because we're creating a template from the HTML. So we need to render out the content from the template into the HTML site of it. And that's using the question mark bracket in order to return the content so you can pause the video here, try this challenge out for yourself, and I'll walk you through the solution coming up. So first we're gonna need a variable in this variable. I'm just gonna call it data and I'm actually going out, put an object, and then I'm gonna string if I that as I out put it on the client site. So this way we can pass in a value of test and test one can be to one or this one could be status status of 200. Now we've got a variable value. And just as we did before, with temp, we can assign a value to it. And we're gonna sign ah, value of temp data within the temp object attached data, and then we're gonna sign a value to tempt data of whatever the value of data was. Now you can shorten. This is well in just copy and paste that in do tempt data equals this. So it's optional. I got to keep it separated for now, in case you want to make some updates to the data that's coming. Eso we're still evaluating and we refresh and we don't see anything quite yet. That's changed. And that's because on the client side we have to grab the data and we do that with the tags and this breaks back into the server site and then using Jason String. If I as this is an object that's being passed him, if you have a variable, you don't have to string. If I it, of course, and then break back into the HTML, the client side. And now when we refresh, we get the content that's sitting on the server side being passed through to the client site. Now there is a way to use it within the client's side. JavaScript, And this is one of the neat tricks that you can use in order to bring that object information into your client side. JavaScript Data GS and using the same format and a copy and paste it within the script tags and then use the consul log and output the contents of Data GS and Refresh. And then going into the Inspect, I see that I've got the content being sent back and notices well that even though we've got string, if I up here that we're evaluating and so valuing to an object and used data and if I know the object that has an object test value in there, I can return back just the test value that way so that so you can return the content. And I did want to cover one more thing that when I'm working with templates, I usually try to add the try and catch in cases. Any errors with any of the functionality. Eso I'm not returning anything. That's errors because I'm using the template. So there's more opportunity to have some errors, so I usually add in the try and catch. And if it's catching any of the errors that I'm going to use the return and use content service in creating text output and the output that I'm creating is going to be Jason String if I and it's being returned back within an object. So we saw these parameters before these e parameters before, so it's returning it back as a stratified object, and this is a case. Anything goes on. So typing in that within this object I got through an error and then some information about the error itself is gonna be too contained within the object. And let's set the mean type because we were turn it back as Jason. So you can set the various mean types, and this is also contained within the content service object where you could set that different mean type. So specify the mean type and the mean type that we want to set is Jason. So I know this has been quite a bit here that we've covered with the try and catch and just important to know that this is an option. Teoh have better functioning cord you can do to try and catch. And if there's something wrong, s only if there's something wrong, then it's you going to use the catch. So let's say, for instance, we have index see, and we're trying to put something so we're throwing an error. So that way we don't just see nothing. We see that no HTML file named Index see, it's gonna help us with debugging the application, and that's coming from the catch and out. Putting the text content as Jason and then the air apart is there so you can catch the error on the client site and you can use that as well and output messages and also debug your application that way, and that's optional. 7. 6 Spreadsheet Data: this is gonna be an important lesson, because this is the one where we start tying together getting data from the spreadsheet. And then later on, I'll show you how you can use that data and output it into the Web app that we've been looking at in the earlier and previous lesson. The new service, called Spread Sheet Service, and this allows you to create access, modify Google sheets files and essentially using the content stored within the spreadsheet as a source for the data. And we're gonna also have exercise for you to connect to the sheet, select data from the sheet and then also select the values from the sheet cells and then return it as a string and then pass it to the client site. So that's all coming in this lesson. There's more information about the spreadsheet service, and, as you can see, there's a tonic classes and with the spreadsheets. It's one of the real, most robust services that Google has, because there's really a lot of stuff that you can do with it, and we're going to need to have a spreadsheet as a source for the content and for the data and we can create a spreadsheet directly within the online editor Arrigo New spreadsheet, and it's gonna open up a blank spreadsheet. Let's give the spreadsheet a name. We'll call it Tester, and we need to populate some data in there. And I'm gonna put just some dummy data. And, of course, whatever data you have, we're gonna be ableto output using the same methodology that we're covering in this lesson . So first thing you need to do is be able to identify the spreadsheet at every Google doc has its own unique i. D. So every spreadsheet as well has its own unique i d. The easiest way to get access to that I d is just looked within the Web URL so past where it goes spreadsheet for a slash d Ford slash. There's a long character strength, and just before it closes and rights at it, that's gonna be the I D. For this document. So create a spreadsheet and select the I D from the spreadsheet, and you can comment and paste it into your Google script so that you can reference it at in time. And objective of this lesson was to create a function that can connect to the spreadsheet and retrieve back data from the spreadsheet. And a lot of that information that we need in order to connect to the sheet data is already covered under the spreadsheet up that's going to give you a bunch of sample code and just different things that you can do with spreadsheet. So once you have selected the spreadsheet and using the spreadsheet app, that's got all of that information. You could also select the active sheet and then get the content from the sheet. And that's gonna be when you have a bound script that you can select the active sheet. So if you want to pause the video here and address the challenge were you connect to the sheet, selecting the range of data and then using get values to return back the values from the active range and return it as a string and then output that back to. So we just need to have a return at the end so you can go ahead and pause the video here and I'll walk you through the solution coming up. So first we need to set up a sheet I d, and we already picked that up in the last lesson or we picked up this character string that identifies the sheet. So I'm gonna put it in as a sheet string value and then next we need to select that into a sheet object. So just as we saw before, where we were specified the different class and this one is called spreadsheet app. And then we pressed the dot where you have presented with all the different methods that are available. And in this case, we're looking for open by I d. There's a number of different ways to open spreadsheets. You can open it by. You are l as well. And actually, I find that that doesn't work as effectively. So usually my favorite way to you do this is to use it by i d. I can update that burial name to be I d. So this way we're picking up the sheet object in a variable called s s. And then we also want toe actually grab the sheet from the spreadsheets that's actually a spreadsheet object as us, and then we want to pick up the sheet, and that's gonna be sheet one. So usually the best way to pick up the sheet is pick it up and use it by the sheet ning. There's number of ways to get sheet and there's also a general one. So we've got to get sheets that gets all of the sheets and returns it as an array. And if we want to use the 1st 1 we can just specify that we're using the first sheet and specifying the index as getting at index value of zero and next. What we want to do is one select the range of data that we want to use, And there's a quick and easy way to use one method that's going to select all the available data into the range. So we don't have to specify the rows and columns and how many rows and columns we want to return back and we're just called. This one role is so variable called Rose, taking the sheet object and then using get data range is going to turn back all of the available data in a data object in order to see the values that we use one more method, which is called get values and let's ah, law goat. The contents of roses were running the function. My data. And you also have to accept permissions because the app now is interacting with another Google product. And we need to always accept permissions when we do that. And you only need to accept the permissions once, and you can always remove out the permission seeking room. Voke the access When you go into your Google account, this APP will operate as this user and these that permissions that we're providing the app and just hit. OK, so we're allowing that function to run and then view within the logs. You see, we've got all of the data that's contained from the Array spreadsheet, and it's all output within an array format. Each role is its own array, and it's all contained within a giant array. So each row is separated out by its contents as items within the array contained within the larger or rate in order to split off the first rope. There's number of ways to do that, and we can just call this headings because typically when you do have data and I don't have that in this case, but you probably have headings there, So if you had people. You'd have first name last name email, So show you a quick and easy way to pull out the headings information. So we've got the rose and picking up the zero. And this will return back the first roll off contents Rose array. And there we see. We've got all of the headings that would be typically within the first part. And so what we want to do, We want to separate out the headings from the rest of the contents. And this is where we can use the another. Variable called it data. It will take the rose and using a typical JavaScript, we can slice the content starting at roll one and get all of the remaining content and that will split out the content and remove it. The headings. So we're gonna only return back the remaining content that doesn't include the headings. So you see, it's going to start with a two. So going into view logs, we see this particular value for this variable starts at 82 So that's what we most the time we're gonna want If we have headings. If you don't have headings in your sheet and you could just get the full role is data and use that as the object. You don't have to do this, of course, because it's already in a ray format that can be returned back. But if want to use it our Web app, that's always a good idea to return it back and have it within an object format in case you want to other at other parameters. And I'll just call that data and this is gonna return back all of the data that we pulled back. And then, lastly, we're going to return the contents of output, and we're ready to move on to the next part of this challenge. And that was to output it within the Web app within the client's side. So you can also do that as well between this video in the next video and I'll walk you through the solution coming up in the next video 8. 7 Spreadsheet Data 2: Hey, welcome to Part Two. So we're successfully were able to return back data and output it from the spreadsheet, and that's what we're doing within that function where we've called it, my data create the do get function, and this is the one that is gonna put content within the Web, and we'll open up our Web app to see what we've got. The latest code. We're not returning anything with him do get yet. We're not returning anything quite yet within do get. And let's do a quick overview of what we didn't know earlier lessons where we created variable value and this is what we're going to return back within the Web app. So we had something a variable of test, and then we also had status of 200 they were using that data and returning it back into our variable file or template and using the HTML service method, create template from file and that's expecting a string file name. So call that index, and this is going to use that same one that we used in the earlier lessons the index and using data so we can have it like data and have it data at first, and then we'll after update it with my data. Once we make sure everything is working properly, it's attempt. And we need to evaluate that as we're passing data and then returning the HTML object to return html. And we're ready to try that within the Dev part of the Web app so safe and go into the dead version and we see we're putting that content. So going into inspect and this is being output into the consul, we have to update Index because we're no longer looks like we're on Lee updating the test value. Let's save that and refresh. So putting the value once again. So ready to weaken either comment that it. And then from here we're gonna output the contents of whatever's returned back from the function, my data, and I'll minimize that against now. When we refresh, we should have the contents of the spreadsheet in an object called data. And there we've got all of the contents of the spreadsheet. So that was just a matter of utilizing it within the Web and making use of the content. So we also want out. Put it as a J sound file. So a Web endpoint off that content and that's coming up in the next lesson instead for turning it back within the client's side of the Web app. We're going to simply return that back without any template and output it as a Jason object in the Web. Buehrle. So it's coming in then up up in the next lesson. 9. 8 Data as JSON Output: in this lesson. We're gonna look at that class content service, and we saw that earlier that this is a way that we can output tax content and weaken set a mean type and then also from the content service. We can select the mean type to be Jason, so out putting the content as proper Jason format. And that's gonna be a The objective of this lesson is to output content as text output. And then also, I'd put it as a Jason String as text output and using the do get out, putting it within the Web up and then afterwards, in the next lesson will bring back the data and put that into the Jason. You are l So you can actually pause the video here and walk through this exercise? And this is a repeat of what we looked at in the earlier lesson where we set up the do get and first creating text output. And you can just put simple text output like hello world and then afterwards update it and include the try catch return the content service as Jason if there's an error and then also out putting the content from a variable JavaScript object and out putting that within the text content output area instead of the Hello World. So pause the video here tried it, and I'll walk you through the solution coming up. The first part of the challenge was just to use the Web app, and I would put content that's contained within the Web app and output it as simple text, using the content service in creating simple text output and providing a string value to output. They're simply doing a text return and then going into the Web, that suit that looks like so there got hello world being output as text. That's perfect exactly what we wanted. And now you can comment that it's that was challenge number one next challenge, and this one is a little bit more complicated. We're still using that do get function, but we're going to create a JavaScript object that's gonna contain some data. So variable call it output and using the Jason string. If I no need to sign the value there to Jason String, if I and then we're gonna put an object value in here, so having an object and then this object continued contain keen things like the status, and this is always good to include. If you've got an endpoint, that status is success. You can also include the status code since, well and then within here, this could just be a simple or rape. So just that we've got some data in a more complex structure that we're gonna be returning back. And so now we want to output the content and trying to do the output and then also a catch . If we do have any errors and the catch is gonna take the object information and we're going to return that back to the user. So either case, we've got something that we're returning back and Botham are gonna use the content service and creating text output. And the output in this case is just output coming from that output variable that we just created earlier and then set the mean type. And this is where we need to use the content service again and selecting the different mean types and this mean type is gonna be Jason. I mean, type. They saw there was a number of different options there for mean types as well, so we were simply returning back that content service and we can try and do the output there. So we've got status success, and we're out putting it as Jason object. And that means that we can also pick that up when we were making a request to it using JavaScript for memory website. So one more time just said we get some good practice in creating these methods to create text output. And that put this time is Jason String. If I and the object is gonna be the one that we're gonna create from the error message, so we've got our result and the result is going to be a string value of error and also we're gonna have error and then we're gonna have that error object that's gonna be contained within that I would put So if anything goes wrong, then we can output an error message. And we can also use that within the endpoint to pick that up. If there's are if there are any errors throwing, so we could also do the status as an error. So if we get a status box of error, then we know that we're not looking for the data, but we've thrown an error, and we need to trouble shoot it. And if we get status of success, then we can output the contents of data. Let's try that it within the Web, you, Earl, and coming up next, we're gonna bring the sheet content as a Jason object and output that to the Web URL so it's coming up next. 10. 9 SheetData as JSON Output: Hey, welcome back in this. Listen, we're going to be looking at how we cannot put sheet data as Jason Output And also what Jason String, if I does so if you're not familiar with this, this is a javascript that converts an object or value into a Jace on string. And there's more information available at the Mozilla Developer network. And there also is a challenge in this lesson is to get the sheet data into an object format and then use Jason Stratify to convert it to a string Earl output and then output the sheet data as Jason into the Web URL. And I'm gonna walk you through the solution coming up. So take a quick look at string if I and there's some examples over at the Mattila Developer network how string of fireworks and let's side dive into our code. So we had a lot of the solution already done, and this is gonna be really quick. So if you wrote it from scratch, that's always good practice. And you could have also reused some of the code that we had earlier where we had the my data value. So I'm gonna bring this one up as we're using this again, I'm gonna uncommon that code. So toggle the comment coat as we're still getting the content from the same spreadsheet and then out putting that content a status data, and we're returning it still back from the function, my data So we don't actually need the output content. Or if we want, we can keep the success there. But we already have status success here as well. So we're gonna be essentially duplicating the content that we're using so we can simply output the my data and we don't even need a value. Or we can update output to be equal to whatever is returned back from my data. Either way, we're able to get the content from the spreadsheet and out put it into our Web app as Jason code and see it's still a JavaScript object. And that's because we didn't Jason string. If I and this is where we need to do that Jason string, if I in order to bring it out of the object format and output it into a readable format in the URL, otherwise you're going to get error message like object object, as it's not able to interpret the object information in a readable format. So when we refresh now, we've got the spreadsheet information in an object. And within that object, we've got an array associated with data value, multi dimensional ray that contains all of the rows of content from the spreadsheet. 11. 10 WebPage Connection Fetch: How's it going so far? Great job on making it this far into the course. In this lesson, we are going to be looking at using JavaScript fetch to connect to our girl that were out, putting the contents as an endpoint and using JavaScript fetch to connect to that Web app and then pulling back the data that's contained within the Web app and writing it into our Web page. So there is an exercise in this lesson as well. If you already familiar with such, then you can go ahead and deploy the Web app. So make sure that it's within the execute herbal format, as you won't be able to pick up the deaf format using fetch and then also make sure that it's shared so anyone, even anonymous has access to it. Also next use fetch to connect to the end point. So that's the girl that you're connecting to, and I put the data first into the console and then loop through the data and out put it on the Web page and walk you through the solution coming up. I'm gonna be using code pen in order to build this application, and you can use your local editor. Or you could use any other editor that you please in writing this code and making that connection. Let's take a quick look at what's available and how fetching works. If you're not familiar. Thatch, there's more information on the Bridezilla Developer Network s. So this is an Ajax request where we're making a request from the website to the where the data is being housed and that's gonna be the A P i n point. That's the Web app URL and then that's gonna return back the requested data within a Jason format. So it's that data there and sending it back into the Web app and the Web app is gonna pick that information up and then make use of it within the application. So it's essentially a basic Ajax request. You can also use the XML http request object as well and JavaScript in order to make the request as you're simply connecting to the end point, the more modern replacement for X h r is fetch and this is based on promises. So whenever you make the connection, you get the response text back, and then once you've got the response, object back, then you can do something with it. Within the court, use Code pen, which is an online editor that's free to use front end code. So html, CSS and JavaScript. And of course, we are focusing on JavaScript and out putting the content within the Web page. So it's very good to be doing in this lesson also going into our Web app. Make sure that we're deploying it as an execute herbal, because that's gonna be the girl that we need. Teoh access. I'm gonna call this project version sheet data executing the APP as my default Gmail account that created the application. And then anyone, even anonymous, has access to it. And this is the Web URL that we're going to connect to from the Web application and then within the JavaScript weaken pace, the Web, Buehrle in there and let's right the JavaScript code. So, using fetch, we can connect to Europe. I gotta give this a variable name of this can be constant you, Earl, and make it a little bit bigger. So first, making a fetch request to the girl and then once we were speed response. Gonna have a function. I'm not going to use the arrow method, but you can use that as well to simplify what we're doing here. Once we've made the connection and we've got a response, we can return from the spots object as Jason, and we know that within the Web app were out putting it as Jason Format. And then once we've got the G zone back, that we can run a function and get the content back as data and then use that for now, let's put into the console just to make sure that we do have the content sitting as data in the console and going into the console. So Consul Button is down here at the bottom to see that within the object, we've got the content. And once we clear that and we make an update to it and that those errors were just because it's constantly running the JavaScript. So it looked like we were able to pick up the information properly, and you actually can't say that unless you log in so we just leave it as unstow you see within the object. We're returning back the data, and this is going to contain within data data. So this is the content coming from the spreadsheet from directly from the spreadsheets. And now it's conclude the challenge where we looked through the data data each method. Take the content that's contained in there and these air all the rows in the spreadsheet. So we're out putting them, and you can also output into the console. So depending on how you like toe work, bit by bit, clear the console. And this is that putting all of the rows of information that's from the spreadsheet, and I'm going to just use document right in order to create a diff and this story, we're gonna house the content that's being output from roll. And I'll use Jason String if I the contents of role, because this is still within an array format. And then let's close off the diff Now you can see that content is being output there. We don't have to use the console anymore. This is all content coming directly from the Web app. There's also the catch and fetch, so to catch any errors that we might be having. And this is another way to make sure that we are running the application properly and catching anything that might be going wrong. And this you can just put in the console. So since we clear that on this is the solution to the challenge to output it make a connection to the end point and then output that using JavaScript. 12. 11 Request parameters: a in this lesson, we're gonna be looking at the request parameters. So every time the user visits the up the program sends the up http get requests. The AB script runs a function do get e. And when the program sends the up request in both cases, the argument represents the event parameter that can contain information about the request parameters. So in this lesson, the challenge is to use the do get function and output as create HTML output the content contained within the request parameters, and then update the request parameters to have a value of message and use the contents contained within message and output it within the HTML. So we're gonna be simply passing in value, picking it up within, do get, and then out putting it in the visible area of the web app. You can pause the video here and I'll walk you through the solution coming up, set up the do get function and make sure that we're passing in the event information and we're just going to use the variable e in order to pick that up, setting up a default message. So, in case there is nothing sent through, we have, ah, default message off Hello and then returning that back using HTML service and just creating a simple HTML output from a string value. And then, for this string, we could have an H one tag. So opening and closing the H one tykes and let's play some content in here and this is gonna be whatever is contained within the message. Variable should try that in the death version of the up, and we get the value of Hello. So what we want to do as well is we want to pick up contents contained within message, and we'll have hello world as the message and we wanna output that to the page. So let's use the variable Haram's Take the Jason String if I to string. If I the event object that's being passed and you can see the value of what's being passed him in the log, let's run that you are all one more time and view in the logs. So we're picking up that object. So the message object in the first item there in the object value, and we can have a condition that will check for the value of message and this is going to correspond with our request parameter value their message and check to see if it exists within e parameters. And if it does exist that we know that we do have a value there, a message that's being sent in the euro. And this is where we're gonna update the variable message with the content of E parameters and selecting the message object. So whatever is contained within message and we only want to return back the first item that matches contained within the message go back in the URL, refresh it. And now we've got hello world or whatever is being passed in the request parameters is going to get out. Put on the page. 13. 12 Request parameters 2: all right, I have one more lesson about the request parameters, and that's gonna be a little bit more complex where we're picking up different parameters. So we've got an S, a P that we're gonna look for and use. Run some code depending on which values are present within the request parameters. And this is to indicate that you can use those requests parameters, apply some logic in the code and run chunks of code returning back some customized data, depending on what's within the request parameters. So there is an exercise for this lesson, and that's to try the girl with the request parameters and then try it with S and P and updating if there's no s value than just using the random number and using that within the content that's being returned back and also returning it back within a Jason format throughout putting the content in the Jason format this time. So, string. If I output as Jason as data, it's a little bit more complex. You composite view here tried it and I'll walk you through the solution coming up. Let's do an update on what we had in the last lesson. Instead of doing hello will create a variable called Styler. And within Google APP script, you can use JavaScript functionality such as math, random and math floor. So taking the math, random value, multiplying it by 10 sweeter number 2 10 and we're gonna add one to that. So we get a number from 1 to 10 that's gonna be returned back in Styler. So this is always running and we always getting this random value. That's if we don't have value for S we're just going to use the random value and because we're turning it back as a JavaScript off the object. Let's create a placeholder for that. Call it data and now we're ready to apply some logic. So checking to see if S is in e parameters. And if it is, then we're gonna run that block code, and we're going to do one for Pius. Well, because we're looking for those two different values and we're no longer looking for a message. So it's clear that one and we also don't need to signify that. So we're running two different chunks of code. One is for S one is for P and then constructing the output content as a variable getting used Jason and Jason stratified as we're gonna be out putting the value as an object. And we're also gonna be a putting it as Jason so setting the status to be 200 returning back a status code, something a little bit different. And then for the data, let's return back Whatever is contained within the data object. So that's the one that we built over here as a placeholder and returning back the content about to return it back as Jason content with some customized response, depending on what the request values are. So creating text outputs and we'll use the contents contained an output. And we're string defying up here so I don't have to string if I within the return and setting the mean type and that we're using the content service. I mean type and Jason as our mean type for the content that's being returned so far. Let's see what happens and what that would put looks like. So we don't have any content within data, and that's where we apply some logic. So let's update the value of Styler, and that will be whatever is contained within the E parameters and the value of S and returning back the item with the first index value. And we can also construct it a little bit differently. Let me move the data around, and then this will sit as Data Styler and then within the condition will update the value of Styler to be whatever was passed him. So depending on if we have a value for s, we already have a default set. If it's there, then we're gonna update it with whatever values being passed in. And if not, then we're gonna just use the default one, which is the random number on. We could do the same thing for P as well, where we're taking whatever values being passed and let's do data. So do a quick refresh. And now we should have some data Styler coming back in there. Let's try those values in the web, Buehrle Soapy is gonna be 100 and s is gonna be 50. We can also try it without us. You can see that now we're getting that random value for us. So Styler is randomized and this is all dependent on what's passed into the request. You, Earl 14. 13 Sheet Data as JSON API Conclusion: so just a quick run through of all of the content that we've covered earlier and objective of this course is to answer some questions that I regularly get from students. And that's how to use Google spreadsheets and output content contained within the spreadsheet into a Web AP I that can be connected to from various Web applications that you might have running. So we set out to create an end point and then also looked at the different parameters that we can set and pick up and apply some logic within the code. We can customize some of the output that's coming into that request or constructing that Jason object. I would put it in different with different conditions. And there's been a number of exercises that we've gone through, and you can mix and match some of the source code practice and get more familiar with what can be done within Google scripts using the Google Web at as an end point. If you have any questions or comments, I'm always happy to hear from you within the Q and A section. I've also included a handy PdF guide that's gonna have all of the links. Some code snippets and source code, as well as the exercises out mind that you can use alongside the lessons of this course to practice and get more familiar with the content being presented. Thanks again for taking the course. I'll see you in the next one.