Hello, and welcome to another episode of GDL-IL, GDL Israel.
Today we're going to do some [INAUDIBLE]
things.
We'll start with checking out how we could run NodeJS on
Google infrastructure on Google Cloud, and we'll use of
course Compute Engine to do that.
And later, we'll see how easy it is to use NodeJS in order
to run things like fetching data from other
sources on the web.
It might be another API, in our case we took a different
set of APIs, not directly Google ones, just to show you
the power of getting information from any open API
that you have out there.
And then, using Google Sheets in order to upload this API,
this data, and then later on of course using it and
leveraging it inside Google Sheets.
The nice thing or the nice theme that is connecting all
the dots in this episode will be JavaScript.
And we have JavaScript on the server side.
And of course, we'll use NodeJS to execute it.
And then, we'll have JavaScript as the scripting
language of Google Docs and Google Apps that allows you to
do clever, sophisticated things on the information, on
the data that you already fetched and put in your Google
Sheets in our case.
So without further ado, let me just share my screen with you.
And maybe today, I'll just share my old desktop so I can
jump quickly.
Or maybe it would be wiser, I'll just try to go through
this little blog post that just summarizes all the things
that we're going to touch on.
The blog post is live as of 10 minutes ago, so feel free to
go and check it out.
And of course, I'll put links to this blog post on the
footnotes of this show on the developer.google.com site.
So feel free, you don't need to write anything.
Basically, what we're going to show here is
the ability to go.
And in this case, I just choose Yahoo Finance API just
because I got lots of questions on how to use it and
leverage it from inside Google Sheets.
And here in the example you [INAUDIBLE], basically Yahoo
allows you to do things like get a certain URL.
Let me make all the fonts much larger.
And then, if you put in here, I put Netflix.
But you could put any ticker.
And after you put a ticker, you have a set of let's say
parameters, or it's more crypting keys that will fetch
the right information that you want.
And once you are clicking on it, basically you're
downloading a CSV file that you can later on work with it.
In our case, we want to do something nicer.
And basically, to take even farther this approach of
getting data, getting information, and then putting
it into good work, into good usage inside of Google Sheets.
So what I did here is after you've installed NodeJS, and
if you remember correctly, I have an episode in the past
here on this [INAUDIBLE]
how to install NodeJS on Compute Engine in less than
five minutes.
So just go search it and do that.
That's the first step that you need to do.
But after you install NodeJS, basically you just need those
three packages.
And luckily for us, the community around NodeJS is
holding a very large set of libraries and frameworks and
abilities, basically already made wheels for us.
So we don't need to reinvent the wheel again.
And in this case, I just choose those three libraries.
One is the request model that just lets you do
work easily with URLs.
Basically, we're using a very easy way of fetching the
information with a get request.
But once you install it, basically you have all the
different possibilities of doing authentication, doing
OAuth, post, and all the good things that you'll
want to do later on.
So it's good thing to have right at the beginning of a
new project that is going to do something interesting with
this API that is being fetched over HTTP.
Then, I put cheerio.
It's a cool name.
And for us, it's basically jQuery on the server side.
So the ability to work with that dollar sign that we all
love, and just to be able to have these CSS selectors to
get the right piece of information from this or that
location in the DOM.
Very easy to do.
So that's why I use this one.
And last but not least, just to make our life easier when
we're going to integrate and upload and edit and update our
information, let's just leverage again already built
model that's letting us just work with Google Spreadsheets.
Having those three pieces of code in place, our code will
be really, really short.
So in these three lines, we're just fetching the models so we
could work with them later.
And then, of course, we have those few parameters that we
want to keep as global to our script.
I highly recommend you to remove them, as in line 19
it's written here.
Not black and white, but clearly enough, you want to
remove them from the global scope.
In this specific, simple case I just left it there.
But probably the first thing that I'll do tomorrow morning
would be just to remove them from the global scope, make
sure that I'm encapsulating them inside an object so when
this project will get bigger and bigger and not just live
as a gist, we'll be able to have all the best practices of
software development.
And we want [INAUDIBLE]
so quickly.
So the basic constants here are just the ticker, and
probably it would be wiser just to put it as a variable
so that the user will enter from the script itself.
Later on, just the end point.
And in your case, it might be Google Cloud endpoint.
It might be any other API that's letting you just fetch
information over HTTP or HTTPS.
And then, just two actually arrays that are letting us
have the keys, or mainly the metadata on the data that
we're going to fetch, and the details themselves.
Then, we have a few functions that we're using.
sheetReady, we'll touch on it later.
And in this one, basically we're
just calling the request.
So after we're fetching the URL.
Here, you can see that first and foremost I just want to
see that everything came back correctly with
status code of 200.
If it's 400 or 500 or other things, I want probably to
just notify the user.
And in this case, I'm just loading into cheerio the body.
And then nicely enough, I have this dollar sign that
I could work with.
And in this case, I'm just choosing one table that's
containing all the information.
And then, I'm getting all the tds in this table into my
array, the keys.
And I'm doing just the same thing on a different class
name and fetching all the details
into a different array.
Once I do those two things, all I need to do now is
basically just of course letting ourselves know that we
have the right information in place.
And we'll run the script in a minute.
And then, I want to upload it.
So look how easy is the API itself.
I call it.
I'm creating a new one.
Basically, I already created one.
But here, I'm just creating or calling the constructor of
this object that here it's called spreadsheet.
And the debug information is just for us right now.
Actually, I see that we have two lines of it.
So I need to remove one.
Here, of course, you want to put your credentials, probably
your Gmail address and then your password.
And very important, the sheet name and the worksheet.
So both of them need to be here so we know which sheet we
are going to update.
And then we're calling, in the callback after this, returning
the sheetReady method.
And if we scroll back to this method, basically you could
see that it's a very, very simple one.
We're adding to the spreadsheet on the top.
So it will be at column one, row one, and then column one,
row two, the attribute and the value.
And then, we're just going to iterate and
just in those arrays.
And luckily for us, any looping here will just call
our object, the spreadsheet, and will add an array.
We're giving it the starting point.
And once I get the started point, all we need to do is
just attach the array, and all the information will be there.
And of course, we're sending all the information in one
batch and getting the response.
Here, probably you want to notify the user that
everything is done correctly and is in the good hands of
Google Cloud.
So all the data is now saved.
And of course, if something wrong happens, just
communicate it back.
That's all the script.
So you could see here that at the bottom, there's nothing
more than it.
And now, we could check or let me show you how you could
quite easily run this script.
So let's see again in this window.
This is my terminal.
I'll make the font even bigger, and
even one step bigger.
And what you can see here is that I'm calling node with the
fetch example that we have here.
And once I'm running it, quite quickly we're
going to Yahoo API.
We're fetching the information.
Here, the console log just making sure--
actually, it's from here--
that I'm fetching the information.
And you can see that in this case, for instance, the key
would be the market cap.
And then, I have the overall value of the company.
And again, and again, lots of other parameters.
And then, when I'm wanting to upload it to Google, I got a
log in error.
And this is because all I have there right now are the to
dos, and not my real credentials.
But I highly encourage you to fork the gist that we just
showed and just put your credentials and work with it
and see how fluidly it's working.
The other beauty is that you could take this code that we
ran, and let me just again share with you
that snippet itself.
We could actually see it here.
And this code, with very few changes, will work from Google
App, Google Sheets as well.
So if you don't want even to have the ability or the
efficiency of running this simple script inside Node, you
could do the same from JavaScript
that will run in Sheets.
And that's another benefit for the ones that don't want to
work with a Node instance.
However, I highly encourage you to check Compute Engine
and to check the ability to run with NodeJS because the
performances will be much, much better.
And if you want to do something more than just
putting a simple example like that, you want some heavy
lifting on the server side, some data manipulation, some
things that you do want a strong server side.
NodeJS on Compute Engine will give you those benefits.
And it's highly recommended to check it out.
So if we have any, questions right now is
that the right time.
Let's see here.
Let's see if in the chat we have some.
I don't see any question right now.
So if you have later on any questions, +GreenIdo on G+ or
GreenIdo as one word on Twitter.
Both will work quite nicely.
Happy hacking, and be strong.
Bye-bye.