Hey there stranger!

Sign up to get access.

Spreadsheet Automation 101 Lesson 4: OmdbAPI get ApiKey, get Data in URL

About this Tutorial

Capture data from OMDb in our Google Sheets with a free API Key. It’s easier than it sounds. Let’s do this!

Video Transcript

0:00 All right, for this lesson, we're gonna actually jump out of Google Sheets. We, we will come back to it. So make a a, make a sheet in your document.
0:09 If you're following along, call it API key, all lowercase if you wish, and put your API key there when we get it.
0:15 So we're gonna go to om db api.com. You have to go over to API key. You can get a free one.
0:23 Just enter your email name, how you're using it. We're learning how to use APIs in how to access APIs in Google Sheets.
0:30 And hit submit should be very, very quick. You should be able to get this by email. You will have a thousand daily limit.
0:36 I have my own API key, so don't take mine. Go get your own. It's absolutely free. But let's talk about getting, how do we get data from om d b?
0:46 Okay let's go back to api. We have some usage and some parameters. If you're, if you're ever trying to figure out how to use APIs, there will almost always be documentation about the usage.
1:01 So if we scroll down, we see the usage, send all data requests to this url, and it has here, I don't know if you can really see it that well.
1:10 ODB api.com/question mark. API key equals your key. So it has some brackets here. We don't need those brackets. It's just showing us that we need a put in our API key and then an ampersand.
1:22 Okay, so what does that ampersand mean? It means that anything after that ampersand, we can add parameters. So in this particular case, we can add a t.
1:32 This is the parameters. It has a parameter here, has some options, has some other data, and it tells you what it gets.
1:38 So for what it is, so we have a movie title to search for, we're gonna be able to search for a title like Titanic and get back all of the information in the api.
1:47 Essentially, that's what's going on. We are calling this this URL that has some data at the end. And we're saying, what do you have?
1:53 And it will send back a response, which will be our data, and we'll capture that data in our Google sheet.
1:58 And then we're gonna parse through it with some data on which we'll get to later. But just to be clear, in this particular case, the API is a restful api.
2:12 We can actually access this by the url. So check out this, we can take our url, copy it. We're gonna go to a new new place where you need to enter our API key.
2:24 I have mine saved here. Put our API key in. Then just like in this example, have an amper sand. Okay?
2:33 So we add an amper sand, then T equals, and we're just going to type in Titanic. So we get, let me put this all in here for you.
2:45 So that's the whole URL that we're, we are putting in. And we're just gonna put this in a URL in Chrome.
2:52 And what do we get back is text. This is the response we get. Just like we would get in on a website, we Would get, get some data back.
3:00 But that data then is parse, like what's the html, what's the css, what's, what's all the parts of it. And then it builds a sort of browser.
3:06 We'll build it together. But here we just have some data. What's really cool about this data, which I'll show you in another video about J S O N.
3:16 This is j s O and it's structured. It actually has the title here, Titanic has year. It has a lot of data in here, and we're gonna be able be able to parse that inside of our Google sheet.
3:30 So I'm gonna pass that along to next video. We're gonna actually parse this. We can literally take this exact url, URL and put it in this fetch.
3:42 So we will take all of this url, put it into this fetch, hit command S for save. Now, when we run, get data and we log, it's the exact same data that we get here in our, in our url in our Chrome browser.
4:00 This is pretty cool because what it allows us to do is check if our, if our code is wrong but this is correct, then okay, we did something wrong in our code.
4:09 But if we use the URL in our u in our browser and it's wrong, okay, we know it's the URL, not our code.
4:16 It gives us that power to see what is actually wrong with what we're doing. So that's really cool. So sometimes you might misspell Titan Titanic and you're gonna get response, false movie not found, and you're like, ah, if your code is right, you have the right name, Titanic, or if you don't have the right name, but your code is right, you're gonna get this error.
4:36 So really, really cool way to see, okay, we got our url, got some data. Now we can go write our, write our code in the next video.

Courses

Spreadsheet Automation 101: Introduction to Pre-course Videos

Think Like a Programmer: Develop The Mindset of an Apps Script Coder

Spreadsheet Automation 101: Functions

Spreadsheet Automation 101: Variables

Spreadsheet Automation 101: Dot Notation

Spreadsheet Automation 101: Camel Case

Spreadsheet Automation 101: Parentheses

Spreadsheet Automation 101 Lesson 1: GetValue - Introduction to SpreadsheetApp

Spreadsheet Automation 101 Lesson 1: Spreadsheet Taxonomy

Spreadsheet Automation 101 Lesson 1: A1 Notation vs Row,Column Syntax

Spreadsheet Automation 101 Lesson 1: getActiveSpreadsheet() vs getActiveSheet()

Spreadsheet Automation 101 Lesson 1: onOpen() Trigger - Custom Menu

This Seems Like Automation

Spreadsheet Automation 101 Lesson 2: Get Values - Introduction

Spreadsheet Automation 101 Lesson 2: Arrays

Spreadsheet Automation 101 Lesson 2: For Loop

Spreadsheet Automation 101 Lesson 2: Bracket Notation

Spreadsheet Automation 101 Lesson 2: Logger.log()

Spreadsheet Automation 101 Lesson 2: If ( ){ } and Checkboxes

Spreadsheet Automation 101 Lesson 2: onEdit() Trigger

Introduction to Spreadsheet Automation 101 Lesson 3

Spreadsheet Automation 101 Lesson 3: MailApp

Spreadsheet Automation 101 Lesson 3: Email Yourself For Loop

Spreadsheet Automation 101 Lesson 3: Send Email Every Week Trigger

Spreadsheet Automation 101 Lesson 3: Email Other People For Loop

Spreadsheet Automation 101 Lesson 4: Access APIs Introduction

Spreadsheet Automation 101 Lesson 4: UrlFetchApp

Spreadsheet Automation 101 Lesson 4: OmdbAPI get ApiKey, get Data in URL

Spreadsheet Automation 101 Lesson 4: OmdbAPI get data in Apps Script

Spreadsheet Automation 101 Lesson 4: JSON (beautifier) and OmdbAPI parameters

Spreadsheet Automation 101 Lesson 4: OmdbAPI Parameter Picker

Automatically Clear Content | Refresh Reuse Recycle Templates

Automate Google Sheets With Zero Experience

Automatically Uncheck A Daily Checklist

Activate A Certain Sheet When Opening a Spreadsheet