Using Google Sheets with Python (COMPLETE Beginner GSpread Tutorial!)

what's going on guys it's for cheer from the mill a bit tutorials and today we're going to be using G spread to use Google sheets as a database as a back-end for some of our Python code so you know let's say you have some data usually when you have this data you can use a variety of services things like firebase some some of the resources in AWS to set up a database but sometimes that setup process can be a little bit tedious and it's a little bit harder to understand and view your data visually so today we're gonna be using a source you're probably already familiar with which is just a Google sheets spreadsheet and we're gonna be using Python and G spread which is the library that we're using to actually take our data and put it into a Google sheets so without further ado let's get straight into how we can code Python using G spread okay before we even start I want you to create a Google sheets spreadsheet under any gmail account and just call it email subscribers for now so what our project is gonna be is we're going to have a names column and we're gonna have an emails column and we're going to use Python code to populate some fake names and some fake emails directly into our spreadsheet and then we'll go over how we can retrieve that data so you can tell that this is this is a pretty simple way to use Google sheets as the backend and it'll be easy to just visualize it as well by going into the Google sheets platform so the very first thing we're going to do is what's called authenticating your account so this basically allows us to give permission to the Python code and say hey this Python code is written by me and it's okay for it to edit my spreadsheet it's the same way you you know sometimes you hit share and you share your spreadsheet with different accounts instead think of this as we're sharing our spreadsheet with our Python code so to do okay so the first thing you're going to want to do is head over to console developers.google.com/plus orce manager you should get to a page like this and this is basically how you can enable certain api's to use with your projects so because we're using Google Spreadsheets we have to enable Google Drive API and the Google sheets so search up drive API and go ahead and click on it click create project and we're just gonna call this project cheese bread okay and then click create now go ahead and click enable for the Google Drive API now go ahead and type up the printable sheets API like that and go ahead and enable it so now we've enabled both the Google Drive and the Google sheets api's into our project and what we're gonna want to do next is create what's called a service account so this is the account that you're going to share your spreadsheet with by literally clicking share and adding the account and that account will be associated with your Python code so we're creating an account and then we're going to attribute that account to our Python so that the sheet knows hey this is Python and we are allowing it to access our spreadsheet so go ahead and click on api's and services and then credentials so again we're doing this because we want to create credentials of an account to share with and go ahead and click create credentials and we want to create a service account a service account means that we're creating an account for our code for Python code an OAuth client ID is creating an account for a person to use code which is a little different so clicks make sure you click service account and then you can set a name so you can set Jesus read account what it will do is create a service account ID for you this will be the email address that you share your spreadsheet with you don't need to make a description if you don't want to you don't need a role and now what you want to do is click create key this will produce a JSON key called credentials JSON that you will reference in your Python code that way your Python code will be associated with this key that we've made and now we will share our spreadsheet with that same key so that might not make complete sense yet but just stick with me and it'll start making sense let's look at the JSON basically the JSON file contains what's called a client email if we share this email using our spreadsheet and then we keep this JSON file in our Python code and we reference it the Google spreadsheet will allow that Python file to access its spreadsheet since we've we've shared it to gee spread account now we're going to use gee spread account in our Python so that we can access this spreadsheet so now what we're going to do is create a new Python app go ahead and create an empty directory somewhere and just put two things in it put in your main PI which will just be your Python script that we're using to populate our spreadsheet and put in that JSON file that we generated earlier so okay so we've imported G spread and before I even continue all of the code that we're going to be using in this tutorial is linked in the description below so go ahead and click on that and that way you don't have to type everything out again although I recommend you do there's some some parts of this that are just easier if you copy and paste so click that link in the description below and copy and paste some of the coding so what I just did is I imported something that will let me use this JSON file as credentials and okay so before we start coding let's think about what the end goal of our project is we want to populate our names column and our emails column using our Python code so let's start with just creating a list of emails and let's create a list of names and then we can fill these with some dummy items hey cool now this is some of the code that I was saying is easier to just copy and paste again it's in the description but this is just some authentication code you don't need to know too much about what it does it basically takes your JSON file that's right here and let's rename it actually chase on and it authorizes this code to act as the account that we shared our Google spreadsheet with earlier so now we have permission to actually use the spreadsheet okay so now the next thing we're gonna do is actually create our spreadsheet so let's just create a spreadsheet people's mind got open and what's our what's what's our spreadsheet called it's called email subscribers so just go ahead and put this in at the end of this tutorial I'm gonna show you a bunch of different ways you can open spreadsheets as well as the different tools gee spread offers but right now we're gonna use client open next we're gonna create a worksheet so a worksheet if you're not familiar is these little these sheets that we can just add tabs into so we're gonna just create a full tab I'm going to call it email subscribers and how how many rows and how many columns do we want well since it's going to be filled with names we want it to be as large as the emails list as well as as wide as two because we want two columns okay so now we just need to think about logically how are we gonna fill our spreadsheet basically the way you can add an item to a worksheet is using update underscore so and you can just put in for example row 1 column 1 and then PI so we want to fill every single item in column 1 with the names and every item in column 2 with the emails and each row represents a new or you know so let's see what we're looping through is just the rose because the columns we already know names is one and emails too so let's loop through and we start at one because the rows start at 1 and we'll go up and toll we're out of emails so we're looping through all of the different emails or the indexes of the emails and for every single one we're gonna do worksheet to update cell in row is gonna be whatever is changing because the names and the emails are changing and and column is just going to be one for the names and and for the emails we'll just make it to right so now we're looping through and we're updating ourselves so now what we can do is probably just click run and let's just hope there's no errors project finished and now look there's a new tab that says email subscribers and we have oh alright so it looks like there's a bug because we only got two items from our list instead of all three then you're probably gonna face similar bugs because it's hard to visualize Google sheets data while we're just coding so you can try a few things for example look at your loop and notice that we're only we're only looping through two items because we're starting at one and we're stopping at two which is the length of emails so we should be stopping at three but if you do that you can't you can't reference the third item because this is an indexes so you can only reference the second index in a list of three right because this is index - this is an excellent Synnex zero so we should probably subtract one from here and add one from there so just made a few changes let's go ahead and delete this spreadsheet and try to see if we saw the bug it could be I miss something again but hopefully it's fine so it's actually kind of interesting to watch the spreadsheet be made in front of you so I don't know if you saw that but the items just appeared and there you go there you have it those are all three rows just add into the spreadsheet cool so now what we're going to do is talk a little bit about what else we can do with juice bread so basically we've completed the task already of adding items to a spreadsheet but sometimes you want to do other things sometimes you want to read from a spreadsheet sometimes you want to search for a row in a spreadsheet so let me just go through some of the functions and I have detailed notes in the description for you to download and you can reference those so so let's just get straight into it first of all notice how we referenced our spreadsheet earlier we used client dot open and then we just put the title of the sheet now another way you can do it is saying find actually let me just make this a new category for you to understand another way we can do it is saying client dot open by URL which means you just put the link to the spreadsheet or open by key which is the key is just the end of the spreadsheet or this highlighted portion is the key and so there's a variety of ways to do so so for example if I did open by URL I would just put in the URL in quotes another way if now recall in this case what we had done was we just opened our existing spreadsheet and then added a worksheet to then put information in but what if we wanted to read from an existing spreadsheet well we can do that in a vision in a variety of ways let's first populate our spreadsheet with you know column 1 column 2 so these would be like our headers and then we can say just some pieces of data just so we can see how it's formatted now what we can do let's remove our previous code that we don't we don't care about it anymore or we would just put it to the side and instead of doing client def open we can do dot work a sheet one this is called sheet one that's why we're doing sheet one and then all we need to do say request data equals spread sheet dot get get all records will return a list of dips that represents the spreadsheet and then we're just going to go ahead and print our request data so so ignore this error because we're getting this error because we are ignoring off their code but we have a list of dicts and each dict represents each additional row contributed to that column so for example we have our first dict is column one associated with first row and then we have column two associated with first row and then we have column three associated with first row then we have our second deck representing the second row column 1 4 which is the second row or technically third I'm saying second because I'm talking about indexes and then column 2 representing second row which is 5 and column 3 representing second which is 6 now if you this is a little confusing because it's you know in a list of dicks so you can also do get all values and this returns sort of a little bit easier to understand a list of Lists so now you have the first row column 1 column 2 column 3 then you have the second row 1 2 3 then you have the third row 4 5 6 and you can easily it's a basically a 2d array that you can loop through and you can loop through the rows and the columns easily ok the next thing you can do is you can get specific values from the rows or columns so now let's just say values this equals spreadsheet dot row values of 1 so now we're getting the values in row 1 and we're just gonna print it so if we you know click run we have all of all of the values in Row one we can do pretty much anything with this so we can also do spreadsheet dot pretty much a specific cell so a cell and then let's say we want to get a three a three and then we can print out it won't be a list it'll just be a value and we have cell and it represents four so anyways this was pretty comprehensive juice bread tutorial but there's a lot more gee spread can do but this is a I think a pretty good start all of the code resources notes are in the description so make sure you click that link and follow along on your own computer that's it for today guys so please make sure you like comment subscribe to the YouTube channel because that helps boost to the algorithm and if you have any problems make sure to comment and I'll personally answer any questions that you've had

Channel: Millibit Tutorials: Ruchir's Coding Clips

Views: 5,796

Rating: 4.8110237 out of 5

Keywords:

Id: wrR0YLzh4DQ

Length: 16min 27sec (987 seconds)

Published: Mon Jun 29 2020