1
00:00:00,690 --> 00:00:07,380
Now that we have created a new Google sheet where we'll be looking at data and the necessary credentials

2
00:00:07,560 --> 00:00:12,480
for our flow can go ahead and start building our flow.

3
00:00:13,080 --> 00:00:19,180
So now we're working in version three of the flow that are copied a bit earlier in version three.

4
00:00:19,200 --> 00:00:23,070
You can see that there is the lock data to cheat sheet node.

5
00:00:23,380 --> 00:00:29,820
And what I want to do first before I do anything else more complicated is to make sure that this node

6
00:00:29,820 --> 00:00:34,700
can actually create new rows inside the Google sheet.

7
00:00:35,160 --> 00:00:41,910
So I'm going to set it up first and then trigger it manually using perhaps a an inject note.

8
00:00:42,570 --> 00:00:47,250
So let's go ahead and set up the credentials first set up in the properties tab.

9
00:00:47,250 --> 00:00:50,700
You'll see that got the new OTH option.

10
00:00:50,730 --> 00:00:59,010
So select that and click on the penciled button and here we'll copy our adjacent file with our credentials.

11
00:00:59,220 --> 00:01:04,380
Select copy and paste that in here.

12
00:01:04,720 --> 00:01:05,190
All right.

13
00:01:05,490 --> 00:01:08,760
Remove the last line and that's it.

14
00:01:08,880 --> 00:01:17,130
Now for the spreadsheet I.D. Let's copy the necessary segment of the URL, which is this bit here right

15
00:01:17,130 --> 00:01:20,910
after the slash D and just before edit is what we need.

16
00:01:21,630 --> 00:01:22,980
So copy that.

17
00:01:27,570 --> 00:01:34,210
And pasted in this pretty out the field now for the cells.

18
00:01:34,230 --> 00:01:41,430
I just need to provide information about the first cell that looked like the sheet node to append to

19
00:01:41,430 --> 00:01:44,750
its first row or its first data item.

20
00:01:45,210 --> 00:01:50,130
So that is going to be this cell right here, because we've got the header in row one.

21
00:01:50,130 --> 00:01:51,460
So it's going to be a two.

22
00:01:52,230 --> 00:01:53,630
This is some row.

23
00:01:54,570 --> 00:01:58,710
So this is still a two in sheet number one.

24
00:01:59,160 --> 00:02:04,980
And you may rename this sheet, of course, but you need to make note of whatever name you give it because

25
00:02:04,980 --> 00:02:07,190
you have to use it in the cell field.

26
00:02:07,200 --> 00:02:13,860
So this is going to be sheet one it by an exclamation mark.

27
00:02:13,870 --> 00:02:20,910
And then with the name of the cell that I'm targeting for the first update, eight to.

28
00:02:21,320 --> 00:02:21,660
Right.

29
00:02:22,170 --> 00:02:24,160
And that is enough.

30
00:02:24,180 --> 00:02:26,560
Now this so everything goes well.

31
00:02:26,580 --> 00:02:29,580
Should send its first data item here.

32
00:02:30,690 --> 00:02:33,450
Now let's see if it really works.

33
00:02:33,480 --> 00:02:36,060
I'm going to add and inject note.

34
00:02:39,040 --> 00:02:45,130
So you can manually trigger the mood and just say hello.

35
00:02:45,320 --> 00:02:46,530
What's really matter, what you put in here?

36
00:02:46,720 --> 00:02:53,020
Just a little test and this is going to be a string, just going to send the string hello.

37
00:02:53,230 --> 00:02:55,330
And store it in my Google sheet.

38
00:02:55,360 --> 00:02:56,410
Just make sure it works.

39
00:02:56,620 --> 00:02:59,890
So deploy and see if it works.

40
00:03:00,130 --> 00:03:03,780
Click on the trigger to trigger the node.

41
00:03:04,010 --> 00:03:05,230
The node.

42
00:03:07,930 --> 00:03:08,530
And it works.

43
00:03:09,540 --> 00:03:13,320
For some, again, you can see.

44
00:03:15,310 --> 00:03:22,940
Again, OK, the next helo came through rule number three, so was suspended in the next available through.

45
00:03:24,660 --> 00:03:25,040
All right.

