Python, Google Sheets API Tutorial | Read and Write Data | Aryan Irani

[Music] hey folks welcome in this video we're going to be taking a look at the google sheets api and how you can work around it using python so let's get started now step one for this video is going to be having a sample google issue so this is the google sheet that we're using that contains some name and the role number and score in two subjects the next thing is going to be going to the cloud.google.com the console and opening it up and this is what you will be seeing here the next thing that we're going to be we're going to be doing is creating a new project in our console so if you have if you if you come to the site for the first time you might need to log in and stuff but if you've already created projects all you need to do is go here and click on the new project button on clicking it you have to give a name to the project a suitable name so i'm going to say aryan python and you don't need to specify any organization you can just go ahead and click on create um okay you cannot add any character special characters it is it allows to be just letters so we're just going to go ahead and click on create you can see on the left side in a few seconds it's going to be saying creating project and the project will get create after creating the project which is going to go to the projects and we're going to select the project saying are you in python now we need to add the api so that we can work with the google sheet to do that you can either go to the navigation menu go to the apis and services click on dashboard or you can directly just go to go to apis and services and a few seconds you'll be seeing something like this now you want to add apis you can either go to the library button or you can just click on enable apis i'm going to click on this and here you can search for aps so the first api that we're going to be adding in this video is going to be the google drive api so i'm just going to type in google drive api and here you can see this is the api that we need to add so we're going to go click on enable and a few seconds the google drive api will get added to your project now that we have created the project and we have added the api it's time to get some credentials to work with the google sheet to do that we're going to go to the credentials part here and we're going to just click on create credentials so we need to create a service account using which we can interact with the google sheet so we're going to click on service account and here you need to give a name to the service account that can be anything so i'm just going to give it ran google sheets and you don't need to have a description you can just click on create and continue now you need to select a rule you're going to be working with a sheet so you might want to read data you might want to get data and you might want to make some edits in the data so we're just going to give it a basic one saying the editor so that you can make changes in your google sheet so we're going to just click on continue here and there's no need to give any users and roles and you can just click on done so now you've got the credentials here normally a json file should get downloaded into it but that's fine you can go to the you just you just click on the service account here and you need to go to the keys section using which you can interact with the google sheet so gonna click on add key you need to create a new key and you have to select the json and click on create and a few seconds a json file will get downloaded into your com the next thing that we're going to be doing is adding the google sheets api so that we can interact with the google sheet directly so to do that we're just going to go back to the just go back here go back to this to that to do that we just want to go back to the cloud platform we're going to go to the apis and services and we're just going to add the google sheets api by following the same steps going to enable and adding in the sheets api click on the sheet cpi and click on enable here and here the google sheet api has successfully been added to your cloud project here i'm just going to be adding the json file that we previously downloaded here i'm going to drop it in here and now i'm going to be using vs code for this video if you have used if you want to use any other editor it's perfectly fine so we're going to go i'm going to open the vs code here i'm going to go to i'm going to go i'm going to go to file i'm going to click create open the folder and go to python api select the folder and this is the json file that we're going to be using for this project so we're just going to go ahead and rename this to credits json the next thing that i'm going to doing is importing the g spread library to do that i'm going to open the command prompt and this is the command that you need to type in it's going to be pip pip that is going to that is used to install libraries in python then we're going to be doing g spread and we're going to do o auth club hit on enter right now it's showing the requirement already satisfied because i've already done this a few times previously but for you it will be showing you a lot of stuff ahead so we have successfully imported the g spread library into our project you can see that and i'm going to just close the command prompt and we're going to be taking the email address from here that is the client email and we're going to be sharing it with the google sheet so that we can interact with it from here so i'm going to go back to my sheet i'm going to click on share and i'm going to be adding the sheet link i'm going to click on send and now we can start writing our code so we're going to go back to our visual studio code i've already created a new file saying the sheets sheets dot p by and here we're going to start so i'm going to start out by doing import g spread that widget we just previously installed using the pip paper command and the next thing that i'm going to be doing is from the oauth client i'm going to be getting in this service account followed by importing the service account credentials so i'm going to be doing dot service account and i'm going to be importing the service account credentials so here the next thing that we're going to be doing is having some scopes here that we're going to be that is going to be used to interact with the sheet i'll be leaving it in the description you can go ahead and copy it from there if you want to type it you can do that too but it's going to be a little long one so i'm just going to go here paste in the scopes here and hit on enter now we're going to be writing the next part of the code that is really important the next thing that i'm doing is getting the keys from the json file using the scopes that we just declared and then we're going to be authorizing the credits and then we're going to be interacting with our sheet so to do that we're going to be doing credits we're going to create a new variable called credits in which we're going to be using the service account credentials and getting it from the json key file so you're going to be getting the account credentials from the json file so here i need to specify the name that's going to be creds dot json followed by these scopes that i'm going to be passing as an argument i'm going to pass in the scopes so the last thing that is going to be authorizing the currents here so we're going to be creating a new variable called client and we're going to be authorizing it so we're going to be using the g spread library g spread dot authorize and we're going to pass in the credits variable as the argument now we have authorized everything and we are ready to do it the next thing that we're going to be doing is getting the sheet so i'm going to be creating a new variable called sheet and i'm going to be doing client and i'm going to client dot open so we can open the sheet and i'm going to paste in here you need to give in the name of the google sheet so it's going to be student details and you need to specify the sheet number since this is a big spreadsheet and you need to specify so i'm going to be doing sheet 1 and this is going to be the sheet one here and the next thing that we're doing is getting all the data into one variable so that we can print it and check whether we've got the correct spreadsheet so we're going to be doing data i'm going to create a new variable called data and i'm going to do sheet dot get underscore all underscore records okay so this is it that is done now we're going to go ahead and print the data i'm going to print data and i'm going to click on run here you can see successfully the data has been printed from the google sheet into our vs code terminal so this means that we have got the correct sheet comparing the data if you say this is going to be name roll number score and match is going to be the same headers that we had here now that now the next thing that we're going to be doing is getting a specific kind of data say i want a specific row or a specific column from the sheet so i'm going to be doing create a new variable called row and i'm going to doing sheet i'm going to be doing sheet dot get the row so it's going to be row values and i'm going to pass in say i want the third row of data i want this one joy 7 630 you see remember it i'm going to be doing 3 and i'm just going to comment this one out here i'm going to print row and i'm going to hit say just going to clear this out and i'm going to run here you can see j7 6 and 13 has successfully been printed the next thing that we're going to be doing is printing a specific column so i'm going to create a new variable called call and i'm going to do sheet dot row so it's going to be called sorry gonna be call underscore values and say i want the i want the second one i want to i want all the roll numbers so i'm going to be doing second i've specified the second column the next thing that i'm going to be doing is printing it i'm going to do call and i'm just going to comment this printing the row i'm going to comment it out here and i'm going to click on run and here you can see roll number three seven two four five five has successfully been printed so we've got the specific value so the next thing that we're going to doing is printing a specific cell out of a column i'm going to create a new variable called cell and i'm going to be doing sheet dot cell and i'm going to pass in say i want the word roll number here so it's going to be the first column it's going to be first we're going to be passing out the row comma column so it's going to be the first row it's going to be the second column so i'm going to specify 1 comma 2 here and i'm going to get dot value and i'm going to print the value saying print cell and i'll just clear this out and i'm going to click on run and here you can see roll number the word that we were expecting has successfully been printed in our terminal the next thing that we're going to be doing is changing data it's going to be inserting data or deleting data from the google sheet so now we're going to be creating a list i'm going to be saying insert row i'm going to create a new variable called insert row value it's going to be just insert row and here i'm going to be creating a sheet so here i'm going to be creating a list i'm going to first specify the name it's going to be z comma i'm going to specify the the second that's going to be the roll number score into subject so i'm going to say it's his rule number is eight and yes code fifth is gonna be scoring 15. and he has scored say 15 and once again in spanish here we have created the list that we want to use the next thing that we're going to do is sheet dot insert row is going to be the function that we're going to be using is going to be insert row here you need to specify the data that is the values that you want to pass followed by the index of the row so i'm going to follow i'm going to pass in the insert row com variable followed by say the row that i want to do that is the index see i'm going to be adding it in this one it's going to be the eighth one so i'm going to add i'm going to it and save i'll print and confirmatory message say the row has been added you see row has been added so i'm going to clear this out i'm going to click on run this and you can see the row has been added has successfully been printed and you can see then 8 15 15 has successfully been added to our google sheet now that we have added data it's time to say delete a row out of the sheet so to do that i'm going to go i'm going to say you don't need to specify any list of data something you just need to go ahead and do you need to specify the command that is going to be sheet dot delete underscore row and here you need to pass in the index that is going to be the row number say i want to delete the fifth row that says question 4 21 and 34 so i'm going to write in the fifth one and i'm going to print another confirmatory message saying the row has been added now hit on save gonna clear this out i'll just comment the previous lines of code here and i'm going to click on run and here you can see this the the row has been added and here you can see that the row has been deleted from our google sheet in this video we have covered accessing reading and writing data in google sheets using python i will be covering more functionalities and things that you can do using sheets api and python so this is all for this video i hope you understood how to read and write data in google sheets using python if you like this video then don't forget to like share and for more content hit the subscribe button i'll see you in the next one you

Channel: Aryan Irani

Views: 363

Rating: 5 out of 5

Keywords: python tutorials, google sheets python, gspread python, google sheets, python google sheets api tutorial, google sheets database, python google sheets, google sheets api, Google Sheets, python, api, read, write, data, Python, Google Sheets Python, Learn Google Sheets API in Python, Google Sheets API Python tutorial, Aryan Irani

Id: 7dGPLKD-FeM

Length: 14min 48sec (888 seconds)

Published: Wed Aug 11 2021