1
00:00:00,600 --> 00:00:07,950
In this video, we are going to load the daughter of a Google Sheet, a private Google Sheet in Python.

2
00:00:08,790 --> 00:00:16,379
For this, it's obvious that you need a Google account and you need a Google Sheets in your Google Sheets

3
00:00:16,379 --> 00:00:16,860
account.

4
00:00:17,100 --> 00:00:22,830
And it's good to have two sheets, so I have this 2013 and 2014.

5
00:00:23,100 --> 00:00:30,930
Now in the lecture resources, you can get the link to a public sheet and you can get those data.

6
00:00:31,530 --> 00:00:38,810
So get them like just copy them and then create a new sheet in your account and just paste these data

7
00:00:38,820 --> 00:00:41,130
into different sheets.

8
00:00:41,760 --> 00:00:48,750
So we have some 2013 weather data for the first sheet and 2014 weather data for the second sheet.

9
00:00:49,380 --> 00:00:50,430
Then don't do anything.

10
00:00:50,430 --> 00:00:51,170
Don't share it.

11
00:00:51,180 --> 00:00:52,920
For now, we're going to do everything.

12
00:00:53,160 --> 00:00:54,300
So just escape.

13
00:00:54,810 --> 00:01:00,660
We're going to do everything step by step, because this time the link is not enough.

14
00:01:00,660 --> 00:01:08,520
So previously, all we had to refer to from Python was the link to the Google Sheet.

15
00:01:09,300 --> 00:01:11,170
But this time we have to do something else.

16
00:01:11,170 --> 00:01:20,220
So we have to configure the Google Sheets API because you have to tell Google through Python, you have

17
00:01:20,220 --> 00:01:25,830
to tell Google that you are the owner or that you have access to that sheet.

18
00:01:25,950 --> 00:01:31,140
So Google have to give you an API key, which then you puts in your Python script.

19
00:01:31,140 --> 00:01:38,610
And so Python script access to access the Google Sheets, change them daily Delta updates and so on.

20
00:01:39,600 --> 00:01:42,570
So for this, we need the G spreads.

21
00:01:44,800 --> 00:01:47,890
Library, so not that one, but.

22
00:01:49,540 --> 00:01:50,590
This down here.

23
00:01:50,980 --> 00:01:51,490
Spreads.

24
00:01:55,960 --> 00:02:00,910
Then the next step is to get an apology from Google.

25
00:02:02,360 --> 00:02:09,289
So for that, you want to search on Google for Google API console.

26
00:02:11,150 --> 00:02:14,360
So go to council, the developers dot Google dot com.

27
00:02:16,390 --> 00:02:21,940
Now you want to make sure in case you have more than one Google account, you will want to make sure

28
00:02:21,940 --> 00:02:26,470
you are in the Google account where your Google Sheets are.

29
00:02:27,100 --> 00:02:33,760
So I have a throwaway account for this video and this is a Google console.

30
00:02:33,880 --> 00:02:39,130
No, you want to create a new projects or go to create projects and give a name to that.

31
00:02:39,130 --> 00:02:44,020
Two projects such as Automate with Python Press on Create.

32
00:02:46,010 --> 00:02:49,280
And this is a dashboard for your project.

33
00:02:49,550 --> 00:02:53,450
Then you want to go to enable apps and services, click that.

34
00:02:54,110 --> 00:02:59,060
And here you want to search for Google Drive API.

35
00:02:59,090 --> 00:03:00,020
Press Enter.

36
00:03:02,460 --> 00:03:06,630
Then you want to press on the Google Drive API card here.

37
00:03:07,690 --> 00:03:14,410
And you want to enable the Google Drive API, so we are enabling this API for this particular project,

38
00:03:14,590 --> 00:03:19,600
and we're going to get an API key for that particular project and put that in Python.

39
00:03:22,030 --> 00:03:27,490
Then this page will show up, so you want to go to create credentials, that button.

40
00:03:29,170 --> 00:03:34,960
And then here you want to select an API, and this should be Google Drive API down here.

41
00:03:35,230 --> 00:03:37,900
Select that and check application on.

42
00:03:39,970 --> 00:03:40,730
You can press on.

43
00:03:40,750 --> 00:03:43,240
No, I'm not using them here and boom, next.

44
00:03:45,930 --> 00:03:51,940
Write a name here, whatever you like, like I'll to mate with life and.

45
00:03:54,110 --> 00:04:02,060
Account, so as a service account, I.D., you can leave the description empty and press on, create

46
00:04:02,060 --> 00:04:02,870
and continue.

47
00:04:03,320 --> 00:04:05,190
Now you want to select a role here.

48
00:04:05,210 --> 00:04:08,240
This should be the project role.