46
00:03:26,670 --> 00:03:34,380
This works and of course, the information that will be coming from the flow to Google Sheet is not

47
00:03:34,380 --> 00:03:37,490
just going to be static and it will definitely not say hello.

48
00:03:37,890 --> 00:03:42,090
It's going to be more like a set up Comedy Limited file.

49
00:03:42,900 --> 00:03:44,190
Something like this.

50
00:03:44,310 --> 00:03:47,910
Right, is going to have the timestamp at the beginning.

51
00:03:47,910 --> 00:03:52,410
Then it's going to have the name of the device where the data is coming from.

52
00:03:52,620 --> 00:03:58,700
This is a name that the ISP 32 is sending to the flow and then it's going to have the data.

53
00:03:59,160 --> 00:04:06,350
This item here is the real value of the potential or the soil humidity sensor.

54
00:04:06,350 --> 00:04:11,210
So this is the state of the motor and then the voltages.

55
00:04:11,940 --> 00:04:21,690
So let's send this through instead of just hello and see what it looks like once it gets to the other

56
00:04:21,690 --> 00:04:22,920
side to deploy.

57
00:04:24,330 --> 00:04:33,840
And send it through and it looks like this, I'm just going to reformat this so there is no back.

58
00:04:34,470 --> 00:04:35,170
That's better.

59
00:04:36,570 --> 00:04:45,630
Now, as you can see, I have to send the data to Google Sheet in a bundle in delimited string.

60
00:04:46,170 --> 00:04:52,020
And what I want to do is to be able to split this string so that each of these items, the timestamp,

61
00:04:52,020 --> 00:04:55,180
the name of the client, etc, appears in its own column.

62
00:04:55,890 --> 00:05:01,680
So the Google sheet, like Excel, has a split function.

63
00:05:02,010 --> 00:05:09,750
So we can use that to achieve that result because the adjacent node itself cannot append data items

64
00:05:09,750 --> 00:05:11,640
across multiple columns.

65
00:05:11,670 --> 00:05:14,730
So we have to do some further processing in the sheet itself.

66
00:05:15,330 --> 00:05:22,050
So I just say I just use the split function and I say that this is what I want to split, select the

67
00:05:22,050 --> 00:05:27,360
item, the seal that contains string that I want to split, and then the limiter is going to be the

68
00:05:27,360 --> 00:05:27,850
comma.

69
00:05:28,170 --> 00:05:33,810
So I'm going to define the delimiter inside then, of course, and that should do it.

70
00:05:35,760 --> 00:05:41,160
I'm going to remove the bull's character from the output.

71
00:05:41,170 --> 00:05:46,620
And now we've got the time and date stamp, the time stamp client.

72
00:05:46,620 --> 00:05:49,260
So humidity, etc. looks good.

73
00:05:50,490 --> 00:05:55,350
So I can get rid of the first few tests and we're going to be like this.

74
00:05:55,530 --> 00:05:58,190
Let's send one more to be sure.

75
00:05:59,480 --> 00:06:08,660
And it's good she came through, I just need to apply the same function to it like that if I want to

76
00:06:08,660 --> 00:06:10,510
do the actual processing later.

77
00:06:12,640 --> 00:06:21,210
OK, so this gives me confidence to know that the node for a pending charity, the Google Shit, works,

78
00:06:21,220 --> 00:06:27,160
or I can go ahead and finish up with the new version of the flu.

79
00:06:29,910 --> 00:06:38,850
So, you know, now I need to set up the data that will go into the Google sheet as a stream of text

80
00:06:38,850 --> 00:06:42,010
where the individual values are separated by a comma.

81
00:06:43,080 --> 00:06:49,650
What I am getting from the operation function is basically whether the pump should be on or off, and

82
00:06:49,830 --> 00:06:52,900
it doesn't give me the information in the format that I need it.

83
00:06:53,370 --> 00:07:01,260
So what I have to do is to create a new function so that it can prepare the data in a format that I

84
00:07:01,260 --> 00:07:02,010
need them to be.

85
00:07:02,520 --> 00:07:05,940
So this function, let's call it repair

86
00:07:08,880 --> 00:07:12,210
sheet haloed.

87
00:07:15,500 --> 00:07:21,800
And in it, I'm going to use a bit of JavaScript to concatenate the committee limited string as I need

