Control Google Sheets with Node.js / JavaScript (2021)

okay hey so i was making this website where you can essentially request videos for me to make which hint is at videorequest.jameshow.com but essentially i wanted it so it would send all this data to a google sheet and well i didn't actually know how to do this so i looked into the documentation and realized that it was actually kind of complicated and it did take me a little bit time to figure it all out so i had an idea why do i make a video on how to make a node.js application that links in with google sheets so here you go enjoy all right so before we can even start writing any code we first of all have to get some credentials from the google api console so essentially when we try and read and write from our google sheet we google knows it's us right so we have some api keys and so on this is really simple to do so you just need to go to console.cloud.google.com the link is in the description and then you should see a page like this you may already have used this before or have a project but either way you just need to go to the top here next to the logo it may have a project name but mine just says select a project i'm going to click that and then i'm going to press new project in the top right hand corner now i can give this project a name so this is just going to be i'm just going to call this youtube video and uh i can leave everything else as default if you're obviously creating this project for real just give it a you know a real project name so that you can reference back to it in the future press create now in a second you should see your project appear like this if you don't press select projects in this notifications panel that comes up and then now what we need to do is enable the google sheets api within this project we created so to do this i'm going to press on the menu in the top left hand corner here and then press apis and services and inside of here at the top i should get a button that says enable apis and services and then a search bar and there's all kinds of services you can build into here within the google api console but we're going to be using google sheets so i'm just going to go ahead and search for sheets here and then the google sheets api should appear i'm going to click that and then press enable once you see this page and it's successfully enabled we now need to go ahead and create those credentials so on the left hand side here there should be a button that says credentials if you go ahead and click that you now have this page here so we need to go ahead and do here is create some credentials we get a couple of options but what we need is a service account because we're going to be hooking into our own google sheets and not asking the user to log in so click service account and then go ahead and give that service account a name so you can close anything you want i'm just going to call it the same as my project which is youtube video and then you see it's created an email address here now this is going to be really really useful which you'll see in a second but i'm just going to go ahead and click create and then just press continue i'm not going to change any of this stuff and press done and now what we see here is this page and we have this email address here now what i'm going to do is i'm just going to go ahead and copy this email address because i'm going to use it in a second so copy like that and then i'm just going to go ahead and click on it so we can go ahead and download the credentials so once we clicked on it i'm going to scroll to the bottom of this page and then under keys here i'm going to press add key and create new key and then it should be json here press create it's going to go ahead and download a json file which we're going to go ahead and include in our node.js application later so it's going to press close here now with that email address you copied what we can do is we can essentially share our google sheet with that email address and then that email address will have all the permissions it needs to read and write from our google sheet so what i've gone ahead and done here is prepared a little google sheet and what i've done is i've created four rows here for our little video request demo application this is so we can see what it's like to read from our google sheet but of course our real application here is just going to be writing to it rather than reading but i wanted to have some example information in here you can go ahead and create a new google sheet and put some dummy information in it or whatever you want to do or just copy this on the top right hand corner i'm going to go ahead and share this google sheet with that email address so with that email address i copied i'm just going to paste it in and click whatever and then on the right hand side here make sure you've selected editor this is the default option that came up for me here but make sure it says editor and then uncheck this notify people option and then press share and you're all done now this google sheet is shared with that email address which will give our node.js application full authorization to read and write from our google sheet all right then so let's get to coding so i've got a finder window open here i'm just going to right click create a new folder and i'm going to call this video request since that's what we're eventually going to be making and then i'm just going to go ahead and open this up in visual studio code all right then here's my project open in visual studio code what i'm first of all going to do before i start anything else is actually take my credentials file from earlier and just drag it in and if you look into it you'll see it's just got a whole bunch of private keys and stuff so make sure you don't share this with anybody because it'll give them access to your google account just to e for ease here i'm going to go ahead and right click and rename this and i'm just going to call it credentials.json because we have to reference this and yeah they give it weird names so there we go we have that now i'm going to go ahead and open up the integrated terminal um you can of course use the well you know the terminal on your operating system but vs code has a nice built-in one and what i'm going to go ahead and do is i'm going to create a new npm project here so we can start you know using node.js so i'm going to do mpm init and then i'm just going gonna press enter like a hundred times and that's just gonna go ahead and create a package.json here so we can use uh npm and then i'm gonna install some dependencies this is gonna be a web server so i'm gonna go ahead and install express so npm install space express i'm going to install ejs for my front end which we'll do later on and of course also the google api so google apis so npm install express space ejs space google apis and then press enter now that's all done we just need to do one more thing which is to install nodemon which is what we're going to do so we can run node index.js every single time we make changes so i'm going to do mpm install dash capital d and nodemon all these commands will be in the description okay perfect so now that we've done that all we need to do is go ahead and create our index.js file in our root so i'm just going to right click here new file and call that index.js perfect so let's create a really basic express boilerplate for our web server so const express equals require express and let's also go ahead and import our google api so const and then it's a named export of google from require google apis like that let's create our app our express app and let's go ahead and create just a single root endpoint here of forward slash with a request and response and let's just res.send hello world this is not an express tutorial but express is very easy there's nothing we're going to do it's particularly complicated here so if you're not familiar you can just go along and have a look at express later but i won't be going through the specifics of express or node as a matter of fact and all we need to do now is just go ahead and create you know listen on a port so i'm gonna do app dot listen and then let's just do it on one three three seven and create a call back when the server is running running on one three three seven and save and now in our terminal we can go ahead and run nodemon index.js and there we've created our web server so i'm gonna go over to a browser and refresh and what i should see is a hello world blank page here okay so let's start messing with the google sheets api so i'm going to go back to visual studio code and in our root endpoint here i'm going to go ahead and start writing our code so we can interact with the google sheets api so the first thing we need to do is authenticate with it so i'm going to create an auth object here const auth and then create a new google auth object which is what we have to do to authenticate with the api so it's a new google which is what we imported dot auth dot google auth like this and that's a function and takes in an object and this object is going to take our key file and the apis that we want to use so the key file is key file and then the name of the key file in your root so i called mine credentials.json credentials.json and the second one is the scope we want to use so this is a kind of annoying i'll have this either on github or in the description below but it is essentially a url to the google sheets api so like that so you can go ahead and copy and paste that from the description or from github where this will actually also be so that's that so now we need to create a uh a client object that we can use that we can pass into all of our requests so we're going to use this auth object in that so i'm going to do here is do const client and then we're going to await auth.getclient and internally here google's going to essentially make a request and to their oauth and then give you a client object back which you can use in your requests so this is why it's in a weight so since this is the weight we need to make this function async like that i was actually filming this before and i i was trying to figure out why it wasn't working i'd literally just forgotten the async here so there we go await get client and then now what we can do is we can go ahead and create an instance of the google sheets api so it's kind of three layers here um so this is get our client so create client instance for auth now we need to create an instance of google sheets api so we do this by doing const google well you can go anything you want so i'm going to call it google sheets and then to access it it's google or sorry it's yeah no actually it's google dot sheets and then it takes an object of a version and we're on version v4 this is for v4 and then the authentication that we created which was the client so auth client like that so now we have a google sheets object that we can use to access all of our information so it's perfect so let's go ahead and do our first test here so let's go ahead and get all the metadata about our spreadsheet so let's do get metadata about spreadsheet to do this we're of course going to assign it to a variable so i'm going to call it metadata and then the request is await this object we created google sheets and then dot spreadsheets which is the same for all requests and then it's just going to be dot get for this one and this dot get takes an object now in this object we have a couple of things we need to provide the first one is the original auth object this one up here we called auth and the key is called all so you can just type auth once and this is the same as writing auth auth and the second one is the spreadsheet id of the spreadsheet we want to actually look at here so where do you get a spreadsheet id from so if you go back to your browser and to your google sheet in the url here you'll see there's a really long random string here after the slash d and before slash edit this is your spreadsheet id you want to take that and copy it and what i'm going to do is i'm going to actually put it in a variable because we're going to use it a couple of times so i'm just going to make one up here called spreadsheet id and then oops paste in my spreadsheet id just like that and what i can do down here is the key is called spreadsheet id oh let's let's match ours so give our spreadsheet um i'll give our variable sorry the same name and we can just write spreadsheet id and that's it let's go ahead and output this metadata variable here and we can see what we get so i'm going to save that go to google chrome and refresh our page and there we go there's all of our metadata about our spreadsheet we can see it's on dot data so i'm just going to go ahead and set that to dot data so we can see a little better refresh and there we go so we can see properties like the title locale all kinds of formatting stuff and if we scroll down we can actually see the list of sheets that we have so of course google sheets has multiple sheets within it the default one is called sheet1 and there's some other information here about row counts or other stuff so this is interesting so what we want to do then is we want to go ahead and target sheet1 so we can actually look at what rows we have inside we of course want to get back these rows so what i'm going to go ahead and do is i'm going to make a new request here to read rows from spreadsheet and to do this you need to go ahead and do well first of all we're going to make a variable i'm going to call this get rows and we're going to go ahead and await google sheets again and it's dot spreadsheets but this time dot values because we want to talk about the values the actual data within the spreadsheet and it's dot values.get because we want to read from them and of course it uh takes an object and the exact same as before it takes the auth it takes the spreadsheet id but this time it's going to go ahead and take a range because we're trying to read from a particular sheet bear in mind there can be many sheets within a you know entire spreadsheet here so we want to go ahead and do is we want to target the sheet1 name or whatever you've actually called it you could have renamed this and this is done by setting the range so i want to set the range to sheet 1 and it has to be named here in quotes exactly how it's written so if you whatever you've written here exactly how it's written exactly how you saw it in the metadata in fact in this title you need to write it in your range now you can actually go ahead and provide um you know a column range as well but if you just provide sheet one and save and then of course actually let's output the result so get rows it's going to be getrows.data now in our res.send save that refresh the page and there we go there's all of our values in our spreadsheet so that's it that is how you get rows in a spreadsheet now you can actually go ahead and i said filter it by columns so maybe i only want the first column i don't want you know the the first column and the second column i can go ahead and filter this by doing sheet 1 with an exclamation mark here and then provide a column range so this could be a to a or you know a to c whatever you want to do i'm just going to do a to a like that save that refresh and there we go we just have the first column so great so that is reading from the sheet so how we're going to actually write to the sheet to do this it's really really simple so we're just going to do another request like we did before so we're going to write rows or one or multiple rows in fact to spreadsheet and the syntax looks like this so we don't necessarily have to have an output here um so we're just going to go ahead and straight go straight into our request so we're going to await google sheets as before spreadsheet dot spreadsheets as before dot values as before but this time we're going to go ahead and use the function append like that now pen takes an object as you would expect and inside this object we need very much the same stuff so the auth the spreadsheet id the range of course we want to say where we want to write to so we want to write to sheet 1 again and let's also specify a range we want to go ahead so we want to put our exclamation mark and we only want to write in rows a and b so i'm going to say a to b a column b like that perfect so now the there's actually two options so whenever you're writing we want to set the actual information that we're writing to the spreadsheet but also the way we want google sheets to pass that information which can be very useful so the this the first so the first of these two keys is the first one is value input option it's actually required and there's only two useful things to set this to the first one is raw which means whatever values you input into the spreadsheet are past raw you don't change them anyway the other one is user underscore entered in capitals now what this does is it attempts to pass whatever information you've sent in so if you send a date it will try and pass it as a date if you send a number it'll try and pass it as a number and so on so i'm going to leave it as that which is pretty useful i would say and then of course the last one is the actual information that we want to send through and this is called resource now resource is an object and in it you have to specify the values that you want to insert in and this values is an array because you can actually send multiple rows at once and for each row you need to specify an array again so values is equal to array because you could send oops because you could send multiple rows and then each row is specified with an array in itself and this row array is just you know from left to right column values so if we have our request spreadsheet which if we remember looks like this we in our first column we have our request itself and our second we have the name our first value in our array is going to be whatever we want to go here and our second value is here so i'm going to make a dummy request here make a tutorial and the second one is going to be the name of who requested it so just you know test i don't know and pretty is clean this up for me but bear in mind it's a dual array not just one array we save that and if we go to our browser and refresh this is actually showing the same thing but we've actually now made a push to our google sheets so we go to our google sheet we see that we've added a new row so it worked let's also test adding two rows so if we copy this put a comma and add a new one so now we have two arrays within our values array and let's say make a tutorial two and one and save that and oops i refresh the wrong one refresh this page we now see that we've inputted two rows at once so it's working absolutely perfectly okay so if that is all you need to know you can go ahead and stop here in the second half of this section what we're going to do is we're actually going to create a basic form which you can submit these columns to and go ahead and update this spreadsheet so essentially using the exact same google sheets code but we're just going to create sort of a basic front end to submit those values too so if you already know how to do this um you can stop watching here but if you're interested then we'll just continue so you can maybe create a complete application here all right so to create our basic front end here where we're going to have a form that submits these values what we're going to do is make a couple of adjustments to our node.js application here so at the top we want to go ahead and set our view engine and set our encoding for form data so our view engine is going to be ejs like we imported so app.set view space engine and set it to ejs and the second one is we're going to go ahead and app.use some middleware here and that is express dot url encoded which is a function and that takes the object of extended true now this is required and this is so we can receive form encoded data so like a html form i'm going to be going quite fast through this section so uh because this is not like a html or an express tutorial i just want to kind of show you a complete application i guess so we're going to set our url encoded like that now this app.get here which actually submits information this is going to go ahead and be our post from now on because this is after we've posted data and up here we're going to create a new app.get of the root directory and this is where we're going to go ahead and display our form so app.get just rec res like that and this one is going to go ahead and res.render an index file an index.ejs file that we're going to create so in our project directory we're going to create a new folder called views and inside views we're going to create index.ejs and create our html form so i'm going to use the exclamation mark emmet abbreviation to create a html boiler plate and inside of our body we're going to go ahead and create a basic layout so i'm going to do a h1 and the h1 is just going to say video request and then below that i'm going to create a form with an action of nothing it's just going to go to the root but a method of post oops post this is going to have two fields the first one is a text area so this is going to be a request text area and then we're going to create our text area box like that and then below that we're going to create our name field where people are going to input their name so label for name and name and then input type of text with an id of name and a name of name like that really simple html form and then below this here we're just going to create a button with a type of submit and then just write submit in there and that's all that's needed so that looks good so let's save this let's go to our browser and refresh and we now see our little request form which is very much like the site i created for requesting tutorials so in here we're going to write some information and we'll press submit we want those values to be dynamically added to our google sheet using the code we wrote earlier so i'm going to go to visual studio code i'm going to exit this because this is completely done now and what i'm going to do is i'm just going to go ahead and receive that form information so at the top here this is going to be i'm going to go ahead and make a constant variable here and i'm going to go ahead and destructure the request body so i'm going to set a empty curly brackets here to rec.body request.body and this here we'll have in an object all the fields we sent through which we called them request for the text area and name for the input box and that's it so now we have two variables which represent the two values submitted by the form so we can use these now at the bottom down here in our right so we can set our first value to request and our second value to name i'm going to go ahead and get rid of the second row and in our res.send let's just say something like successfully submitted thank you and save now we go to google chrome refresh let's go ahead and create a crest so i'm going to say make google sheets tutorial doing it right now and then my name is james so submit and there it is successfully submitted thank you and if we go to our video requests there is added at the bottom so that's how you can make a complete application which speaking of which this is basically just a google form i just recreated google forms but if you always have a like from scratch google form this will be a pretty cool way of doing it so uh yeah i really hope this helps you make something cool you

