Use Google Sheets with Node and Express in Fun Side Projects

[00:00] Start by going to console.developers.google.com. We need to create a new project, so Create Project. We'll name this Animals. You can name it whatever. I'll hit Create. Then in Sheets, we need to create a sheet. I'll call my spreadsheet Animals.

[00:18] From here we need to share this spreadsheet with the service account from our project. I'll go into Credentials, Create Credentials, a service account key, and select the default service account. Hit Create and this'll download a JSON file. This JSON file I downloaded has the private key and the private ID and everything we need to hook this up to the sheet.

[00:46] I'm going to grab the client email. Just copy this. I copied that to the clipboard. Then go into my sheet and say "Share with this service account." I'll hit Send and now this sheet is accessible by my personal account here and it's also shared with that service account.

[01:05] I'll go ahead and add some data. Cat. We'll have 22 cats. Dog. We'll have 30 dogs. Cow. We'll have 11 cows. To make this dataset easier to access, I'm going to name this sheet Animals and I'm also going to name this range here under data named ranges, and I'm going to select column A and column B as my range.

[01:29] Hit OK. I'll just call this All as the range, hit Done there.

[01:34] When I access this from the API, I can access it using the range Animals All. Animals, which is the name of the sheet, and All, which is the name of the range of column A and column B.

[01:44] Lastly, a very important step is in Library, if you go down to the Google Apps APIs, click the Sheets API and enable this API, this way our project can actually access spreadsheets. Otherwise it won't be able to. I will need that Credentials file that I created, so I'll drag that in here.

[02:01] I'll just initialize this as a node project, yarn and hit Y to accept the defaults. I'll also need the Google APIs. Yarn add Google APIs. Create an index file and require the Google APIs. Require Google APIs.

[02:19] I also want to require this Credentials file. I'm going to rename this to Credentials, then require it here. Credentials is required. Credentials.JSON.

[02:33] The first step is to set up our authentication, so I'll call this off. This'll be a new google.off.jwt or JSON Web token or jwt for short. The parameters of this will be our credentials.clientemail. The client email's down in here. You'll remember that we used that to share our sheet with.

[02:55] I'm just going to look at the documentation to show you that the second parameter is a key file. We're not using a key file. We're using Credentials, so I'll say null. The third parameter is a key. This will be our credentials.privatekey, which is in our credentials as well, this big, long thing.

[03:12] A list of scopes and I've actually copied and pasted this in. This is an array listing the different scopes we want to use and the one we want is googleapis.com/off/spreadsheets.

[03:25] The last parameter is a subject. Impersonated accounts email address, and we're not using that either. With our authentication setup, we can say Google.options and use the off object as a property on our options object. We can get the sheets API from google.sheets and we want version 4 of the API.

[03:47] To test this out we'll go ahead and say off.authorize. This takes a function with an error and tokens and I'll just console log the tokens. Go ahead and run this node index and you see it successfully authorized and gave us back these access tokens.

[04:02] There are many scenarios where you might need these tokens. We're not going to so I'll go ahead and delete that.

[04:08] To get these values from this spreadsheet I need this spreadsheet ID right here, starting with 1 all the way to capital Y. I'll copy that and call this spreadsheet ID and just paste it in here.

[04:24] I can use the sheets API to get that. I'll say sheets spreadsheets values get, and then get takes an object that requires the spreadsheet ID and the range. Remember back in our spreadsheet the name of our page is Animals and the range is all. I'll say the range is Animals, then an exclamation point All.

[04:52] After the object is a callback function, which gives us the error or the response. To test this out, I'll just say consoleLogResponse.values. Hit Save. Run node index and you'll see that we get back the values from the spreadsheet that we have.

[05:12] Adding values to a spreadsheet looks a lot like this get. I'm going to copy this and comment it out and then paste it up here. Instead of get, we're going to append values. We use the same spreadsheet ID and the same range.

[05:27] We need to add a value input option. We're going to call this userEntered. If you opened up the API and look at value input option you can see userEntered is parsing the data as if they are entered into the sheet through the UI. Numbers stay as numbers and strings are converted to numbers, etc.

[05:49] Include values in response to true. This isn't necessary. It's just usually nice to see what was posted.

[05:59] I need a resource, which is an object, which has values, which is an array with the rose inside of it, so another array. I'll say pig 50 and now response.values isn't proper. It's now response.updates. Now when I run node index, hit Enter, you'll see the updates that we get back show everything that was changed. The updated data is inside of Updated Data Values.

[06:27] I'll check my spreadsheet and you'll see that pig 50 is now in there. If I want to add multiple values, I can do bird 100 and I'll change this to fish and then run node index again. You'll see that this time it added those two rows, so fish and bird, because we did one row, two rows worth of data.

[06:49] Let's go ahead and add express and body parser to our project and then just set up a very typical express, HelloWorld app. Where I require Express, I create an app from Express. I require the body parser, so require body parser and tell the app to use bodyparser.json so that when we post we can post JSON objects and read the body in.

[07:17] At the bottom we can tell our app to listen on port 4000. To make sure this is working, we'll just app get the root route and then send back request and response. We'll send back hello. Comment this out for just a second, then run node index. We'll check port 4000 on the local host and we get hello.

[07:41] Let's take this and wrap these things we've commented out, first by saying app get/animals request response. Take all of this, cut it and paste it in here, and uncomment it. Then we'll just send back the response.values. I'll stop this and start it again. We can go to /animals. You'll see we get back all the values from our spreadsheet.

[08:07] What you'll probably want to do, because these are arrays nested in arrays, you can easily map these rows so that they return the name and the count. We can de-structure the array here to name, count, and then return an object of name and count.

[08:23] If I stop this and start it again, now instead of arrays these will be objects of name and count, because we de-structured that nested array, then we returned an object with name and count in it from the de-structured array.

[08:38] We can go ahead and do a post around the Append app. Post@/animals. We'll get the request and response. Remember, we have request body will be JSON, because we're using bodyparser. We'll go ahead and grab all this, cut it out, paste it inside of a post, uncomment it.

[08:59] Instead of these nested arrays I'll get rid of the second one, change the first one to request.body.name and request.body.count. Also change the console log to a response. Send. I'll run this again and if I refresh here, I can still get the data.

[09:19] If I open an app like Postman, where I can post some test data, I'll go ahead and say http:localhost4000animals. The body will be some raw JSON and my JSON object will be that name of duck and count 22. I'll hit Send.

[09:44] The response I get back is that updated data. You can see that if I come back here and I'll refresh, you'll see that duck is now in there. If I check my spreadsheet, you'll see that duck is now in my spreadsheet.