1
00:00:00,570 --> 00:00:06,810
And this video, you learn how to update an existing column with new values and also how to add a new

2
00:00:06,810 --> 00:00:09,300
column in a Google spreadsheet.

3
00:00:09,720 --> 00:00:13,320
So let's start by updating this entire column.

4
00:00:15,790 --> 00:00:19,810
These are temperature dots in Celsius, degree air temperature.

5
00:00:20,170 --> 00:00:27,850
We want to convert this scene to Fahrenheit, which is the American temperature measurement unit, so

6
00:00:27,850 --> 00:00:38,380
we're looking at the column E, which spans from two to 25 E to E 25.

7
00:00:39,540 --> 00:00:47,090
So I have established a connection to the Google Sheets accounts, created a spreadsheet object, created

8
00:00:47,100 --> 00:00:48,090
a worksheet object.

9
00:00:48,420 --> 00:00:53,220
Now I can point to worksheet one, not update.

10
00:00:54,570 --> 00:01:00,270
We said it was E to two e twenty five.

11
00:01:00,540 --> 00:01:04,260
We want to update this with a new column.

12
00:01:04,470 --> 00:01:07,350
Now the column comes as a list.

13
00:01:07,650 --> 00:01:13,560
So before we supply a column here, we need to have that column ready.

14
00:01:13,830 --> 00:01:19,020
So let's calculate a list of new temperature values in foreign lines.

15
00:01:19,620 --> 00:01:28,590
First, though, we need to get to the actual the current values from E to to E twenty five.

16
00:01:29,040 --> 00:01:36,360
So that is the existing column we store that's in a variable.

17
00:01:36,930 --> 00:01:39,300
And let's see what we have.

18
00:01:40,540 --> 00:01:41,800
As existing column.

19
00:01:49,400 --> 00:01:50,750
So that is the ultimate.

20
00:01:52,120 --> 00:01:58,530
Over the existing column with existing temperature values, you'll notice that each value is inside

21
00:01:58,540 --> 00:01:59,020
a list.

22
00:01:59,200 --> 00:02:02,770
So it's a list of only two lists tiny lists.

23
00:02:03,070 --> 00:02:06,130
So the seat of A4, we need to take that into account.

24
00:02:07,320 --> 00:02:14,760
When we create when we calculate the new column list, which is equal to a list comprehension, so whenever

25
00:02:14,760 --> 00:02:21,240
you want to create a list out of another list, you use list comprehension.

26
00:02:21,390 --> 00:02:22,950
You can also use a for loop.

27
00:02:23,790 --> 00:02:30,840
But the list combinations are more by phonic and you just do the entire calculation in one line.

28
00:02:32,050 --> 00:02:38,350
So what do we want to do is we want to iterate for AI in existing column.

29
00:02:38,890 --> 00:02:41,750
Now what is I, I will be?

30
00:02:41,770 --> 00:02:44,690
Each of these lists these.

31
00:02:45,130 --> 00:02:50,950
That's and that's now what do you want to do to each list?

32
00:02:51,550 --> 00:02:51,910
Hmm.

33
00:02:51,940 --> 00:02:55,870
Well, first, we want to extract the item out of that list.

34
00:02:56,080 --> 00:02:58,450
So I zero, right?

35
00:02:59,140 --> 00:03:02,770
So if you print new column at this point.

36
00:03:04,950 --> 00:03:10,270
You're going to get a flat list, so these are strings.

37
00:03:10,300 --> 00:03:13,620
No, they are not lists with strings inside.

38
00:03:13,950 --> 00:03:19,440
So we're doing better now, but we need to do something else to those strings.

39
00:03:19,710 --> 00:03:23,250
First, we need to convert them into floats.

40
00:03:23,880 --> 00:03:24,270
Run.

41
00:03:25,880 --> 00:03:32,700
Yet now we have numbers, no quotes around those numbers, then for each number.

42
00:03:32,720 --> 00:03:40,890
What we want to do is we want to apply know the formula that converts Celsius degree to foreign nights

43
00:03:40,910 --> 00:03:41,990
and that is the formula.

44
00:03:41,990 --> 00:03:50,600
So Celsius degree of value times nine divided by five plus three gives Fahrenheit.

45
00:03:51,110 --> 00:03:56,630
So we're going to have a list of foreign nights for an existing column.

46
00:03:57,840 --> 00:03:58,680
Executed that.

47
00:04:00,630 --> 00:04:02,460
And these are the values.

48
00:04:05,150 --> 00:04:11,930
And if you want to round those numbers because we have too many decimal points out, puts Iran's.

49
00:04:13,060 --> 00:04:21,329
Function around the formula and put the formula and then put the formula inside parentheses.

50
00:04:22,029 --> 00:04:26,260
So that is the first argument of the round function.

51
00:04:26,650 --> 00:04:28,900
Then the second argument is one.

52
00:04:29,500 --> 00:04:33,400
So one digit is all we need to offer the decimal point.

53
00:04:35,910 --> 00:04:39,180
Now we're ready to execute the update function.

54
00:04:39,540 --> 00:04:50,250
So that column will get as values this new column so you can see new column there or what I would suggest

55
00:04:50,850 --> 00:04:56,430
is to put the new column in a new column like four g, for example.

56
00:04:57,510 --> 00:05:02,650
So I'd like to change this to G to G 25.

57
00:05:02,850 --> 00:05:03,870
But it's up to you.

58
00:05:03,870 --> 00:05:08,310
So if you leave them as they are, you override the existing values.

59
00:05:08,580 --> 00:05:09,990
So be aware of that.

60
00:05:10,620 --> 00:05:15,180
So run now and initially you gets an error.

61
00:05:16,050 --> 00:05:27,090
That is because the update method expects a list of lists, but our list is flat for now.

62
00:05:28,270 --> 00:05:36,060
You see, so if you want to have that list of lists, you want to put these values into lists, so the

63
00:05:36,100 --> 00:05:43,150
accounts function outputs, you have to put it into a list of when you execute again.

64
00:05:45,370 --> 00:05:49,300
And so this is the new column now list of lists.

65
00:05:51,290 --> 00:05:54,800
And this is that new column in the spreadsheet.

66
00:05:55,670 --> 00:05:59,960
If you want to have a heads or you could do something like.

67
00:06:04,260 --> 00:06:13,720
Foreign nights for the head there, so a least plus that lists because you see what we're doing is for

68
00:06:13,800 --> 00:06:14,280
a night.

69
00:06:15,490 --> 00:06:19,000
Plus, something like one to three, et cetera.

70
00:06:19,660 --> 00:06:22,510
At least you get a concatenated list.

71
00:06:23,050 --> 00:06:29,410
So that's first goes as the first item on the list of the other values go after that, the first item.

72
00:06:29,620 --> 00:06:32,860
But in our case, we have this nested list.

73
00:06:32,870 --> 00:06:36,610
So you want to use double square brackets executes.

74
00:06:38,350 --> 00:06:43,150
And don't forget that we need to change, you know, so foreign aid goes to one.

75
00:06:45,930 --> 00:06:47,310
Yeah, here we go.

76
00:06:47,700 --> 00:06:50,970
So we have a new column in the spreadsheet.

77
00:06:52,840 --> 00:06:56,060
And that is the entire codes for this video.

78
00:06:56,210 --> 00:06:56,940
Thank you so much.

79
00:06:56,960 --> 00:06:57,830
I'll talk to you a later.