Learn to use the Google Sheets API. In this week’s episode, I show you hands-on how you can turn any Google Spreadsheet into a data store that is accessible via API.
In the video, I show you how to use the worksheet as a step-by-step guide. It makes your life much easier because it guides you through the process, and helps you to get all the nitty-gritty details right. Without it, you would probably spend a lot of time reading the documentation or figuring it out by trial and error.
Get the Google Sheets API Worksheet, I will mail it to you. Just leave your address below.
Learn about APIs in a more structured way, with step-by-step guides:
- Book on RESTful API Design
- Course on RESTful API Design
- Books on other API Topics
- Courses on other API Topics
Transcript
Today, I’m gonna show you how to use Google sheets API.
So if you have a spreadsheet in Google sheets you can use this spreadsheet as a data store and you can access this data store via an API. It’s a read-only API so what you will do is you would put in your data where there’s a really great user interface and then from your application you can access the data as a regular JSON API.
I’m gonna show you how you can do this in this video.
First, we fire up the spreadsheet Google spreadsheet so I will put some data in here so we just see some this spreadsheet is what we’re gonna use in order to call the API now we need to do some preparation and all the steps for preparation I have collected in this worksheet here for you alright you can download this worksheet.
I will put the link up where you can get this worksheet so you can go through these steps and you have a little guidance that takes you along there are couple of steps involved first you have to set up your Google sheet you need to go on file publish to the web let’s do that file then publish to the web over here where we basically say we want to have the entire document published and click on publish are you sure you want to publish yes so what we will do in addition is that we will share this worksheet right now the sharing button here says this private to only me I will change this and I would say I want to have this sheet available for anyone with a link right now I get a link copy this link and click on done.
What I can do now that I have the Link is that I extract the unique sheet ID.
If you know REST, the sheet ID is the resource identifier. It’s the ID unique identifier of this resource and let me copy this URL that I got over here into my worksheet.
In the worksheet I can see a pattern of a URL of such a publicly shared URL and there’s a sheet ID in there. So what I need to do is I need to identify exactly that part of the URL that matches the sheet ID and it’s gonna be this here it’s actually not that complicated what you have to do this is my sheet idea can copy that out here and I use this sheet ID in order to construct a new URL. This is gonna be our API URL. It’s written down here. The URL contains the base path is spreadsheet google.com slash feed slash cells and then I have the sheet ID and the page number so what I’m gonna do is I’m gonna replace in this or let me do it like this I’m gonna copy this I’m gonna copy this and put another copy in here and then I’m gonna replace the sheet ID good and now I also need to set the page number so this is the first page I can just replace this by one and as you already see in this path that we have constructed there are something called Jason here in the end and that shows how the data should be rendered so this gives us now access to JSON data.
We can do a get on this URL on this API. That’s what the browser for example can do for us and we get this here what is this well this is a JSON representation of our spreadsheet oh that’s simple you just copy that that’s our spreadsheet copy that into an editor over here paste it format this a little bit and now I can see what’s in this spreadsheet there’s some kind of a header that specifies encoding who has created that spreadsheet and it also shows the entry and an entry we have all the data the data in our spreadsheet there is a category okay some kind of category of how this data is formatted or these entries are formatted and then we have content and in this content there is a $t a text this “a1” and if we have a Google sheet cell with a text a 1 this is what we have put in here and so first jump back into our UI rendering in the web app and I see here is a one and this is the text a one that I put in here and it tells me that this is this text or input value that is in column number one row number one and it also gives me a direct URL how I can access exactly this value this $t value “a1” by just calling an API okay so if you want we can try to call that and but before that I think let’s jump just to the next content element which is b1 just so you get a an overview of what’s in this file there’s a B1 column 2 Row 1 yeah that’s about right and another URL then we have third entry with a2 and a fourth entry with B2 okay so what I’m gonna do now is I’m gonna test if I can get to the a1 directly with this URL here so I’m gonna copy that URL that I’ve put in there and public full this thing here and then we’re gonna put in out Jason asked before and let’s put this in the browser which is basically doing a get call on this API and I would get another JSON construct let’s see what this jason construct contains just take this into the browser format it a little bit and as you can see this does not give me the complete spreadsheet this is the complete answer it’s a smaller Jason and it shows me here the content directly the content is a u1 shows me the cell row-one column-one and its ID with any links attached to it.
So this is very practical because now you have an API call which can address any cell directly in your spreadsheet.
What I think is really great about this approach is that Google wanted to build this application Google sheets and they built it as a single page application (SPA) by building that it’s kind of normal that you have an API for accessing the data into that SPA and now some applications just try to hide this data and try to hide the the API access to this data Google sheets did a different approach to said well we’re using this API internally into ours SPA maybe you’re interested in using that API as well and yeah actually we are so we we can at no additional effort on Google side get all the benefits of using an API like Google sheets is great now of course you always need to remember that these spreadsheets are kind of shared and that they are published to the web and there are public so this whole concepts of course has limitations you cannot have confidential data you cannot really protect these APIs directly that needs to just be taken into consideration when you work with such spreadsheets but I think for things there are public anyway like web pages or even dynamic web pages which are public anyway this is a very interesting concept
So that’s it. I hope you liked this video you’ve learned something and if you did give it a thumbs up and subscribe to the channel thanks is stay tuned for next week’s episode learn something about APIs, see you there.
Also published on Medium.
