Google Sheets - JSON API, Wep App - e.j1 p.1

in this video I'm gonna try to convert a spreadsheet to essentially a very basic JSON API now I have seen some comments that this doesn't work when you try to call from an external service like your website so I'm gonna start by doing a little trial to see if this is even something that we want to pursue and then we'll go forward from that point on so I'm gonna take this spreadsheet right now it has some data right now I'm not gonna be using this data yet I will just go to script editor and create a quick project here so I need some sort of do get function and this should return some service now let's just create a response for this Jason object maybe we'll put this in a write or something so let me just look in the docs for a second so we have HTML this is gonna be content service so let's go ahead and open that so content service basically we need to do create output and then provide the string content so this has to be in string format so what we'll do we'll just do json stringify so it basically makes a string out of this that and this will now be a string so we'll pass it to this so let's just call it something for our variable so now we'll take that so what else do we have here we have set mime type so let's see is there a JSON type there's JavaScript interesting so let me go back to the docs really quickly you so it seems like Jason is in here but it wasn't showing up so maybe we should access that through content service instead of just directly ah there it is that makes more sense okay Jason okay good so I'm gonna set the mime type for this so I think we could have done that right here let's actually just incorporate it here on top instead of doing this separate line and at this point let's just return this without actually setting it like that just like this I'm gonna just move these to a separate line so it looks nicer I'm glad I did that now I'm gonna remove that extra semicolon okay so that's gonna be my do get function so I'm gonna go ahead and publish this as a web app to see if we can actually access that JSON response so publish deploy as a web app and execute as me and anyone deploy that's our URL so now if I open this if everything goes well I should see that JSON response and I do so I get a write status cool this was the step of creating something for us to call now the trick here is to see if we can call this from an external service because right now I'm just going directly to their page and unloading that now let's try to call this from a different page so I'll try a local thing and then I'll also maybe put something on github and try to call it from there to see what happens so let's start by creating folder and I'm going to open this in vs code so pretty much just keep it simple just create an HTML file and a JavaScript file and go to my HTML exclamation tab to get some main content and link to that script file let's just do an element here with an ID so we can put something in here after hopefully this works so what I'm gonna do I'm gonna try to call that page get the response and put it inside of this div with an ID app so I'll go to this JavaScript and we'll use regular JavaScript fetch API that would be fetch and we should be calling that page that we just generated so I'm gonna go back to my app script get that link and pretty much just go back here and paste it right in there because this is very long let's actually do this in a different way let's just put this in a variable like this in quotes and then let's just put URL here so we can actually read and understand what's actually going on so we have this variable URL which is basically a link to that whatever it was generated for me that JSON now I'm gonna go ahead and fetch and then then after that we should get a response so initially that should be a JSON response I'll just take the D and then we'll just convert it to jason and then that should be the json response at this point let me just move these two separate lines so it looks nicer again and over here we're gonna get whatever we get back so again I'll call it D I'll do some brackets here for me to write some code so if everything goes well at this point this D should be the response we get from here so basically this array with a status cool so if I go back to this we should be able to basically just take that D and that's an array so if we get to that zero and inside of that zero we have that status which should say cool so let's just put this inside of that app so we'll take document.getelementbyid d and the ID is app and we want to make the text content of this equal to that now I don't want this to run as soon as I open the page so let's just go ahead and let's just change this to this new syntax that's fine so let's just put it in a function so that's what I'm gonna call this function I'm simply just gonna move all of this code right inside of here and then I'll create a button and when we click on it will basically just call this test J s function or GS or whatever it is so I'll go back to this and make a button right here that's just a sign of to that so when we click on that button we would like to run this function that we've just created save this I think that's pretty much it this should be enough for us to test to see if this works so if I open this I'm gonna open the live server my button I'm gonna click that button and we got cool so that works I thought it was not supposed to work but it works so far so good it will probably I guess not work if you don't use like a local server but go and open the file manually so if I go and open that folder see this try this and open that HTML file like this let's see what happens it still works I don't know how it doesn't work but apparently it works fine for me so far now I want to try another thing I want to put this thing that I just built here these two files pretty much on github and try to run it again to see if this works and if it does we should be in a good shape at least for do get we have to try to post too but for now we're gonna do this so I'm gonna go to github so this is going to be a public repository so I just need to basically upload those files here so I'm just gonna drag these two files over Changez we'll change this HTML file and make it index.html so it's the front page okay so now I'm just gonna make this public so like a website sort of public so we'll go to settings and make key top pages out of it so we'll take that master branch we just made and that should convert it to a public page like this so if I open this I don't know why that doesn't open let's just manually do our link index dot HTML and see if it opens up does so let's just do click me so that works no matter what I will have to look up and see why it's not loading that index.html right away but that kind of doesn't matter here the main thing here is we're calling Google sheets and we can use it here so that's what we wanted to prove here and so far so good now the second thing I want to do I want to make sure we can write to the spreadsheet so this was reading from it now can we also do a post request to write to the spreadsheet to do this I'll go back to my code and create a do post function and I haven't used this for ages but this should receive some sort of event we'll have to go check what this event has so let's just open the docs for do post in app scripts so this is the content of text of the body so I think that's what we need so let's just grab that and try to use it so that's coming from this e let's just save it in some variable so that will be the body we'll be sending to this so let's assume we're sending something like an object that says well let's not do it on this page let's do this on this page test and the object will be let's say somebody's name and we'll just add it as a row here something like that so we'll just simply basically say named Joe something like this so assuming that's what's being sent to us this is going to be that body I think that's gonna just return text so we need to convert it to a JSON object now and by the way sister's body is gonna be txt and it's gonna be Jason it's basically gonna be like this name Joe but once we run it through this parse for Jason we'll have a regular JavaScript object we should be able to work with so that point if I do body jason dot name i should be able to get joe and then we'll add it to our spreadsheet so let's just do spreadsheet app will basically get our active spreadsheet then we'll get our worksheet that I've just called test and to that worksheet we are going to append a row and that should basically be an array and since we're gonna have just one column it's just gonna be one thing so if I do body jason dot name at that point that should be whatever we sent here I think that's all we have to do so I'm just gonna run this new post to make sure we give all the permissions to access the spreadsheet app it's gonna give us an error but that's fine there's the error so that's fine so now we did all the permissions now I'm gonna just publish a new version now one thing you cannot do if you're doing this you have to publish your script you can't use the depth version for this because we're calling it from an external application that's probably what most people are having problem with they probably don't realize they have to deploy this so it's public because we're calling it from external service unless it's public there's no way to access this so again the rest is the same I'm gonna update this that should give us this which should be I believe the same link but if it's not I guess we'll find out a KF seems to be the same all right so now let's try to create something to write something to that spreadsheet now I'm not sure if we have to do a content type or not but I guess we'll find out in a second so what I'll do I'll just basically copy this rename this function will call it bought 10 - and we have to create that button - so I'm just gonna go here and copy save this so that's our button to to add a row now we need to call that button too and call this function right here now we're gonna call the same URL but this time we are going to actually do a post request so that means we're not gonna be able to do what we're doing in here so I'm gonna remove all of that for now and we should be able to pass the URL and I think the fetch also has the object I may have to look up their Doc's again let me open the docs see when you start working with bunch of things it's impossible to remember any of this so you just have to check the docs all the time or maybe I'm just stupid and I can't remember stuff who knows it's one or the other but anyways so that's see this so we call the URL and this is the object so the method post mode blah blah blah blah I'm just gonna copy all of this because that's gonna help me write from Mozilla documentation and go here and paste it right in here as an object so this time we're doing a post request to the same URL mode do we have to do that I don't know so let's just ignore this cache I guess no cache remove this headers application Jason will keep that redirect I think we have to do followed because app scripts does that weird thing where it kind of changes the URL on you but we'll see again I'm gonna try to not do that and the body we're sending to that so we need to send the data so right now I'm just going to create an object and we'll just make it the name is Olivia something like this I think I'm gonna take this and try and see what happens so I'm gonna take my HTML and open the live server by the way this goal life that's not by default available in vs code so you would have to install this extension live server so if you go under this extensions and search for live server see that's the one that shows up for me it's already installed so I don't have the install button but if you do you're gonna click install you may have to refresh or reopen vs code but once you do that you should have this button or you could right-click and do open live server it's kind of the same thing so you have to do this on HTML not JavaScript so I'm gonna go go live and click on add row so before I check my console let's go and check the spreadsheet ok so that's not good so let's go check the console and see what we get as a response request was blocked okay so the reading was okay so far but this was not okay so let's go back and change a couple of things click and close this okay so let's change the mode for this first let's actually just enable that and see what happens and save and go back and reload this let's click Add row same thing credential tried to do a myth so let's actually change this to no course option and try this again let me do the HTML file I'm gonna click Add row let's see what errors we got this time huh no errors let's go check our spreadsheet Wow there it is I'll leave use in here it's nice look at that now let's try this again add row that should add another Olevia we got it okay now I'm curious how much of this I didn't have to do do I have to do credentials so let's save that go back and try to we got to ol EVS now click Add row no errors this time to three Olevia so that works too okay so we don't have to do this we just have to make sure the mode is no course the method is post obviously the cache it's up to you but I'm gonna do no cache and the header should be application JSON I'm not sure if that's required we're gonna keep it anyways but just for testing purposes I want to try this without that to see what happens so I'm going to save this go back let me delete these click Add row waise so not required but we're gonna keep that part this I'm gonna get rid of and that's pretty much all we have to do so if we go and change this to John save this we should be able to now click add row and add John to our spreadsheet okay so now finally let me also put this in this key table and make sure this whole thing works so I'll just keep this simple copy paste the HTML the same for JavaScript now if I test this and it works we should be in a good shape I think I closed that page so I have to go find it Oh it works I don't have to do index.html anymore so apparently just takes a little bit of time so if I click on click me that should load cool if I click on let's delete this first if I go back if I click on add row we got John if I click on it again we're gonna get to John's good so this works so we proved that this works from different resources which is what I was worried about so we figured out how to call it to get the data using fetch API we also have this to post the data and it works just fine so with this in a separate video I guess we'll do something that will get a better JSON response from the spreadsheet working with some more realistic data and try to make something nice out of it but for this video that should do it thanks for watching please subscribe and I'll see in the next one

Channel: Get __it Done!

Views: 16,550

Keywords: Google Sheets, JSON, API, Wep, App

Id: VPI27L_fQC4

Length: 23min 4sec (1384 seconds)

Published: Tue Jun 16 2020