Build Levels.fyi in DotNet! - How To Use Google Sheets API in C#

you've probably seen this floating around on the internet but levels.fyi the website that's really popular for publishing information about different Tech salaries and big Tech that was Once Upon a Time created on spreadsheets there was no database backing all of the information it was just spreadsheets online and a lot of people find this hard to believe like how could you possibly have this really popular website and the data was just in spreadsheets right we're all used to having SQL databases or object stores or something way more complex but they were able to do it with just spreadsheets so in this video I wanted to look at using CP and.net to be able to access Google Sheets from Google Drive before I jump over to the code just a quick reminder to check that pin comment for a link to my courses that I'm working on for Dome train and my free Weekly Newsletter let's head over to visual studio all right we are going to start with some Google API so we need some Nate packages so what I'm going to do is jump over here and show you that I've included the Google apis for sheets and it's version four at the time of recording and I'm using version three for Google Drive if we look over at the version here I guess this is the actual version of the nougat packages but this is what we're dealing with for the API version when you're watching this if it's in the future it might be different but I just wanted to show you this is the snapshot that I'm working with the other nougat package that I have here is just to be able to access some types that I've created that I use in my projects and we'll see those coming up for what it's worth you don't need to have the one that I have highlighted you don't need to use my Nate package at all this is just convenience stuff for me really the two that you need are these Google ones that I have listed on the screen one of the first things that you're going to need to do is have a Json credential that comes out of the Google Cloud console I have mine here on the screen but it's all collapsed uh it's just a string right now that has the Json text that has information about your private key just so that you're able to load that up I will show you briefly in the console for Google Cloud where you can go because the way that I'm doing this to get this all to work is that I made a service account and the reason that I made a service account is that I can take my files or folders in Google Drive and then share them with that service account so that it has permissions to see this stuff because what I found when I was exploring this is that I set it all up with other credentials and everything was technically working but when I was calling the apis it kept saying hey I can't find that and it can't find it because the account or the service that I was using didn't have access to these things so a service account is what I'm using here and I'm sharing my folders and documents with that service account from Google Drive and just to quickly show you where that is in Google Cloud console I go over to service accounts and create a new one here so on this screen that I'm showing I've deleted a couple of entries so you can't see some information then I've just changed the email and the unique ID but when you're done you should be able to see a screen like this and you should be able to get your credential out of here so if you go over to keys or use the tabs here you can get all the information you need to get that Json credential pulled over to use it in your code you'll also need to make sure that you've enabled these apis for usage so you can go ahead and search that up here at the top and enable the Google Drive and Google Sheets apis and technically if you wanted to access other types of Google services you can go enable those as well here so back into the code we go the first thing that I'm going to be doing aside from just putting my credential into the code here which if you're doing this in a real production setup you probably don't want to just have your credential dropped into the code you'll want to use some type of Secret store but this is just for demonstration purposes now what I'm doing from here is I just made a little Factory class and this is pretty simple but it's just because I wanted to have the different Google services that I'm using for this demo all grouped together and just to show you what I mean by that what I need to be able to do is load up this Google credential object this comes from the nougat packages we saw and I'm passing in my Json credential here so this part of the code enables that credential for me and it provides the scope that we want to have access to in this particular case I'm using drive and spreadsheets because that's what I want to access from Google Drive beyond that all that I'm doing is creating these two service objects that we get from these nougat packages and the reason that I need both of these is because when we go to use the sheets service API that's provided from that nougat package we don't have a very simple way to go open sheets unless you know the unique identifier of the sheet and for me I don't know that I just happen to know the path to my document and that's how I want to look things up so in this video I'll be showing you how I go approach looking things up by their path to be able to open up the sheet without having to know the fancy idea ahead of time from there all that I'm doing is passing back a record that I created that has a reference to both the drive and Sheet service I just called it Google services and if I scroll up a little bit you can see it declared right here from line 142 to 144 now going back up a little bit further to the beginning of our program here I have those Services now pulled into this data transfer object to be able to reuse later in our program but that's where I'm making a new class called Google Drive client this is something that I built it's a little bit more purpose-built than what we get coming right out of these Nate packages and that's to be able to support this type of thing I want to be able to look up files by their path and that doesn't really work with the Google Drive API well kind of we're able to do it but you have to be able to chain these different parts together to be able to go for folder by folder to find the file so I'm going to walk us through that next now this isn't by any means a perfect algorithm you could absolutely optimize this and I might recommend that if you're doing a lot of different lookups you might want to do something like caching some of these results in this particular code that I have on the screen that we'll be walking through I don't cach anything and that means that if you were to ask for the same path we'd have to go do multiple API calls to be able to go navigate to go find that file even if we've already found it before so you might want to do something like caching some of these path components that we'll be looking at if performance is of a concern to you the first thing that I'll call out because I mentioned that I'm using my own nougat package is some of the syntax that we see up here and all that I'm using my nougat package for is to be able to have this tried X type and it's a result type and that's because I don't really like throwing exceptions I like to be able to make sure that I have a result type to either have the result of the function or an exception that comes back and I prefer that over throwing into TR catches because I find that it cleans up my code it's a personal preference but I just wanted to show you that's why I have it here so I have this code that calls safely and then I'm able to go execute things and it effectively wraps it in a TR catch and that way we can bubble up one of these types that is either the return type or an exception but the important part is within here so what I start to do is break down the path into different pieces so what I'm doing is splitting it by the forward and back slash because you might want to declare your path in a particular orientation depending on your preference this supports both to be able to split the path Parts by either a forward or a backslash but what I'm doing from there is I'm queuing all of those up into a q object which I have declared on line 77 next you'll see that I have this path so far which is a string Builder I'm just using this for debug purposes you don't need something like this to make it work but if something goes wrong I wanted to print how far along this path that we got because the way that this algorithm works is that we have to go folder by folder to go find the file that we're looking for so what I do is I start with the beginning of the queue here so I'm just making sure while we at least have something in the queue let's go pull off the first item and then path so far gets the path part that we're pulling off of that so this is going to be the top level folder in our particular case and what we do from there is we create a request and I'm going to jump down to that method right now and I just have this as a convenience thing because I have to call this several times but this is the API that we get from the nougat package in your particular use case you might want to tailor this to your liking but this is what I'm doing to be able to get the results that I'm looking for I have a page size coming back as two and that's because in my particular case if I find two things that match I already have a problem but if I ignore it and only put the page size to one that might mean that somehow I'm not totally familiar with Google Drive and if I'm able to have things with the same name say like a document versus a sheet or even a folder name that matches the same thing there's no extensions from what I can see in Google Drive for these different types of documents so it might get kind of weird if you have different things with the same name so I pull back at least two because we'll be checking to see if we get two or zero these are going to be error conditions now I have the Q property which I believe is supposed to be for the query that we're trying to do and I'm looking for something named with the path part that we're interested in but what's interesting about this is that in order to make it work and I mentioned that we have to do this query multiple times is we can look within a parent folder I haven't personally found another way to do this to go look up things by their entire path so we have to go folder by folder to go look for the different piece that we're interested in so when you start at the top level folder there is no parent folder that we need to go ask for but once you're in that next level then you need to say hey look this parent folder that we started with find all the things within that that match this parent ID and that way we can keep going lower and lower into more subdirectories so once we specify a parent ID we're going to tack on this next part of the query but this function essentially just returns a list request which is one of the nougat package objects that we have access to so with that list request what we're going to do is then ask the API to go execute the request for us and we should get back a file list but a file list is a little bit confusing because it's not just a list of files it could be folders as well so the naming is a little bit off but that's what this Nate package offers and like I said I have two different error cases that I'm interested in one is when there's nothing that comes back and we want to be able to say hey look something is not the way that you expected it and you'll notice that I'm returning an exception and not throwing it because like I said that's my preferred way to do it and I don't like having to throw exceptions to catch them the other error condition is when we have more than one thing that matches I haven't personally tested this to see if it's possible but I figured BAS on what I could see in Google Drive I wanted to make sure that I could protect against it however if there's only one thing which is essentially the else condition once we get past these two if we have nothing left in the queue we've essentially gotten to the point where we found the file or the folder that we're interested in so we can return right out of here otherwise we still have to keep going and that means however far we've gotten to that becomes the new parent so if we're looking for a subfolder before we get to our destination file we need to set the current folder that we're at to the parent ID from there the next iteration of the loop we would then go look within this parent and you'll notice that I'm just adding on a slash to path so far so that if we have to go print these exception messages somewhere then we can see how far we got now technically this part at the bottom I think should be essentially impossible to reach but I guess the only way that that could happen is if you passed in an empty path so I should probably guard against that right at the top here and then we probably wouldn't even have to look for that at the end but this is the algorithm that I'm using to be able to navigate a path structure to go find a file within folders scrolling back up to our little demo app that I have up here in my Google Drive I have a folder called Dev leader and then a folder called social media and then I have this Google sheet that's just called media schedule if you recall I said I don't know if there's file extensions and the file that I have that's media schedule is a Google sheet there is no file extension on it so this looks kind of weird but technically this is a Google sheet file now you'll know notice that I don't have a try catch around this whole thing because it returns that result object and I'm just checking to see whether it's successful or not I am throwing rate here just because we're at the top level of the program and I wouldn't be doing this in production code but if we can't find it I just wanted to throw the exception to Halt whatever we're doing at this point though if we have found the file we have a file result which has the file reference from Google Drive and we can use the ID from that file reference to go look up the sheet and that's what this code is here so the whole reason that we had to do all of this is that we aren't able to as far as I know look up a sheet by the name we have to look up a sheet by its ID so by looking up the file first based on the path again that whole algorithm we just saw then we're able to use the ID off of that file to go look up the sheet fortunately there's nothing fancy that I had to do with the Google Sheets API as soon as I have the ID we can go look it up and recall that you needed to make sure that you have your permissions set so that your service account can see this file the same thing with this folder structure up here if there aren't permissions it will not be able to navigate through the structure but at this point you should be able to have access to your sheet and that means that you can go do some fancy stuff like printing out information about the sheet or finding ways to manipulate it in my little demo application I just wanted to print out some information to show you that it works all right and when we run the application you can see that I have one sheet within my spreadsheet that we found and that one sheet is going to be called content schedule and then I'm just printing out the different columns that are within there and yes there are two blank columns at the beginning and then because it's a media schedule I just have the different areas that I publish content to at least a bunch of them this is technically not all of the places that I publish content to but this is something that I found helpful for being able to maintain where and when I need to publish and that's going to wrap up a quick tutorial on how you can access Google Sheets from your C applications so if you want to go build the next level's FYI website in asp.net core and access Google Sheets you have everything you need to go do it now let me know in the comments what you plan to build with your Google Sheets API thanks and I'll see you next time