Node.js Node + JS Interactive 2019, 19 Dec 2019

Previous Meeting Next Meeting

⏯

youtube image

►

From YouTube: Work Less and Do More: Google Sheets for JavaScript Developers - Franziska Hinkelmann & Leah Cole

Description

Work Less and Do More: Google Sheets for JavaScript Developers - Franziska Hinkelmann & Leah Cole, Google

Say goodbye to the days of TPS reports, and come learn how to automate the tedious tasks in your work day. Thanks to the extensibility of Google Sheets (and a special JavaScript client library for Sheets), as well as Cloud Functions, that’s easy. You can have all kinds of data, for example GitHub, Jira, uptime health, and even sales data combined and imported into a Sheet, analyzed, and output as an email report or slide deck-without ever opening the Sheet.

A

Good afternoon welcome back from lunch. We hope you're having a great time at node and J is interactive. So far, okay, good good good! Thank you for joining us. We will show you some cool things that you can do with spreadsheets and nodejs.

A

You might know me from my talks about the aid of JavaScript compilers, but I switch teams, I work in the Google cloud org now so I want to talk about cloud stuff, so this will be very different from any other talks you might have seen from me will give you sort of a basic introduction to some of the Google cloud platform products specifically service functions and we'll tie it all together using spreadsheets for prototyping conveniently sheets spreadsheets sheets is also part of Google cloud. So all the api's work together really nicely.

A

My name is Francisca Hinkleman I'm at Google, based in New, York, City and I'm, a member of the nodejs technical steering committee and I'm co-presenting with Lea hi.

B

I'm Lea Cole I am beast out of Sunnyvale California and I typically use node to write samples for data analytics and ml products in cloud and I am by no means a node expert in fact, Fonzie's. My go-to node expert, especially when I want advice on best policies for our samples and I'm. Also a super-duper G suite enthusiast I definitely use G suite before Google, but once I started using it everyday for my work, I found that I started using it outside of work too, to like plan vacations and stuff.

B

A

So, let's get started with work less and do more sheets for note developers. Why are we talking to you about spreadsheets? Well, the great thing about spreadsheets is that they allow you to collaborate with anybody, not just with developers spreadsheets. They have this universal language. You might say that pretty much anybody can pick up. So anybody that you want to work with of their background. They can edit the data. It can read the data they can filter data sorted play around with it and draw their own conclusions, which can sometimes be really useful. I'm.

A

Sorry, my puppy is crying all right. So sheets are good if you want to collaborate with non-engineers and not only can pretty much anybody work with the sheets sheets, make it really easy to visualize data, and so you can analyze your data by looking at the visuals you created. Obviously, you can create all kinds of visuals punch, pie, charts bar bar charts line graphs whatever, and yes as engineers, we could write code.

A

We could use d3.js and generate really pretty graphs, but it usually takes a lot longer than just using a sheet to visualize some data and when I need to iterate quickly, then sheets. Allow me to focus on the trends and correlations instead of the code, so lots of advantages to sheets and because of all those sheets are really useful when you want to prototype something with data and metrics, so probably not for a full-fledged serious production product, but if you're just prototyping- and you want to iterate quickly sheets, give you a lot of advantages.

A

B

Don't know about you, though, but I do not enjoy entering and manipulating data in spreadsheets, but it's because it's not something that I do all the time.

B

I do think, though, that sheets are an awesome tool for the output and especially for beautiful visualizations like this one, and so that's why we're going to use that today, but because the manual parts aren't fun, we're going to eliminate them and instead automate them and because we're at node and J is interactive and we love node we're going to use that I mean you heard Franz he's a node core contributor and on the technical steering committee, and she talks about how awesome note is a lot and it's it's.

B

A good influence before I was working with her I, definitely gravitated towards Python in Java, because that was what I was more comfortable with, but especially now that sink away, it makes things more readable, I, definitely gravitate more towards node for that automating.

B

Alright, so we're gonna start by writing this node application to work with sheets we're going to install the Google. Api is client library from the NPM registry. Like so, and it's a full-fledged API for talking to sheets that we're going to use but before we dive in I, do want to give a quick shout out to the G suite api's as a whole, if you're not sure, we've been using this term G suite a lot, it's our family of productivity tools.

B

So it's not just sheets which we're focusing on today, but it's also Docs slides, gmail calendar, among other things, and you can call all of those api's for free, which is awesome. Cuz free stuff is great. You don't have to have an enterprise account and, as Fonzie said earlier, G suite is part of Google Cloud, which also encompasses Google cloud platform. So if you've ever played around with Google cloud platform and use those api's using the G suite API is pretty much the same. It's the same account same family. They are generated the same way.

