Automate Google Sheets With Python - Google Sheets API Tutorial

this video will show you how to automate Google Sheets with python specifically how to connect to a workbook how to create and open existing sheets how to modify the cells how to add formulas how to do formatting and we'll go through a lot of different nuances and some ways to more efficiently perform these operations at the end of the video I'll even walk you through a mini project that will illustrate how to take an array of different values add those into sheets add some headings create some formulas Etc with that said let's get into into it after a quick word from our sponsor nordpass nordpass provides the ultimate password and credential management solution in today's digital age we've got all kinds of passwords Keys credit cards and credentials and I don't know about you but I always have trouble finding them and actually sharing them with other people now to combat this nordpass not only stores all of this information securely but it lets you autofill it across multiple devices and share it with colleagues or employees with Advanced Access Control now what that means that you can actually use strong passwords by default and easily Grant or revoke access not to mention that if there are any security vulnerabilities Nord pass's data breach scanner can automatically detect those and inform you of them immediately now if you don't believe me you can try a three-month free trial of nordpass using my activation code techwithtim at the link nordp pass.com techwithtim now password management is an absolute nightmare when it's not taken seriously and that leads to Stats like this where 52% of development teams have actually missed deadlines due to issues accessing infrastructure get your business secured today with a 3-month free trial of Nord pass so that you're not a part of that statistic you can go and use my activation code Tech with Tim at the link nordp pass.com techwithtim for this limited time offer let's begin automating Google Sheets now in order to do this we're going to need to go to the Google Cloud console so to do that we're going to just search Google Cloud console we're going to find the url that is console. cloud.google.com once you click into this here we're going to create a new project now before we do that just make sure you select the correct Google account in the top right hand corner where you want this project to be created let me go ahead and do that all right so once you've selected your primary Google account you can go up here where it shows the project list and you can simply create a new project you can see I have a bunch of them here uh in this case we can just call this something like tutorial sheets doesn't really matter Call It Whatever you'd like you can select organization if you know what that is if not just go ahead and click create from here it's going to create the new project for you once it's created we're going to select that project from the project selector and then I'll show you the next step so here select project okay we're now inside of this project now we're going to open up the navigational menu here and we're going to go to apis and services from this menu we're going to click on enabled apis and services there's many ways to get to this but you just want to get to enabled AP apis and services from here we're going to click on this button that says enable apis and services and we're going to search for the Google Sheets API by the way if you're ever trying to do any automation with Google pretty much all of the apis are available here so we're going to search for the Google Sheets API you can see it will pop up right here click into it and then we go ahead and click enable that's pretty much it we're just enabling the Google Sheets API now what we'll need to do is create a set of credentials that will utilize the API that then we can have access to in our project so once you've enabled the API you should be brought to a page that looks like this now for some reason you can't find this page you can always just go back to the enabled apis and services you can go back here you can search for Google Sheets API again click into it and then simply click on manage and it should bring you back to this page regardless you can press this button right here which says create credentials or you can select the credentials menu down here and then create create credentials there's a bunch of different ways to do this so we're going to click on this big blue button here for creating the credentials now it's going to ask us what type of API we're using we're going to select the Google Sheets API and where we have user data or application data we can simply select application data that'll bring us to next and this is where we're going to create a service account so what we need is a service account so what we'll do is give this a name I'm just going to call this python API like that and it's going to go ahead and give us a service account ID we're now going to go ahead and click on Create and continue and we're going to Grant this a role now this step is very important make sure that you give this the editor rule so you're going to search for editor select editor here the ID doesn't matter it can be whatever you want you just want to make sure you have this as an editor you're then going to click on continue and from here you can go ahead and click on done all we're doing is creating a set of credentials that we'll we'll be able to use sorry from our python script that will be able able to edit things like spreadsheets great now from this point on what we need to do is actually download the credentials so we're going to go to the credentials tab here we're going to click on the service account that we just created and it's going to open it up for us now here you can actually modify the account if you want but what we want to do is go to keys from keys we're going to click on ADD key and we're going to create a new key now we're going to just download a Json version of this key which we'll be able to load in our python script so go ahead and select Json and click on create it's going to download that most likely into your downloads folder and what we need to do now is get this in the same project as our python file all right so I've just opened up visual studio code and you can see that I have my finder open here if you're on Windows obviously this will be Windows Explorer now what you're going to want to do is find the file that was downloaded so whatever location it went to and just take it and put it in the same project where your python file is going to be so just to backtrack a little bit what I didn't vs code here is I just open up a new folder it's called Google Sheets tutorial okay so just go file open folder open any new folder can be on your desktop wherever you want it to be I created a python file called main.py and now my goal is to get the file I just downloaded so this one here into the same directory now to do that I can simply take it and drag it over here in vs code I also could simply copy it and then locate the folder on my desktop and then paste it in I'm assume many of you are able to do that just get in the same directory as your python file now it's going to have some information here obviously you don't want to leak this I will clearly delete the project after this tutorial you don't need to know what any of that means what we're going to do next just to make our life a little bit easier is we're going to look at this file zoom in a little bit here we're going to rename it so I'm just going to call this credentials like that so that it's a little bit easier for us to reference in our python code all right so now that we have our credentials in the same folder as our python file the next thing we'll need to do is actually link the Google sheet that we want to work with with the service account that we created and you can do this with as many Google Sheets as you'd like but obviously you need to have ownership of them so what I'm going to do is transition over here and you can see that I have a Google sheet that I've created it's just an empty sheet I simply put one test value here in the first um kind of column first row what we'll do now is we'll find the email address of the associated service account and we'll simply share access of this Google sheet to the email address so if we go back here to our Google Cloud console you'll see that we have our keys and we have our service account so I'm already in the service account but again if for some reason you can't find it what you're going to be doing is going into the Google Sheets API and you should see a service account here and it should actually specify the email for the service account so what we're going to do is copy that email you can also click into it here and it will show you the email for the service account that you created so we're going to get that email we're going to go to our Google sheet we're going to click on share and we're going to paste that email inside of here now we're going to make sure that we have editors selected as the access and we're simply going to share this Google sheet with the email associated with our service account now you can do this with any number of Google Sheets that you'd like as soon as you do that anyone who has access to the service account which in this case is us and our python script will be able to modify and access this Google sheet so there you go again take the email share it from the Google sheet now you're good we can go back to s code and we can start working on connecting to this Google sheet so now that we've created and shared the Google sheet what we need to do is install a few packages that we need to actually interact with this now to install these packages we can do it just in our main system or our main python installation or we can create a virtual environment which will make things a little bit neater and the virtual environment will be a bit different depending on your operating system but if you're on Mac you can type python if we could spell this correctly Python 3 Hy an M ven V and then the name of the environment you want to install this in which can be something like sheets you can name it anything you'd like if you're on Windows You're simply just going to have python if you're on Linux it'll be Python 3 so we're going to hit enter here and it should go ahead and create the virtual environment for us now that we have the virtual environment created we need to activate this now there's a few ways to activate it if you're on Mac or Linux you can type the following command now this is going to be Source the environment name which in this case is sheets and then bin and then activate now when you type that you should see that you get a prefix here with the name of your environment and now in this terminal we have access to the virtual environment now if you are on Windows the command's going to be a little bit different what you're going to want to do is make sure you're inside of Powershell and you're going to type dot SL sheets SL bin SL activate so same thing here okay but you're just having the dot slash and this should run this file as an executable and uh initialize the environment for you you okay so now that the virtual environment is initialized what we can do is install the different modules that we need so we're going to type pip 3 install and then the first one we need is the Google API python client and I will leave these in the description in case you just want to copy them now as well as that we also want to have the Google and then this is the o- HTTP lib 2 and then we want the Google off dash and this is the o off lib and then lastly we want g-spread okay so these are the four libraries that we need Google API python client Google o HTTP lib 2 Google o oo lib and g-spread again I will link this exact command in the description so you can simply copy it and paste it we're going to go ahead and hit enter and install all of these different libraries if you are on Windows you might note here that you can actually just use pip instead of pip 3 okay so if pip 3 doesn't work just do pip and that command hopefully should work view okay so now everything should be installed in our virtual environment and the virtual environment is activated I'm going to clear the screen minimize the terminal and we'll start typing now and actually getting access to our sheet so we're going to begin by importing something called g-spread and let me just zoom in a little bit here so you can read this now g-spread is a library that makes it easy for us to actually interact with the sheet this is kind of a highlevel API on top of the lower level operations you can perform on the Google sheet so that's what g-spread is next next we're going to say from Google do o if we could spell this too oo 2. servicecore account we're going to import the credentials in case you need any of this code I will link it uh in a GitHub repo in the description and what we're going to do next is we're going to specify some Scopes now Scopes are the different things that you can do kind of the operations or the access you have when you're accessing a specific file document Etc using the Google apis now in our case we just want access to a spreadsheet so we're going to say Scopes is equal to a new list and inside of here we're going to write out the following scope I'm actually just going to copy it in so I don't get any typos again this will be linked in the description so this is the googleapis.com sloths spreadsheets now what we're going to do is create a new set of credentials using the Json credentials file we have here in our directory so we're going to say creds is equal to credentials. from undor service account file okay and then we're going to type the name of the file which is credentials. Json matching up exactly with what we have here now when we do that let me just remove this here we are now going to specify that Scopes is equal to the Scopes that we have here saying okay we want to use the service account and these are the Scopes we want with the service account for what we're accessing now that we have that we are going to authorize ourself so we're going to say client is equal to g-spread we got to spell that correctly do authorize and we're going to authorize the credentials that we just created that's going to give us a client now that will be able to access different Google Sheets all right so now what we're going to need is access to the ID of the Google sheet that we want to access so we're going to say sheet unor ID is equal to an empty string and we are now going to go back to Google she sheet so let me open up this file right here and we can find the ID now the way you find the ID of the sheet is you simply copy the URL here you look for where the slash D is I'll zoom in so it's a little bit easier to see and then we will highlight everything that comes after the slash D but before the edit okay so after the slash D this is the ID of this document so you're just going to copy that there from the URL and we're going to go back and we're going to paste this in our sheet ID now that we have the sheet ID we can use this to actually open the spreadsheet to do that we can say sheet is equal to client. openen by and this actually not URL sorry this open by key and then we can simply pass in here the variable sheet ID now I will note there's a few other ways to open this you can open by URL open all you can open by a name you can open by the key which is what we're doing the key is going to be the most foolproof because you're just using the ID so for example if the name of the spreadsheet changed you're not going to have an error here right CU you're using the actual ID so what we can do just to make sure this is working is we can say values unor list is equal to this is going to be the sheet and then do sheet one. row underscore values and then we can simply pass in one here now you might not know exactly what this is doing but I'm just going to print the values list and if we go back to our sheet here you'll see that we have a test value in the first set cell so what should happen when I run my code is it should simply print out that test value so let me just clear the terminal here notice what I did right is I have my sheet I'm accessing the worksheet within this Google sheet or this Google workbook the sheet is called sheet one okay and then I'm getting all of the row values in row one and I'm printing those out so now if I go here and I type Python 3 main.py that's how I'm able to run this file you should see that we get some warning that's fine don't worry about that and it should print out the test value for us and it does it says test value inside of a list telling us that this is indeed working so just to prove to you that this is really working we can go add another value here so we can say value two we can save this sheet notice that it's been saved now to the drive and if we go back and we rerun this we can wait a second and we should now see that we get two values appearing here and we do we get test value and value two so we have successfully connected to our workbook or to our Google she sheet now I want to show you a bunch of different operations that we can perform on it all right so I've just opened a piece of documentation here that's going to be very useful to you and is exactly how I learned how to do this now this is just the official documentation for g-spread which is the module that we're using and it pretty much shows you everything you'd want to do from creating sharing selecting worksheets formatting changing colors Etc so obviously I'll walk through some of these operations with you in the video but if you want something specific that I didn't cover you can go to this link which will be in the description for example we can see how to select a worksheet so we can see that we have the ability to get a worksheet by its index so just to be clear here the worksheets are what are down here right so we have only one worksheet but we could create another one like sheet two and now this would be index one where is this one would be index zero so that's what it means by kind of selecting the worksheets you can also select one by its name and you can just reference the name like this so sh. sheet1 if it's called sheet one you also can get a list of all of the worksheets you can create a new worksheet by adding a worksheet with a title and the number of rows and columns you can delete a worksheet you can update the name and the color Etc so what I'll show you now is just how we update cells so if you kind of basic operations here so let me go back to vs code and then we'll just work on a mini project that will kind of bring all of this full circle so first let's list out all of the worksheets that we have let's then select one and then we can just add some cell values and kind of read in what we have so let's go like this and say that this is actually going to be our workbook just so it's clear and we're going to say sheets is equal to the workbook Dot and we can just say what is this work sheets like that then we can simply print all of the sheets and if we go here and we run our code again ignore the warning doesn't really matter for right right now you see that we get worksheet sheet one and then worksheet sheet 2 they both have an ID and they also have a title now let's say we want to get all of the titles so we don't want these worksheet objects what we can do is the following we can actually put a map around this so we can say map and then we can put a function like Lambda we could say Lambda xx. tile this just means we're going to apply this function to every single value inside of this list and then what I can do is I can just print out the list version of my sheets and you will see that if I run this here let's make it a little bit bigger it should give us the titles which it does of sheet one and Sheet two so that's how you see the different titles now that you have the titles you can select a specific sheet so we know what the titles are so we can say sheet is equal to our workbook Dot and then worksheet and then we can simply use one of the titles to select one so we can select sheet one and then what we can do is something like sheet Dot and maybe we want to update a cell so I'm going to say update and then notice we have a few options here right we have index Note Tab title Etc maybe we want to update the title of this so let's update the title to be hello world okay let's go ahead and run our code and give it a second here and we should see now if we go back here that the title of sheet one is hello world okay so that's how you update a sheet now once you're inside of a sheet so now we're actually going to select the sheet that has the title hello world cuz we've changed the title right if you want to update a cell in there you have two options you can use the update a cell now when you use aell this is going to take a notation like in all capital sorry A1 or B2 or B3 or something like that right where you have a character or you have a letter and then you have the corresponding column I guess or no no sorry row whereas if you use cell you're able to actually reference the row and the column that you want to access using numeric values which is a little bit easier when you're working in Python so in this case I can access one one which should be the very first cell rather I can access 2 one so if we go and do one one we can say something like Hello World this is changed and now if we clear and run our code you'll see see in a second once this finishes that we will update a value in the cell so if we go back here you can see now that we've updated that very first cell and it says hello world this is changed okay so again you have those two options you have the option to update aell where now you're only going to pass one thing so you can pass say A1 or you can do cell where you're going to pass the row and the column that you want to update now let's say rather than updating a cell you just want to actually get the value of a specific cell well in that case we can say value is equal to and then it's going to be our sheet Dot and in this case we can type just cell or asell okay so same thing before and we can do something like a1. value and then if I print out the value here so again let's go clear and run you should see that we get the value which in this case is Hello World this is changed okay so again you have access to Asel or simply cell and then you're going to use value and it's going to tell you the value that's inside of that cell now it's possible that you may not actually know the location of the cell that you want and instead maybe you know that it has a specific value so maybe the value is something like Tim is great so you know there's some cell on the page and it has some string value right but you don't know where it is and you may want that location because some other information may be relative to that location so in this case what we can actually do is we can find a Cell by its text so what I can do is I can say cell is equal to sheet sheet do find let me just remove all of this here and we can put in here whatever the information is that we want to find so in this case it was Tim is great and then what I can do is I can print out some properties of this cell so I can say cell. row and cell. column and now if it finds the cell that we're looking for that contains this value we can get some information about it right like the row and the column so if we go here again and we clear and we run you should see that we get that information and we get 1 three okay so it located that cell and it told us well it's in row one and it's in column 3 so now we can use that information to maybe access or update another cell last thing that I'll show you here is that we can actually perform some formatting and then we'll get into just a tiny little project that will kind of illustrate more about how to do this and work with Google Sheets so let's say we want to actually just format the very first cell and we want to make it something like bold text well to do that we can simply write the name of our worksheet so something like sheet do format then in our letter number notation so something like A1 we're going to specify the cell or you can actually specify a range of cells so I can do something like A1 to B1 that would then modify two cells in this case we'll just do A1 and we're going to specify the text format if we could spell this correctly to be equal to and then this is going to be bold colon true you can imagine there are a lot of other formatting things you can do this is probably just the most simple one and now if I go and I run my code we'll see in a second once this finishes executing that if I go over here we now have bold text in cell A1 I should probably zoom in a little bit so it's easier to read but you guys get the point okay that's how you perform the formatting there is like a million other things you can do with the formatting and I'm actually just going to quickly open up a link that's going to be a little bit helpful if you want to do some more advanced formatting so I'm going to leave this in the description but there is actually a module called g-spread formatting which lets you do some much easier formatting using python in this case you can see we can actually create like a more advanced formatting with horizontal alignment text format background color Etc and there's all these cool things that you can do I'll let you guys reference that if you want from the description so if you're going to do a ton of formatting definitely check this out otherwise what I'm doing here is totally fine okay so now we know how to format access cells update cells updates uh worksheets modify worksheets Etc what we're going to do now is get into a tiny little project so let's do that all right so for this project I have some values now all I want to do is just put these values inside of a new worksheet but I only want to put them in that worksheet if the worksheet doesn't already exist if it does exist then what I want to do is override the values if it doesn't exist I need to create that new worksheet and then add all of these different values I also want to perform a little bit of formatting and I want to use some equations or formulas in my sheet which I'm going to show you how to do so we have these values okay and what we're going to do now is start by actually creating a new worksheet if the one that we want doesn't already exist so first thing we need to do here is just get the name of all of the sheets that exist so we can see if this already exists or not so to do that we are going to say the worksheet list is equal to we're going to use the same thing I showed you before where we have map Lambda x x. tile and then this is going to be the sheet. worksheets and what did we call it we actually called this the work sorry this going to be workbook do worksheet that's going to give us the names of all of the different worksheets that we have inside of our file now that we have that we're going to specify what the name of the worksheet we want to create is so we're going to say new worksheet name is equal to and I'm just going to call this values okay now what I'm going to do is check if this exists inside of this so I'm going to say if the new worksheet name is inside the worksheet list then what I want to do here is simply access that sheet so I'm going to say my sheet is equal to the workbook do the worksheet and then we're going to have the new worksheet name so this is if it already exists otherwise we want to create it so to do that we're going to say sheet do add sorry not sheet we're going to say workbook do add worksheet we're going to give it a name well the name is going to be the new worksheet name and then we need to specify the number of rows the number of columns that we want when we create this new sheet so we're saying 10 rows 10 columns this is the name of the sheet we're going to add this to our workbook so now next time when we run this it will already exist so rather than adding it we'll simply access it and that also reminds me we're going to associate this with the sheet variable so this way now no matter what happens whether it existed or didn't exist we're going to have some sheet that we can start working on hopefully that's clear but that just checks okay does it exist if it does access it otherwise create it all right now now that we have it we're going to clear it so I'm going to say sheet. clear that's just going to remove everything inside of it so we're starting fresh and now we can start writing in some new values obviously you don't have to do that but that's what I'm going to show you how to do here now the long way to actually take all of these values and to put them into our Google sheet would be to write something like this now what this does is simply Loop through all of the different values that we have inside of the values list and then Loops through every single one of the columns that we have in those rows and individually updates every single cell inside of our sheet to contain this value now that's fine but the issue is that these are happening kind of sequentially and we can't start updating the next cell until we've updated the previous cell now what this means is this code even when we only have something like 12 values here to actually update is going to take a few seconds to run and it's really inefficient to update one sell at a time unless you need to be doing individual cells so what we can do instead is we can actually update a range of cells at the same time so to do that we can use our sheet name we can say sheet and we can say do update so rather than update cell we can actually update a range of them now to do that we can put an F string here and we can say we're going to update from A1 to C and then this is going to be the length of our values now the reason I'm doing this is because well if we have four rows then we want to go to C4 what I'm specifying is the Box essentially and we can go back here and look at it right so the box is something like this that I want to update with different values so C4 is what I want to go to because we have four rows if we had five six seven rows we'd go down there right in this case I'm hardcoding C because I know we're just going to have three columns okay so I'm saying A1 to C and then that Dynamic value and then what we're going to do is specify the values we want to update this with which will just be values okay so we have the array here of values what this does is take all of these and correspondingly place it ins inside of the box that we specified here okay before we go any further I want to test to make sure this is working so I'm going to clear this and I'm going to rerun and it should now make that new sheet for us and add those values inside of it uh and we got an issue here it says the order of arguments okay that's fine um deprecation warning don't worry about that anyways if we go here we now see that we have a new sheet called values okay it's a little bit smaller cuz we only added 10 rows and 10 columns and we now have our name price quantity we have the name of the items the price and the quantity now if we want to do something like make some of these titles bold or maybe we want to add the total price or the total quantity we can do that and I'm going to show you how we do that now okay so we have sheet. update now what we're going to want to do after this is we're going to want to perform a little bit of formatting so we have some bold titles right so what I can do is say sheet. format and I want to format a range of cells so in this case I can format from A1 to C1 which are going to be my headers and I can simply say that I want this to be the text format of bold true and that should give me some bold headers so let's see if that works or not we're going to again run our code we'll go over here and we should see in real time that actually updates them to be bold so you can see it clears it adds it back and then makes our headers bold very nice okay so that automation is working uh last thing we'll do sry to keep going back and forth is we'll make it so that these down here that I'm selecting have the sum of the price and the sum of the quantity so in order to do that we can update some individual cells so I can say sheet. update cell let me just make this a bit smaller so it's easier to read in terms of updating cell we can pick the cell that we want to update so the cell that I want to update is going to be the Len of values plus one because I want to get however many rows I have in this case it's four plus one so the fifth row right that's the one that I'm updating and then in terms of the column I want to do the second column and we're going to make this equal to an equation now to do that I just do equals sum and then I can say B2 to B4 so that means okay hey let's go back to the sheet quickly and have a look from B2 which is this to B4 I want to take the sum of that and store that in B5 that's all I'm doing okay and I'm just doing it a bit dynamically by using the length of values so if we added more values this would adjust now let's do the same thing here except this time we're going to go to column 3 and we're going to do C2 and C4 and now let's run the code let's go over here and let's watch the update get performed so we should see it clears adds the values back in and then notice now that we have the sums okay so we have the sum of all the prices which is 7797 then we have the sum of the quantity which is 8 perfect there we go I think that's pretty much everything I needed to show you so that is a automating Google Sheets with python obviously there is a ton more stuff you can do I just wanted to make this video to give you a primer show you the setup show you how you connect to an individual sheet and then give you a bit of realistic information on kind of how to set up a mini project if you guys have any questions do let me know in the comments down below if you enjoyed the video leave a like subscribe to the channel and I will see you in the next [Music] one

Channel: Tech With Tim

Views: 20,958

Keywords: tech with tim, Automate Google Sheets, Tech with Tim Tutorial, Google Sheets Automation, Python Scripting, Google API Tutorial, Spreadsheet Automation, Python Programming, Data Management Python, Google Sheets Python Integration, Automating Tasks Python, Python Projects, Tech with Tim Python, Google Sheets API Guide, Python Automation Tutorial, Data Analysis Python, Google Sheets Tips, Python for Google Sheets, Spreadsheet API Python, Learn Google Sheets API

Id: zCEJurLGFRk

Length: 32min 27sec (1947 seconds)

Published: Fri Feb 02 2024