Hey there stranger!

Sign up to get access.

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

About this Tutorial

Learn how we get information out of a JSON response. I’ll even show you how you can bring in some cool images in Google Sheets!

Video Transcript

0:00 All right, welcome back. We are gonna talk about J S O N, not JSON and the Argonauts, but J S O N.
0:06 This is the type of, of very common type of data that comes to us in APIs. It comes to us on the web and it looks like this.
0:15 It has some curly brackets, it has quotes of sort of the names of things and then the value of those things, the key of it or the value.
0:23 But what I wanna show you is we're just going to get outta Google sheets for a hot moment. And here's sort of a respon, a typical response of json.
0:31 I'm gonna copy that. How do we look at this? How do we get the information out of this? Well, one thing is we can beautify it.
0:39 So I literally just searched for Google for JSON Beautif Fire. Here's the top one. I just used that one. And we paste the information on the left.
0:48 And over here on the right is the data, but it's beautified. It shows us the title Titanic year 1997. It shows us all the options writer.
0:57 We can get the actors. We can't even get the plot of the movie. What I wanna really get, and we will get by the end of this, is we're gonna get poster here.
1:07 So we're gonna be able to bring in some cool images into Google Sheets. But this json whenever you trying to access a API in specifically a restful API or arrest api, make sure you're getting the right data.
1:24 I say this because in our app script, we use this dot get content text. This could be a very easily skippable step.
1:33 What we're end gonna end up doing is we will end up needing, what is it? Let me double check. We need variable js o n actually values, let's say equals mm at j o n dot parse.
1:52 And we'll put all of this response dot get content text inside here, okay? Now what this allows us to do, this JS O again, is built-in functions that allows us to look into this data.
2:04 And we'll, what we wanna do is we wanna return just say poster and we'll return values and we'll use bracket notation and we'll do poster.
2:16 Let's see what happens. If we get poster, we got equal, we'll get poster a one. We are getting literally only one item out of all of that json We're getting the poster.
2:31 We can wrap this with a normal Google sheet formula here, image that takes a u URL of an image and creates the image in here.
2:42 So that's pretty cool that we can actually bring an image inside two Google sheets from an api, right? So we got that because we got to beautify this J s o.
2:52 We got to see the actual structure of it. We got to look at what this poster is, what this plot is.
2:58 And if We, we go back to our information, our, our script. This is normal bracket notation, right? We've learned about what bracket notation is when it looks at arrays of information, like the first array, the first element, the second element, the third element.
3:13 We added brackets to it. But in this case, we're using the name of the key, which is poster or title or year or actors or director, right?
3:25 We can do that and we're gonna get an error here cuz it's not an image, actually no error. But this is pretty cool stuff.
3:34 We have been able to now parse lots of information across json. And in the next one, in next video, we're gonna create a new script that allows us to seek out which parameter we want.
3:47 We don't want to have to hard code this.

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