B

You turn those api's on the same way, which is great and if you are at this conference and maybe aren't a node person or maybe you're new to node fee or not, we do have these client libraries and other languages, but because we're at node and JIS interactive. We are going to focus on node today. So let's take a look at hello world in sheets. This could just be a script. That's running locally on my machine and we'll start by requiring that package that we just got from the NPM registry.

B

So we'll start by initializing the sheets, client and then, above that we see this string. That has our scopes, and so that's the level of access that we want to grant this script. We know that files live in Google Drive with other file types like Docs and slides. But specifically we only want to write to spreadsheets today, I, don't know about you, but I get really nervous when any kind of application asks for permissions.

B

That I think they shouldn't have like wanting to get into my Drive folders and see everything in my emails in my location, history and my mother's maiden name like we don't want that best practice is definitely to restrict to only what we need so here we're going to make our hello world call and we're going to say which cell or cells we want to put into in sheets. So in cheese we use what's called a1 notation, and that's because a1 is that cell.

B

That's in the top left corner of the sheet, and that is where we're going to be. Writing our hello world today and we're also using async/await, because we love that. But if you did want to use a previous version of node, you could do that with the script too and don't worry about trying to memorize it. We have all this code on github and we'll show this link again at the end, all right.

B

So here's what the code looks like when we run super cool hello, world and a1, just like where we said it was going to be and I'm, not sure. If you remember from the last cell, there was this variable that was called spreadsheet ID and this spreadsheet ideas in that red box at the top. That's the same ID that you'd find in the browser with any spreadsheet you'd have open.

B

So that's what tells us to write to this Google sheet, in particular all right, but let's be real most the time you need to do more than just write hello world to one single cell and the best way to make multiple modifications to one sheet is to batch your requests together in this method. This batch update request is very common across all of the G suite api's. So why should you group your requests? There are two main reasons. The first is that every call is going to have a little bit of network latency.

B

So if you group them together, you can save yourself from that, and the second is that, while the api's are free, there is a quota. So if you back to your requests together, they will count as one towards your quota instead of many, so definitely want to optimize for that. So each of these, like mysterious tasks, could be a different kind of request like it could be writing data like we did with hello world or it could be adding a chart or formatting a cell or deleting your sheet altogether filtering your data.

B

If you name it like, if it's in the Google sheets UI, you can do it in the API, which is pretty great. We say it's like a big hammer for all kinds of things. You can do or insert your favorite tool. I don't know. So we have this basic toolkit for working with the sheets API and we're doing it in node.

B

So let's talk about a real-world problem: I don't sit in the same offices from they were across the country from each other, but one thing we do have in common is our love of open source and we also both really love the insights, tab and github. It tells you so much about your project and I know: Franz II, there's a lot of looking at open issues and PRS.

B

So we're looking at that nodejs node repo, the node core, node runtime right now and if you're to go there, you could see all the changes going in for node, 14 and Franti is really concerned with the health of the node runtime overall and is a very metrics oriented person. But the thing about the insights tab is it really only tells you info about github they're, just numbers like what is nine hundred and one issues mean without additional context.

B

Do we know if that's healthy or not for node and like it's, because a lot of other factors affect productivity like if a product team were to come in and change a ton of requirements? That's gonna change, your velocity who's ever been there or if you were to have a ton of technical debt that can slow you down.

B

But, on the other hand, if you have really good test coverage in continuous integration, that can make things good or what, if like people are hangry, and that makes them not as productive like you, get it for Anzhi, with a holistic picture of what goes into making the node repo healthy and what helps folks be more productive. So we're gonna focus on the question that I mentioned there. That's hard to define we're gonna be a little bit silly and we want an experiment to find out if food satisfaction yields better github metrics.

B

So this is a hard problem, not just from a technical perspective, but because it's a real-world problem. We have our contributors at their machines. We have github, we have food and we want to write a prototype to automate gathering information about all of those and that's what we're going to use sheets for the prototyping to manipulate the data and, of course, we're going to do it in node so that we can use node to monitor the node repo. So, let's start by breaking down that problem,.

A

So my intuition is that when we're well fed, we write better code, but we care about metrics. So, let's start with the data.

A

Node core is a very active repo and we would like to know how many open Pro requests do we have how many issues and how many peers are we merging and how many issues are we are we closing.

A

Luckily, the github API provides all this data for us and using octa kit, the github, the client for JavaScript. It's very easy to get this data. It's just a few straight-up API calls and we know how many open issues we have instead of looking at the website, so I'm collecting this data by doing a few calls to the github API and then just like in the hello world example that leah showed us I use a Sheetz API call to write this data into a spreadsheet.