49
00:04:10,230 --> 00:04:18,810
So perhaps you want to go down to the letter P project, so select projects and then select editor.

50
00:04:19,589 --> 00:04:21,360
So there you go there, ED..

51
00:04:21,720 --> 00:04:25,500
Press that and go to continue.

52
00:04:27,800 --> 00:04:30,140
Now, this is optional, so you can just press done.

53
00:04:32,580 --> 00:04:35,730
Then you see this, we created a service account.

54
00:04:36,060 --> 00:04:37,740
So this is what we have just created.

55
00:04:37,860 --> 00:04:39,000
If you click that.

56
00:04:40,230 --> 00:04:48,960
And go to keys and go to ads key and create a key a you want to select Jason here?

57
00:04:49,590 --> 00:04:56,190
So create a this will download now a JSON file with your API keys and other credentials.

58
00:04:56,340 --> 00:04:59,850
So allow the download and.

59
00:05:01,270 --> 00:05:06,870
That this is a decent file, so now I want to upload that into my report.

60
00:05:08,760 --> 00:05:11,910
So go there, upload file, that's.

61
00:05:13,500 --> 00:05:17,400
And perhaps rename it to a more simple name, such as secretes.

62
00:05:19,690 --> 00:05:20,590
But, Jason?

63
00:05:21,720 --> 00:05:24,150
If we take a look here, we are going to see.

64
00:05:25,570 --> 00:05:32,620
Some dots, such as a client I.D., the client's email, which we need in just the minutes now and some

65
00:05:32,620 --> 00:05:33,850
other credentials as well.

66
00:05:34,300 --> 00:05:40,630
Now we want to go back where we were in here, so we downloaded the GS and file.

67
00:05:41,810 --> 00:05:48,200
Then you want to go to this menu bottom and go to API in services, click that.

68
00:05:50,510 --> 00:05:56,990
Then you should go again to enable apps and services and search for Google.

69
00:05:58,400 --> 00:06:04,850
Sheets AP this time, so previously, we enable the Google Drive API.

70
00:06:05,060 --> 00:06:07,730
Now we also need to enable Google Sheets API.

71
00:06:09,080 --> 00:06:09,650
That's one.

72
00:06:09,650 --> 00:06:11,780
Click it and press on Enable.

73
00:06:13,550 --> 00:06:23,630
Once that is enabled, then we are done with the configurations, we can go back to our Google Sheet

74
00:06:23,990 --> 00:06:25,850
and then go to share.

75
00:06:27,390 --> 00:06:33,330
Then you want to invite your Python scripts via an email address.

76
00:06:33,600 --> 00:06:38,120
Where do you find the email address in the Jason file?

77
00:06:38,550 --> 00:06:41,670
If you scroll down here, you'll see clients email.

78
00:06:42,030 --> 00:06:48,420
You copy that with all the quotes so that the entire email, in my case, copy it.

79
00:06:49,500 --> 00:06:52,650
Go here and paste on the invite box.

80
00:06:53,880 --> 00:06:56,340
And you want to give this the editor.

81
00:06:57,490 --> 00:07:00,400
Permissions, so presence and.

82
00:07:03,430 --> 00:07:07,690
That's completes the boards of configuring the Google APIs.

83
00:07:07,990 --> 00:07:15,550
Now we want to go back to the code and Import G Spreads, which stands for Google spreadsheets.

84
00:07:16,240 --> 00:07:23,650
Then what he want to do is we want to establish a connection to the Google Sheets by using gene spreads

85
00:07:24,340 --> 00:07:25,720
the service.

86
00:07:26,850 --> 00:07:35,310
Account that's math now expects as input the path to the Jason Fire, which contains the credentials

87
00:07:35,550 --> 00:07:41,610
that allow Python to log into that account to the Google Sheets service account.

88
00:07:42,150 --> 00:07:51,960
Then once you have, that's a variable, you can get a spreadsheet from that Google account with those

89
00:07:52,410 --> 00:07:53,340
credentials.

90
00:07:53,730 --> 00:08:03,210
So in my case, that's a the B G C Dot Open includes goes the name of the sheet.

91
00:08:03,720 --> 00:08:12,720
So whether private in my case, whether underscore private, underscore private, then as you may know,

92
00:08:12,780 --> 00:08:15,360
a spreadsheet is made of different sheets.

93
00:08:15,660 --> 00:08:18,090
So we want the first one, for example.

94
00:08:19,170 --> 00:08:23,760
There are two ways to go that you could either get a work.

95
00:08:25,520 --> 00:08:34,610
Sheet by index, so I'm referring to as worksheets to these sheets of these tabs with data, and the

96
00:08:34,610 --> 00:08:38,750
spreadsheet is the entire sheets containing multiple worksheets.

