Access the Dictionary API to get Definitions for Words in Google Sheets

Advanced

About this Tutorial

This tutorial has no description yet.

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Apps Script APIs used

SpreadsheetApp UrlFetchApp

Apps Script methods used

.getActiveSpreadsheet() .getRange() .getSheetByName() .getValue() .fetch()

Video Transcript

<div>00:00 I think this video is going to be pretty fun because you're going to join me as we work through a problem, an API problem, where we're going to try to use the Collegiate Dictionary API that exists. 00:12 I was looking through some Google Sheet videos and I saw this Miriam Webster Slash Dictionary.

URL and I thought oh this might still exist where you just add the word and then you can get the definition with this import XML but it doesn't really work we can even try to do something maybe try to get this 00:30 to work with like a sometimes it needs the www sometimes no it's not working so I was like hey there is an actual API here wouldn't it be cool if I could figure out the API and I wanna walk through figuring it out so I havent figured it out yet so youre gonna walk with me through it I have been reading 00:56 the documentation theres json documentation all this stuff its really good to read all of this but also sometimes its really good to just walk through so I did get an API key were gonna use an API key here I dont think Ive set up anything I have a extensions app script going to go over here create API 01:18 key all caps im gonna grow grab it and put it in there yeah we have nothing set up so far so were gonna just call it a function get definition were going to get our word here and then were gonna have our variables here were gonna use URL fetch app of course .fetch and were gonna fetch the URL which it 01:40 will tell us but yeah make sure you get your API key put it in an API key if you want to follow along yourself lets see we have an introduction we have data structure entry metadata labels inflections I think these are all parts of it but lets see just the overview ah terminology cool we want to see  02:09 the api uhm lets just use dictionary we had need json right json fields request here we go request url ok grab this and put that in as our url actually we can call that variable url here we go and we need an api key so we'll add that and we need a word here this is the word so we will add word and we 02:42 will add that as a uhm variable here and now we add our api key plus api key our api key is going to be variable api key equals spreadsheet app dot get active spreadsheet just do this so many times get sheet by name api key in all caps dot get range i know it by heart start a1 i knew i put it in there 03:14 alright so there's our api key we're gonna hit save we're going to fetch this url we want some options i think i think we're gonna need some options here let's go back to our api documentation maybe not lets just go grab the data we have meta definition def there it is def so lets look at our data that 03:47 comes back without any options so variable response equals this and we're going to return response dot get content text that's how we get access to that API or most APIs were going to save this right here in lets do equals get definition a3 lets make this a little bit bigger so we can see it. 04:16 And see what we get invalid API key not subscribed for this reference alright let me go figure that out so I went back and looked at the documentation I think I forgot this test word here so we're going to add that uhm wait no test seems to be the word one second so what we might want to do is add some 04:38 options here where the options is method is uhm not post but git and we want to try that let's see if that works and it doesn't really say that our reference is bad it does say reference not subscribe for this reference invalid api key but we can maybe look at that api key again ok i see the issue this 05:11 is so silly this is silly this is a appscript issue I didn't get value, I only got the range of the API key. 05:20 Really was this was the absolute exact thing to give us because the API key was wrong it was just not getting the value great now we have this data wow awesome so now we are grabbing the data correctly and we just need to parse it should be pretty easy we can look at this json beautifier let's look at 05:43 the best one here and what we want to do is get where's the definition there it is short def def oh my god its so far down uhm so lets do this lets make this into json or uhm data equals json dot parse response and now we can do return data dot i think we want one dot short def i think that's what we 06:25 want to do because looking at this we want short def here we might want even zero one perfect we want zero probably just one short definition save that hmm theres no short def for software but there is short def definitions for these words i dont know why software doesnt have one type r cannot read properties 06:59 of undefined so this software doesnt have a short definition but now we have definitions for all these words we have at least one definition set act or action of setting yeah coach to go in a coach these are some version of the definition we can possibly put both in there maybe lets do this variable  07:24 short def one each equals this and variable short def two if there is a second one join uhm just short def one plus lets put a forced uhm put a space in between them short def two and see what happens. 08:06 Now we're getting errors because they probably don't have short def hmm what we could as well do is instead of this allowing for more we can just say try try here try this and catch if theres an error i think we need to Put, error return error so it'll try it it won't give us an exact this error it'll 08:43 just give us some blank so we can try it it makes it a little cleaner I think actually each of these datas have a short def so we'll do this and I bet we're gonna get something for everything but everything has one that I made that mistake im looking at this again and I see ok there's a zero here one 09:02 two that means theres probably three definitions for this particular word but if theres only one definition were gonna have to only access this zero so well just take the first definition uhm you know this is not absolutely perfect cause we did this in about ten minutes uhm but I think this is a really 09:16 cool way to show you how to take just a random here API look at the documentation capture this URL add in our words add in our API key we just added a new sheet put get value we made sure that that was a little sticking point uhm and we just do this over and over again this response equals we do the  09:40 fetching we add some options if we need it to we might not even need those options i bet it works without these options let's try it it's always good to just try something and if you keep getting errors then just read through those errors and see what happens let's replace all of these yeah we don't  10:00 need those options we're just grabbing the data that we're getting back and we're parsing through it with json which is this data equals json.parse and then were gonna go here and parse through it and we looked at it through this json viewer here hopefully this was fun for you and now you can use dictionaries 10:20 inside of your spreadsheet tools cool</div>