Academy ↓
Hey there stranger!
Sign up to get access.
Spreadsheet Automation 101 Lesson 4: JSON (beautifier) and OmdbAPI parameters
About this Tutorial
Video Transcript
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
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
Add Icons To Your Sheets With a Domain Name
00:04:21
Convert Google Sheets into a REST API
Add Tasks to Google Tasks From Google Sheets
Build a Book Recommender with AI in Google Sheets
Build a License Key Generator and API in 20 Minutes
Create Your Own API by Deploying a Web App
Access Free API's with Google Sheets
Use Random Quote API in Google Sheets
OpenSea Data Inside Sheets
Tag Emails From A List of Members in Google Sheets
Spreadsheet Automation 101 Lesson 4: Access APIs Introduction
How to Validate Email Addresses
Build Your Own Free Domain Availability Checker
Build a Job Status Checker API in 15 Minutes
Spreadsheet Automation 101 Lesson 4: UrlFetchApp
Spreadsheet Automation 101 Lesson 4: OmdbAPI get ApiKey, get Data in URL
Spreadsheet Automation 101 Lesson 4: OmdbAPI Parameter Picker
Spreadsheet Automation 101 Lesson 4: OmdbAPI get data in Apps Script
Spreadsheet Automation 101 Lesson 4: JSON (beautifier) and OmdbAPI parameters
Quickstart Tutorial OpenAI API in Google Sheets
Shaman: AI Blog Writer
ChatGPT Clone in Google Sheets Part 1
ChatGPT Clone in Google Sheets Part 2
How To Set Up Stripe Webhook to Google Sheets with Google Script
00:22:10
Create Instant QR Codes
Access the Dictionary API to get Definitions for Words in Google Sheets