MARTIN: Hey, Brett. Where did you to
get that lollipop? BRET: Oh, oh. Hey, Martin. Well, the Slip
Slap Candy Company came by and asked for
help connecting their code to a Google spreadsheet. I told them we might have
something in our tool box. MARTIN: Sounds great. Stay tuned if you
want to know more. [MUSIC PLAYING] BRET: I met with Max from
Slip Slap Candy Company. He explained that many
of their internal systems need to know what ingredients
they have in their warehouse. MARTIN: It turns out the
warehouse staff already keeps track of this
in a Google sheet. That’s great news because it’s
easy to read a Google sheet from Google Cloud Platform. BRET: Max didn’t want to
deal with server setup and maintenance, so
we went server less and picked Cloud Run. And he has previous
experience in JavaScript, so we decided to
use Node.js together with the Express Web framework. MARTIN: Cloud Run code executes
with a default identity, the compute default
service account. Max opened the
warehouse spreadsheet, click the Share button, and
gave that account view access to the spreadsheet. BRET: Then he enabled the Sheets
API in Slip Slap’s GCP project. MARTIN: Max designed
a really simple REST API with only one end point. If you hit slash
ingredient ID, you will get the warehouse
details for that ingredient. BRET: The Express
library makes it easy to pick up the ingredient
ID and take action on it. MARTIN: Once Max has
the ingredient ID, he can call the get
ingredient function and return that ingredient
to the caller in JSON form. BRET: The code for reading
from the Google sheet is in the get
ingredient function. It creates an off object
with the spreadsheet’s scope. It then reads
columns a through e for all rows that have any data. Notice how there are no
passwords, keys, or OS secrets. The account running this code
already has read only access to the sheet. MARTIN: Once the sheet’s
API returns the rows from the sheet, Max can loop
over them, find the ingredient with the right ID, and
return that ingredient’s data to the calling function. Check the video description
below for the link to the full code example. BRET: This code reads
all rows in the sheet every time the
REST API is called. That’s OK for a small
sheet like this. Slip Slap only has about 1,000
ingredients in their sheet. If you have a much
larger data set, you should use a
regular database like Cloud Firestore
or Cloud SQL. MARTIN: As the warehouse
staff updates the spreadsheet, the REST API will always
return the latest data. Max loves this as
that means he doesn’t have to write a separate
admin user interface. BRET: That’s less than 50
lines of code for a simple read only REST API that’s
backed by a Google sheet. Nice. Well, Max gave me
this US $1 bill. He wonders how much
could Slip Slap use this service for that dollar? MARTIN: Let’s say Slip
Slap’s internal systems hit this service on average
once every 30 seconds, 24 hours a day, seven days a week. This would fit comfortably
within the free tier for Cloud Run and they
wouldn’t have to pay anything. But what if they’ve used
up their free tier running other Cloud Run services? In that case, it would
cost Slip Slap a little under one US dollar to run
this service for one month. BRET: Not bad. I’m also sure that Max
is happy he doesn’t have to manage any servers. Hey, where’d you get that? MARTIN: Um. To see how to connect a REST API
with a Cloud SQL database using Cloud Run, see our next episode. BRET: If you have
questions or a topic you’d like us to cover in
a future episode, please leave a comment. Thanks for watching. [MUSIC PLAYING]

Free your Google Sheets data: build a REST API with Cloud Run – Serverless Toolbox

8 thoughts on “Free your Google Sheets data: build a REST API with Cloud Run – Serverless Toolbox

  • October 7, 2019 at 11:02 pm

    Google is getting better and better with your cloud training videos. Great job, everyone.

  • October 8, 2019 at 7:25 am

    Great vid, typo at 2:17 line 4 missing [ 🤫

  • October 8, 2019 at 9:00 am

    Good video. I'd love to see a video showing how to query cloud SQL via php or python. Thank you.

  • October 8, 2019 at 10:43 am

    Easy peezy, very nice!

  • October 8, 2019 at 1:05 pm

    Quite honestly, this is a very very very useful api and tool.. Amazingly done. This has the potential to have huge impact on small businesses. Btw, is there a python lib as well?

  • October 8, 2019 at 7:03 pm

    Also do one connecting to firestore pls

  • October 8, 2019 at 7:33 pm

    Great example, but why wouldn't you just go for a Cloud Function in this case? Easier than setting up the container, etc. I don't understand the benefit for Cloud run in this scenario…

  • October 9, 2019 at 1:55 pm

    Can we have a tutorial for IAM with Cloud run / Rest API


Leave a Reply

Your email address will not be published. Required fields are marked *