A

Now, obviously, when I'm getting the data from github I could just copy it and then paste it into a spreadsheet. That would work instead of talking to the API, but it's manual, work I have to do this every time. I want to update the data and it's error-prone so I'd rather add another two lines to my script, that already pulls the data and then push it writer to the spreadsheet by using the sheets API.

A

Alright. So now for the food part, um we left surveys, so we just did a survey where we figured out how happy the node contributors in our office are with their food on a given day. Clearly, this is very scientific, we're using the the Google Form for Google survey if you've used it before the results are already automatically stored in a spreadsheet. If you use anything else to gather gather your data just do another sheets, API call to write a to the spreadsheet, just like we did with the github data alright.

A

So now we have both the data for food and for github, and we want to know. Are there any correlations? What's a good way to find correlations? Well, if we can visualize the data that would help us, so here's the chart we'll look at the results in detail later for now we're interested in. How did we generate this chart? Of course you can click in the UI to generate a chart, but we don't want to use the browser. We don't want to open sheets.

A

We want to script all of this, so we don't make any mistakes that we don't do any manual labor and yes, this you guys did an API call to generate chart. So what does the code look like? We create an ad chart request and we have to define the chart and its specifications.

A

So here we are defining the chart, type we're picking a line chart and then we have to specify the domain so the x-axis of your chart, in our case, that's the different dates that were interested in and then we also have to enter this series. Two different data points we have from github and from the food survey and once we've defined the chart, then we call this badge update method that can do pretty much anything for us and we send the add chart request all right.

A

So we started with these data, so it says we have some data from the github API. We have some data from a form and then we do a write. Api call to the sheets API to put this data into the sheet, and we do another API call to generate a chart for us, and if we do this, we never have to open a sheet in a browser. All our scripts run locally.

A

So we can decide what node version we want, how to run it and all that it's just a little script, that we run the talks to the sheet API, but Lia is going to take this one step further. Now.

B

Thanks franzine all right so at this point we're gathering data we're getting insights, but it is only running locally on one machine for Anzhi knives and across the country from each other. This is not going to be super great for collaborating if it is only on one machine locally, so we should probably be hosting it as a service.

B

Ideally it would be like a full-fledged application, but we need a client and a server and I mean if we really want to be abiding by good cloud principles, there would be high availability fault, tolerance, which would mean multiple servers and load balancing and a cache and that's a lot of pieces. We said this was a prototype. Let's not. We want to focus on our code, not our infrastructure, so instead we decided to go with a server list solution which, in Google cloud platform, is called Google cloud functions.

B

So you just upload those narrow focus, bits of codes to GCP and the cloud provider figures out where to put it, not ASCII on your behalf, you don't have to know anything about the machines that it's running on and you only pay for one. It's executing you don't have to manage your servers. Security's taken care of you can use the languages tools and frameworks you're familiar with. So if it's node you're doing it and Express python is flask, there's also go in Java runtimes when they're triggered by various events.

B

So there are two types of events in cloud functions and we have there's like background functions that are triggered by events like if you upload something to a cloud storage bucket or send a pub/sub message or it can be triggered by an HTTP function, which is what we're going to focus on today. So those are great for processing data are calling external services I know in the real world. Folks use them to talk to things like stripe and Twilio.

B

So let's take a look at the hello world for functions for HTTP functions when I say function, do I mean a function yeah, it's only two lines of code and we have a fully functioning back-end, which is super cool if you've used Express before those requests and response objects should look pretty familiar. That's where they're from and so with. All of this, no additional configuration you end up with an HTTP endpoint at a fully qualified domain with dynamically generated, SSL TLS certificates, and that's what it would look like in the browser.

B

Okay. So now that we're familiar with cloud functions, we're gonna, wrap up the code. We have in our local script and deploy it there. So we have our service back-end it'll, read that data from the sources and produce the chart while running in the data center, so franzine I can actually be useful to each other sitting in our offices all right. So what we're gonna do is we're. Gonna take the code.

B

We already have and wrap it up in an exported function, called github chart to call our main logic and then, when the processing is done, we'll send that 200 success just like we did in the hello world and we'll send also the ID of the spreadsheet that we've been working with okay. So how do we actually do this deployment? We did it using the G cloud CLI tool, which is what you can use to manage all of your GCP tasks and for cloud functions.

B

You just pass the name of the function that you're going to use and then also we specified that it's an HTTP trigger and it uploads your code and it picks up the package.json, which is great, but not the modules, because that would be a ton of stuff, and so this is great, for if your internet connection is not super duper and those dependencies are then downloaded in the cloud and used by it, you could also edit the package.json and the function if you wanted to in this UI the cloud console which is nice for quick fixes.

