[MUSIC PLAYING]
WESLEY CHUN: Hey, developers.
Welcome to the G Suite Dev Show.
I'm Wesley Chun.
Before we get started, I'm excited to announce
you'll be seeing more of my colleagues in future episodes
as our team has grown, and I'm looking forward
to learning from them.
Now, what will I be doing?
In addition to G Suite, I'll be spending more time on the Cloud
Platform side of the house.
As such, I thought it would be fitting to combine features
from G Suite as well as GCP.
Before we get started, you know how sometimes,
when you get something done, you stop as soon as it's working?
Take these stairs here.
We could quit now because it works, right?
Well, I'm here to argue that if you just finish the rest of it,
it'll look more appealing visually.
And that's the theme for today's show, completing that last mile
and literally making things more presentable.
We're going to walk through an App Script app that takes you
from big data analysis with BigQuery,
all the way to a Google Slides presentation,
leveraging Google Sheets in between.
We'll link you to the code LEP at the end
so you can build it yourself.
Now, the motivation for this app?
I was inspired by three samples our team has already created.
First is the Apps Script BigQuery sample in our docs.
Second is the sample app in the generating slides
from spreadsheet data video.
And finally, the third was the Google Slides API code lab
app, which uses BigQuery to analyze open source license
data.
Now, first thing.
What's Apps Script?
Well, it's a customized JavaScript serverless runtime
for G Suite automation, extension, and integration.
More importantly, how do you access Google and other
services with it?
Well, at this time, there are two ways--
built-in and advanced services.
A built-in service is one in which a native Apps Script
object provides API functionality.
In our app, we'll use both the Google Sheets
and Slides built-in services.
And advanced services, where there is no native Apps Script
object to use.
Instead, these rely on the HTTP-based REST APIs,
meaning an advance service is just
a thin wrapper around that API.
Our use of BigQuery is exactly that.
There is no BigQuery service, but there
is an API and an advanced service that uses it.
In some cases, such as for Sheets and Slides,
both built-in and advanced services exist.
So which do you pick?
Well, generally, APIs operate at a lower level.
So a built-in service may not have all API features.
On the other hand, built-in services
operate at a higher level, and often, a built-in service
function is easier to use, that would otherwise
require multiple advanced service or API calls.
Now that we got that squared away,
let's begin with BigQuery, GCP's big data analysis tool.
I mentioned I was inspired by our Apps Script BigQuery
sample.
Well, it's an app that finds the top 300 long words, which
means longer than 10 characters, in Shakespeare's works.
It's a cool query, but I'm just interested in the top 10 most
common words in Shakespeare.
So let's switch to this query instead.
All right, we're going to dive into the code,
and you'll see where the query comes in.
At the top, we set the name of the query,
which will also be the name of the spreadsheet and slide deck.
Next is a project ID, which is required by BigQuery.
After you create your project in the developer's console,
click on the Settings three-dot menu
that you see up in the corner next to your avatar.
Select Project settings, and then copy
that project ID string to the project ID variable
you see in the code.
The "if" statement is there to stop you if you don't have this
set.
The only function in this first version is runQuery.
It starts with the query that we picked earlier.
The last few lines kick off the job in BigQuery.
And the job ID is used to help collate the results.
Now, the next section of code waits for BigQuery
to finish using the exponential backoff technique,
awaiting a bit longer if results aren't in yet.
Well, the results are ready once the job complete flag
goes true.
Now it's time to fetch all that data from BigQuery,
appending chunks of page at a time, or flag an error
in the logs if nothing comes back.
At this point, we're holding onto a lot of data,
so you need somewhere to put it.
Now, you could display it to the screen or put in a database
and be done with it.
But to promote G Suite with GCP and to make it more visual,
it's better to put in a sheet, right?
Well, in this part of the code, we
create a new sheet with the query name
and then add the first row headers, first
converting them to uppercase.
The results from BigQuery isn't formatted in a way
where we can just push it directly into a sheet.
Instead, we need to put the data into a more normal 2D array.
By the way, massaging the data into a format
your app can better process is generally
referred to as ETL, which stands for extract,
transform, and load.
Once the data has been added to the 2D array,
we can put those values into the sheet.
Got to start at row two so we don't
overwrite our headers, right?
OK.
So now that our data is in a sheet, cool!
Now I know what the most common words in Shakespeare are,
and I didn't need to count them up myself.
Certainly better than being in a database somewhere.
Now, it looks great, but it would even
be more cool if we could visualize it
just a tiny bit more, say with a chart.
OK.
Here's the new function that we'll create for our chart.
It takes a spreadsheet object and creates a columnar chart
based on the data in cell range A2 to B11.
It then inserts the chart starting
on row 5, column 5, which is E. Now, one tweak we need to make
is to return the spreadsheet from runQuery.
So we need to add this as the last line there.
Now the last step is to create a new driver app that
calls both of these functions.
The first to query and return the newly created sheet,
and then to add a chart to it by passing it to create
column chart.
When you run the modified app, you
get the data, plus this cool chart!
Much more visually appealing, right?
Now you have an idea of how the Google Sheets and its API
reduces manual labor.
By bringing in some automation, you
can make better use of your time.
OK, next step.
Now you could send this to management,
but can I make the result even more presentable?
Like with a presentation?
Well, the answer is yes.
So we'll use the Slides built-in service to do that.
Let's create a third function that takes a Sheet and Chart
objects and creates a new slide deck,
filling in the title and subtitle on the default title
slide that you get with all new presentations.
Now, add a blank slide for the spreadsheet data.
Much like how we copied the resulting data from BigQuery
into Sheets, we need to do something
similar to get that data from Sheets into Slides.
And don't forget the headers.
The code you see here reads all of the data from the sheet,
creates a new table on the new slide,
and then writes all that data into it.
Now, let's add one more blank slide,
then insert the spreadsheet chart into it.
Pretty straightforward.
The final step is to update our driver app to call all three
of these utility functions.
Don't forget to update Create Column
Chart from the previous step to return the charts,
so we could pass it to Create Slide Presentation.
Now check out the beautiful slide deck we have here.
You've now completed that final mile,
going from big data analysis all the way to a slide
that you can deliver to management.
And best of all, without spinning up
any server resources yourself.
The goal today was to show that you've already
done the 80% hard work, but the last 20%
is to put frosting on your cake.
So what's next?
Well, there's many directions for you to go.
To build this app yourself and to see all the code,
we invite you to do the Code Lab and take a look at our GitHub
repo.
This app was featured in our serverless talk
at the 2018 Cloud Next conference.
Check out the talk to find out more about interoperability
between GCP and G Suite.
Hint, in the near future, you won't need the BigQuery advance
service anymore, as there is now a BigQuery connector
feature in Sheets.
Also announced at Cloud Next, you
can edit your BigQuery query directly from Sheets.
Cool, right?
And that's it for now.
You've done the hard work already,
but need to justify your work to management.
So we hope the sample app inspires
you to code that final mile in all of your projects,
with GCP and G Suite dev tools.
This is Wesley Chun, and we'll see you upstairs in the suite.
[MUSIC PLAYING]