Google Sheets API - JavaScript NodeJS Tutorial

alright so in this video I'm gonna try to go over some main concepts of Google sheets API mainly I'll be using node.js to interact with it so this will be specifically nodejs Google sheets API tutorial now if you want to be following along you gonna need a few pieces of software installed one of those is no js' so you can install it by going to node.js org and you can just download the recommended or latest version both should be fine and install it on your machine should be available both for Windows or Mac the other piece of software I'm gonna use is a text editor for that I'm gonna use Visual Studio code and you can find that at Cody's real studio com that's the website you can download it and install it again available both Windows Mac you can install it on both once all of that done will you need a Google account with Google sheets so basically I have this spreadsheet it's an empty spreadsheet there's nothing on it right now as you can see so on my machine I have made a folder called sheets - API - project it's an empty folder as you can see now I'm gonna make this folder my project folder in my Visual Studio code so I'm gonna go to Visual Studio code software click on open folder or you can go file open it's the same thing I'm gonna go open folder and I'm gonna basically just navigate to that folder so he'd open it should open that folder which is empty right now so if you need a little more help with installation of nodejs and Visual Studio code I have a tutorial with Excel and nodejs and I go a little more in depth with the installation and all of it I'm not gonna repeat all of that again please what that tutorial if you need more help with this installation of the software now once this is all done I'm gonna open my integrated terminal and you can go on top here I'm not sure if you can see that but on top there's this menu and you can basically go under view and open the terminal and that should open integrated terminal within Visual Studio code now the first thing we need to do since this is gonna be a no js' project we're gonna basically just initialize and node.js application here really quickly you know we can do that by Roenick npm space in it and it's gonna basically ask us for the package name I'm just gonna leave it default whatever it is version description entry point you know anything is fine I'm gonna change it to main dot J s and enter enter enter enter enter basically yes so that should basically create our package JSON file that's fine now once we have this the next thing is to install Google sheets API package for no js' so for that I'm gonna move to sheets API documentation see there is the command npm install and this so I'm gonna just copy and paste that that's fine go back to visual studio code paste it in my integrated terminal and hit enter that should run this installation and install the package we need there are rappers available for Google sheets that are supposed to make it easier to do the hole calls and everything else I'm not gonna use any of those wrappers I'm just gonna use the original package from Google the reason for that first of all I have some pretty bad experience with some of these packages because as they change to different versions of API they discontinue a lot of these packages and they supported anymore so for that reason it's probably best to go to the source it's gonna be a little harder to start maybe but then it's less headaches and then number two I don't need an extra package so this works for me so we have this now I'm gonna create a file which would be a JavaScript file to write our scripts I'm gonna create a new file here by rolling over here new and the file is gonna be mein Jas which is if you remember that was the entry point I did good that's our JavaScript file we have it ready now we can start coding the first thing we need to do in this application we need to make sure we import the package we just installed so for that I'm gonna do Const goog oh and that is gonna be equal to require and the name of the package and for that is gonna be Google API sand see how it shows up there good so this Const is basically you can think about it as VAR if you're not familiar with this so basically we're declaring a variable like that in your JavaScript there is new ways to declare variables one of those is Const so basically that means it's a constant variable it cannot be changed so I'm gonna leave it at that for now and move on to doing some extra setup so the next piece of setup actually I'm gonna put the links here so you can hopefully see so the things I did so far I went to no js' dot org that's one of the things we've used here the second thing I did is visual studio code and again you can use any text editor honestly I just like this one and finally the last thing you need to do is is goto by the way the link for this documentation let me put that there too and finally the last piece you're gonna need is going to Google's developers console so you don't need to type this whole thing here the way I have it on top where you need to go I'm just gonna copy the link and put it on a screen so you can see where you need to go so you need to go to this console dot developers.google.com and that will take you to your developers console now in this developers console you need to create a project to basically give you some keys so you can connect to Google sheets API I'm not sure how it shows up the first time you open this if you've never done it it would probably look very similar to this so you need to find this option to create a new project which is what I'm gonna do I'm gonna click there you want to name this project with some unique name I'm gonna call it nodejs GS API test so once I have that I go ahead and click create apparently there was too many characters so I'm gonna remove click create that's gonna create that project it may sometimes take a little bit in the meantime when you actually look at some of this examples that Google documentation provides all this examples usually work with this system of basically connecting and authenticating yourself by using the regular method when the user is gonna grant permission for a spreadsheet and that's not really what I'm looking for in this I just want to connect to my own spreadsheet make some changes to it pull some data and add some information to it it doesn't seem like there is a lot of documentation maybe I'm not good at finding it now by this time hopefully this is done so see it finished that so I'm gonna open that click on that project it should now show that project name here on top now in this project we to enable the api's we're gonna need so I'm gonna scroll down see this Explorer and enable api's now keep in mind this UI for this changes a lot so it might not be here next time when you try this so try to find whether you enable the API so I'm gonna click enable the enable API and services and then it's gonna ask me which API I would like to enable so you can scroll through this whole thing see there it is Google sheets API if you couldn't find it you can just search here like Google sheets pretty sure that will show up there this then you click on Google sheets API and here you're gonna click enable so that will enable Google sheets API for this project enabling is not enough you have to create credentials so you can actually connect and make some changes so I'm gonna do that create credentials this is what's gonna show up so we're gonna choose the API we're using so Google sheets API where are we gonna be calling this from so for us it's gonna be web server which is like nodejs that example that's good so we're gonna be doing application data belonging to our own application finally are we planning to use it for this No so that's good I'm gonna click on this now it's gonna ask us for the service account so this is gonna basically create a service account and what a service account is basically it's just some sort of like email account that it creates that you give permissions to do some things on your spreadsheets or some other applications in this case is gonna be our spreadsheet so we're gonna create a name for this service account any name you just type something so I'm gonna do serve this man 11 and then the key type that we're gonna download you're gonna download the key with your credentials it's gonna be JSON that's fine I'm gonna go ahead and click tinu oh by the way we have to select the role so when you select the role for this project you're gonna decide what type of permissions you're gonna need for this script so if you just want to breathe information then it's gonna be basically a viewer if we want to be able to change data we're gonna do edit and then if you want to have full access it's gonna be owner for this I think editors should be fine so I'm gonna do editor that's good enough I'm gonna click continue and what that's gonna do it's gonna download a JSON file see this shows up it says this is the file do you want to open it I'm just gonna click save file that will download the JSON file with all the credentials so once that file is downloaded you want to go ahead and move that file to the same folder where your project is located so in my case see this is Visual Studio code so you want to move it to the same folder for this project we had so that's exactly what I'm gonna do this is that folder see now we have some files in there I'm gonna go grab that from my downloads and copy that over so there's that file with a crazy name I'm gonna actually rename that call it keys now this file contains all the credentials for you to be able to connect and do changes so you want to make sure that this file is safe you don't share the information in this file or the file itself so if you're doing some sort of web application make sure that this is stored in a safe location so people cannot access the file otherwise they're gonna be able to see all the credentials and be able to modify and read your spreadsheets too so for me I copied that over there's that Keys dot JSON file so now we need to put that information to our application and the way I'm gonna do it I'm gonna create another constant I'm gonna call it keys that's just a variable name and again I'm gonna require so I need to require this file that's located in the same folder as where my application is this one so for that I'm gonna dot that's the current folder and this actually needs to be s string so dot that's my current folder and in the current folder I want not the folder over here I want the file so the file is keys dot JSON now once we have our keys what I'm going to do I'm going to create client it's a good enough name and I'm gonna create a new so that's now gonna be coming from this library right here so Google dot and we're gonna get under off and dot and here there is this C just JWT which I believe stands for JSON web tokens so we're gonna do that so to create this JSON web token see if I open parentheses it gives me email the string then the key file string then the key string and then scopes which is like an array right here so the first thing is the email to make this easier I'm just gonna send this to a new line like this let's do semicolon to finish this off now all of that is gonna be coming mainly from this keys file and if you open that Keys file you'll see what your credentials are and all of that information for me I'm gonna keep it like I said safe I don't want you to see it but you should be able to open your JSON file and see what's going on in there so out of that keys if I do keys dot client email is one of those comma the second thing so let me just go back so you can see see there there was the email the second thing is the key file string for that we just not gonna pass anything so I'm gonna say no so the third thing is the key itself that's the third argument and then finally the Scopes and the rest we're gonna skip so the third thing was the key so the key is the private key so you do key dot and private key which is gonna be gang coming from that JSON file and finally the last thing is the Scopes you want and that's gonna be an array of scopes and basically this is what things do you need permissions for I'm actually gonna send all this stuff to a new line to make this a little prettier like this and the Scopes we're gonna need in my case I'm gonna read and write two sheets let me actually get you the documentation for scopes so here this so that scopes I'm gonna copy that link and add it to this so hopefully you'll be able to see that in a larger font so that's the link where I go for scopes back to it so we can access a lot of different Google stuff for me I want Google sheets so I'll just quickly search here there it is Google sheets API and see the Scopes we can require we can require access to Google Drive I don't want to actually manage and delete and edit the files in this case and I don't want to do this too so I don't want to download the drive files what I do want to do I want to see edit create delete your spreadsheets that I do so that's one so if you only just want to read data from the spreadsheet but you don't want to write anything back to the spreadsheet this would be enough so for me I want to read and write so that would be what I need so I'm gonna copy that go back and for scopes it's gonna be basically that as strengths so I'm gonna pass that to this array safe so that's gonna create this JSON web token or whatever it's called which is great so now after we create that token we should be able to try to connect and see if we can actually establish a successful connection so I'm gonna scroll down a little bit I'm gonna create that variable client we just made and on this there is gonna be C this method auto rice so that's good I'm gonna open that and that is going to basically accept a callback function so the function is gonna be this and semicolon to finish this line send this now this function is gonna accept a couple of arguments one is the error the other one is the actual tokens you're gonna get so what happens when you connect to Google sheets with your credentials what you get is a token that gives you access and usually it's like a temporary for some period of time and then you have to renew it to get it to work again so for me in this particular case I don't really want to use these tokens but if you wanted to get those tokens you could maybe I'll live it here but I'm not gonna use it so that's fine first we want to make sure there are no errors so if I check if there are any errors so first we're gonna do an if statement to just make sure there are no errors in this so if there's an error we'll just log the error so constant log is app scripts equivalent to log or log it's just much better than log or log but it doesn't matter so that's console log and then I want to just return out of this return basically we'll just get us out of this function at that point and not run the rest of this so that should check if there's an error we just log the error we get out of it otherwise we want to do what we want to do so basically the else statement is gonna be if we're hopefully successfully connected let's just check this so I'm gonna do console log and I'm gonna say connect it so if we that means that our connection was successful so I'm gonna run this so to run this we go to our terminal again and the application we have is called mange s so to run that I'm gonna do node space and then the name of the file mange a s so I'm gonna hit enter and I got an error so key is not defined so let's go see here where's the key oh see this should have been keys not key which is this thing right here okay hopefully I didn't make any other errors in my typing so I'm gonna run this again and see it says connected that's good news after we connect to it we should be able to do some actions so for example read data from our spreadsheet so for that I'm gonna do it in a separate function and to basically have less callback stuff I'm gonna use an async function now if you don't know what async function is just don't worry about it and go with this so otherwise that's more benefit if you don't understand what async functions are so we do async function I'm gonna call this GS run and for this I'm gonna do this so this function needs this credentials to be able to run so once we are able to establish this connection through this client right here and basically just make sure we're connected we need that client to be able to run all our Google sheets commands so that means we need to pass that to this so I'm gonna say CL short for client so in here we're gonna do all the steps that we're gonna do with our spreadsheet so the first thing I'm gonna do I'm gonna create a constant I'm gonna create this constant and call it GS that will be our actually GS API would be great so I'm gonna do and for this we're gonna again go back to that google variable and inside of this there is sheets and this is where we're actually connecting to Google sheets API so we need to pass an object here and that object needs to have a couple of things one thing it needs to have is the version of API we're planning to use so the version we're going to be using is version 4 so I'm gonna do v4 as the version and the second parameter is gonna be the client that we were able to connect so that's gonna be the off so that's gonna be actually this CL this client will be passing to this function and the way we'll be passing that to that function is here so once we're connected after we're connected here we'll be calling that function GS run right there and we'll be passing the client from here to here so that will pass that client over here and that will be here as well and that will hopefully establish the connection to our Google sheets API version 4 once we have established a connection we should be able to now run some commands to get some information from our spreadsheet so I'm gonna create another constant here and call this one opt for options I suppose it's good enough so that will be an object and this object needs to have some pieces of information I'm gonna do like this because it's gonna be quite a few of them so what is the spreadsheet ID so the spreadsheet ID will be connecting to so I'm gonna go back to here go to my spreadsheet find the ID so the idea is gonna be everything after this D slash and going all the way until the slash here so I'm gonna copy that ID for my spreadsheet go back here and paste comma the next argument I'm need I want the range from which I'm getting this information and that's a string range in a1 notation so first let me actually add some information here so now if I want to get information from here the range is from a1 through B 5 and that range is coming from data tab so a1 through b5 let's remember that go back to this so the range is gonna be data tab exclamation sign just like you would do in Excel or Google sheets and a1 through b5 we're gonna see if that's enough to get our information maybe we need to pass more parameters here but that's gonna be the options I'm gonna need the spreadsheet ID in the range now to be able to get all of this now I need to use this connection to my Google sheets API so I'm gonna go GS API dot and here see we have the spreadsheet and in the spreadsheet I want to get values from that spreadsheet not sheets I'm gonna get values and that has all of these C different methods so we can get values we can update values we can do batch get values batch update so bunch of different things we can append we want to in this case get and that get is gonna be accepting that parameters which is this options so I'm gonna pass it right there so that's gonna return something now when you make calls to an API because it's an external service we don't know how long it's gonna get us to get that information back because it's probably gonna take some time to make the request it may be not too long it could be like a little many seconds but still it's gonna take some time so we need to wait until we get that information back and this was the whole reason of using an async function because if I use an async function I can have the new notation of doing a wait and wait until it's done and that's exactly what I'm gonna do I'm gonna wait until that's done and when it's done I'm gonna store that information that comes from it in a variable I'm gonna call it var data and as a matter of fact instead of var I'm going to use the new way of Claire is let and I'm not going to talk about let and differences between var and let he can't explore it on your own just think about this is the same as var okay so that will basically just wait until this coal is done and we get the information back and then it's gonna come back in this data variable so at that point let me just cancel log that to see what's in there do we even get anything back or not so I'm gonna cancel lock the data variable right here save this and run go back here up arrow key to type the same node space main J s which is the name of the file hit run I think something went wrong here so let's go and take a look so it says unhandled - warning missing required parameters spreadsheet ID so spreadsheet idea apparently should have been with an uppercase I okay save that I'm gonna try this again go back and run and hopefully this time it will be better okay something is still wrong I'm gonna go back and see so it says the color does not have permission and the reason for that is when we made that jason see it made us this email address if you remember and i should be able to get to it if i go back here managed service accounts see there is that email service man 11 and that whole crazy thing after that so i'm gonna copy that email and the reason I'm gonna need that I need that email because I have to give permission to that email to read and access and add it this spreadsheet so for that I'm gonna go to the spreadsheet and share that spreadsheet and I'm gonna share it with that email which is that service account so I'm gonna do this and if I want it to be able to edited I will also grant it can't edit permission send that should give permission to that account to be able to read and edit this file so now let's go back and rerun exactly the same thing see what we get this time so I'm gonna go back here op arrow and run and this time it seems like we didn't get an error so status 200 means no error okay so if we scroll up we should be able to get some things we got whatever this is this is the basically it gives us some information about this and then we get the range from which we're getting this data and then it says values to get to that values we need to get to this data and that data I think is on a top level of this object so if we do data and apparently we call this data and that is data - which is kind of gonna be confusing but it's still gonna be data dot values so let's do that and save and rerun this to see what we get see what I got as a result of this log I get the information from my spreadsheet now I probably don't want to get this first thing over here I want to start from a2 through b5 so I'm gonna go back to this and change my range a to through b5 save it and rerun this script and now we should get an array of our data from our spreadsheet so what I want to do now I want to write some information back to that spreadsheet so maybe I'll take all of this and change it up a little bit and then put it back in a spreadsheet or maybe not put it back over these values but put it back someplace over here let's do that so I'm gonna go back here first of all this is gonna be an array so let me just save it in a variable so we don't have to deal with this 15 dots so I'm gonna say let data array equal to that so that data array now it's basically in a race so because it's an array we can use all array methods on that one of those would be map array method and this map method will accept a callback function and that function for each time it's gonna be a new row now the map method is gonna return a new array so I'm gonna say let new data array equal to this and I'm not gonna do anything meaningful basically this is supposed to just show you how we deal with API not how we deal with data so now we're gonna take that array and we're gonna change it up so I'm gonna just simply take that and maybe I'll add like third concatenated column in the end which will be the ID - the name so for that I will basically have to return something so what I'm gonna return is basically whatever the current array is which is going to be every time it's gonna be one of these so it's gonna be first time it's gonna be one at Ana right and then to dad I'm gonna concatenate actually I'm just gonna push because that's going to make it easier because I need just one so I'm gonna push to add to the end or 0 which will be the value from this first column and then I'm gonna add - and then I'm gonna add r1 which is the name that will be our push now this our push is not gonna return the array so I need to do an extra step here so I'm gonna do let I'm gonna call this temp data equal to this actually we don't need to create that variable because we can just push that to that array and then we can just return the are that should just work so just to make sure I did this correctly I'm just gonna cancel lock that new data array to make sure we did the right conversion there save that go back and run this script yep so that just made the third column with our concatenated values so once I have this third column I don't want to console.log anymore I just want to put that array back to the spreadsheet and I could have done it over here to override it but I'm just gonna put it on the side over here someplace for that we're gonna go back to this now if we're trying to communicate with our spreadsheet again we have to do through the API and this time we're going to be updating information and for updates the objects you're gonna have to pass are a little more complicated than you do forgets so forgets we just do spreadsheet ID and arrange we're good to go now for updates there's gonna be a few more pieces we need to add in addition to that but we still have to do those too so I'm just gonna copy this paste that now I'm gonna call it update options and we still need the spreadsheet ID we need to provide the range now the nice thing about this API is that if I want to put my data here in the spreadsheet I don't have to say II to Drew G something and figure out how many colors and bros all you have to do is just provide the starting cell and it will just figure out how to put your array here so I'm just gonna say e to and that's it so data spreadsheet worksheet is data and E to cell so that is fine it's the same spreadsheet be if you were trying to do this on another spreadsheet you want to make sure you give that spreadsheet and share that with that email account too but for me I'm gonna do the range the ID but now we need a couple of other things one you have this parameter that is required which is called value think input options option I hope I remember that right but we'll find out when it gives us the error so there are a few different ways we can interpret that information so the most common way you're gonna do this is basically user entered so basically I think there's Row is another version of this but basically we just want to interpret the values we're entering as if somebody was typing that in the spreadsheet like this and then finally the last thing which is an important one is the response so this response in its turn it's gonna be an object again and one of those things in that object is gonna be the values which is gonna be array you're passing to that so the array we're passing to that is gonna be this new data array so that's gonna be the options now once we got these options together we should be able now to call our API and enter that data and to call our API we're gonna go back to this Jas API thing dot and spreadsheets dot and again values is what we're changing dot and this time instead of get I'm gonna do an update and for this update we're gonna pass the parameters which is gonna be this update options from here now again this is an API call every time you do an API call it's gonna take time for it to do something and get your results back so that means we have to do wait for this and we're gonna create a variable we're gonna save let so I'm gonna say response maybe we call dressed short equals to that and then we'll probably just cancel lock the response to see what response we get but honestly if this works then it should be updating our data or ready so that response will just verify that it work or it didn't work so we'll save it so now if I go back and rerun this and we got an error again so it says invalid JSON response values didn't type a response right did I tear this response so let's go back and rerun this hopefully I didn't miss type anything else well maybe I did so let's go check apparently it's not response again I'll have to go check what it's supposed to be give it one second so let's see reference so that was a date there we go and it's update we can do value input options and request body which is gonna be a value range and that's gonna be a resource okay so go change that resource save this go back and bring around that okay so we got okay response that means we should be successful with our coal I'm gonna go back and take a look at our spreadsheet so here we go so we're able to write that information back to our spreadsheet by doing that now one thing I want to mention here which is kind of weird see how I mapped through this data by the position in my array now let me show you something so the way that Google this API returns information so let's say one of these things was blank there's a good chance that if I run this my code is gonna give me an error well apparently it did not so let's actually go check what we got so oh so we got undefined see so let me actually clear that and Rhonda that will make probably a little more sense for you or it will make sense once I explain what's going on here but he or she basically did three and then in an Excel did three - undefined and what's going on here is for whatever weird reason they decided that when you get data that has blanks so let's just console.log this data array I don't want to lock this right now I don't think we're logging anything else that should be good enough I'm gonna save it and run so see when I get this back what I get is one Anna to Joe and three and there is no blank so it doesn't give the blank as one of those and for that reason what I'm trying to access that it's basically it's giving me undefined because I'm trying to access that position in this array that doesn't exist by doing this r1 for that reason because you're not gonna be able to probably control how people enter this and whether you have blanks or not you will have to add some handling one of the ways you could just make sure that you basically just fill up this blanks and make sure that it's two columns all the time even though it's not two columns here when you get this return data so the way to convert our data to two column sort of data we can map our data again so I'm gonna say that data array and I'm gonna map that and I'm gonna create another array out of it and this array will hopefully have all those blanks that I'm gonna need and I'm gonna save it back to the same data array so that should be fine and then we'll just console.log that after this transformation to make sure that what we did actually worked so for each row I suppose that's what we'll call this or we're gonna take that and we need to basically make sure that it's two columns long in this case so as long as it's not two columns we need to keep adding pushing spaces in the end so the way we're gonna do that we're gonna say while it will create a little while loop here and I'm gonna say while our dot length which is going to be the number of things in here is less than in this case too because I want to column lay out while it's less than two we're gonna take the r and we're gonna push blank so you can do a null but in sheets usually it treats like empty this double quotes as the space or blank so I'm gonna push that to it so I'm saying while the number of things we have in here is less than two like in here it's gonna be just one I'm gonna say push the thing to the ends so there's gonna be adding another element a blank and if both of them are blank then it's gonna check and it's gonna be basically zero and it's gonna take that and add once and then it's gonna check it's gonna be one so then it's gonna do it again until it gets to blanks so this way while we'll keep doing that until it gets enough spaces to it as two columns so I'm gonna push that and then I'm finally gonna just return that final or so let's save this and I'm gonna rerun it to see what I get see now that adds that blank here which means for the rest of the script save this let's go back and look at our spreadsheet see now it does this blank where it was supposed to be and then 3 - and the blank goes in here so we have less of this weird effect so basically just be careful with that because that's the way it's gonna bring over now keep in mind that if you have like three columns data like this and you have blank in a metal it will bring over this blank it's only just this trailing blanks in the end that it's basically just gonna skip and I think that covers pretty much what we need to cover hopefully that was helpful thanks for watching please subscribe and I'll see you next video

Channel: Learn Google Spreadsheets

Views: 79,420

Keywords: google sheets, api, node, js, tutorial, javascript

Id: MiPpQzW_ya0

Length: 45min 29sec (2729 seconds)

Published: Fri May 24 2019