Python Google Sheets API Tutorial - 2019

hey guys and welcome back so in this tutorial I'm gonna be showing you how we can use Google sheets to run kind of a database for our Python files this is super easy pretty quick and completely free so hopefully you guys can follow along and just make sure you're paying attention to the steps closely because it is quite a few and if you mess one up you might run into some issues okay so our first step is to just create some kind of spreadsheet on any of your Google accounts and give it an appropriate name something that's easy to remember so I just named mine tutorial and I just filled it with a few random entries but it doesn't have to have anything in it next we're gonna go to this console dot Cloud Google com now you can simply find that by just typing like Google Cloud console on Google and it should bring up a page looks like this now for you it probably says something like create new projects so what we're actually gonna do is we'll create a new project for me since I've already have some previous projects I just have to go up here click on new project and then wait till it brings me to a page like this so I'm just gonna name my project like test let's say test sheets okay and for organization you can just leave that blank so I'm gonna click create and I got some error here but it is actually creating beeping the project up here so once it's created we should just be able to click on it where it should actually just bring it up for you guys and it should bring us to a page that looks something like this so you can find them just by going up here or by clicking the other way that I just did anyways what we need to do now is download some api's for this project so we're gonna go to go to API overview or you can click on this little link on the side and go api's and services so we'll do that and we're gonna get two different api's so the first one we need is Google Drive so we're simply just gonna search Google Drive like this and well I guess that doesn't work so we're going to go to library and search Google Drive okay so we're going to click this one and we're going to click enable and now we're going to start setting up some credentials for it and then we're going to install the Google sheets API so gonna click create credentials we're gonna go to Google Drive API we're going to select web server so nodejs or tomcat and then we're gonna go application data and click no will click on the little blue button here and we'll give this some kind of name in my case I'm just gonna name it Tim and then as a roll we're gonna go project editor we'll make sure Jason is selected and we'll click continue and we're gonna download a JSON file now which will store our credentials so you can see it's downloading this file for me now and we'll just click close and now we're gonna go back to our library and get another API so we're going to go to now to the Google sheets we're gonna go Google sheets API and we're gonna click enable wait for this to enable and that should actually be about all we need to do and now we're ready to move on to what do you call it actually hooking up some stuff with our code ok so that's it for this kind of Google cloud platform just make sure you keep track of where this JSON file is because we are actually gonna have to open that up now so what I'm gonna do to open this file is just use pycharm and I'm just gonna create a new project and throw my JSON file in there you guys can use whatever you want but just open up the JSON file with a text editor so that you what can see what's inside of it because that's important so I'm gonna create a new project on my desktop called tutorial and what I'm gonna do now is just grab this JSON file that I had here and I'm gonna drag it into this folder I'm just gonna call this creds just because we're gonna have to type the name in later so it'd be easier to name it that ok so what we're looking for now is this client email we're gonna copy this client email from inside our JSON file we're gonna go to our Google sheets here we're gonna go to share and we're simply gonna paste this email and and just click send this is gonna allow us access to the Google sheet from our API ok so that's about it for where I'll leave the Google sheet open because we're gonna play with it in a bit but we're going back to pycharm now and what we're gonna do is create a python file so new python file and i'm just gonna call this sheets okay now in here what we're gonna have to do actually is we're not writing any code yet we have to install two packages or modules with pip so that we can actually use the API so to do that in Python we can just open up the terminal here or we can go to command prompt and start typing the following I'm just gonna do pip install G spread and then Oh so Oh off to client once you've done that you can hit enter and for you it should actually download quite a few things but for me since I already have it installed it's not bothering to do that so pip install G spread and then Roth to clients okay once you've done that what we're gonna do is inside our Python file we're gonna import a few modules and just make sure everything's working so the first one import is G spread and the second is from o off to client service account we're gonna import service account credentials so now I'm just gonna create a configuration for my file it's gonna go to Python just call this run and select my script and then we're gonna run this and make sure that everything's working again you guys can do this in whatever Python editor you want as long as you know how to just run the Python script okay so I'm gonna run this and no errors so we're good to go and we're ready to actually start connecting to our Google sheet so now what we're gonna do is we're going to actually and you guys are gonna have to copy this from the description unless you want to type it out with me we're gonna create a scope now usually you just put one string in here but I was running into a lot of issues with that so I found that the best way to fix this it'll probably work for you guys is to put all four of these so these are the four strings in case for some reason you don't want to copy it but it will be in the description and under the source code link it'll be on my website as well so you can just copy the string and really nice we're gonna copy all of these in and then we're gonna go to the next line I guess I should have cooked enter here and we'll start setting up our credentials so now what we're gonna do actually is I gotta open up my other file here is we're gonna say creds equals and then we're going to say service account credentials dot from Jason key file name and then in here what we're gonna do is put the name bar Jason file which is creds Jason rendered a comma and then we're going to put scope and this should refer to the variable that we just created up here the next thing to do is say client equals G spread authorize and then here we're gonna put creds and that's almost about it for actually I'm hooking up to our sheet so now the next thing we have to do is just say sheet is equal to client dot and I believe it is open yeah and then here we're gonna put the name bar sheet which is tutorial and then just put dot sheet one and this will just make sure we're getting the first sheet clearly if you had sheet 2 sheet 3 then you guys can probably figure out how to get that so let's actually just try running this first and make sure that everything's working before we move too far so we're gonna run this and just wait see if we get on the output and we don't get anything which actually means that everything's working so now we can try to print out the contents of our sheet so remember that my sheet looks like this so we have kind of three columns and six rows so what we're gonna do is just say data equals sheet dot get all records and then we can just simply print out data so let's run that and just wait a second and you can see that we're printing all of the data from our sheet out here and it does actually correspond obviously to what is on the Google sheet so already that's pretty interesting and pretty cool now if you want to make things print a little bit nicer what you can just do is import or actually will do this from PP print import PP print which just stands for like pretty print so if we do PP print like this then our dot will be formatted a bit nicer when it comes out so you can see it's just in its own rows so it's nicer to see okay so now it's time to talk about how we can get certain rows and certain columns so from our spreadsheet say we wanted to just get the first row or the first column it's really easy to do that so we're going to do if we can just say row equals and then sheet dog get actually is know what it is one second I gonna have a look at here ah sheet dog row underscore values and then here we'll just put the row so let's say I want to get Row three I'll put Row three and then start printing data will print row and I'll show you what we get so wait a second we get to Bill and blue which corresponds to what's on a sheet which is to Bill and blue so that's row number three now for a column it's very similar all we have to do is we'll copy this actually and we'll change wherever it says Row two column so she so-called got value I believe is what it is and now we can just print out column and this will actually give us the third column in our sheet which is exactly what you for it fav color red blue orange pink yellow awesome so if we want to get a specific cell the way that we can do that is we could say cell equals sheet dot and then I believe it is actually just cell and in here we'll just put the coordinates word so I could do cell 1 2 and then dot I believe its value yeah dot value so now I can just print sellout and if we're corresponding to cell one - well that should be what am i confused here I think that should be this so we'll see what we get and wait a second and indeed oh we get names so I guess I was confused on what 1 2 is 1/2 would be that ok so yeah that is essentially how that works in terms of getting stuff so now I'll show you how we can insert rows insert columns and yeah just do a bunch of cool stuff like that so if we want to insert an entire row what we can do is we can say let's let's make a list there's gonna be everything that's contained in that row so we'll say insert row is equal to and then here we'll say like hello 5 red blue and this is going to be the contents of our row so each column is be one of the items in our list here and then what we can simply do is just say sheet dot insert row we'll give it the bro and then we'll just define what row we want to insert it at so let's say we want to insert it at row four which should be right here well let's run this and see what happens to our Google sheet okay so I have this open here and you should actually see that updates and it does we are inserting to Bill blue now notice that it doesn't actually override the other row it just pushes everything down and that's honestly kind of a nice thing because a lot of times we don't want to override we just want to insert at that index right so if we won't actually change rows or delete rows I'll show you how we can delete a row so it's very similar to insert row except we don't need the contents we just need the index so if I delete row four run that open up my sheet you can see that row four will disappear and row four is gone everything shifts up one now if I want to actually update a row or update a a cell per se what I can do is I can do something like update and then we'll do sell and then here I just have to do the coordinates of the cell so let's do two two and then whatever I want to change it to in this case I'll just say changed like that let's run this and let's go to our sheet and watch what happens so - - and you can see that we change that to change and that is how you update the cell now there's a ton of other things you can do with this I recommend you guys read the documentation for this API which I will again have on my website and in the description but one last thing I guess that you can do is get like the amount of rows and the amount of columns and all that and to do that you can just say like let's say num rows is that should be the other case is equal to sheet don't I think it's rows row count yeah that's how you do that and then if we were to print num rows obviously it would tell me how many rows I have so let's wait for that and you can see we get a thousand rows and that's obviously because this sheet actually just goes down to a thousand right if you want to see how many rows actually have content in it what you would do is just get the length of this data so if I could want to do that I could just say blend of data and then you should see that we are only getting six I believe we'll see though in a second of five yeah so because we only have one two or one two three four or five that's why it's giving us five so anyways that has been it for how to use the Google sheets kind of API and use that as a data base with just this knowledge alone you guys should be able to do some pretty cool stuff and if you know how to update a cell essentially you know how to update row you know how to update columns you know to do all that so with that being said I hope you guys enjoyed the video and if you did please make sure you leave a like and subscribe to the channel [Music]

Channel: Tech With Tim

Views: 154,214

Rating: 4.9444151 out of 5

Keywords: tech with tim, python tutorials, google sheets python, gspread python, google sheets, python google sheets api tutorial, google sheets database, python google sheets, google sheets api

Id: cnPlKLEGR7E

Length: 12min 59sec (779 seconds)

Published: Tue Mar 12 2019