88
00:07:21,800 --> 00:07:23,120
it for the sheet.

89
00:07:24,020 --> 00:07:30,230
So in this challenge, could do extensive use of the flow variables.

90
00:07:30,350 --> 00:07:37,550
So I'll be collecting variables from different parts of the flow and then concatenating them into a

91
00:07:37,580 --> 00:07:38,700
single string.

92
00:07:39,410 --> 00:07:44,690
So the script is a bit expensive, so I have prepared it earlier and then I'm just going to copy it

93
00:07:44,690 --> 00:07:50,420
in here and explain what it does and then you'll see exactly how it works.

94
00:07:50,840 --> 00:07:55,980
So first of all, we need to use the date function to create the timestamp.

95
00:07:56,270 --> 00:08:03,650
And then once we have the date and time in the today object, I am using the various functions such

96
00:08:03,650 --> 00:08:10,790
as set month and get date and so on, to put them together into a single date string variable.

97
00:08:11,330 --> 00:08:20,390
Then I can use this date string variable which contains the timestamp that I need in a new payload variable

98
00:08:20,390 --> 00:08:21,700
that I've just started here.

99
00:08:21,740 --> 00:08:28,510
And this is going to be where the comic delimited string is created step by step or part by part.

100
00:08:29,060 --> 00:08:37,370
We start with the date, then I'm adding a comma with a plus equal sine concatenated.

101
00:08:37,370 --> 00:08:40,480
That is the string of Capponi, the comma to the date.

102
00:08:40,880 --> 00:08:47,810
Then the next component would be the device name, which I am reading from a flow variable with the

103
00:08:47,810 --> 00:08:48,860
name device name.

104
00:08:49,220 --> 00:08:50,440
Then I add another comma.

105
00:08:50,990 --> 00:08:58,430
Then I grab the value stored in the raw humidity value flow variable and then add another comma and

106
00:08:58,430 --> 00:09:01,190
so, so component by component.

107
00:09:01,190 --> 00:09:07,970
I eventually end up with the string that contains all the bits of data that I want to transmit over

108
00:09:07,970 --> 00:09:09,230
to the Google sheet.

109
00:09:10,100 --> 00:09:17,000
Eventually I'm going to use this new payload variable as the payload for this function.

110
00:09:18,380 --> 00:09:23,870
Of course, I need to create those flow variables elsewhere in the flow rate.

111
00:09:23,870 --> 00:09:26,030
So for now this is what we have.

112
00:09:26,030 --> 00:09:29,950
But I'm going to have to create a flow variable for the device name for the real community value.

113
00:09:30,260 --> 00:09:36,800
The pump state motor voltage and empty voltage is actually very easy to do, as you'll see in a moment.

114
00:09:36,800 --> 00:09:40,040
And I already have all of the data that I need to do that.

115
00:09:40,730 --> 00:09:50,570
So click on down here to commit the changes and then connect the new function node to the data, to

116
00:09:50,570 --> 00:09:51,110
the Google.

117
00:09:51,110 --> 00:09:55,430
She'd node and move this a bit to the side as well and make it a bit of room.

118
00:09:56,630 --> 00:10:03,080
And then the next thing that I need to do is to prepare to make some changes to the existing pump operation

119
00:10:04,130 --> 00:10:10,790
function, because this is where I need to insert some rights to the flow variables that I need.

120
00:10:11,630 --> 00:10:13,250
So I'm going to do a bit of typing here.

121
00:10:13,250 --> 00:10:21,170
So first of all, we need to set the flow variable for the raw

122
00:10:23,450 --> 00:10:32,690
humidity value, which is going to grab the necessary data from real humidity value like this.

123
00:10:35,130 --> 00:10:44,010
And will it did the same thing for the device name or the device name here, so just pasting will do

124
00:10:44,010 --> 00:10:45,040
the job just nicely.

125
00:10:45,450 --> 00:10:54,170
There's one more that we need to sit on the floor that we need to set, and that is the pump state.

126
00:10:54,300 --> 00:11:03,180
I'm just going to copy the text from here and then insert the fluid set function, of course, to change

127
00:11:03,180 --> 00:11:07,500
the name to be pump state like this.

128
00:11:10,480 --> 00:11:12,860
And one more item here.

