Google Sheets - Python API, Read & Write Data

alright so in this video we're gonna use Google sheets API to read some data from our Google sheets spreadsheet using Python and also write some data so you'll need Python installed and you need some sort of text editor I'm gonna be using Visual Studio code for the text editor and Python let's see which version I have so three point seven so if you don't already have Python installed you need to install it you can do it by going to python.org and you can download and install it and if you want Visual Studio code that would be code dot Visual Studio com all of these is available for a Mac Windows Linux so we're gonna be using some documentation from google for this their API documentation mostly and basically I have my spreadsheet and we also need access to developers console so the first thing you need to do make sure you have Python installed vs code installed which I have I'm gonna go ahead and create a folder for this project and just drag it over here to open that folder and in here I'm gonna create a Python file so I'm gonna go a new file and we'll call this read dot py for our Python files let me just zoom in a little bit turn on the terminal so a lot of this we're gonna be using from the documentation so I'm gonna go to this QuickStart and here what I'm gonna do I'm just gonna scroll down and see this is the install so we basically need to install these three packages that are listed here and we can install it with a single command once we have Python installed I can just copy this go to my terminal and basically just run this now usually you would probably create a virtual environment but that's outside of the scope of this tutorial so I'm just gonna install it like this now in my case I have Python 3 installed so instead of using this command Hiep install i'm going to change it to pip 3 install so i'm gonna do that and run this thing and again it's telling me to update my pip version I'm not gonna do that right now but you could do that too now the next thing I'm gonna do I'm just gonna scroll down to this example they have and copied and I should be able to do that by just clicking on this copy or you can just select and copy and just pretty much just go back here and paste that in our code I'm going to close this panel on the left so there's more space so this is pretty much what I copy pasted from their documentation I'm probably not gonna be using a lot of this because their Doc's are usually basically for reading and writing from spreadsheets where you have to ask users permission to grant access to read the spreadsheet in our case we're just reading our own spreadsheet a lot of this is not gonna be useful for us so the first thing I have to do I have to create a project in developers console so for that I'm gonna go to that developers console link again I'm gonna just add this links in the description and here we need to create a new project so once you go to this list of projects or if you don't have any maybe there's a button like add a new project if it's your first project I'm sure you'll find it so you want to be a little flexible with Google interfaces because they change pretty fast so I'm going to open this see click on this new project and here we're just going to name this project doesn't matter what you call it just make sure it's something unique so I'm going to click create and that's gonna create the project it may take a little time see that's dumb for me so once it's done you want to activate that month so I'm gonna click on the one that I just did and it's gonna open that project so now at this point we need to activate Google sheets API so I'm gonna go ahead and scroll down here see Explorer and enable API s-- and apparently i have another button here i'm gonna click on that too and basically just find Google sheets API so it's right here you can also search and find it but it doesn't matter Google sheets API I'm gonna click on this and basically just enable now we need to set some credentials see there's this thing create credentials now I'm not sure by the time you get to this video if this button is gonna be here you might want to just find this credentials someplace on the left and get to it and basically here see there is this service account so that's what we're gonna create a service account so right now there are none as you can see so if I do manage service accounts it's gonna get me here and then there is create service account which is pretty much what I need to do now we need to give this service account some sort of name and that's gonna be assigned to this email for that service account so just think about this service account as an email you're creating so imagine if you had somebody from your company and you made them a new email and you gave them that emails so that they can access and read spreadsheets and do something in Google sheets so I'm gonna go ahead and click create and we're gonna give some permissions to this account so the role so I'm gonna open this and here for project I'm just gonna make it an editor because I want the script to be able to read and write to the spreadsheet good enough I'm gonna click continue nothing else to do here just click done so basically we just need that service account so that service account has this email so similar to like when you have somebody that has an email when you want them to be able to work on a spreadsheet what do you do you go to the spreadsheet and share it with them that's the same type of thing I have to do here I need to make sure that this sheet is basically shared with that email that was just created for the service account so that's what I'm gonna do I'm gonna go here go share and just add that person here so just paste that email right in here and we don't need to notify this person because it's a robot basically an editor we want them to be able to write and read the spreadsheet so that's what it's gonna be I'm gonna click share so now that should be now shared with that email so I'm going to go back to that page now that's the email we've created now to be able to log in under this service account we have to get some credentials with this so if I go ahead and open this service account see all the way in the bottom here there is this option to add key think about the key as the password or pass key or whatever it generates basically some secret keys and some other information that's necessary to be able to log in so I'm gonna go on there add key and do create new key and the key is gonna be Jason so that's what I'm gonna use I'm gonna click create and that's gonna download a file to my computer which is gonna be a JSON file since I'm on Firefox this thing pops up I'm gonna go ahead and save the file and that file is probably in my downloads folder so what I'm gonna do I'm gonna copy to the same folder where my project is so that folder that I've created to do the Python file in here I wanted to make sure I put that file in this folder as well it doesn't have to be in the same folder for me to make this simple I'm just gonna place it in the same folder and that places this file here I'm gonna rename this long name you don't have to but I'm gonna do that to make this simple I'm just gonna call it keys dot Jason so that's the file it has all the credentials you can open it it's gonna have that same service account email it's gonna have some secret key so that's the file you need I'm just gonna place it in here so that creates a service account so like I said this code they have is not for service accounts this is for that standard pop up message click on allow and all of that stuff we don't want to do that we just want to be able to read and write from our spreadsheet so for that we need to basically get two service accounts and if I open this Docs see this server-to-server applications so if I scroll down it's gonna basically see tell you to create the service account which we pretty much just did see there's this Python Python Doc's are a lot easier than nodejs Docs there's a lot more that you can find apparently see here it gives us this script that is using to actually connect to a service account and generate credentials so I'm gonna go ahead and copy this go back to this and just pretty much paste it in here so that basically allows us to go to the service and import this service account and see there is the path to the service JSON file so in our case the file is in the same folder so all I have to do is just point to the file so I'm just gonna say this is Keys dot JSON and then this line over here creates the actual credentials with the Scopes that you define now with scopes here it's going to some sequel service admin we don't want those scopes our scopes are gonna be different they're going to be Google sheets scopes so I'm gonna just grab these and put them on top now if you're just trying to get read access to spreadsheets this is actually fine but I want to be able to write to the spreadsheet too so I'm gonna remove this read-only part and just keep this much and we're gonna be asking for access to those scopes and what the Scopes means if you ever got that Google message where you have to allow you may have seen like it lists different services it's asking access to so that's pretty much the same thing we're doing here we're saying this is the type of service we need access to and this is basically our credentials to allow that access now let's run this line and make that happen and we're going to be using this service account file which is this and we're gonna use the Scopes that are basically listed over here as a list or array however you want to call this over here so good enough so this basically this few lines will generate all the proper credentials we need we're gonna now change the rest of this code that they did so they have the spreadsheet ID they have the sample data range so I'm just gonna get rid of this date of range and I will keep the spreadsheet i didö so to get the spreadsheet ID we need to go to our spreadsheet copy that go back and put it in here and remove all the other parts of the URL that are not the ID so that's basically going to go until this D slash so I'm gonna remove all of that the ID starts here and it ends right before the slash add it so that will be the spreadsheet ID we're trying to read and write too so this point we get to our main function I'm gonna remove this function it doesn't mean you shouldn't have it but I'm just gonna get rid of it and that means I'm also gonna scroll down and get rid of this part that runs that and now let's see what changes we're gonna do here so first of all we're gonna set our credentials to none now we should have done this before we generate the credentials here so I'm gonna remove this line and I'm gonna go on top here and do it for this right above so I'm gonna set the credentials to none to begin with and then we'll set the credentials to the credentials we get from running this now the next thing we need to do well they're doing this they're saving the stuff in a pickle we don't need to do any of this so this if statement I have here to open the file to do the pickle whatever I'm gonna get rid of that I don't need all of these refresh tokens or any of this other stuff so I'm gonna get rid of all of that and we're gonna get to this point where it's a service equals build and because we're working with Python we cannot have this space indentation that's not matching T's so we have to make sure we move all of these back like this so they're on the same level so the next thing is gonna be to create the service so that's gonna run this build function and that build function is coming from here see from this Google API client discovery we're importing that build and that's where this is coming from so that build is gonna have sheets service to access Google sheets API we're saying we're gonna access sheets API the version we need to access is v4 and credentials we're gonna use to basically access that is gonna be well these credentials we've just created so I'm just gonna copy that credentials and put it in here now this is saying credentials the same credentials that's probably confusing so what I'm gonna do I'm just gonna do creds for all of these so that there is less confusion which one is which so we're gonna call this creds it's gonna be none then we're gonna generate and then we're gonna pass it here to actually get access to this Google sheets API service now at this point they're basically calling the API so first they're doing this sheet they're doing the service spreadsheet that's pretty much what we're gonna do and then here they take that sheet variable and basically do a get request to get some information from the spreadsheet and if we just look what's going on here see it's going the sheets dot values get and then we need to pass some arguments to this get method and the argument is gonna start with a spreadsheet ID which is this ID we have here and then the range we want to be able to read from the spreadsheet so this I don't have the variable for it anymore because I removed it I'm just gonna pass the string right in here so the range I need to read from my spreadsheet right here is from this worksheet called sales and the range is gonna be this from a1 through G 16 so I'm gonna go back here and do sales exclamation sign a 1 : G 13 so basically the same way you would do it in a spreadsheet you're saying from this worksheet sales we want this range from a1 through G 13 which is basically what I have actually it's 16 not 13 let me go change that like this so that point we're gonna get this results and basically from that results here what we're doing actually let me just comment this line for now and let me just get rid of all of this other code so let's just try to do this let's just try to print that result at this point so for those who are not familiar with Python print is similar to like a console log now I'm gonna remove some of the stuff that were imported on top that we're not gonna need so if you look here what we did we use this service account which we're using right here and the other thing we did use is this build function and that was coming from this import so all these other imports we're not really using so I don't need these I don't need all of these I just ended up with this condensed version where we're gonna basically just import these two lines and we're using one of them here the other one here so at this point let's try to run this to see how this works so I'm just gonna go here for me it's gonna be Python 3 because that's what I have installed and then this file is called read dot py so I'm gonna do read dot py to run that let's run this and see what happens so as you can see it basically returns an object and it says in this object there's this range and this is the range that we basically read the major dimension by default it goes like ROS so basically it's an array of arrays or in Python I guess that would be lists of lists that's pretty much what we have here under these values and that was the next line that we're doing here so basically it's saying in this next line if there are values then get the values and if there are not just do an empty list and that's pretty much what we're gonna do so we're gonna just instead of getting all of that we only need this list of lists which is gonna be the information from the spreadsheet so I'm just gonna remove all of that and now let's just print the values instead of printing the actual result that we're getting so now if I go back and rerun that same file see the result is basically gonna be it starts right after this command see a list of lists in JavaScript land that would be a ray of a race and that takes care of reading the spreadsheet now at this point you can take that list of lists and do whatever you want in Python land so you could take pandas and do something with this you can take numpy and do something with this if there's interest in more detail Python stuff I can do it but this one I'm gonna just keep about the API access so that gives us read access now let's try to write to the spreadsheet so for that we need to basically do this sheet dot values and instead of get we're gonna do I don't remember what the method was called so I'm gonna have to go take a look so let's go to our documentation and try to see where this is see there's references for API I guess down see there is get and there is update there's clear there's this app and which adds row we're gonna do update to simply just write to arrange basically so I'm gonna do this so update is the name so see the value input option that's what we need to provide and let's just scroll down see there is a Python example here and see that it creates the credentials same type of thing goes on here so to update we basically need to do this so I'm just gonna copy this and just go back to this and just paste it below will still read it I guess that's fine so this still gonna read the spreadsheet and we're gonna get values so in here I'm using see it's a service spreadsheet we already saved all of that in the sheet variable so I'm gonna remove all of this and do sheet dot values similar to here and instead of dot get we're gonna do not update and the spreadsheet ID is gonna be the same spreadsheet ID we have the variable for it so let's just use it the range we're writing to and let me just move this to the next line similar to that so now we need to say where we're writing it to so that's gonna be again in that assuming we're writing it to the same sales worksheet actually let's make another one let's make another one and write it to this one so this is gonna be called sheet 2 so it's gonna be sheet 2 now if this works the same way as nodejs works you don't have to do the whole range you simply just have to give it the starting point so if I do something like a 1 and give it a list of lists it will figure out how far it needs to go automatically I don't have to do a 1 through like B 4 you'll see what I'm talking about in a second as a matter of fact let's do like B 2 so you can have better idea how it works so now the next one is value input option so this again I have to go from the docs to see there are a few options here you can use so let's just go back to their documentation and see what those options are see this value input option if I open this see those are our options so raw user entered so and so basically this is how is that gonna be treated so the importance of that is let's say I have a value that's gonna be let's just create a list on top here so let's say we have this list and in this list we have this two column thing and we have this like one one twenty twenty and then the second column is let's say a number and then if we have another list we would go after this one and the third one so let me just enter some different dates and some different numbers so this is what I have now what we're basically saying is when we enter this value how do we want this to be treated so one of the options here see this user entered do we want that to be basically treated the same way if the user went in here and typed 1 1 20 20 is that the way we want this value to be treated when we're entering to the spreadsheet so that would be the user entered version of this and that would be C converted to this basically that's what happened here so that's what I'm gonna do I'm gonna do user entered because that would make sense and the other option there if you saw there was like raw so will not be parsed and stored as this so we're gonna do user entered and I'm gonna go here and basically I think as a string should be fine but I guess we'll find out if we get an error so that's gonna be value input options and then the body so it says value range body I'm not sure if that's just the list or there's something else let's just go and look at the docs so if I scroll down here value of range body and where is that defined here it's not really saying anything so I'm just gonna assume that's gonna be that range of data but I have a feeling like it might be different see value range let's open this and see what's in it see it seems to me like it could be like an object that has a values attribute that has the array and I think that's the way it was in no js' so I'm gonna go with that and if it doesn't work I'll just try the regular array so let's just do that so I'm gonna go back and here we'll just do an object and say that values is going to be this AOA which is our list of lists basically coming from here I think that's the way this is gonna work if it's anything like the node J s example so I'm gonna do this basically at this point I'm gonna try to run this to see if we're able to write this data to our sheet 2 let me just go back here around the same script so we got invalid syntax oh I got too many brackets here save this let's run it one more time so no errors we just got this returned back that's good news now let's go check if this data is in our spreadsheet so does not seem that way so maybe that was not correct what's interesting to me is that we didn't get any errors I want to just log out this request let's just see what the response looks like so I'm gonna save it let's see if it says the body is incorrect or something oh I think I know what I did see this is a request object I forgot to do this execute part so it didn't execute it okay so now I have this so now let's try this again so now it says what it says updated range this updated rose it seems like it gives us that it worked so let's just go and check this out and there it is so we got our data so as you can see I didn't provide from b2 through c4 I just said b2 in sheet 2 and then it just figured out where this list is gonna go automatically so that's kind of nice you can just give it a starting point and let it figure out the rest and there it was so this is basically gonna write to our spreadsheet it was apparently very similar to note J s and this will basically let us read from the spreadsheet and all of that we're able to do by generating this credentials from that JSON file using that service account thanks for watching please subscribe and I'll see in the next one

Channel: Learn Google Spreadsheets

Views: 61,209

Rating: 4.9600286 out of 5

Keywords: Google Sheets, python, api, read, write, data

Id: 4ssigWmExak

Length: 28min 0sec (1680 seconds)

Published: Tue Oct 06 2020