[MUSIC PLAYING]
MARTIN OMANDER: Hi, Lan.
LAN TRAN: Hi, Martin.
MARTIN OMANDER: What are we building today?
LAN TRAN: So I need to automate some Admin tasks in Looker,
which is Google Cloud's business intelligence and big data
analytics platform.
I've heard that it can be done with Cloud Functions.
MARTIN OMANDER: That's right, but I have to warn you,
I don't know Looker at all.
LAN TRAN: No worries.
If you bring your Cloud Functions know-how,
I will bring my Looker skills.
MARTIN OMANDER: Sounds good.
Let's do it.
[MUSIC PLAYING]
LAN TRAN: So I often need to register Looker user accounts
for my coworkers, but sometimes I forget
and people get annoyed.
So I would like to automate that process.
MARTIN OMANDER: OK, got it.
LAN TRAN: So my users usually fill out a Google form
so that email addresses end up in a Google spreadsheet.
Can we build a Cloud Functions so
that it reads that spreadsheet nicely and then creates Looker
user accounts for my coworkers?
MARTIN OMANDER: Yes, we can.
This is actually pretty common use case for Cloud Functions.
Run on a schedule, read a Sheet or some other data source,
and take actions based on that data.
LAN TRAN: Sounds like Cloud Functions is a glue [INAUDIBLE]
in the Cloud.
MARTIN OMANDER: Yes, that's a good way of putting it.
Cloud Functions are great for gluing together other systems.
LAN TRAN: So I look at Cloud Function Documentation
and I saw that we can write the functions in Python.
MARTIN OMANDER: Yes, we can.
Google Cloud Functions also support node, go, Java, .NET,
Ruby, and PHP.
LAN TRAN: Good.
I know Python pretty well, but how
do you read Google Sheet in Python?
MARTIN OMANDER: Here is an example.
First, it gets the default credentials,
then it creates a service object, then a Sheet API
client.
And we can call values.get on that Sheet API client
and passing the Sheet ID and range of cells to read.
But now it's my turn to ask you a question.
How do you create Looker accounts from Python?
LAN TRAN: It's pretty simple, actually.
So we need to import the Looker SDK like this.
We create an SDK object and then we can call methods on it,
like the right user to create new user account.
MARTIN OMANDER: All right.
So I will go to my project in Google Cloud Console.
And here I'll select Cloud Functions.
I'll wait for it to be enabled.
And then I click here to create a new function.
I give the new function a good name.
And I'll make sure that Require authentication has
been selected, so that it can't be triggered by just anyone
out there on the internet.
And when I click Next here, the Code Editor opens up.
Good.
So we can wrap these code fragments in Python functions,
then we just need to tell Google which of these functions
is the entry point.
That entry point should take a flask or request object
as its only input parameter.
LAN TRAN: I think between the two of us,
we know enough to write this code.
MARTIN OMANDER: Let's do it.
First, I'll write the imports.
And the first two will be needed to read
the Google Sheet and the next two import the Looker SDK.
LAN TRAN: And I will write the function
that is the entry point.
It calls a function that reads the email addresses
from a Google Sheet, looks over these emails,
calls a function that creates a Looker user account for each
of these email.
MARTIN OMANDER: Very good.
Then I will write the function here
that reads email addresses from the Sheet.
It will create a Sheet client and call values.get on it,
and read all the values from the B column.
I will open our Google Sheet also
and copy the Sheet ID from that address
and paste it into the Sheet ID variable in our code.
LAN TRAN: Then I will write the function that creates
user's account in Looker.
First, it calls create user on a Looker client,
then it calls create user credentials email
to set up new email for the user's credentials.
MARTIN OMANDER: Excellent.
And I'll also put in our requirements.txt file.
All right.
But Lan, how does Looker know who we are
and what Looker project we're adding users to?
LAN TRAN: So Looker will read the base URL, the client ID,
and a secret from any file.
MARTIN OMANDER: Oh, got it.
OK, so we put it in there.
And I'll deploy the function by clicking this button.
LAN TRAN: Let's test the function.
MARTIN OMANDER: I'll click here to call it.
Oh, that doesn't look good.
Let's check the log.
Something about permissions.
LAN TRAN: We never granted access to the spreadsheet,
did we?
If I click Details, it looks like the Functions is
running as [INAUDIBLE] account.
So I copied that, I go to the spreadsheet, I click Share,
then I grant the viewer access to the spreadsheet.
Click that test button again.
MARTIN OMANDER: All right, here it goes.
Oh, looks like it works.
And the log, oh yeah, the log says here
that the accounts were created.
LAN TRAN: But Martin, there's one part
you have forgotten about.
MARTIN OMANDER: Didn't we do it all?
LAN TRAN: No.
We have not scheduled it to run nightly.
MARTIN OMANDER: Oh, you're right.
Let's do that in Cloud Scheduler.
First, I'll copy the URL that triggers this function.
Then I go to Cloud Scheduler and click this button
to schedule a job.
I give a little name here.
I'll enter 00***, which means every night at midnight
in the UNIX cron format.
Then I'll set it to your time zone, Lan, Japan Standard Time.
I will set the Target Type to HTTP,
and paste in the URL for our Cloud Function.
And because the function isn't public,
I will need to set Auth Header to OIDC token.
I'll pick the default compute account for now,
but if we take this to production,
we should consider creating a custom service
account that has access to trigger Cloud
Functions and nothing more.
LAN TRAN: Yeah.
That's the principle of the list privilege.
I like that.
MARTIN OMANDER: Yes, exactly.
LAN TRAN: Let's test it right now
so we don't have to wait until tomorrow to see if it works.
MARTIN OMANDER: Oh, good idea.
Let's click this Run button and give it a minute.
OK, I'll click View Logs.
Looks like it ran.
And if I remove all these filters here,
we see the logs from our Cloud Function too.
LAN TRAN: Great.
It's working.
Now I can just tell my users to submit their email addresses
in my phone and Looker accounts will be
created for them at midnight.
MARTIN OMANDER: Very good.
Thank you everyone for watching.
If you have questions for Lan or me about this episode,
or feedback, or ideas for future episodes,
please enter them in the comments below.
Bye.
[MUSIC PLAYING]