129
00:11:14,110 --> 00:11:24,670
Now, remember that the value that we actually are storing in the Google sheet can assure you here is

130
00:11:24,670 --> 00:11:29,440
going to look like this or this is how I'd like it to look like, because essentially this is going

131
00:11:29,440 --> 00:11:31,300
to be for human consumption.

132
00:11:31,540 --> 00:11:36,210
It's not going to control a device or it's not going to be read by the security, for example.

133
00:11:36,670 --> 00:11:45,490
So I prefer to use words like on and off instead of numbers like one and zero for this particular case.

134
00:11:45,580 --> 00:11:54,370
So I'm going to just say on here, instead of just copying the number one from the variable itself and

135
00:11:55,000 --> 00:12:01,270
we're going to say fourth down here, instead of saying zero, it's going to make it a bit more readable

136
00:12:01,270 --> 00:12:03,190
in the Google sheet.

137
00:12:04,940 --> 00:12:10,610
And unless you've got a typo somewhere here that this function is now ready, so I'm going to click

138
00:12:10,610 --> 00:12:11,090
on Done.

139
00:12:12,350 --> 00:12:20,030
Am I prepare, she payloaders, requires the flows to be said for motor voltage and MCO voltage.

140
00:12:20,060 --> 00:12:22,490
So let's do that here.

141
00:12:23,150 --> 00:12:26,030
Can make a little bit of room down here.

142
00:12:26,040 --> 00:12:35,530
This is where motor voltage and the MCU voltage are being retrieved from the queue or from the In Quiddity

143
00:12:35,540 --> 00:12:36,110
broker.

144
00:12:36,710 --> 00:12:42,040
So, again, you will need to add a couple of new functions.

145
00:12:42,050 --> 00:12:43,610
So the first one is this.

146
00:12:45,800 --> 00:12:57,140
And call this one set to you with voltage vulnerable is going to be the flow of our operation to be

147
00:12:57,140 --> 00:13:07,300
more specific and slow, said MCU Voltage.

148
00:13:09,230 --> 00:13:14,180
And this is going to be set to whatever comes in to the function.

149
00:13:16,830 --> 00:13:27,670
For the return statement, I'm going to return Haloed, the same panel that comes in, I'm going to

150
00:13:27,750 --> 00:13:28,800
propagate it out.

151
00:13:30,050 --> 00:13:34,490
And I've got a clip here, and, of course, I need to finish up with the semicolon.

152
00:13:37,930 --> 00:13:46,440
All right, so let's do a couple of changes, remove that connection and set it to this and then.

153
00:13:48,140 --> 00:13:49,280
To the dashboard.

154
00:13:50,060 --> 00:13:50,440
OK.

155
00:13:53,800 --> 00:14:00,100
With my drawing here, I tried to make or the outputs to be on the same column as much as possible just

156
00:14:00,130 --> 00:14:05,030
to keep it a bit clearer and it makes it easier to read it.

157
00:14:05,050 --> 00:14:05,570
All right.

158
00:14:05,580 --> 00:14:10,900
So I'm going to do the same thing for the motor and I'm going to just copy and paste.

159
00:14:11,930 --> 00:14:20,720
The note and make a few quick changes with the connections, and then I'll do the renaming inside the

160
00:14:20,720 --> 00:14:24,230
node three inside here, the signal for the motor.

161
00:14:27,240 --> 00:14:30,800
And this is going to be the motor voltage.

162
00:14:35,350 --> 00:14:35,870
Like that.

163
00:14:39,050 --> 00:14:39,790
Well done.

164
00:14:46,610 --> 00:14:48,650
All right, so let's see.

165
00:14:48,680 --> 00:14:49,620
Is there anything missing?

166
00:14:49,640 --> 00:14:55,550
I've got the device name, real humidity, dunned pump, state motor voltage done, NMC voltage all

167
00:14:55,550 --> 00:14:55,840
done.

168
00:14:55,860 --> 00:14:59,390
So it seems like everything we need for this to work is here.

169
00:14:59,930 --> 00:15:11,150
Now, another thing that I was thinking was that I want to control the rate by which I update the Google

170
00:15:11,150 --> 00:15:13,810
sheet or I access the Google API.

