Now that we have created a new Google sheet where we'll be looking at data and the necessary credentials
for our flow can go ahead and start building our flow.
So now we're working in version three of the flow that are copied a bit earlier in version three.
You can see that there is the lock data to cheat sheet node.
And what I want to do first before I do anything else more complicated is to make sure that this node
can actually create new rows inside the Google sheet.
So I'm going to set it up first and then trigger it manually using perhaps a an inject note.
So let's go ahead and set up the credentials first set up in the properties tab.
You'll see that got the new OTH option.
So select that and click on the penciled button and here we'll copy our adjacent file with our credentials.
Select copy and paste that in here.
All right.
Remove the last line and that's it.
Now for the spreadsheet I.D. Let's copy the necessary segment of the URL, which is this bit here right
after the slash D and just before edit is what we need.
So copy that.
And pasted in this pretty out the field now for the cells.
I just need to provide information about the first cell that looked like the sheet node to append to
its first row or its first data item.
So that is going to be this cell right here, because we've got the header in row one.
So it's going to be a two.
This is some row.
So this is still a two in sheet number one.
And you may rename this sheet, of course, but you need to make note of whatever name you give it because
you have to use it in the cell field.
So this is going to be sheet one it by an exclamation mark.
And then with the name of the cell that I'm targeting for the first update, eight to.
Right.
And that is enough.
Now this so everything goes well.
Should send its first data item here.
Now let's see if it really works.
I'm going to add and inject note.
So you can manually trigger the mood and just say hello.
What's really matter, what you put in here?
Just a little test and this is going to be a string, just going to send the string hello.
And store it in my Google sheet.
Just make sure it works.
So deploy and see if it works.
Click on the trigger to trigger the node.
The node.
And it works.
For some, again, you can see.
Again, OK, the next helo came through rule number three, so was suspended in the next available through.
All right.
This works and of course, the information that will be coming from the flow to Google Sheet is not
just going to be static and it will definitely not say hello.
It's going to be more like a set up Comedy Limited file.
Something like this.
Right, is going to have the timestamp at the beginning.
Then it's going to have the name of the device where the data is coming from.
This is a name that the ISP 32 is sending to the flow and then it's going to have the data.
This item here is the real value of the potential or the soil humidity sensor.
So this is the state of the motor and then the voltages.
So let's send this through instead of just hello and see what it looks like once it gets to the other
side to deploy.
And send it through and it looks like this, I'm just going to reformat this so there is no back.
That's better.
Now, as you can see, I have to send the data to Google Sheet in a bundle in delimited string.
And what I want to do is to be able to split this string so that each of these items, the timestamp,
the name of the client, etc, appears in its own column.
So the Google sheet, like Excel, has a split function.
So we can use that to achieve that result because the adjacent node itself cannot append data items
across multiple columns.
So we have to do some further processing in the sheet itself.
So I just say I just use the split function and I say that this is what I want to split, select the
item, the seal that contains string that I want to split, and then the limiter is going to be the
comma.
So I'm going to define the delimiter inside then, of course, and that should do it.
I'm going to remove the bull's character from the output.
And now we've got the time and date stamp, the time stamp client.
So humidity, etc. looks good.
So I can get rid of the first few tests and we're going to be like this.
Let's send one more to be sure.
And it's good she came through, I just need to apply the same function to it like that if I want to
do the actual processing later.
OK, so this gives me confidence to know that the node for a pending charity, the Google Shit, works,
or I can go ahead and finish up with the new version of the flu.
So, you know, now I need to set up the data that will go into the Google sheet as a stream of text
where the individual values are separated by a comma.
What I am getting from the operation function is basically whether the pump should be on or off, and
it doesn't give me the information in the format that I need it.
So what I have to do is to create a new function so that it can prepare the data in a format that I
need them to be.
So this function, let's call it repair
sheet haloed.
And in it, I'm going to use a bit of JavaScript to concatenate the committee limited string as I need
it for the sheet.
So in this challenge, could do extensive use of the flow variables.
So I'll be collecting variables from different parts of the flow and then concatenating them into a
single string.
So the script is a bit expensive, so I have prepared it earlier and then I'm just going to copy it
in here and explain what it does and then you'll see exactly how it works.
So first of all, we need to use the date function to create the timestamp.
And then once we have the date and time in the today object, I am using the various functions such
as set month and get date and so on, to put them together into a single date string variable.
Then I can use this date string variable which contains the timestamp that I need in a new payload variable
that I've just started here.
And this is going to be where the comic delimited string is created step by step or part by part.
We start with the date, then I'm adding a comma with a plus equal sine concatenated.
That is the string of Capponi, the comma to the date.
Then the next component would be the device name, which I am reading from a flow variable with the
name device name.
Then I add another comma.
Then I grab the value stored in the raw humidity value flow variable and then add another comma and
so, so component by component.
I eventually end up with the string that contains all the bits of data that I want to transmit over
to the Google sheet.
Eventually I'm going to use this new payload variable as the payload for this function.
Of course, I need to create those flow variables elsewhere in the flow rate.
So for now this is what we have.
But I'm going to have to create a flow variable for the device name for the real community value.
The pump state motor voltage and empty voltage is actually very easy to do, as you'll see in a moment.
And I already have all of the data that I need to do that.
So click on down here to commit the changes and then connect the new function node to the data, to
the Google.
She'd node and move this a bit to the side as well and make it a bit of room.
And then the next thing that I need to do is to prepare to make some changes to the existing pump operation
function, because this is where I need to insert some rights to the flow variables that I need.
So I'm going to do a bit of typing here.
So first of all, we need to set the flow variable for the raw
humidity value, which is going to grab the necessary data from real humidity value like this.
And will it did the same thing for the device name or the device name here, so just pasting will do
the job just nicely.
There's one more that we need to sit on the floor that we need to set, and that is the pump state.
I'm just going to copy the text from here and then insert the fluid set function, of course, to change
the name to be pump state like this.
And one more item here.
Now, remember that the value that we actually are storing in the Google sheet can assure you here is
going to look like this or this is how I'd like it to look like, because essentially this is going
to be for human consumption.
It's not going to control a device or it's not going to be read by the security, for example.
So I prefer to use words like on and off instead of numbers like one and zero for this particular case.
So I'm going to just say on here, instead of just copying the number one from the variable itself and
we're going to say fourth down here, instead of saying zero, it's going to make it a bit more readable
in the Google sheet.
And unless you've got a typo somewhere here that this function is now ready, so I'm going to click
on Done.
Am I prepare, she payloaders, requires the flows to be said for motor voltage and MCO voltage.
So let's do that here.
Can make a little bit of room down here.
This is where motor voltage and the MCU voltage are being retrieved from the queue or from the In Quiddity
broker.
So, again, you will need to add a couple of new functions.
So the first one is this.
And call this one set to you with voltage vulnerable is going to be the flow of our operation to be
more specific and slow, said MCU Voltage.
And this is going to be set to whatever comes in to the function.
For the return statement, I'm going to return Haloed, the same panel that comes in, I'm going to
propagate it out.
And I've got a clip here, and, of course, I need to finish up with the semicolon.
All right, so let's do a couple of changes, remove that connection and set it to this and then.
To the dashboard.
OK.
With my drawing here, I tried to make or the outputs to be on the same column as much as possible just
to keep it a bit clearer and it makes it easier to read it.
All right.
So I'm going to do the same thing for the motor and I'm going to just copy and paste.
The note and make a few quick changes with the connections, and then I'll do the renaming inside the
node three inside here, the signal for the motor.
And this is going to be the motor voltage.
Like that.
Well done.
All right, so let's see.
Is there anything missing?
I've got the device name, real humidity, dunned pump, state motor voltage done, NMC voltage all
done.
So it seems like everything we need for this to work is here.
Now, another thing that I was thinking was that I want to control the rate by which I update the Google
sheet or I access the Google API.
There is a limit to how many times you can actually sent data to Google Sheet and if you exceeded,
you will be locked out of it for a period of time.
And also, you don't want to send out too many updates because then it's just logical, shared with
a lot of data that are not really necessary.
So I was thinking, what is a reasonable amount of updates to be sending to Google Sheet?
And I decided that at least if they experimented with it for a while, that about once per minute is
probably more than enough.
But it's good enough, especially while you are testing it so you don't have to wait for too long for
updates and that it is probably useful as well to be updating your sheet every time that there is a
change in the state of the modem.
So to make this possible, I will not just connect the RBA note to the function, because if I do that,
then I'm going to be getting updates only when there is a change of the motor, not when there is a
change in the motor state plus once per minute.
So instead what I'll do is I'm going to use a trigger.
Notice that there is Trigonal going to use this note here.
And attach it to around here, just move things a bit to the right, a little more like this.
So what this will do is that.
When there is a change in the state of the motor, this trigger note will trigger the function, which
will cause an update to the Google sheet.
But in it, I will also configure it so that the node itself for the trigger node itself will trigger
an updated sheet once per minute.
You can change this to one minute.
And then resent every one minute, so it's going to send number one, every one minute and of course,
all the information or all the data that the function needs for it to do its job are available via flow
variables.
It will actually send fresh data to the Google sheet and not like old stale data.
So this is how you set it up.
Can give it a name.
And connected, so this in a way, also acts as a throttle, so it does not flood the Google API with
new requests.
Now, this flow is pretty much ready.
There are just two things that I want to add, and they are simple debug.
Notes like debugged note to come from the payload function so that I can see exactly what is coming
out of it, so I'm going to call this sheet function.
You can see the output as I am in the first few updates, make sure everything is working properly.
And then I will also create a.
Debug mode for the.
Good for the J.A. output.
So this is going to be geeshie
paillard.
All right.
And this is ready to test.
So this lecture has been long enough already, so how about we have a quick break here and go over to
the next lecture, where will you test?