Channel: James Grimshaw

Views: 25,473

Keywords: google, sheets, v4, google sheets, sheets 2021, google sheets api, node js, node, js, javascript, automation

Id: PFJNJQCU_lo

Length: 25min 50sec (1550 seconds)

Published: Wed Feb 24 2021

Reddit Comments

[u/jamesgrimshaw]

I was building a site and wanted to send all the form data automatically to a Google Sheet. I found the API documentation confusing, and information online out of date. I thought I would make this to help people and also to show people the possibilities of Google Sheets! Any feedback is appreciated :)

[u/LaputanEngineer]

Wish this was available a few months ago! I couldn't get it to work so I rather created a google apps script with api endpoints to control the google sheet(s).

[u/iamanenglishmuffin]

wish i had this a year ago. have had to do this multiple times for my job. even more of a headache when i have to use drive as well

[u/MonsterBluth]

I like the video, clear and concise. There's a ton of things you can do with the google API. It's nice to have this one in the tool belt. Cheers, James!

[u/yamicaGreenbowl]

Awesome I just got into the app script stuff, was wondering how easy it was to control it from my local environment

[u/ppafford]

why not npm init -y instead of pressing enter a bunch of times?

[u/phillwilk]

Thinking of using google docs for a personal project and this looks decent. Is there an end point for different file types? Looking to store in a JSON for quick access and to avoid parsing.