97
00:08:39,230 --> 00:08:46,070
So to get a worksheets by index, you do worksheet one.

98
00:08:46,070 --> 00:08:50,150
For example, we want the first worksheet equal to spreadsheet.

99
00:08:50,510 --> 00:08:53,870
Don't get work sheets methods.

100
00:08:54,410 --> 00:08:57,500
The first worksheet has an index of zero.

101
00:08:57,740 --> 00:09:00,140
The second has an index of one and so on.

102
00:09:00,770 --> 00:09:02,090
So that will give us.

103
00:09:04,520 --> 00:09:10,820
Just a worksheet object, I can print it out so you can see what it is.

104
00:09:15,310 --> 00:09:22,300
So that is the Oldfield's is just a worksheet object, we have the name of the worksheets, but to get

105
00:09:22,310 --> 00:09:34,660
the data you want to do that are equal to the worksheets one does don't get all records like that and

106
00:09:34,660 --> 00:09:38,840
print the data just to see what we get executes.

107
00:09:39,550 --> 00:09:40,750
And this is the output.

108
00:09:41,880 --> 00:09:43,860
Sue, let's take a look.

109
00:09:46,160 --> 00:09:51,170
This is a list, so it starts here and it ends here.

110
00:09:51,320 --> 00:09:54,240
And this list is made of dictionaries.

111
00:09:54,260 --> 00:09:59,030
You see, the first dictionary starts here and it ends in here.

112
00:09:59,810 --> 00:10:03,650
Then we have the second dictionary starting here and ending in here.

113
00:10:03,830 --> 00:10:06,220
So we have a list of dictionaries.

114
00:10:06,260 --> 00:10:08,660
What does its dictionary represent?

115
00:10:09,590 --> 00:10:13,610
Its dictionary has pairs of keys and values.

116
00:10:13,940 --> 00:10:18,710
So we have here and the value of here for the first rule.

117
00:10:18,860 --> 00:10:26,080
So each dictionary represents one row, for example, the very first rule we have here amongst the whole

118
00:10:26,160 --> 00:10:30,140
temperature pressure with these values here, these ones.

119
00:10:30,500 --> 00:10:33,890
So 2015, one one zero minus 20.

120
00:10:34,310 --> 00:10:35,510
This is what we have here.

121
00:10:36,020 --> 00:10:37,010
2013.

122
00:10:37,010 --> 00:10:37,850
One for the month.

123
00:10:37,850 --> 00:10:38,540
One for the day.

124
00:10:38,550 --> 00:10:40,190
Zero temperature minus 20.

125
00:10:40,190 --> 00:10:40,670
And so on.

126
00:10:40,850 --> 00:10:45,350
And then comes the second dictionary representing the second row and so on.

127
00:10:45,800 --> 00:10:54,560
So each of the dictionaries contain the name of the column pressure temperature, our as a dictionary

128
00:10:54,560 --> 00:10:54,890
key.

129
00:10:55,310 --> 00:10:57,080
That's how the dots all look like.

130
00:10:58,250 --> 00:11:08,900
That means you can extract, for example, the tenth row by doing this list indexing operation that

131
00:11:08,900 --> 00:11:12,980
should give you only the dictionary representing the 10th row.

132
00:11:13,220 --> 00:11:21,170
However, there are better ways to extract sort of rows and also columns from your sheet from a worksheet.

133
00:11:21,440 --> 00:11:24,290
And we're going to talk about them later in the course.

134
00:11:24,560 --> 00:11:25,670
But this was just.

135
00:11:26,790 --> 00:11:28,920
A quick way to get a role.

136
00:11:29,550 --> 00:11:35,820
Now this was how to get a sheet based on index, but if you want to get the sheet based on its name,

137
00:11:36,300 --> 00:11:40,500
for example, you have 2013 year and 2014.

138
00:11:40,860 --> 00:11:48,450
So in that case, let's add one more row gets a work sheet by name.

139
00:11:49,380 --> 00:11:50,160
That'd be.

140
00:11:52,400 --> 00:12:01,370
Worksheet one equal to spread sheet dot worksheets, methods you see now we have to give a title to

141
00:12:01,370 --> 00:12:09,110
this, the title of the worksheet, which is 2013 or 14, whatever whichever worksheets you need.

142
00:12:09,470 --> 00:12:10,850
So I can comment that out.

143
00:12:10,970 --> 00:12:19,130
Print and we're going to get the same data representing the worksheet with name two thousand thirteen.

144
00:12:20,160 --> 00:12:23,770
And that is the complete codes for this lecture, thank you for following.

145
00:12:23,790 --> 00:12:27,240
Let's do some more operations with Google Sheets.