In this video, we are going to load the daughter of a Google Sheet, a private Google Sheet in Python.
For this, it's obvious that you need a Google account and you need a Google Sheets in your Google Sheets
account.
And it's good to have two sheets, so I have this 2013 and 2014.
Now in the lecture resources, you can get the link to a public sheet and you can get those data.
So get them like just copy them and then create a new sheet in your account and just paste these data
into different sheets.
So we have some 2013 weather data for the first sheet and 2014 weather data for the second sheet.
Then don't do anything.
Don't share it.
For now, we're going to do everything.
So just escape.
We're going to do everything step by step, because this time the link is not enough.
So previously, all we had to refer to from Python was the link to the Google Sheet.
But this time we have to do something else.
So we have to configure the Google Sheets API because you have to tell Google through Python, you have
to tell Google that you are the owner or that you have access to that sheet.
So Google have to give you an API key, which then you puts in your Python script.
And so Python script access to access the Google Sheets, change them daily Delta updates and so on.
So for this, we need the G spreads.
Library, so not that one, but.
This down here.
Spreads.
Then the next step is to get an apology from Google.
So for that, you want to search on Google for Google API console.
So go to council, the developers dot Google dot com.
Now you want to make sure in case you have more than one Google account, you will want to make sure
you are in the Google account where your Google Sheets are.
So I have a throwaway account for this video and this is a Google console.
No, you want to create a new projects or go to create projects and give a name to that.
Two projects such as Automate with Python Press on Create.
And this is a dashboard for your project.
Then you want to go to enable apps and services, click that.
And here you want to search for Google Drive API.
Press Enter.
Then you want to press on the Google Drive API card here.
And you want to enable the Google Drive API, so we are enabling this API for this particular project,
and we're going to get an API key for that particular project and put that in Python.
Then this page will show up, so you want to go to create credentials, that button.
And then here you want to select an API, and this should be Google Drive API down here.
Select that and check application on.
You can press on.
No, I'm not using them here and boom, next.
Write a name here, whatever you like, like I'll to mate with life and.
Account, so as a service account, I.D., you can leave the description empty and press on, create
and continue.
Now you want to select a role here.
This should be the project role.
So perhaps you want to go down to the letter P project, so select projects and then select editor.
So there you go there, ED..
Press that and go to continue.
Now, this is optional, so you can just press done.
Then you see this, we created a service account.
So this is what we have just created.
If you click that.
And go to keys and go to ads key and create a key a you want to select Jason here?
So create a this will download now a JSON file with your API keys and other credentials.
So allow the download and.
That this is a decent file, so now I want to upload that into my report.
So go there, upload file, that's.
And perhaps rename it to a more simple name, such as secretes.
But, Jason?
If we take a look here, we are going to see.
Some dots, such as a client I.D., the client's email, which we need in just the minutes now and some
other credentials as well.
Now we want to go back where we were in here, so we downloaded the GS and file.
Then you want to go to this menu bottom and go to API in services, click that.
Then you should go again to enable apps and services and search for Google.
Sheets AP this time, so previously, we enable the Google Drive API.
Now we also need to enable Google Sheets API.
That's one.
Click it and press on Enable.
Once that is enabled, then we are done with the configurations, we can go back to our Google Sheet
and then go to share.
Then you want to invite your Python scripts via an email address.
Where do you find the email address in the Jason file?
If you scroll down here, you'll see clients email.
You copy that with all the quotes so that the entire email, in my case, copy it.
Go here and paste on the invite box.
And you want to give this the editor.
Permissions, so presence and.
That's completes the boards of configuring the Google APIs.
Now we want to go back to the code and Import G Spreads, which stands for Google spreadsheets.
Then what he want to do is we want to establish a connection to the Google Sheets by using gene spreads
the service.
Account that's math now expects as input the path to the Jason Fire, which contains the credentials
that allow Python to log into that account to the Google Sheets service account.
Then once you have, that's a variable, you can get a spreadsheet from that Google account with those
credentials.
So in my case, that's a the B G C Dot Open includes goes the name of the sheet.
So whether private in my case, whether underscore private, underscore private, then as you may know,
a spreadsheet is made of different sheets.
So we want the first one, for example.
There are two ways to go that you could either get a work.
Sheet by index, so I'm referring to as worksheets to these sheets of these tabs with data, and the
spreadsheet is the entire sheets containing multiple worksheets.
So to get a worksheets by index, you do worksheet one.
For example, we want the first worksheet equal to spreadsheet.
Don't get work sheets methods.
The first worksheet has an index of zero.
The second has an index of one and so on.
So that will give us.
Just a worksheet object, I can print it out so you can see what it is.
So that is the Oldfield's is just a worksheet object, we have the name of the worksheets, but to get
the data you want to do that are equal to the worksheets one does don't get all records like that and
print the data just to see what we get executes.
And this is the output.
Sue, let's take a look.
This is a list, so it starts here and it ends here.
And this list is made of dictionaries.
You see, the first dictionary starts here and it ends in here.
Then we have the second dictionary starting here and ending in here.
So we have a list of dictionaries.
What does its dictionary represent?
Its dictionary has pairs of keys and values.
So we have here and the value of here for the first rule.
So each dictionary represents one row, for example, the very first rule we have here amongst the whole
temperature pressure with these values here, these ones.
So 2015, one one zero minus 20.
This is what we have here.
2013.
One for the month.
One for the day.
Zero temperature minus 20.
And so on.
And then comes the second dictionary representing the second row and so on.
So each of the dictionaries contain the name of the column pressure temperature, our as a dictionary
key.
That's how the dots all look like.
That means you can extract, for example, the tenth row by doing this list indexing operation that
should give you only the dictionary representing the 10th row.
However, there are better ways to extract sort of rows and also columns from your sheet from a worksheet.
And we're going to talk about them later in the course.
But this was just.
A quick way to get a role.
Now this was how to get a sheet based on index, but if you want to get the sheet based on its name,
for example, you have 2013 year and 2014.
So in that case, let's add one more row gets a work sheet by name.
That'd be.
Worksheet one equal to spread sheet dot worksheets, methods you see now we have to give a title to
this, the title of the worksheet, which is 2013 or 14, whatever whichever worksheets you need.
So I can comment that out.
Print and we're going to get the same data representing the worksheet with name two thousand thirteen.
And that is the complete codes for this lecture, thank you for following.
Let's do some more operations with Google Sheets.