B

I love a command-line, but I do also love a useful UI, especially for a quick fix all right. So at this point our codes in the cloud which is sweet, but it still needs to be triggered manually if you're doing something where you want to be pulling in data regularly. You don't want it to be triggered manually that stinks. That means you have to remember to do it. So, instead of needing a human, we chose to use cloud scheduler to run our code. It's basically a big fancy.

B

Cron in the cloud you set up the cron job in the cloud console, that's the same, UI that you can use for cloud functions and you use the same syntax as if you're using normal cron. Our job runs every morning at 9:00 and we specified that HTTP URL to trigger that cloud function. And so at this point we have the data collection job in the back end and Franzese gonna talk you through you're, adding a client.

A

All right, so we have two code in the cloud, but we have to remember that we add URL for the HTTP endpoint and would be just a little more convenient if we had a UI client for it.

A

So we did that. We added a client that shows you the chart and if you click on share analysis, the data is updated once more and then the new chart is sent out to all your teammates. So we have two data updating at 9:00 a.m. every morning anyways, but we don't want to spam everybody daily, so we're only sending out emails. If somebody consciously clicks, touche analysis button and a front-end, that's that's why we have to front it and since we are sending out emails, we're using a gmail API for this task.

A

Just like in the service no js' code, we have client libraries for front-end JavaScript as well and again those make it much easier to talk to the Google REST API than if you were to directly script against the rest interface, so we're doing Gmail that users that messages that sent and we pass in as a resource to Raw email, which can be HTML or plain text.

A

So now we have the front-end order crunch up the triggers our cloud function and then the cloud function pulls in the new data and the cloud function also generates a new chart and then the Gmail API and the front-end sends out the new charge of the team members all right, pretty easy. So far. Right start the whole story, though, what's the big thing that we left out, we left out off. We never mentioned that, and we are we're sending emails, pretending to be somebody and we're reading and writing spreadsheets and clearly I.

A

Don't want people to be able to read my spreadsheets just because they figured out how to use the sheets API. That's not a good security model. So, let's look at how authorization works in our app in the front end. We actually have a little authorize or login button, and when people click that then the Google sign-in flow is triggered and Google sign-in is a secure, send occation system that allows you, users to log in with their Google account that they already have, and then they can authorize your apps in this case to send emails.

A

They can authorize your app to use Google services on behalf of these end users, and all of this is happening in a secure manner.

A

All right so Google uses the OAuth 2 protocol and the application needs user consent before it can access the data. Just like you saw the end user has to consent to send out emails on their behalf, and the key point of OAuth 2 is that the application does not have to use as credentials the users locked in, but they didn't login to your app, they locked into the Google auth service, and then they only gave consent.

A

So that's a big plus because you don't have to worry about storing these credential security securely because you don't get them always to relies on consent, not on credentials. Let's look at how this protocol works in detail. So more specifically, the authorization flow is called three-legged OAuth, it's three-leg because there are three entities. So there's your application there, the Google service, and then there are the end users.

A

So when your application wants to make a request to the Google server, then the user is prompted to login and to consent, and if they do so successfully, then your application receives a token and after validating the token, the application can use this token to make API calls to the Google applet.

A

Luckily, the client libraries handle this somewhat complicated workflow for you. One thing that Lea showed us earlier was that you have to specify the scope so just like in the back end and the front end, we have to specify the scope before initializing the Google client. So here the scope is for Gmail that sent and that's why the sign-in flow said. Do you consent to this application sending emails on your behalf?

A

Now, if the user agrees to that and then your app gets a token back and now you take this valid token and you try to read the user spreadsheets, even though it's a valid token, it's not valid for the spreadsheet scopes. You get a 4:3, so only what the user consented to and that's specified by the scope that you pass in is makes the token valid for all right. So in the front end we have an end user clicking the login button. We use three-legged OAuth with and user consent.

A

How do we handle us in the backend where we just have a cloud function talking to the sheets API, but no end users? It's it's! A server! Less cloud function no front and no nothing well. We since end users are not directly involved. We use server-to-server authentication and in this case the spreadsheet belongs to the application rather than to an end user.

A

Heads-Up, though, whenever you work on a production app, not just a little prototype like we did, you always want to use three Legos for anything involving G suite. So when you work with with Docs or sheets or slides or the file drive, you always want to use three leg hours and use end-user consent.

A

