0:00:00 Hello, so this is going to be a fun video, very chill video, very free video where we're going to play with some free public APIs.
0:00:10 I found this site, freepublicapis.com It's tested 229 free public APIs that are tested every single day so we know they work, quote unquote, but maybe we don't know how to access them so why you may want to watch this video is we're going to start from scratch We're going to try to use URLFetchApp.
0:00:30 We are going to. to access APIs, get that data into Google Sheets, but we're also going to add a little bit of uhm, sort of interesting stuff to a Google Sheet.
0:00:37 We're going to do it for a number of different APIs, so you'll watch me access these APIs, you'll see how we access APIs, how we get data into Sheets.
0:00:46 I'll try to go through a few different ways, like directly in a cell, just grabbing data from an API, but also trying to capture or scrape these APIs for multiple pieces of information.
0:00:59 We'll see you will try to do a few of those different things, but let's see how, how far we get.
0:01:05 Um, I thought some of these were pretty fun, so like we have a Harry Potter API, I think we're going to start there.
0:01:13 We have a Kanye.rest, uh free rest API for random Kanye quotes, that might be fun. Um, these could be used, obviously they're pretty random, uh they're pretty Funny, we have a Rick and Morty Public Holiday might be really good.
0:01:31 But we're going to discover these and look at their documentation and try to access them. We have a blank Google Sheet already right now.
0:01:39 So let's see, let's see where we're going to start, are we going to start with Harry Potter or Kanye? Um, the free random one might be simple, because I might be just like, call it and then get it.
0:01:52 So we have, get a quote from me, it says api.kanye.rest. And is that it? Let's click. Okay, so we have literally just, we get a quote back.
0:02:11 Alright, so we just call this URL and get it. So let's do it. Let's go over to Extensions Apps Script.
0:02:17 If you've never used Apps Script before, it is free and available in every single Google Sheet you have. So just go to extensions Apps Script.
0:02:26 In Apps Script, let's call this get Kanye quote. Get Kanye quote or what is this? Yeah, Kanye quote. Okay, um we're not gonna put any variables in here.
0:02:40 Uh we are going to have to return something. So we'll return a quote. Uh this quote is just a, that word quote here is just a variable that we're gonna create variable quote equals and then we're gonna put some text here.
0:02:55 If you wanna try this out, I'm want to create a custom function, you don't have to do this but I really love doing it.
0:03:03 You just add custom function. I'm actually gonna call this Kanye all caps and save it. Right now, what it will return is just text.
0:03:15 So if we just type in here equals Kanye. See it comes up, Kanye. It's just gonna return the word text but we don't want text.
0:03:24 We want whatever comes out of this URL. So let's call it variable URL equals, and put this in quotes. API.kanye.rest.
0:03:35 And instead of quote, we will use URLFetchApp.fetch. All we need is a URL and some options but let's just grab the URL for now and see what happens.
0:03:50 I think I know what's going to happen but I just want to see it. how we get nothing, right? So what we need to do is actually get a response from there.
0:04:01 Variable quote equals response dot get content text. I think we're going to need that. So we're just calling this URL, API.kanye.rest.
0:04:13 And whatever the response is from there, we're going to get it in this get content text. Let's see what we get now.
0:04:19 We're going to save it, go back. Ah, we get function native code. So what does that mean? Oh, actually we don't, we got that I think from just the response, but we get content text.
0:04:31 Means we go to that ah code that comes back and we just get the text out of it. But we have to do a little bit more, one more step.
0:04:39 Cause we have this, I don't know if you can see it. Let's make this text a bit bigger. We have ah curly brackets and we have the word quote and then the quote.
0:04:51 So what this is is, it's called JSON, J S O N. And it's a structured data that APIs are going to give us back.
0:04:59 But we want to what's called parse it. We want to work our way through the JSON tree. Ah and what we'll do is instead of quote we'll call it JSON and variable quote is actually going to be JSON uhm J S O N capitalized.
0:05:18 Which is a function inside of Apps Script. Just like URL fetch app is a function that calls URLs and gets a response.
0:05:26 JSON is, we're looking at the JSON. And there's only two things here we have options for. We can just parse it.
0:05:33 That's what we're going to do. We're going to parse the JSON. And we can call this like text maybe or JSON to, to make it a little less convoluted that we have JSON two times.
0:05:45 So we're just using this variable text to say this response dot get content text. Let's save that and see what happens.
0:05:54 We get nothing. The reason is, is now we are parsing this, we need to actually tell it which part of the JSON to give us.
0:06:02 So I think we just do dot quote. I think. Let's see if that works. And it is. I was just speaking to someone that told me their life story and they used to be homeless.
0:06:14 So we have now a random Kanye quest, quest, uh quote from Kanye with the function Kanye and we did that relatively in just a few minutes, right?
0:06:27 Let's have fun with All right. So the first one. We can use this in a number of ways. I would say you could use this if you had some sort of checklist.
0:06:41 Insert checkbox. And let's say we wanted to give of a quote when you checked off three things. So we go equals if count if a2, a4.
0:07:01 It's true. Is over or equal to three. And now if that, right now it's false. So we want to do nothing.
0:07:12 And then add another comma if, if the value is true, actually we'll do Kanye. And nothing if it's false. Actually we'll put in false.
0:07:26 So we'll say, not done yet. And now if we have a checklist like item one, item two, item three. And we said, okay when these three things are checked off we want a quote from Kanye.
0:07:44 Now it's loading and here we go. A quote from Kanye. Great. And this will call, so one weird thing about APIs and Google Sheets is I think it's going to call it like every 10 minutes.
0:07:56 It's I'm gonna cache it, C-A-C-H-E, meaning it'll save the data. Umm for a little bit, but it'll call it again in 10 minutes and come back.
0:08:04 So that's why we like these like free APIs. Umm. In the next one I think I will show you how to keep this information in a sheet without having to call the API again.
0:08:18 So let's look at this Harry Potter API. The search API providing data on Harry Potter characters and spells includes routes for all characters, specific characters by ID, students, staff, characters and all spells.
0:08:31 So here we have some more documentation. And in this case we are going to need to read the documentation because it's not just like one call and get a response.
0:08:41 So this is a little bit more interesting. Use these routes to request data on Harry Potter characters or spells. So let's look at all characters.
0:08:50 Oh that is literally all characters. So this might look like gobbley goop to you, but it looks like to me, Jason, let's click on pretty print here and we can see this.
0:09:01 that we have. Uh curly brackets. We have square brackets. This is sort of structured data that lists every single character.
0:09:09 Uhm. Let's look at all spells. And as well we have a list of an id, the name and the description.
0:09:22 So maybe if we call the right name, can we get or if we want to get just download this data?
0:09:34 How do we get that into a sheet? Hmm. Is that really all the spells? Pervious. Levicorpus. Mufliato. Are we all of them?
0:09:51 Hmm. What could we do with this? We also have all characters, Hogwarts students. So look at this. . So we have ID, name, alternate names.
0:10:06 Does everybody have an alternate name? Maybe we want to give the um name and get back a um alternate names.
0:10:19 Or something else or the house maybe. Let's see what we can do with this. So function. Potter. Actually. got Harry Potter.
0:10:35 We're going to need to use the URL here the end points, which is called endpoints. They have different endpoints for each one so.
0:10:44 All Ravenclaw and all spells, ehm. Let's look at this again. If there's anymore, no API key is required. Explore and enjoy the magic.
0:10:59 Check. Hmm. Sorry. we're just going to get everyone, right? Ever. Every spell. Let's just type in a spell and get back what it does.
0:11:10 Okay. So we have all spells. Let's copy that. And use that variable URL equals, let's put in quotes and get the spells.
0:11:24 And we're going to do exactly the same. We're going to get a response. We're going to. Thank you. Parse that.
0:11:36 Mmm. How do we handle everything? So we're going to get back. Not dot quote. Let's say return. Mmm hmm hmm hmm.
0:11:52 Is in here a little bit, let's just get back. Everything for now. Just the text. Return. Text. We won't even need quote here.
0:12:08 Let's see what happens with this. Uhm. Let's call this instead of Harry Potter Hogwarts spells. Or. All caps spells. Again we will add this at uhh custom function.
0:12:27 Because I think we're going to need to do something else here. And I think we can- do something pretty fun here.
0:12:33 So this is Kanye. Make a new sheet. Call it spells. Equal spells. Let's see what happens if we just type in everything.
0:12:47 Ah! We got all of that. Hmm. So we have that there. And we want to say variable umm. Cause we can do, we can parse the JSON, right?
0:13:13 Mm-hmm. But we need to get everything. Maybe we want to just print this all out into a sheet. Mm-hmm. Instead of using this as a, custom function.
0:13:35 That might be simpler or more interesting. I don't know. Say spells equals gson.parse text. And I think we can, I think we can then do spells.
0:14:01 Mm-hmm. . Let's see what happens there. I think we're just gonna get an object or nothing. We need to parse this somehow.
0:14:10 Mm. This is curly brackets first. Mm-hmm. Mm-hmm. So we sort of have to create our own API that's gonna say give us, if we put in a spell, it gives us back the um description of that spell.
0:14:31 Mm. So maybe instead of parsing this with JSON, we just print it to the umm uh to a sheet. Mm-hmm.
0:14:54 Mm-hmm. Mm-hmm. Cuz this is an array, so I think we, so this is gonna seem weird, but we're gonna put a zero here.
0:15:10 Let's see what that does. Does it update? What was it? That spell uh name. Mm-hmm.
0:15:35 See it is now. Okay, so we can do something different here. Because it's not really well done. So we can get each of the spells if we iterate through all of them.
0:15:48 I wonder if we can get return spells dot length. Let's see if that changes to a number. Mm-hmm. So we need to just iterate through all of them.
0:16:07 How do we iterate through all of them if we don't know the length? Mm-hmm. Should be able- there it is.
0:16:14 77. Okay, maybe it just didn't save. So there's 77. So spells dot length gives us- this is the weird thing here.
0:16:21 Cause it's a- an array. And it's just all of the spells. We can- instead of returning spells dot length. Let's- actually run this as a git all spells.
0:16:38 This'll be fun. We're going to go to better, see that it snippets and get a custom menu. And this is going to give us a cool little function menu at the top.
0:16:50 And we're going to call it API menu. And we're going to say git all Hogwarts spells. We're going to run the function.
0:17:03 And get all spells. We're going to not return spells.length. We're going to do something else here. In the sheet that we have, we're going to append.
0:17:15 So whatever active sheet we're on, we're going to say spreadsheetapp.getactivesheet.appendrow. We're going to first append a row called um.
0:17:29 What are the three things? We have id, name, and description. description. So id, name, description. So let's switch all these two single.
0:17:51 Okay, if I just run this, actually from here, uh no, I'm going to save this, make sure this orange button goes away.
0:18:01 I'm going to refresh our, our, our . And over here, we're going to have a menu pop up. It's going to say API menu, get all Hogwarts spells.
0:18:09 So in a blink spell, I'm going to click get all Hogwarts spells. Ah, we need to authorize it. Okay, we'll authorize it.
0:18:18 Allow. In the active sheet, it should append. There it is, id, name, description. See, it already showed up here. Let's go back and see.
0:18:31 See you. We're going to write a little for loop. You can say for all of the entities here, so for i equals zero, we'll start at i less than spells.length is how far we're going to go.
0:18:50 And then we're going to iterate i++. So this is going to be a little iterator. We can say iterate. Iterate tour.
0:18:58 If we want, we can use any variable we want here. Iterate tour. We are going to write uhh we're just going to append each row.
0:19:11 And in the row we are going to append uh spells iterator. mmm dot id.
0:19:29 And then, comma spells iterator. So this iterator is usually just i.
0:19:47 And it's going to start at zero. And it's going to keep going up and up and up until it gets, until it's no longer less than the length.
0:19:54 Meaning 77 in our case. Uh we want to dot. I keep forgetting which one. ID name and description. So this will be dot ID name.
0:20:07 Now we will do spells dot description. Okay. Messing, uh oh yeah we are our OK, so now it should, when we click get all Hogwarts spells, it will be.
0:20:33 It will still append a new row of this ID name description. So I will delete that and it will print another one.
0:20:41 And every single spell is now coming into our sheet. That's pretty cool. Man. There will be 77 of them. Or 78 rows with the header, right?
0:20:55 There we go. Got all of them. Awesome. So we were able to grab all the spells. We can probably get all the characters.
0:21:02 Let's get all the characters. . characters as well. So we're just going to copy get all spells. Paste it down here.
0:21:09 And let's get all characters. Instead of spells at the end we're going to do characters is probably it. Umm. You can just copy.
0:21:22 Yeah. And let's look at it in our URL. Pretty print. We have name and let's just get the name and alternate name.
0:21:32 Mmm. Maybe name and, Is there a house? House. Yeah. So let's get everybody's name and everybody's house. So instead of ID we don't want that name and house.
0:21:50 We can rename the spells to characters. We don't need description. And we don't need ID. Ah, for the- In this case we don't need ID.
0:22:06 But we will copy this again and get house. Okay, so we're just doing dot house here. We're iterating through these.
0:22:13 Umm. Now we want to make sure we're on a new tab. We're gonna create a new tab. Call it houses.
0:22:28 We can run this get all characters from the app script. We don't even have to put it into our on open menu.
0:22:34 You But I just thought that on open menu is really fun to make a custom menu here. So let's actually run it from here.
0:22:39 Get all characters. Umm. We maybe have a problem with active sheet. Let's see what happens. If it just automatically goes to the active sheet that we're on over here.
0:22:53 There it is and there's all the names in houses. Nice. Ah some people don't have a house I think because they're not students.
0:23:02 questions. No? Oh there's a lot of. There we go. That's pretty cool. And we can do this with all kinds of other things, right?
0:23:14 All the raving laws, different people. Cool. Let's go back and look for more APIs. So it's public holiday API. So an API for public holiday supports over 100 on your country.
0:23:34 History's documentation is here. Let's read it. It provides a simple way to query the holidays of over 100 countries. Also it is possible to query long weekends.
0:23:44 Uhm. Oh there's an endpoint to check if today is a holiday. Hmm. Interesting example. Get the public holidays from the given year and country.
0:24:00 So country code is a two letter code. Looks like. And the year is here so. Does that bring us anything?
0:24:14 Yeah it does. Cool. So we have here public holidays, types, local name, name. If we type in US. These are just.
0:24:34 Columbus Day. Local name, name. So let's just get the name of all the holidays. And the date and name. So we just need to call this API.
0:24:47 So we can do something similar. We'll start it from scratch function. Get. All holidays. Variable URL equals. Here. . . . Sponse equals URL FitchApp.fitch URL.
0:25:13 We want variable text equals response.getContentText. We want holidays equals g-silent.parse text. We can do a spread sheet, get active, sheet, append row.
0:25:43 We want to append it with, what was that again? Um. Date, comma holiday. So there's the name of it. 4.
0:26:01 i equals 0. We'll start at 0. We will go until we have holidays dot length. Semi colon i++ iterate through and we will do spread sheet app dot get active sheet dot append row and all we need here is um holidays i and then a dot name or no dot which one is first date.
0:26:35 dot date comma holidays i'm getting my head is in the way holidays eyes and brackets dot name okay let's see if this works get all holidays we want to create a new tab we want to make sure it's saved completely and run get holiday see if we get any errors doesn't look like we're getting any errors but
0:27:06 this is only going to be for U.S. hmm so how do we parse this into a nice way hmm maybe instead of appending the row what we could do is put it into an array so variable holiday array equals And we'll see we're just going to create the first one is going to be date and holiday and here instead of this
0:27:42 it's going to be holiday array dot push we're going to push this into it each one and then we're going to return holiday array see if this works so again we can grab all of these but we're going to do equals ah again our most fun thing add a .
0:28:05 . comment at custom function I'm going to call this holidays we're going to add another little thing to it if this works I think we're going to have some interesting fun with it holidays amazing but we do need to fix I think we need to add this as an array around that let's see if that fixes yep okay
0:28:39 perfect so we have the date and the holiday but the interesting thing here is we are getting a u we have a URL here where we have a year and a country code we can actually put that in there as variables inside of our function so we can say uh country and year and let's create a new URL based on that 
0:29:02 so we're going to use plus Plus. You . Here. Plus. Gotta put in quotes another slash then plus country. So now this one won't work because we have no variables we'll get an error but we'll add the variables we'll say US 2024.
0:29:26 Actually 2023 I think we can use. Let's see perfect. Now we can. Maybe change it to . 2022. Now we have this really cool function.
0:29:38 It's going to update all the time. In real time. We can change the year if we want to get different years.
0:29:46 We can change the country. If we want maybe we change the not change it but um add local name.
0:30:02 If we change the country it's going to have a different local country. . Name. We go holidays. I. And then I want to double check.
0:30:13 It's local name with a capital N. Here. There we go. And in the case of uh line 54. What is not defined?
0:30:30 Holiday's. I just spelled holidays wrong. See that error is not . Just told us which line it's on. All right. Perfect.
0:30:37 This is great for the US. But let's call. Let's see what um. What's Indonesia to letter. Country code. Just ID.
0:30:57 See what is Indonesia. Perfect. So we now have local names of all of these public holidays. We can also probably do MY thing might be Malaysia.
0:31:11 Nope. Uhm. What is MA? Yeah. How do we know which country is which? So we have parameters. Two letter country codes.
0:31:26 Country code is ISO 3161 alpha 2. Two letter country codes. Let's see if we can get a list of those.
0:31:37 Uhm. Oh my god, there's so many. Here we go. Let's grab this. Oh, we have them all. Awesome. I want all of this table.
0:31:56 See if I can copy it all. This is gonna be really fun. To play with this data, right? Okay, let's, Call this codes.
0:32:07 Paste it. So we have a country code here. We have a country name here. And for our holidays, we need the two-letter country code.
0:32:17 But we don't know the two-letter country code. We only know umm the name. So let's create a drop-down menu here.
0:32:25 This is gonna be fun. With range from the country name uhh codes And, b to b. Okay, we've created a drop-down menu.
0:32:43 Here. This is the country. And then for our code, we are gonna do, we can't do vlookup actually. We have to do index.
0:32:54 Our reference. So we'll do match first match. Search key will be this. Our range will be the b column. We will search by zero.
0:33:05 So we're going to get an answer which we'll use as an index for A to A. And we will now have based on this drop-down.
0:33:18 Mmm. And let's try something we know, US, United States of America. Let's see if that works. Perfect. So the United States works.
0:33:44 But something like Bermuda maybe not work? Hmm. Argentina? Works. Fantastic. That's pretty fun. That we are able to now use that information we got from here to know the ISO 3161.
0:34:05 And alpha 2 country code and now we're able to grab all the holidays in that country. Well there's the launch year of the public holiday.
0:34:16 Interesting. So we could also call this first year and add. Let's see if.
0:34:31 Oops. . . . I think that's what it was. Launch year. Oh there's a lot of nulls here. Mmm. So we're just gonna get.
0:34:46 Nothing. But. We can call it launch year then I don't think it's first year. I think it's. Oops. Something went wrong.
0:35:01 I've been doing too much. See do we have the first year of any of them? Mmm.
0:35:20 No I think it's gonna be all the first year. Let's call it launch. Launch year. Coming back with basically nothing because I don't think any of them have it.
0:35:38 Oh for that no wrong one. Yeah they're all null. Null just means nothing. Just has no information. So that probably wasn't very interesting but it's good that we are able to parse it as we wish.
0:35:53 And see that we get nothing back. Mmm. Cool. Let's go look at more. So we now have we're having pretty much fun with these APIs now.
0:36:04 We're able to. Add a little bit more and more each time. We got the codes. We have this drop down menu that now we can see.
0:36:12 See Ghana. See holiday. Oh there's no. This syntax error. Unexpected end of JSON input. Hmm. Oh I keep having twenty twenty three here.
0:36:26 So we can also add a year twenty twenty four. And instead of referencing it here we can say B1. Hmm. Let's see if Tanzania has something.
0:36:39 Nope. Austria. That's cool. So now we can change the year twenty or two thousand. Great. Wow. Did anything change? We have like a different number of holidays.
0:36:57 Any holiday get created in the last few years? No. It's all the same stuff. Cool. So now we have this.
0:37:05 system. Let's see cool little dashboard created like a web app here, right? To pick a country, pick a year and see all the holidays and their dates and the local name.
0:37:15 Cool. Let's look at some more public uh APIs. Got Rick and Morty, Pokey, Mon, useless facts. Let's see what the newest ones are.
0:37:35 9 Hmm. Public holidays in China. Mmm, Guild Wars. Throwaway email API allows users to check if an email address is from a disposable domain.
0:37:58 Hmm. That's interesting. Mmm. Screen shot of a home page? Oh it's free? I don't know if we can do that in Google Sheets.
0:38:14 Should we try? What is the thing that we get back? Screen shot of? Mmm. Get historical screenshots marked down and metadata.
0:38:31 All the way back to January 2024. I don't know if this is going to last very long. on. Let's see.
0:38:39 What happens? Mmm. Try making requests. Get PNGs. It says expect the API to change. So even if we get it, it's just not gonna work very well.
0:38:57 But let's see. Get JSON. Let's see what happens. Mmm. Here's an image. Does this image work? That's just green. Of this doesn't give- oh here it is.
0:39:22 OG image. Mmm. Get historical screenshots more. That's just metadata. Mmm. Ah. Here we go. That is literally the image. Screenshot of let's say google.com.
0:39:54 Cool. Mmm. What happens? What's- I don't know what's gonna happen. Function gets screenshot. I think what we need to do here, I don't think it's gonna work.
0:40:07 because we need the URL. Basically, we're just gonna be able to get text back. But if we got a URL of an image, then we could put that in image formula.
0:40:21 So we'll return. So let's do return URL. Mmm. Variable URL equals. I'm going to just do um. Google.com for now.
0:40:38 We will maybe do the similar thing as we did above later. URL fetch app dot fetch URL. So I want to see what does it bring back.
0:40:51 We get text. What if it's just content equals response dot get content? Let's see. Get screenshot. . Create a new page.
0:41:15 Mmmmmm mmm . . ah. . . get a reference error! not on N While concerned. Oh my god, it's so many things. umm get content text.
0:41:26 What happens if we change that to content text? Do we get something? Value. Text result. It's something longer. Hmm. Let's inspect this and see what it is.
0:41:46 It's probably just trying to give us back everything. But is there a url we can get? Mmm. Hmmmmmmmm. Hmmmmmmmmm . . . So we just get a markdown.
0:42:13 That's pretty nice. We can get a web page in markdown. Get a web page. This is just hmm. We can save that image.
0:42:25 But it is just an image. It's not a url it's coming back. Hmmm. Copy image address. That is the address.
0:42:36 I'm wondering . . Do we even need a uhh api? Equals image. Put this in the url. Allow access. And there we go.
0:42:52 So we don't even need to use url fetch app for this. We can literally just use this here and go google.com.
0:43:02 It's actually instead of the year. Just the url. There you go. So we can just get a screenshot here. We just need to put in the url.
0:43:12 We can add umm ampersand b1. I'm gonna wrap this with if is blank b1 capitalize this image so that if it's blank it doesn't show anything and then we'll type in google.com and it will load an image.
0:43:37 That's a it. Great! Can we do like, what happens if we do drive.google.com? Currents screenshot. I mean wikipedia.com? Probably well known sites only.
0:43:55 There you go. So we got a screenshot of it. That's cool. That's a very fun API. But that trick is we just have to wrap it with the image formula and that's about it.
0:44:07 . Nice. Internet DB. What is this one? Internet DB. Open ports. Mmm. I don't know about that. Mmm. Most popular? What are people using?
0:44:23 Kimmy quotes. Finish f1 driver. Hacker News API. Data USA. Automated reports. Public holiday. We did that. Pokemon. Similar to the other ones.
0:44:35 Get random activities based on type. Participants, price and accessibility rooms. This has a low score. So let's see what happens.
0:44:44 It has a 100% error rate. So we're not even gonna try it. That's cool that this site has those error rates that I'm like I don't care.
0:44:52 Pre-json sports API. API, relational teams, that's pretty cool. If you're doing like fantasy football list of all countries list all teams in a league, search for event, list all leagues.
0:45:14 Oh there's a free tier and uh it's a free sports API. Mmm. You cannot make over 100 of a cuss per minute.
0:45:25 Okay. API key three during development. If you want a free version. Search for a team by name. Is it any team?
0:45:48 I wonder. Like what happens there? Okay we have, we search by team. Mmm. We get back teams. Hmm. Interesting. Resting.
0:46:04 Let's see what happens. Function get teams. We are going to variable URL equals, let's keep put that in there.
0:46:22 Response equals URL fetch app dot fetch URL. We'll do text equals response dot get content text. We'll return. Text. And I want to test this out.
0:46:42 I'm wondering if it's looked like a search gators or muskrats or something. Again, let's add a little custom function here.
0:46:52 Add custom function. We'll use get teams. Let's save it. Make sure it's saved. Let's create a new page. It goes get teams.
0:47:07 games. Let's see what happens. Teams null. Hmm. Let's do arsenal. Arsenal. Cause we did have a response from it. There we go.
0:47:23 We got a lot of stuff here. So maybe we need to parse this like variable. Do something equals. Do something parse.
0:47:35 Bull um. We can. Here's another trick I know of. This isn't very good but we can do JSON beautifier or formatter that you can google them.
0:47:48 They'll all will be the same. Uh paste it in here. Format beautify. Mmm. There is an error. Mmm. Expecting string.
0:48:01 Mmm. It's not very good. JSON. Or there's something wrong with it. But the work. Teams. My D team. We have a bracket here.
0:48:15 Personal. Football. Is it all just football clubs? Let's try. Mmm. Teams. Return. . . Teams. Teams. No. Results. See if that changes anything.
0:48:53 Nope, we get an error. Oh, because it didn't save actually. We've got nothing. So Sierra, let's add Sierra to see it get the first result.
0:49:09 . . . make sure it saves. I don't know, it takes some time to save. So weird. He's gotta go call it again and come back with something.
0:49:18 Will it come back with anything? Hmm. To parse this. Hmm. Let's see again. Here is an example.
0:49:44 Mm hmm hmm hmm hmm hmm.
0:50:07 Search by name. hmm hmm hmm hmm hmm hmm hmm hmmm hmm hmm hmm. but getting nothing's better than getting an error.
0:50:24 Let's see, look at this again. It's not pretty printing, is there more than one here? just id. It's that shed to be deep.
0:50:41 How to parse this. How to parse this JSON. Might as well. D-uh, aha. That's one weird thing we have to do, right?
0:51:31 So it's results bracket teams zero. Let's see if that comes up. Then we need to access, what else? Should we add that?
0:52:08 There we go. To wake up the team name. We don't want the team name. We want something else. We want something interesting, right?
0:52:16 Maybe League or uhh alternate? We're searching for the team name. Let's see, what are the alternate names? There's some alternate names.
0:52:33 Cool. So this one was weird because of that extra little teams at the beginning. main. Right? Um, we had to put that in brackets.
0:52:42 Then zero, the first item, and then in bracket again, whatever we're looking for. Mmm, that's pretty hard to deal with.
0:52:50 We have a lot of options here. Ooh, we have sort of a formed team here. So we can type in a team name and get the first result, whatever their team name was um, whenever they were.
0:53:09 . Formed, but we can also add here team name and put it here and create a nice little search and do equals get team.
0:53:21 Uh, well actually we'll rename this get team form date. Mmm. Formed. So now let's make sure it saves. Well equal get team formed.
0:53:40 . um Does it tell us what uuhm, says Arsenal, but what? List all sports hmm.
0:53:56 Teams in a league hmm hmm hmm hmm hmm hmm hmm okay. Hmmm interesting.
0:54:13 What teams can I look for? Like um. Bills? Let's see. Would it find a buffalo bills? No… Hmm.. What teams?
0:54:39 All team. List of all teams in a league. All teams. What teams can I search for? Alright, this has been, this is really weird.
0:54:55 But I can't figure this out. But we can find, let's say um. What's another one word? One. Team name. Like arsenal.
0:55:08 I don't know. No. Aliens, Astros, right? Like. Aston Villa. Let's see. I don't know if this is going to work.
0:55:30 Oh, they work. Aston Villa. Probably was from the 18s and before. It's a very old um. It would wreck some being here.
0:55:41 1864. I don't know if that's actually when they were. But there we go. So it's going to start for some team names.
0:55:47 Uh, we can make a little web app there. I think it's been pretty cool. A little frustrating, right? With some of these APIs, you know, what's in it and what's not, right?
0:55:56 Are we getting an error because of our doing or the API just doesn't know what we're asking it to do?
0:56:04 Who knows? Um. If you're an avatar fan? and hmm. Let's see what we have here. With the characters, episodes, not a little database.
0:56:21 Cool. Let's see what else, if we can get some random stuff. Board a more random useless facts. Provides random useless facts.
0:56:32 Let's get a useless fact. For our last one, right? Gonna pass. End language. So we want English. Cool. So we just need that URL.
0:56:48 Awesome. Let's go and create a function. Useless. Fact. I hope by this time you are comfortable with variable URL equals.
0:57:06 You know what I'm gonna say. Um, let's actually copy it. here. And then variable response equals, we're just gonna call this URL fetch app dot fetch URL variable text equals response dot get content text.
0:57:31 Make sure we have the parentheses variable. Nope. We're just gonna return text. Next. Alright. We do probably should look at this.
0:57:45 We have to do a little bit of JSON. We want just the text, right? We don't want the ID. We don't want the source URL.
0:57:51 We just want text. So variable. Hmm. Fact equals text. Nope. JSON dot parse text dot text. I think that's what we have to do.
0:58:09 Let's find out by adding a custom function. Making sure it's saved. This orange button goes away. And we'll call this facts.
0:58:24 I don't like the team formed. Equal useless fact. Let's hope it works. The first time the fingerprints of qualifiers are virtually indistinguishable to those humans.
0:58:36 So much so they can be easily crime. He's a confused- other crimes. Oh my god. Those koalas and their crimes.
0:58:44 Great. So again, we can use this kind of random function as like a little Easter egg in our sheets if we use the if is blank.
0:58:55 If you have like a checklist here, insert. Check box and you're like one of them is get use. List fact.
0:59:09 And then . When we check that off, we want it to show up. So if. This. And if it's true. We will write get.
0:59:21 Useless fact. And if it's false, we won't show anything. So as we check off our list of laundry and pick up kids and go to pool and make dinner.
0:59:38 As we pick them up. Oh this is boring right. Oh we pick up kids. Now we get a useless fact.
0:59:43 There we go. The one longest one silverboard is screeched. Go to pool and make dinner right. So this is fun to add little A strikes to your sheets or umm.
0:59:54 Just have fun with APIs. Hopefully you enjoyed this full hour long ride into APIs. I'm excited to see what you do with it.
1:00:08 If you find any other. you uh interesting stuff at freepublicapis.com let me know. If you have any difficulty doing something you want to do and build it in Google Sheets let me know.
1:00:18 I'm happy to help with uh issues. Bye.