Google Sheets API Automation in Python

what is going on guys welcome back in today's video we're going to learn how to use the Google Sheets API in Python so let us get right into it [Music] alright so we're going to learn today how to use the Google Sheets API in Python and this can be very useful for automating all sorts of different tasks especially when you have multiple people accessing the same files at the same time it makes sense to use Google sheet because it's synchronized you can see what the other people are doing in somewhat real time and the same will also apply to the bot that we're going to write in this video today whatever it does we're going to see the updates in somewhat real time so let's say for example you have a simple spreadsheet you have some basic columns with data for example number one number two this is quite trivial here we have a results column and maybe we have a status column so if we have some values here like that whatever they are we might want to have a script that takes these values does some calculation with them maybe a simple addition maybe something more complex and stores the result in this column here and maybe it also updates the status from not done if we have not done here being the default it's going to process the calculation whatever it is it's going to put in some value here and it's going to set this to done and whenever it does that we're going to see what it does in somewhat real time we're going to be able to track the progress even if the script is running somewhere else in the server in a Raspberry Pi somewhere we can use our computer to monitor this spreadsheet and to see the updates when they're happening now this is a very trivial example something more reasonable something more complex would be for example to have a column URL maybe we have company URLs and then we also have a column CEO name and this column is empty here we have some arrays uh not a race some URLs we're going to just write a here uh one use case would be go to that URL do some web scraping extract the CEO name store it in the Google Sheets file so for example Mike Smith uh and then we can also have here a status and we can set it to done or processed so automations like this can be done with python with the Google Sheets API and this is what we're going to learn in this video today and we're going to do that by using the python quick start guide from the Google documentation we're not going to just follow it I'm going to do the explanation myself what but we're going to use this here uh in order to click on certain links because we don't have to look for the sections then manually you will find a link in the description down below to this documentation and of course some prerequisites you have to have python installed you have to have Pip installed and what we want to do here is we want to create a Google Cloud project so you want to follow this link a Google Cloud a Google Cloud project you want to click on it this will lead you to another documentation and here you want to click on the button go to create a project and when you're here you're just going to choose a project name for example let's call this neural nine sheets project and I'm going to just create this here and then this you can see here it's creating the project I have a lock here of some test project that I that I did for um in order to prepare for this video but once you have this project now the next thing we need to do is we need to enable the API the Google Sheets API for that project so um you go to that project you select the correct project in my case this one now the neural 9 sheets project and then you want to click on API here on the left now maybe you have it already pinned I think I unpinned it for some reason but then you just go to more products and um then we can see here apis and services enabled apis and services and when we click on that we should be able to go up here to enable apis and services and then we can look for the Google Sheets API which is this one here so we click on it and then we click on enable this allows us now to access the API or at least for this project that we're going to use the API is now enabled in order to access it we need to do some authentication first but first of all we need to enable it once the API is enabled we're going to go here still under apis and services to the oauth consent screen in order to set up this uh consent screen we're going to click here on external meaning everyone can use this application with a test account um if we or any test user can use it with a Google account and we need to set the test users manually so we're going to create that we're going to give it a name for example neural nine Sheets app I'm going to use my basic Gmail here as as a support email and down here we're going to also use the same email for the developer contact information I'm going to save this and then what's important here is we need to provide scope Scopes you could say our permission so what are we going to be able to do with that API we need to set the permissions here and for that we're going to click on add or remove Scopes and we're going to look for sheets and the one we want to use is the Google Sheets API so auth slash spreadsheets not read only because we also want to be able to edit stuff so we're going to pick this one we're going to update we're going to to scroll down here to save and continue we're going to click on that and now we need to add ourselves as test users so if you want to use this um yourself you're going to have to add yourself if you want to have other test users you're going to have to add them here as well I'm just going to add myself now by saying Social at neural nine.com and then I add this user save and continue and then we have this done so this is now the consent screen and now we need to also get credentials for that consent screen so we're going to go here still under apis and services to credentials and we're going to click up here on create credentials and what we want to create now is an or client ID so the application type is going to be a simple desktop application you can call this whatever you want again neural nine sheets client for example create and then you can see here the credentials now what we're going to use for this video today is we're going to download this Json file so you want to click on download Json and you want to store it in the directory of your of your project so I'm going to go to programming neural 9 python current this is where I'm going to be working and I'm going to call this file credentials.json so credentials.json and once this is done actually this is the whole API process it was a little bit complex but um now we're done with that the rest is just the python code so with this um with this setup we can now go ahead and use the Google API the Google Sheets API so we're going to go into our development environment again and first of all we need to install a couple of packages if you don't have them already for example um because you have already used Google Drive API or something like that what we're going to do is we're going to open up the command line and we're going to type pip install Google Dash API Dash python Dash client then a new package Google dash off Dash HTTP lib2 and Google Dash auth Dash oauth lip like that those are the three packages that we're going to need I already have them installed so if I run this you should see a requirement already satisfied nothing is going to change here in your case it's going to install these packages so once we have everything installed we're going to import the core python module OS we're going to need that here in a second and we're going to import from Google Google dot auth.transport dot requests we're going to import request with a capital R from Google dot oauth to dot credentials we're going to import credentials with a Capital C from Google underscore auth underscore oh off lip dot flow we're going to import installed installed app flow uh did I mistype something or why isn't it recognizing that I think that's fine I think it should work even if it underlines this here and then from Google API client we want to or actually from Google API client.discovery we want to import the function or the method build then from Google API client dot errors we want to import the HTTP error so those are the inputs let me just run this here to see if it works uh no module named Google auth oauth 2 ah sorry not oauth 2. that's the reason oauth lip is the actual name so now it works those are the Imports and what we want to do now is we want to define the Scopes so remember we had the Scopes uh what are we allowed to do the scope was this spreadsheets scope that we um that we added to the project so what we want to do first year as a constant is we want to create a list with Scopes you can also have multiple Scopes uh the one we want to use is hdp or https actually colon slash slash Google apis.com auth slash spread sheets and if you add dot read only you don't even need to add this to the project because by default you can just read the spreadsheets but by using just spreadsheets so without dot read only we're also able to edit stuff so this is the scope uh what we also want to do is we want to provide a spreadsheet ID so when we go back here to the spreadsheet I can call this uh something but what we actually need is we need this ID here so spreadsheets slash D then this is the ID so I can just copy that I can go back into Python and I can say spread sheet underscore ID is going to be that um and then we can start with the main function by the way of course this code is inspired by the quick start guide here so we also have some similar code here you can also use that if you want to copy it I'm going to have it slightly different but basically it's going to be almost the same we're going to define a main method here or a main function we're going to set the credentials to none by default and we're going to say if OS path exists so if the following file exists token dot Json if that file exists we don't have it yet we're going to just load it we're going to say credentials equals credentials from authorized user file token dot Json with the Scopes that we defined so this is just loading the credentials from the token file which will be created when we use the credentials file so we use the credentials file we'll log in we um we we give to certain or we give the respective permissions to this application to this API call and then we have this token so we don't need to do it every time so we're going to say here if not credentials or not credentials on ballot so if we don't have credential credentials or if those credentials are not valid what we're going to do is we're going to say if we um if we do have credentials and credentials expired in credentials dot refresh token we're going to say credentials dot refresh and we're going to call this request here all right um so basically if we don't have credentials or they're invalid we're going to say if we do have credentials so basically this means that we enter this if block because they were not valid and if they're expired and we can refresh them we're going to just refresh them here otherwise we're going to just say flow equals installed app flow this is a little bit of boilerplate code here that we need in order to be able to do anything this is not any um any sheet logic this is just authentication with the Google API it's a little bit tedious so from client Secrets file here we're going to read it now our credentials.json we're going to define the Scopes as well and we're going to say that the credentials are equal to flow.run local server Port equals zero and then whatever we did if we have the credentials now we're going to say open a new file token.json in writing mode as token and we're going to say token right credentials to Json so again just for you to have an overview we Define the Scopes that we want to have writing reading spreadsheets we want to define the respective spreadsheet that we want to access this was not relevant up until now or it was not relevant yet here we have the main function what we do is we set credentials to none if we have already a token that we can use to authenticate we're going to just load it we're going to just get the credentials from that token otherwise if we don't have any credentials or they're invalid we're going to distinguish the two cases that we have credentials and they're expired or that we don't even have credentials and here we're going to just refresh them here we're going to open up this uh we're going to load the credentials file we're going to authenticate ourselves and then we're going to create this token Json file that did not exist before so that's just the authentication process what we want to do now is we want to say try and we're going to also add an accept block here I'm going to pass for now what we want to try is we want to get the service we want to access the spreadsheet service and we want to then access the particular spreadsheet file that we defined up here with the ID so we're going to say here service equals built this is the method that we already imported built sheets version four and a credentials are going to be equal to credentials and sheets is going to be service Dot spreadsheets so this is the actual service that we're going to use and now what we can do is we can just from this file here let's add some information let's remove all this here let's just say we have one two three four five six maybe then we have maybe 10 10 20 30 40 50. come on 40 50 60 and then maybe we have something like a b c d e f like that now what we want to do is want to just get these values we want to see if we can extract them from the file into python so that we can process them for example so we're going to say result equals sheets dot values to get values and we're going to get the following values first of all we're going to define the spreadsheet Brett sheet ID like this in camel case essentially or not exactly what is this case starts with a lowercase but then it's basically Pascal case I forgot the name but the spreadsheet ID is equal to the thing that we defined so it was this spreadsheet ID and the range is essentially even though this is a reserve python keyword here now the range is a keyword parameter meaning what area do we want to access and first of all we need to provide the sheet name so in our case this is sheet1 you can also rename this so sheet1 exclamation mark and then the cells so for example A1 colon and I think we had C6 yeah A1 to C6 which is exactly this section here so this is what we want to have and what I'm going to do now is I'm going to just take this and I'm going to execute this command so we do sheets which is the service for the spreadsheets we get the values from this spreadsheet ID this is the range that we're interested in we're executing the whole thing in order to get the actual values what we do now is we say let me just scroll to the left here values equals result dot get values as a string and we're going to pass an empty list here and now we can just say for example four row in values print values and if I did everything correctly first of all we're going to do the exception here probably properly except HTTP error s error we're going to print the error there you go and of course we need to call the main function so if underscore underscore name underscore underscore equals this underground is for main underscore underscore and then we're going to just call the main function so let's run this and see if it works it opens up here uh what's the problem here some requested Scopes were invalid why is that did I mistyped something let me just scroll back up uh Google apis.com maybe I forgot the www maybe I need that as well let's see now it works okay so I'm going to choose your neural 9 which is my account here it's important you don't want to go back to safety you want to click continue uh you're going to allow this application to access the spreadsheets now it has completed and here you can see now uh actually I want to not print values but the actual row so if I run this again you can see now I don't need to authenticate again I can just rerun this because I have this token Json file and here you can see uh the respective rows so this is now just a theory how can we use that for example to do some calculations and how can I also write to cells for this we're going to slightly change this so that we can get information from one column I'm going to do this here by using the example from before so I'm going to say number one number two results and Status we're going to have some values here like this I'm going to have some values here the results column is going to be empty and here we're going to have not done so this is now our file can I zoom in there you go and we want to now take these values so we want to take now number one number two we want to add them together we want to put the results here and we want to change the status to done instead of not done so what we're going to do first of all is we're not going to access a range of values we're going to access specific cells and we're going to do it by using a row count so I'm going to say here four row and I should remove this then four row in range and since the values start at uh Row 2 I'm going to start from 2 up until 8 because if I go up until 8 7 is going to be included 8 is not going to be included so I'm going to say 4 range to 8. and this is just going to be a number now and what I want to get here is number one being equal to Sheets values get and then here from the sheet I just want to get a whatever the row is at the moment so I'm going to use an F string here range equals F string sheet 1 exclamation mark a and then in curly brackets Row the respective number um I'm going to do the same thing for number two the only difference is that I'm going to use column B um and then I want to get the result so I want to say result or maybe I would want to say uh calculation result is going to be number one plus number two uh and what I want to do now is I want to enter this value in c row how do I do that well first of all I want to print processing num1 Plus num2 and I want to make this an F string so that we have some log message so that we can see that's actually that it's actually working in Python and working in Google Sheets at roughly the same time and then what we want to do is we want to say feeds.values dot update so now we don't want to get values we want to update values and we still provide the same spreadsheet so I'm going to copy that we still provide also the same range so this stays essentially the same I'm going to change this to c now um but what we want to do now is we want to also specify two more things we want to specify that the value input option is going to be user underscore entered in uppercase and we want to say that the body is going to be equal to a dictionary where values is set to and then two lists inside of the two list the actual value is going to be in our case now um an S string a not a sorry num1 plus num2 I'm not even sure if we have to turn this into a string um but what's important here is that we cannot just go ahead and get the get the cell value because we need to get first of all we need to get the actual value then we need to go uh deeper into the list because we're going to to get a list of lists the same way that we get it here so essentially this get function here will return um the same structure as this body object task here so we're going to have a dictionary so basically what I need to do here is I need to say uh execute and then from that execute I need to get values to basically get this key value pair so I'm going to get that and then since those are two lists I need to also um type index 0 and index 0 again get values index 0 Index 0. this is just how the API is structured and all this needs to be turned now into an integer so that we can do calculations and not just string concatenations so I'm going to say integer integer um and then we have this calculation result and what we want to put in here now is actually the calculation result so that is the actual um updating of the results column now we also want to copy this we want to paste this we want to change this here to D row instead of zero and we want to change this to done and we don't need that I think this is actually enough so let me just see if that works processing processing processing and nothing is happening why is that maybe I forgot to execute I think I forgot to execute so we need to say execute in the end here to actually make the change but now when I run this there you go I'm doing nothing you can see that python is updating uh The Columns and I can see that I can monitor that process I can track that process in basically real time so this is quite useful if you have this running on a server maybe you have some complex web scraping um or image editing tools so sorry so maybe you have a data set where you have name of a person and then you have the URL to image of that person and you want to extract certain things like estimated H or estimated gender or estimated uh I don't know whatever comes to mind so you can just process different things that cannot be processed in the spreadsheet so I cannot go ahead and say equals and some formula that will extract using machine learning the age from an image of a person but I can pass that URL into python I can extract it with this Google Sheets API I can process it I can get the estimations and I can put them in here and I can mark this row using a status column again it's done this is quite useful and when many people are working on the same file using this with the Google Sheets API might make more sense than just automating Excel all right so that's it for today's video I hope you enjoyed it and hope you learned something if so let me know by hitting a like button and leaving a comment in the comment section down below and of course don't forget to subscribe to this Channel and hit the notification Bell to not miss a single future video for free other than that thank you much for watching see you in the next video and bye [Music]

Channel: NeuralNine

Views: 83,415

Keywords: google sheets, python, python sheets, python excel, python google sheets, google sheets api, python google api, google api, python google sheets api, python automate google sheets, python automate excel, python google sheets automation

Id: 3wC-SCdJK2c

Length: 26min 35sec (1595 seconds)

Published: Thu Mar 23 2023