The reason is if this is a production app and you have millions of users and all of a sudden, this application owns millions of documents and spreadsheets and that's way over the quota. So your default is whenever you use Docs or access like that, use three-legged OAuth. In our little prototype, though we don't worry about that. We just have one team. Looking at this: it's not a million files, it's one spreadsheet, so we can just do server-to-server earth. This is also referred to as two-leggers. Can anybody guess why Wow there's only two entities involved?

A

Obviously, there's no end-user, there's your application and there's the Google service and for server-to-server authentication you can make your authorized API calls using a JSON web token a JIT instead of an access token and again, if you use the client libraries they handle all of this for you, so you don't have to worry about creating or signing a JIT, so comparing three-leg and to like OS ours.

A

Three leg is on behalf of end users: it requires users, consent and it's the best practice when you work with a G sweet API to elect is for server-to-server authentication, it's on behalf of the application and the resources. The documents deep have to belong to the application.

B

Thank You Franz you for that deep dive into off. So, like Franz II said this is handled for you by the client, libraries, which is awesome, and if you wanted to take a look at our quick starts in our documentation. They will walk you through the authentication path in just a few steps and just remember we want to use OAuth because we want tokens not passwords. We don't want those stored in plain text ever, but let's get back to the whole point of building this app.

B

We want to know if food satisfaction affects github repo health and productivity. So we made a lot of charts showing food satisfaction against github metrics. On the left side, we have open issues in PRS and on the right side we have the happiness scale and the original survey ran from 1 to 5, where 5 was the most happy. So let's actually look at a smaller easier to read chart. So this is closed in issues and merged PRS against food happiness.

B

Food happiness is the red thick line that goes across with the hump in the middle, and, if you blue, is the closed issues green as the merged PRS, if you're not sure, which is which the most important thing to note, is that they all have that hump around the same time, which makes us think that food satisfaction does affect github metrics, which is cool, but know that you could also use these things for other internal systems or application monitoring.

B

That's maybe not as silly as this, but we do want to show you that we have the proof that good food yields good code.

B

We do want to add one last thing onto our system, though another workflow we see a lot, is that folks have to create reports of data regularly either for their managers or for a broader team meeting, and they often have to do it at a repeated time. Interval like weekly, monthly, quarterly or sales folks have to make customized reports for their clients. If you don't have to do it, someone you work with somewhere, probably does so we're gonna pretend that we want to approve our good friend.

B

Good food trend continues over time and we're going to share this with folks all across the company in our report and we're going to script it because doing it manually. It sounds awful I, don't know if you've ever had to update one of these reports, but doing it manually like it works. You can start by copying an old and like moving in the new data, but I know I'm prone to copypasta errors, paste errors and it's very tedious.

B

So, instead we're gonna create a template with placeholders for the things that we know we're going to replace like the current date and we're going to completely automate it so we'll have first, the Google Drive API create a copy of this template every time we need it and we'll use the slides API to tie it all together in this spreadsheet, so we'll initialize that client to make modifications- and this should look a lot like the other Google G suite api's we've been using and if you remember in that template I showed a couple slides ago where we had the placeholder for the date in curly brackets.

B

That's what we're highlighting here! That's what we want to replace and we want to use a replace text, call to replace it with today's date and if you weren't sure this is going to be a batch update, we could also be making other replacements at the same time like if we wanted to submit the number of pull requests that were closed or issues. At the same time. We could do that in this call. That is gross.

B

The good news is, is we only have a few more slides, hello.

B

B

A

B

B

B

Sweet alright, so now we're gonna figure out how to add our charts. So it's nice about the api's is they're just as tightly coupled as the products themselves are so slides has an API called I can insert insert a Google sheets chart just by its chart ID. So you can generate these reports with up to date, data no human intervention, either save yourself or your co-workers. Tons of time you could become that person who has to generate the reports new best friend by teaching them this. So just a quick review.

B

We designed a basic reporting system using node that calls Google cloud function using those two suite api's and it fetches data from external sources and shares that back to the user, either through the Gmail or through an automated report in slides and at its core we're using sheets for the data collection in the manipulation. So we wanted to know if the food effective, github productivity. Maybe you wanted to know that too? Or maybe you want to take the solution to make it fit your needs?

B

If so, this is where the code lives in github, and we would love to inspire you to either try this or try something similar like be creative, take a non-technical problem and try to solve it with the tools you use everyday or a tool that you want to try out, so you can get better at it. We also want to do a quick shout-out to our friend and colleague, Anoosh revoir stiva, who helped write a lot of this code to make this awesome.

B

Thank you to the conference for having us and for you for coming well answer your questions, probably in the hallway or at the Google booth, because I think we're running low on time. So thank you.