Google Sheets API Python: Authenticate Google Sheet API Using Python

subscribe and click the bell icon to turn on notifications in this tutorial we're going to walk through authenticating to the google sheets api creating spreadsheets and tabs and reading writing and appending data to google sheets additionally we'll take a look at adding conditional rules to spreadsheets with the api as well as sending requests that can automatically format dates and sort ranges we'll put all these concepts into a script that uses the api to automatically add format and sort data in a google spreadsheet there's a lot to learn so let's begin to start let's open up a new browser then navigate to google sheets you may need to sign in to your google chrome or google account in order to access google sheets now they've been google sheets click the green sheets icon and then in the templates select annual business budget you may need to go to the template gallery in order to find the budget it'll be by the intuit quickbooks team select the annual business budget this will create the file on your drive and you'll be able to view the file in all the tabs inside the spreadsheet the setup tab shows the starting balance for our annual business tracker the income tab currently has a place for us to enter the different categories of income in the month that it occurred the expenses tab is very similar to the income tab except for it's a little bit longer and has a few more categories the summary tab is what you can expect it's not recommended to edit this sheet the sheet has graphs and also other pivot tables that display the summary data from the income and expenses tabs this is the starting spreadsheet that we'll be using to access the google sheets api sometimes the most difficult thing about accessing the google apis is figuring out how you can authorize yourself to access the apis and to provide the proper scope for the access that you need the first thing that we need to do to get authorization to access the google sheets is to create a google cloud project there is a python quick start that the google documentation provides that helps us do this open up a new tab and navigate to the python quick start scroll down to step one click the button enable the google sheets api this has a pop-up that allows us to name our cloud project after we name our cloud project we are then given credentials that we're going to use in our script to access the api you can see that we're given a client id and a client secret you need to keep these secret so that other people do not have access to your api without your knowledge on the screen you can read that we are now ready to start developing click download client configuration once the client configuration is downloaded you can hit done and we can close this tab and accessing the api i'm sorry that i asked you to close the python quick start tab because now we're going to go back in we're going to revisit that page to take a quick glance at the python quick start script here is the python quick start sample it's not very long but it can seem like there's a lot going on here more importantly for us in the first steps we need to download the modules that are used in the script to do that we're going to open up a command line application and make sure that we have these modules downloaded because i'm using mac i'm going to open up the terminal application if you're on windows i'd recommend using powershell or downloading something similar to the bash shell on unix operating systems that will make it easier for us to use similar commands when we're downloading these modules you may have noticed that when i opened up a new terminal window the base virtual environment was activated i'm going to use conda actually to deactivate that environment i want to download these modules into a place where python can access them globally this isn't ideal for package management but it is pretty reasonable for the scope of this video let's check to make sure that i have pip installed okay i get a response that isn't in there i also have the help instructions on using the pip3 commands back in the python quick start we actually have the code that downloads all the modules that we need we can copy this code and then paste it on our command line whether you're on mac or windows you may need to enter pip 3 instead of pip to correctly install these modules perfect i can see that the modules were installed correctly without error and actually i already had these modules installed so many of the requirements were already satisfied next we are going to open up idle and also open up a interactive shell with python we are going to first connect to the api and execute a lot of commands using the interactive shell python is great for learning in this way because we can execute commands one by one this way we are not using a script that runs through each command in a hole and we can break down the commands to a more understandable size that means that we're going to be copying and pasting a few of the commands from the script that google provides us in the documentation until we get to a point where we can continuously use the google sheets api service now that we have a shell open let's make sure that our google sample script is within view so that we can grab these commands line by line but before we start grabbing these commands let's set up the project making sure that we have a folder and the credentials within reach over the python shell let's import the os module then let's get the current working directory we do this with the command git cwd we're in the documents folder but i want to be in the desktop folder i'm going to create a path that we are going to use to change directories into the desktop folder the best way to create paths when using python is using the path.join method i will not always use methods to create paths in this video however i want you to know this is the proper way for building paths especially when you're using scripts we can also use a helpful method called os.path.exists to make sure that the path that we just created exists okay we know that we'll be able to navigate there let's change directories now we're in the desktop folder and this is where we are going to create our project folder we're going to call it python google sheets once we make our directory we should see the folders show up on our desktop earlier we downloaded a credentials file from google this is in our downloads folder we want to move this file into our python google sheets directory that way we can more easily access it from our script and our project we could open up the finder or any sort of file management user interface or use the terminal to transfer it however we're using python so i'm going to use python to move that file into our google sheets project first import shuttle this stands for shell utility next on the shuttle module we can use the move method here i'm using tab completion to form a path i know that the names of these files and folders exist so when i start to type them in i hit tab and they're found in the downloads folder there is a credentials.json file and i want to move that to the python google sheets folder we're going to use the os.path dot join method to join the current working directory and the python google sheets folder okay it seems that credentials.json is not a directory and that's true it's a file so i should not have included that last slash i can use control p to get the previous command that i executed then i can go back to that line remove that slash and hopefully move the file to the python google sheets directory hooray it worked let's open up python google sheets to make sure that it's in there all right now we can change directories into the python google sheets folder i'm going to use a relative path to do this the shell is now in our project folder with relative paths a period represents the directory that we are currently in we can use the os.list directory method to demonstrate this concept if i just enter a period surrounded by quotation marks and press enter i get a list with credentials.json this represents the names of all the files and folders in this directory in a list if i were to change directories and use two dots this would take me up one folder directory i'm now back on my desktop let's change directories back into our project folder before we start executing commands from the example script let's clear the console by exiting out of the shell and then opening a new shell next we can start copying and pasting the import statements from the example script into the shell we can execute them line by line to make sure that none of them throw an error this might seem a little bit tedious and it is but this will allow us to interactively communicate with the api service so far so good we've imported all of the modules that we need next we are going to modify the scopes variable that is a list with the scope that we want to access the spreadsheet with there are many different scopes that you can access your google drive or spreadsheets with this is the read only scope we need to change that because we are not only going to be reading values we are also going to be writing values to our google sheets let's copy that variable paste it into the shell delete the read only and then the period we can then initiate this variable as a side note scopes is capitalized because it is going to be unchanged in the script it is a constant in the example script notice that there is a comment saying if modifying these scopes delete the file token.pickle we do not have that file created yet so we do not need to worry about deleting it but if you were going to change the scopes again and you had a token.pickle file in your directory you would want to delete that first before you change the scopes next we need to grab and define the spreadsheet id for our google spreadsheet we're not going to name this variable sample spreadsheet id we're just going to call it spreadsheet id this is also not the id for our spreadsheet so let's delete that value go back to our annual business budget tab to find the id for the spreadsheet we need to look at the url the id is the value between the and the edit hashtag this can be a little confusing or difficult to find let me highlight the id in the url there is the id for our spreadsheet let's copy that and then paste it as a string to initialize the spreadsheet id variable this is also a constant the first thing that we're going to do with the api is to access a small range from the setup tab let's go back to the annual business budget and look at the setup tab here we have a starting balance of five thousand let's try to grab that value from this spreadsheet to grab that range we need to define that range as a variable in our interactive shell once again let's copy the sample range name delete the sample part of it and then change the string value to reflect our tabs and our range if you're unfamiliar with excel this is how you can access different tabs and different ranges depending on the spreadsheet this video will not fully cover the syntax that is used in google sheets in excel to access different cell values we want to access the setup tab cells b11 to c11 we need to define one more variable before we start executing our flow control statements that variable is creds and that's going to equal the none object the first code block that we see in the main function in the example script is right here if the path exists for a token.pickle file open the token.pickle file and read the value that doesn't exist so we're not going to execute that in our interactive shell at the moment but we are going to use it in the script later now we do not have any credentials so we want to execute this section of the script if credits is none or if the credits are not valid then get a refresh token if not we're going to use the installed app flow module that we imported earlier this is going to create a flow variable based on our credentials in the json file and the scopes that we defined when we execute this block a browser is going to open and have us sign in to our google account then after we successfully authenticate it's going to save the token.pickle file to our current working directory which is inside our python google sheets project if you copy and paste this over you're going to have some indentation issues because this is indented in the main function i have copied and pasted this into a text editor and unindented four spaces or one tab for this block so i can just copy and paste it into the shell and then execute it okay it looks like i'm actually not in my project directory anymore so i am going to import os and then change directories into that project folder we're going to use two dots for relative path which takes us up one directory into my home path the desktop folder is there and inside the desktop folder i have the python google sheets folder i'm going to close that string and change directories double check that i am in the right directory then i can use the control p command again to get the command that i executed a few lines ago okay so i executed that block and then a browser opened on a different screen to google and wants me to sign it i need to select my account the next thing that happens is google warns me that the app that i'm trying to access google spreadsheets with isn't verified and this is a useful security warning however we know exactly what application we are using and how we're accessing the google sheets therefore we can click on advanced and then we can click on go to quick start or whatever the name of your script is this will allow us to grant access to our desktop application after i have granted access i can then close this tab back in the shell i get a little bit of output here asking me to visit the url so i can authorize this application if we open up our project we now have the token.pickle file which means we've successfully authenticated we can now finally build the api service let's define the variable service and then call the build function passing in the service type that we want to build followed by the version for that service and then finally our credentials which we have saved in the creds variable next let's create a sheet variable and then on the service object let's call spreadsheets and then we're going to get a response object after we call these spreadsheets to access the values we need to call the values function and then we're going to use a little bit of functional programming to string together a few calls first using values and then using the get function and the get function takes in the spreadsheet id that we defined earlier and then a range the range we also defined earlier this creates our request and then we need to call the execute function to execute the request which means that our request was probably successful viewing response objects can sometimes be messy let's import the pretty print function on the pretty print module so that we can see the response object with a little more clarity now let's use the pre-print function to view our result you can see that we used the major dimension rows to access the spreadsheet the range that we accessed was the setup tab cells b11 to c11 and our values were starting balance and 5000. so we successfully accessed that range and pulled those values from the spreadsheet the response that we get is a dictionary so we can continue to use the get function to access values in the dictionary now we have a two-dimensional list with the values that we just read from the spreadsheet if you've gotten this far congratulations we've successfully authenticated and read values from our spreadsheet now that we know that we have the api service object created we can start doing a more diverse range of things with our spreadsheet to start let's look at creating spreadsheets and creating new tabs in a spreadsheet in the shell to create a spreadsheet we need to define the title properties for the spreadsheet we're going to use a dictionary first with a properties parameter and the properties parameter is another dictionary object where we can define things like the title there are many more properties we can define for the spreadsheet but just for this example we're going to define the title then we can build our object to call the api and get our response on the service object we're going to call the spreadsheets and then we're going to use the create function to create a new spreadsheet here we are creating a spreadsheet which is different than a sheet on an existing spreadsheet this is going to create a brand new spreadsheet and finally we need to call execute to actually send the request and get the response then we can pre-print the response you can see that the response is a very large object this represents all the properties that we could have set or that we could have tweaked when we created the spreadsheet there are many options for customization when creating a spreadsheet not only can we set the title we can set the columns the rows the time zone and the many values that affect the way the spreadsheet looks once we have created a spreadsheet we can then call properties on it using the get method because the response is a dictionary here we can pull the spreadsheet id off of the response object and we can also pull the url off of the response object if we open up our google drive we can see that we successfully created a spreadsheet called example here's the example spreadsheet that we just created we just use this as an example we're not actually going to use that spreadsheet because now i'm going to show you how to create a new sheet on a spreadsheet which i think is a little bit more common than creating a brand new spreadsheet creating a new sheet on a spreadsheet will be your first exposure to the batch update function the batch update function is a very useful function with the google sheets api because we can group together many requests and send them all at once to do this let's define the request objects list the request objects list is going to hold all of our request objects before they are sent let's create a new sheet request object we need to define the action first that we're going to use in the batch update each action has its own set of properties and values that need to be defined for it to be successful if we look in the documentation at the batch update function we can find the request objects and all the different kinds of requests that we can send in the batch update this is a good peek at all the different actions that we can perform on a spreadsheet using the api find and replace we can duplicate spreadsheets we can append to cells we can sort the range we can set basic filters there are many different things that we can do with the api if we find the add sheet action we can see the kind of arguments and objects that it takes it takes an add sheet request that has properties where you define the sheet properties here are the different properties we can define for our new sheet let's go back into the interactive shell and define some of these properties the first property we're going to define is the title property we're going to call this new tab transactions then we can define some grid properties if you accidentally select enter or get an invalid syntax error when you're filling out some of these longer properties you can use the command control p on a mac to get the previous command back so we can figure out where we had gone wrong we can define the amount of rows and columns that we want on the new tab with the grid properties we can also define the tab color for the new sheet this is defined in terms of red green and blue but instead of a classic rbg value we're going to use values between 1 and 0. if we decide to put 1 for red this is going to mean that we are going to mostly want this color to be red we want the green value to be represented at 0.3 of a full green we now need to close all of the brackets that we opened previously in the object next we need to add this object to our request objects list we can do that using the append function in python now we're going to create the body for our request which is going to define a request property here we put our request objects list finally we can call the api and get our response on the service object we're going to call spreadsheets and then the batch update function on the batch update function we need to use the spreadsheet id we need to pass in the body and then we can execute the request i forgot to define the properties property on the add sheet object i'm going to jump back a few commands to when we defined the new sheet object under add sheet i need to define a properties property where then i can choose the title the grid properties and the tab color that looks a little bit different because i didn't quite get the indentation right but the syntax generally is correct since we're in interactive mode we'll have to go back and redefine some of the variables that we had defined since or accessed since we started trying to create a new spreadsheet the first of which was the request objects list then we need to reappend the value into the request objects we can redefine the body and then we can try to call the api again here we saw no error when we tried to call the api if we go back to our spreadsheet we have a new tab or a new sheet called transactions and we also added the tab color which was a mixture of red green and blue this is an empty tab with the amount of rows and columns that we defined in our request let's take a look at this response object with the pretty print function in the request object we have the replies property with a list of all the replies from our requests we can look into this response by using the get function and some indexing into the list get the replies property is going to get us the list that is at the front of our response object the first index on that list is the response that we got for our one request on that one response we can use the get property to grab that add sheet response finally we can pretty print this small object our new tab has the properties defined in this object these will return to us once we successfully created the new tab later in this video we're going to use the transactions tab to demonstrate how you can write values to a spreadsheet append values add conditional formatting and also sort ranges using the api now that we have a fresh sheet on our new spreadsheet named transactions let's write values to that sheet using the api first let's define some example values using a list comprehension a list comprehension is beyond the scope of this video at a basic level a list comprehension can let us quickly create lists let's define the data that we're going to add to the spreadsheet this is a list comprehension this is going to create a list with the values 1 to 10. we're going to add these values to our transactions tab let's create a values list which is a two-dimensional list that has the row values that we want to add to our spreadsheet take a look at the values in the body of this request we're going to define a values property and give it the values variable that we just defined then we can pass this body into our request object this request is going to return values that we added so we're going to use the values function on the spreadsheets function to get those values in our response to add a single range we can use the update function this is going to update that range in the spreadsheet we need to provide the spreadsheet id the range that we want to update on the transactions tab let's update starting at cell a1 we then need to choose a value input option there are a few different value input options including raw and user entered you'll typically find yourself using the user entered value for the value input option this is a way to enter the values into the spreadsheet as if somebody was actually typing in the values into the spreadsheet finally we need to add the body onto the request and then we need to execute the request if we define the response object before calling the execute function that's fine we can just use the execute function on the response object that we just defined you may have noticed that the spreadsheet updated when we executed that request we now the values 1 through 10 starting at cell a1 and going down the first row to update multiple ranges in a spreadsheet we're going to use the batch update method let's go into our spreadsheet let's select the values that we just added and delete those back in the interactive shell let's define some column data and then we'll use the row data that we had from the previous example define the variable column with the values a b c and d then create a data variable which is a list this list is going to hold the range objects that hold our updates let's define the object row data open up curly braces the first property we need to find is the range that this row is going to update this range is going to be on the transactions tab and it's going to start at cell b1 we can be more precise with our a1 notation we could do b1 through f11 or f2 and define longer ranges however it may be easier just to define the cell that you want the values to start at and they will fill themselves in accordingly after we define the range we need to provide the values for that range the values has to be a two dimensional list so we can open the list add our row list and then close it let's close that dictionary for the row data initialize the variable and then define the column data the column data also takes in a range property for this one we're going to start the transactions at cell a1 the difference between the row data and column data is how we can define the major dimension the default major dimension is row data but if we want to define values in a column we need to give the major dimension property the value columns and then again just like the road data we need to provide the values let's append both of these values onto the data list now we can define our body object for the request the body object takes in the value input option for the values that we want to add this is going to be user entered and it also takes in the data object defined on the data property let's close that object and now we can send our request with the batch update function you might be able to start to see a pattern where we define the values and some of the other objects that are specific to the action we want to take we then add those objects to a body or a data property and then send that body with a request on the service object this is the general workflow for adding values or performing actions on our spreadsheets with our spreadsheet service object for the batch update we need to add the spreadsheet id followed by the body and then we need to execute the request to get the response again our spreadsheet updated and we have our column data starting in cell a1 and then our road data starting in cell b2 let's pretty print our response from this action on the response object we're going to grab the responses property in this response property we have a list with both of the ranges that we updated these show the updated cells the updated columns the updated range and also the total updated rows furthermore we also get the spreadsheet id returned in this property on the response object earlier in this video i briefly showed you how to read a single range from a spreadsheet let's quickly review reading a single range from a spreadsheet we can jump straight to the response object if we're just going to read a single range we'll call the spreadsheet functions get the values and then use the get method first just finding the spreadsheet id then defining the range that we want to retrieve and finally executing that request again let's use the pretty print function and then the get method to retrieve the values off of the response here we have a two-dimensional array the first value in the array is the first row the second value is a list of the second row the third list of the third row and the fourth a-list of the fourth row this is what happens when you retrieve a grid using the get method we could then possibly iterate over these lists to get them one by one next let's take a look at reading multiple values from a spreadsheet instead of using the normal get function we are going to use the batch get function let's define a variable called range names this is going to be a list with the ranges that we want to retrieve then define the response variable again everything is going to look pretty similar except for we're going to use the batch get function the batchget function takes the spreadsheet id and instead of range it takes ranges as one of its parameters and the ranges is the range names list that we just defined let's close the parenthesis and call execute and for this one we're just going to iterate over these values to print them instead of values the property that we want to pull off of this response is the value ranges property and then let's pretty print each row so each of these value ranges has the major dimension that we grabbed the range and then the values although we grabbed ranges a1 through a4 we grabbed them as rows so each of those values is a list if we wanted to grab them as a column we'd have to specify the major dimension before we get into building our project script i'm going to show you how to do one more thing with a spreadsheet and that is how to append values onto a spreadsheet appending values will add the values to the bottom of a specified spreadsheet it'll also add rows if there aren't enough rows on the spreadsheet to append values to a spreadsheet we need to define the values first for our values i'm just going to repeat the row variable that we defined previously then we need to find a body object passing in values for the values property and then we can send the request the function that we use to append values is named append let's add the spreadsheet id the range that we want to append the values to the body that holds the values and finally the value input option keep an eye on the spreadsheet okay so i was a little bit confused when the three ds got added to the bottom of the a column so i decided to run it again to see if i can get different behavior with a different range however i then realized that the values variable was actually holding three rows with the letter d so when i went to append those rows it actually appended the proper values i had just defined some strange values with the row variable let's go into the spreadsheet return it to what it was when we first started and then make sure that we define the proper values for the row variable so right now row equals the letter d let's have row equal a list with values 1 through 10. then we can redefine the values variable with each row redefine the body object with the values variable with the correct values and then we can jump back and send the request with the first range that we defined transactions column a we got the expected behavior where we added the three rows that had the values one through ten and it appended those values at the bottom of the a column or after the last value that was in the a column later in the video we're going to add many rows this column so that the rows exceed the amount of columns that are currently in the spreadsheet so you can see that it will keep appending rows onto the spreadsheet even if those rows are not currently available we are now ready to start moving into the part of the video where we build our script this will involve us creating example transactions that we can add to the spreadsheet and also adding some excel formulas to the spreadsheet so that these cells automatically add up our values that we put in the transactions tab we'll be looking at some of the batch update methods as well as adding conditional formatting sorting ranges automatically and adding a chart but to start out let's select all the values in the transactions tab and delete those next we're going to build some example transactions from scratch so that we all have the same data that we'll be adding to the transactions sheet i might go a little bit quick through some of these sections for creating these transactions but some of the actions are a little beyond the scope of this video so i hope that you'll be able to follow along as we create these transactions the first thing that we're going to do is we're going to grab the category ranges from the income and expenses sheet we want to grab the categories from the income and expenses because we want these to be in our example transactions we want the categories to line up between the example transactions and our spreadsheet so our formula can parse the transactions more easily to do this we're going to grab multiple ranges from the spreadsheet one range from the income tab and one range from the expenses tab we'll start by defining those ranges in a list the first one being on the expenses page in the e column and the second one being on the income page in the e column as well then we can execute a api call where we use the multiple ranges to pull the values from the spreadsheet we'll use the batch git we'll pass in the spreadsheet id the major dimension that we want to grab is the columns and finally the ranges that we defined in the previous command i'm going to repeat that command and make sure that i'm using service and not services and we get our values let's see what these values look like when we retrieve them okay so we have two two-dimensional lists the first one being the expenses and the second one being the income categories let's put the value ranges into a variable called vowel ranges we can get those ranges by using the response dot get and we're going to create a category dictionary that will have a parameter for the income categories and a parameter for the expenses categories let's define that variable using curly braces for a dictionary the first thing we're going to do is iterate through the value ranges if you notice each list starts with either income or expenses depending on which categories they apply to we can loop through these two lists pull out that value and then assign that as the dictionary value for each of the category lists so first we'll set up a loop through the value ranges then we'll check to make sure that the values are actually there we'll then define a variable in the local scope in the local function scope called cat list and we'll pull the values off of that list and then index into the first value next we'll assign the keys to the dictionary based on the first value in each list which will either be income or expenses we'll assign the value to that key all of the values in the list except for the first one we do that by indexing into the category list let's check the dictionary keys to make sure that we have income and expenses keys let's take a look at the values in one of these dictionary keys there are two things we want to take out of these category names one is the empty values which were pulled because there were empty rows in the column also monthly totals is repeated in the list a few times we want to create a function that will pull out those values and then remove them so that we just have the category names and there are no duplicates to do this we are going to iterate over each of the key value pairs in the dictionary then we're going to use a lambda function to filter out the values that are either empty strings or that equal monthly totals with a full colon so going to grab the keys and values from the category dictionary dot items and then we're going to use each key to grab the values from the category dictionary we're going to use the list constructor to convert this value back into a list i spelled lambda wrong the list that we're passing in to the filter function is going to be the category dictionary for each of the values whether that's income or expenses and then finally let's use a print format to then print the new category list for each of the values in the dictionary it looks like we were close but i put an x here instead of a k let's rerun this and i did not add the other brackets to my print format so the full categories were not printed although this is not super important to this command we're going to get it right anyway now we can see each of the category names for the expenses and the income we have removed the blank strings and also the monthly totals that was repeated in each of the category lists we now have categories that we can sample from for our income and expenses transactions now we need to create amounts that we can sample from you randomly choose to add with our income and expense transactions to generate these amounts we're going to import random then we are going to create some sample transaction values using another list comprehension the sample transaction values is going to take an integer between 1 and 99 it's then going to add a random value between 0 and 1 to represent the cents that may be in between transactions it's going to round that value to 2. so in the end we're going to get some values that look like this we can sample from these values to represent a variety of different amounts that come in on transactions we can now make two types of transaction values income transactions and expense transactions we're going to use the random choice function on the random module sample from the sample transaction values we're going to grab a hundred positive integers from the sample transaction values those are going to represent our income transaction values our expenses transaction values will use the same list comprehension but we're going to add a minus sign at the start this will make every transaction that we grab a negative number can now combine these into a larger example amount values now have the income and expense categories and the income and expense transactions finally we need to get some dates to represent the different dates that these transactions occur to do this we're going to import the date module from date time we can use a few built-in functions and the random.choice function to generate random dates we can pick the year the month and then use the random choice function to pick from a range of different values between 1 and 31. this represents different days in september between the first of the month and the 31st of the month we can then convert this to an iso format to make it a little bit easier to read let's run that function a couple more times to demonstrate this randomness we have three different values in september this will be a fairly long for loop where we'll grab example categories and amounts for whether this is an income transaction or an expense transaction then for both of these transaction types we're going to add a random date in september let's define the example transactions list where we will append our example transactions so we're going to generate a hundred example transactions first we'll grab an amount from our example amount values if the amount is greater than zero it means that we have an income transaction we are going to append a list to the example transactions list the first value of which is going to be a random date we're going to grab a date from the year 2020 the month september and then we're going to randomly select the day it looks like i did one too many random choices we're going to use random choice to select a date from a list of date options between 1 and 31 representing the names and then we're going to convert that to the iso format the second value for each transaction is going to be the amount value and the third is going to be a random choice from the values in the category dictionary that is an income category if the amount is not greater than zero then it means that it's going to be an expense we're going to do the same thing we're going to change the values to reflect the values that would be expenses we can copy the example transactions because we're going to append the same date values and the amount but instead of income we're going to grab from the expenses categories finally we can pretty print the example transactions we actually need to execute the value first and then we can pretty print the example transactions i'm slightly embarrassed to admit that september only has 30 days and not 31 days so when we run this function we need to make sure that our range is between 1 and 31 this will grab values between 1 and 30. we need to make sure that we do this for both times that we're accessing the date and we should have our example transactions we have a hundred example transactions that we're going to add to our annual business budget the way that we get this two-dimensional list representing our transactions into our script or our shell isn't exactly important it's just important that we have a two-dimensional list that has our row values i'm going to add these transaction values to a csv file so our script will be pulling from a csv file and then adding it to our spreadsheet in google sheets however there are many different ways you can grab information or values and add them to your google sheets you just need to be able to convert those values from whatever file format they're coming from into a two-dimensional list so very quickly we can import csv then we can open up an example transactions csv file we can then create a csv writer object by calling the writer function on the csv module they're going to represent a csv file that was pulled from some sort of software by adding names to each of the columns and then finally we're going to write our example transactions to the csv file we get a little bit of output let's list the files in this directory we now have the example transaction csv in our project folder before the spreadsheet can find and add up all of the transactions in the transactions tab we need to add formulas across all of the cells that represent areas where we would add values this is going to be a little bit tedious but we're only going to do it once we're going to take this formula and enter it in cell f3 i can't break down what this formula does step by step but it'll add the right transactions into the proper spot if we get them on the transactions tab we can take this formula and then pull it across each of the rows i'm going to speed up the video as i do this but we'll have to update one of the values when we jump down to the other category down in interest earned we need to change the formula to reflect the category that is in that row this is going to be cell e11 so instead of e3 this needs to be e11 we'll have to do this for the expenses page as well okay let's go to the expenses page and let's enter this formula at the top of each of these categories coming up in the video i'm about to make a small mistake that then i repeat over all the cells in the expenses tab instead of the value in this sumifs function instead of it being numbers that are greater than zero i need to make it numbers that are less than zero that's the only way that the expenses tab is going to be able to determine whether a value is an expense or income on the income tab the formula needs to look for amounts that are greater than zero i'll correct this later in the video but to save yourself to trouble i would change this value to less than zero instead of greater than 0 before you start pasting and pulling all the values across the cells i am going to go through and extend this formula to each of the cells and rows in this spreadsheet however if you don't want to do that you can just pull this first cell across and then just focus on the september column because that's the column that we'll be adding transactions for so you copy this value add it to that row change the e value to reflect the category so this will become e at nine and then pull that down for each of the categories again we need to enter the formula this is e17 so we'll change this to e 17 and then click and hold the bottom right to pull that down to the end of the category i'm going to do this for every category but like i said you could only do this for september because that's the only month we'll be adding transactions for in this tutorial okay we now have all of our formulas entered into the rows and columns so we can successfully grab the values from the transactions tab and use them in the income and expenses tabs we can shrink this window back down and then jump back into the shell let's read the values from the example transactions csv we're going to create a reader object with the csv module we're going to pass in our file and then for each row in the reader object we're going to print the row we can iterate over each of the rows in the spreadsheet in this way and then we can add them to a different list that can be appended to the transactions sheet let's create the transactions list that we'll be appending the values to we can then go into our history and convert this statement to just append the row to the transactions list let's check the length of our transactions list to make sure that our transactions were added we have 149 transactions in our transactions list you may have a slightly different amount in your transactions list i have a few more because i had the error that occurred twice based on the date now when the date was not fit for september the transactions were still being added in that time so my example transactions had a few more transactions when i ran it the third time recall that the first row in the csv file is a column header so when we build the body of our request we're going to ignore the first value in this list let's define the body for the request and then pass in our values for our transactions we're going to ignore the first value in the transactions list and then initiate our body variable we can then do what we have already learned and append these values to the transactions sheet we use the append function we provide the spreadsheet id the range that we want to append to is the transactions tab in the a column we pass in the body and then finally we need to define the value input option which is going to be user entered we can then execute this request we did not get an error and let's take a look at our transactions tab we have all of our values that were appended to the transactions tab and the rows were extended to fit all of our transactions if you scroll over to september we notice that we do not have any transactions showing up nor do we have any transactions in the income spreadsheet either the issue here is that the values for each of the months at the top of the spreadsheet are based on 2016. so we need to update this to 2020 for it to work let's go to september and let's change this to september 2020 and then go to october and change this to october 1st 2020 and then you see our september values do appear if you were adding transactions for different months you would want to go back and change all of these to reflect the proper year i realized that the formula that we used to enter the expenses and to find the expenses is actually looking for positive values and this needs to be a negative value so now we're going to pick up on the negative values that are the expenses from the transactions tab so to change this for all the values we can just pull this down and then for each of the different categories change this value instead of greater than zero we want it to be less than zero okay so not only do we have transactions showing up or totals showing up for our expenses and income sheet but our summary sheet is also linked to these so we have summary values in our summary tab now that we know that we're able to successfully add transactions to the transactions tab and then tally those in the income expenses and summary spreadsheets let's build our script that can do this as needed to start making our script select idle and we should have a blank file available for us when we first launched idle if not we can select new file from the navigation bar let's save this file as main.py inside of our project directory this will be inside of the desktop and then python google sheets in this file we're going to do many of the things that we did at the beginning of the video which was import the proper modules check for credentials get credentials if we don't have them already and then build an api service i'll be copying and pasting in these values for the script but you should be able to do the same based on the commands that you executed previously in the video or from the google python quick start first we need to import the modules then we need to define the scope after that we need to define our spreadsheet id i do not have this value available so i'm going to grab it from the url now we need to grab both of the if blocks that were in the python quickstart that check to see if we have credentials after that we can build our service after we build our service we need to initiate a transactions array that we can add our transactions to this script assumes that there is a csv named example transactions in the current working directory this file can be switched out depending on whatever transactions that you may want to be adding however it needs to be called example transactions then just like in the interactive shell we need to create the body and then we can execute the request and response let's save this file and try to run it if it's successful we should have just short of 300 values in our transactions tab i'm missing a parenthesis let's save this one more time and then try to run it over in the shell i ran the file and i got zero output now i would expect no output if this were successful let's look at our transactions we now have above 149 and looks like we have just short of 300 transactions this is reflected in the income and the expenses if we were to create some example transactions and change the month we could add a lot more example data into the spreadsheet however this current script takes values from a csv file in the same folder and then programmatically adds as many transactions are in the csv file to the transactions tab the next thing we're going to do is we may want to have these transactions sorted in case we had questions about what certain transactions were causing what amounts we're going to format the dates sort the spreadsheet by the dates and then highlight the expenses that are over a hundred dollars the google scripts api defines many different ways you can format dates when you add them to a spreadsheet here you can look at some of the date and time format examples you can also view the format patterns and take a closer look at some of the number format tokens we're going to grab one of these date and time format examples and change our spreadsheet to match that pattern to update dates to a certain format in the google sheets api we need to use the batch update function and pass it a repeat cell request recall that in the sheets api we have many different actions that we can take with the batch update function let's go back to the interactive shell and we'll add this format to the dates in our spreadsheet we are still connected to the api with our service to change the date formats we'll need to get the id for the transactions sheet this is what we'll have to do when we define grid ranges in our request to get the id let's define a variable sheets and call the service object using the spreadsheets function and then we'll call the values function on the spreadsheets let's pretty print the sheets variable that we just defined looks like we lost the pretty print module so let's import it again and then pretty print the sheets we have a discovery.resource object let's redefine the sheets value again but let's remove the values function call now let's pretty print this variable sheets and we see that we get a lot of values for the spreadsheet eventually down towards the bottom it's going to print the properties for the transactions spreadsheet we have the sheet id here at the bottom for the transactions tab so we're going to copy this value and then define a sheet id variable in the shell you're at the bottom of the shell to find the variable sheet id we're going to capitalize that and then paste in the number for the sheet id now we can define a request object and use the batch update function to format the dates in the transactions tab let's start by defining the requests variable which is going to be a list with a single object the action is going to be repeat cell and we need to find a grid range the reason we needed the sheet id was because it is a required parameter when defining a grid range the start row index for the grid range is going to be 0 which is the first row the start column index for the range is also going to be 0 because we want to get the full sheet and the end column index is going to be 1 because we only want to do column a so index 0 to 1 represents column a in the spreadsheet next we need to define a cell property this has a user entered format and this takes an object where we can describe a number format the type for this number format is going to be a date and the pattern we can grab from the browser tab that we were on earlier let's go back to the date numbers and formatting and let's find the example dates and then we can just copy and paste this example date into the shell we add the date pattern we can close a few of the objects and then we need to add one more property to the repeat cell object this is going to be the fields and in the fields we are going to enter user entered format and then select number format need to close one more object and then close the list that defines our request list that has our repeat cell request now we can define a body passing any parameter requests and then we're ready to call the api we're going to use the batch update function add the spreadsheet id then the body and then we're ready to execute this api call this should change all of the current dates to reflect the pattern that we entered in the cell object let's open up our annual business budget and you can see that we now have a different format for all of the dates this will be helpful and we can use it in our script because when we add the transactions from the csv file it may not automatically pick up on the date format so we can execute this every time we add transactions to make sure all the dates are the same let's go back to our script and let's add this api call to the bottom of our script first let's take the sheet id variable and define that then we can copy the request object quickly define the body and then at the end we can call the api again let's save the script and then we'll test that to see if we can add transactions and reformat the dates once they're added now that it's saved we can click run and run module and we don't get an error we now have many more transactions added and they all have the date formatted to the pattern that we provided next let's take a look at the sort range option so that we can sort all the transactions from september by ascending or descending order based on the date we can jump back to our request browser search for the sort range request we can see that it takes a sort range request object the sort range request object takes in a range and then sort specs the sword specs look at the order it can look at the color dimension background style let's take a look at the sort order to see what we're working with this determines the ascending or descending value of the sort order so we can define a range and then choose the sort order to sort the values whether they are ascending or descending this works with dates as well let's go back into our shell and we'll first define a sort range the sort range is also a grid range so we're going to need the sheet id we can then define a request list where we'll add a request object that has the sort range action we'll define a range and pass in our sort range variable and then let's add the sort specs we can add multiple sort specs but the only one that we're going to add to the sort specs list is an object with the property sort order and we'll give it a value of descending we'll close that object close the sort specs array and then we need to close two more objects and the requests array nothing new here we're going to define a body object add the parameter for requests close the body object and then we're ready to send the request with the batch update let's go back to our spreadsheet and check to see if we sorted the ranges yes it looks like all of the dates are now sorted in descending order based on the date this could be another aspect of the script that we add so that when transactions are added per month the dates are sorted in descending order from the most recent to the oldest now what's different about this in the interactive shell than in the script is that we don't need to define another request object we can simply add another object to our request list so instead of defining this whole object and then the body we can just take the sort range object and we can add that to the request array this is one of the useful aspects about the batch update method is we can send multiple updates at once let's save the script and then we'll run that again and we should expect to have dates sorted and formatted looks like i forgot to add the sort range variable and we need to add that below the sheet id let's save it and run it again and it looks like we were successful you may have seen the spreadsheet update in the background and all of our 742 transactions are formatted and sorted the last request that we're going to add is a conditional formatting rule to the spreadsheet the conditional formatting request takes in a grid range so let's define the grid range as my range we'll pass in the sheet id the start column index is going to be 1 which maps on to the start of the second column in the transactions tab the end column index is going to be 2 which means that we just want that second column which will be the numbers and we're going to start at the first row which is represented with a 0. we can close my range and then we can form our requests array the action is called add conditional format rule first we need to define the rule object the ranges for the rule consists of the range object my range but we need to put it inside of a list so make sure that you add the brackets to put that range object inside of an array then we can add a boolean rule we need to find properties on that boolean rule we're going to look at the number greater than or equal to action and for the values we're going to open up another array and define user entered value again and this is the value that we want the decision to be based off of so this is based off of the user entered value of 100. close the object and close the array this applies to values that are greater than or equal to 100 and since we actually want to highlight numbers that are less than or equal to a hundred dollars for the expenses we are going to change this to a number less than or equal to negative 100. the type of boolean rule that we can add can be found on the documentation page the documentation page has a whole list of different values that we can add based on the values in the cell this can involve dates it can involve text numbers it can also involve other boolean values that you have entered into the cell after we define the boolean rule we're going to close the boolean rule add a comma and then define the format this is going to involve a text format we want to change the foreground color which is the color of the text to red then we need to close these objects and then add an index of zero then we can close the array and that defines our requests array a little bit more properties then once again we can define the body we can then call the api using the batch update function we got an error and after i inspected the object it looks like i forgot to add a condition property above the type values and text format so inside of the boolean rule we need to add the condition and then we'll add another object that slightly changes the look of our object but i added the brackets in the correct place so now this should work let's redefine the body parameter and then we'll send the request again the requests seem to have executed correctly but we don't see any changes on our spreadsheet and i realize the reason we don't see any changes on our spreadsheet is because when we selected our sample amount transactions the maximum value was actually a hundred so we should change that user entered value to negative 50 to actually see some of the conditional formats to check that the conditional format was actually added we can view the conditional formatting rules in the spreadsheet you can see that our rule was added for a value is less than or equal to negative 100 and we want it to be red and we're applying it to the proper column so everything that we did was correct except for we chose a value that was outside of the transaction range so let's delete that rule and then we'll re-define the request variable and then redefine the body variable and send the request again let's change this to negative 50. and as soon as we did that we saw a lot of these numbers light up with red highlighting so now we format the dates we've also conditionally highlighted the expenses that were over negative fifty dollars because this is a conditional formatting rule we only want to add it once or we may continuously add it more times than we would like to that means we don't need to include this into our script because once it's added it's there so now any new transactions that we add will comply with that conditional formatting rule let's take another look at our script we can close out of the shell since we're done executing commands let's next go through our application and we'll put some comments in to help break down what the script is doing at each point at the top of the file we are importing the modules that we need a little bit further down we are defining the constants that we will need for the authorization of our script after that we are authorizing the script just above the service declaration let's leave a comment that says we are going to build the service in this area of the script after that we want to load the transactions or data of any sort we want to declare our first request body and then we want to send our transactions or send our data api request after that we're going to send an auxiliary api request and we're going to define a few variables to help with that api request we're going to modify the sort range variable and we're going to capitalize it which means we also need to change where we use it further down in the script and make sure that we are capitalizing it here as well here we're going to build the multi-request array for our secondary api call and then we're going to redefine the body variable for our second api call and finally we send our secondary api call that does it for building our script in this script we've set up a nice template for authorizing the script to access the google sheets sending a api request that appends data to a spreadsheet and then sending other request objects to further modify and format the spreadsheet with the api there are plenty more actions that can be added to the request array to modify the api you just need to use the documentation and follow some of the same steps that we followed in the preceding video lectures to understand how to add those to the requests array let's run the script one more time but first let's delete all the transactions that we currently have in the transactions tab let's check to make sure that our expenses are clear and our income we should not be reading anything for september for those months and the summary is blank as well we are in the transactions tab and let's run this script and there you see that the transactions have been added formatted and sorted and the conditional formatting rule has also taken effect for the expenses that were greater than 50 dollars that does it for our script on accessing the google sheets api if you're not a subscriber click down below to subscribe so you get notified about similar videos we upload to get four free courses in excel quickbooks microsoft project and photoshop click over there and click over there to see more videos from simon says it

Channel: Simon Sez IT

Views: 2,535

Rating: 5 out of 5

Keywords: google sheets api, google sheets api python, google sheets api tutorial, python, python tutorial, python tutorial for beginners, google sheets python, python google sheets, python tutorials, learn python programming, python programming tutorial, python for beginners, python course, python programming, learn python, Authenticate Google Sheet API Using Python, python crash course, python for data science, gsheets python api, gsheets python, simon sez it

Id: MG8njjaOjJY

Length: 108min 50sec (6530 seconds)

Published: Wed Oct 14 2020