171
00:15:13,820 --> 00:15:19,100
There is a limit to how many times you can actually sent data to Google Sheet and if you exceeded,

172
00:15:19,520 --> 00:15:22,490
you will be locked out of it for a period of time.

173
00:15:22,910 --> 00:15:29,660
And also, you don't want to send out too many updates because then it's just logical, shared with

174
00:15:29,660 --> 00:15:32,210
a lot of data that are not really necessary.

175
00:15:32,660 --> 00:15:38,360
So I was thinking, what is a reasonable amount of updates to be sending to Google Sheet?

176
00:15:38,870 --> 00:15:45,110
And I decided that at least if they experimented with it for a while, that about once per minute is

177
00:15:45,110 --> 00:15:46,130
probably more than enough.

178
00:15:46,130 --> 00:15:50,780
But it's good enough, especially while you are testing it so you don't have to wait for too long for

179
00:15:50,780 --> 00:15:59,210
updates and that it is probably useful as well to be updating your sheet every time that there is a

180
00:15:59,210 --> 00:16:01,700
change in the state of the modem.

181
00:16:02,300 --> 00:16:13,130
So to make this possible, I will not just connect the RBA note to the function, because if I do that,

182
00:16:13,460 --> 00:16:21,020
then I'm going to be getting updates only when there is a change of the motor, not when there is a

183
00:16:21,020 --> 00:16:23,810
change in the motor state plus once per minute.

184
00:16:24,260 --> 00:16:31,010
So instead what I'll do is I'm going to use a trigger.

185
00:16:32,920 --> 00:16:37,500
Notice that there is Trigonal going to use this note here.

186
00:16:38,760 --> 00:16:44,290
And attach it to around here, just move things a bit to the right, a little more like this.

187
00:16:44,820 --> 00:16:47,070
So what this will do is that.

188
00:16:48,240 --> 00:16:57,000
When there is a change in the state of the motor, this trigger note will trigger the function, which

189
00:16:57,000 --> 00:16:59,400
will cause an update to the Google sheet.

190
00:16:59,700 --> 00:17:07,440
But in it, I will also configure it so that the node itself for the trigger node itself will trigger

191
00:17:07,440 --> 00:17:10,470
an updated sheet once per minute.

192
00:17:10,560 --> 00:17:12,360
You can change this to one minute.

193
00:17:14,800 --> 00:17:20,980
And then resent every one minute, so it's going to send number one, every one minute and of course,

194
00:17:20,980 --> 00:17:28,450
all the information or all the data that the function needs for it to do its job are available via flow

195
00:17:28,450 --> 00:17:29,260
variables.

196
00:17:29,290 --> 00:17:35,740
It will actually send fresh data to the Google sheet and not like old stale data.

197
00:17:36,640 --> 00:17:38,950
So this is how you set it up.

198
00:17:40,990 --> 00:17:41,910
Can give it a name.

199
00:17:47,640 --> 00:17:57,480
And connected, so this in a way, also acts as a throttle, so it does not flood the Google API with

200
00:17:57,480 --> 00:17:58,320
new requests.

201
00:17:59,730 --> 00:18:02,450
Now, this flow is pretty much ready.

202
00:18:02,460 --> 00:18:07,590
There are just two things that I want to add, and they are simple debug.

203
00:18:09,470 --> 00:18:20,060
Notes like debugged note to come from the payload function so that I can see exactly what is coming

204
00:18:20,060 --> 00:18:24,320
out of it, so I'm going to call this sheet function.

205
00:18:25,510 --> 00:18:32,030
You can see the output as I am in the first few updates, make sure everything is working properly.

206
00:18:32,510 --> 00:18:35,450
And then I will also create a.

207
00:18:37,900 --> 00:18:40,630
Debug mode for the.

208
00:18:41,620 --> 00:18:43,640
Good for the J.A. output.

209
00:18:43,810 --> 00:18:45,550
So this is going to be geeshie

210
00:18:48,310 --> 00:18:48,910
paillard.

211
00:18:51,720 --> 00:18:52,170
All right.

212
00:18:55,700 --> 00:18:58,340
And this is ready to test.

213
00:19:00,110 --> 00:19:05,600
So this lecture has been long enough already, so how about we have a quick break here and go over to

214
00:19:05,600 --> 00:19:08,540
the next lecture, where will you test?