Hey there stranger!

Sign up to get access.

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

About this Tutorial

Use getData and UrlFetchApp functions, as well as Logger to get OMDb API data in our Google Sheet. Learn how to access an API outside of Google Sheets.

Video Transcript

0:00 All right. This is so exciting. In the last video, we were able to get om db API data in our in our url in our Chrome browser, but we wanna get it into sheets.
0:10 So I created a new tab called movie. We also have our API key over here, which we will get in a hot second.
0:16 But what we wanna be able to do is write in here equals GI data and we wanna be able to get something.
0:22 We're gonna get nothing cuz if we go to our app script, get data is correct, that is the correct name, but we're logging it, we're logging the response.
0:30 So let's go return, return response, get content text. So this is the response from our URL that we were able to get before.
0:41 So let's see, command S what that gets us, that gets us all of our data inside of our sheet. This is so cool.
0:48 We have now accessed an API outside of Google Sheets. We have brought the data inside of Google Sheets, but now it just looks like a mess, right?
0:56 But it's Js o, right? We're gonna get into j s o in a hot second, but I wanna do something else with this url.
1:03 We don't need to put this in text here. Text form. We can delete all this. We can bring it all up.
1:09 We can create variable we can do om db equals, and we can use that first part of the URL right there.
1:22 Put that in quotes. Okay? Then we can take variable API key, and in this case this is from our lessons before spreadsheet app dot get sheet, active sheet spreadsheet dot get sheet by name.
1:38 And in this case I think it's no API key is the sheet dot get range.  A one or one 11 get value, right?
1:50 What we've learned before we're using again and again and again. We're grabbing that API key from the sheet API key and A one.
1:59 All right? Now, now what do we have? We need to get the title of the movie. Well, we can pass the title of the movie in the function title here.
2:10 So variable we can actually do something else. Variable title. Ah, we don't want to do that. We can just pass that title in.
2:21 But we need and t equals we need to remember that. All right, so as we build our URL here, we can do om db plus API key because it's just gonna add the API key here, plus put in quotes and T equals.
2:44 Now the name of the movie is gonna be plus title. So again, that's, we're adding together all of this. We're literally using plus to sort of just concatenate join all of these things together.
2:56 But we got this first part of the url, the om D b B part, where it ends with the API key.
3:02 We then have our variable API key. We also have this little bit of text and t equals so that we know that the API knows the title and we're getting the title in the function.
3:13 So let's save this, get data. We're gonna end up getting an error on our movie. But we wanna do we get an error?
3:21 Yeah we will get an error. We can actually see that error here. Nope, don't get, we need the ti. Oh, title equals undefined.
3:36 That's what, that's the problem. Title equals undefined. So in here we do Titanic. We want to pass the movie title there.
3:45 That's so cool. We now have Titanic, we have all the data. We can also cut this, paste it over here, and instead of putting in text, we can do a one.
3:55 And in a one we can write Titanic. So now we can reference us. We're really using the power of Google Sheets here to reference the name in a one into GI Data, and we're parsing it all to grab the information off of a OMD b a api.
4:14 All right, in the next video I'm gonna talk to you more about Js o n and how we sort of parse that with all this messy data.

Courses

Spreadsheet Automation 101: Introduction to Pre-course Videos

Breaking Through Errors In Apps Script

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

Tips to Navigating Thousands of Lines of Code In Apps Script

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

Scoping Functions in Apps Script