Beginners Intro to Pulling API Data into Google Sheets [BONUS: Secret APIs]

hey youtube if you've heard the term api before and you're wondering what it is or have some kind of like basic notion of what that is uh look no further i'm going to give you a quick introduction today to what apis are and then we're going to do a little bit of a deep dive into some of the different data sources that you can pull information from via an api what is an api exactly an api is shorthand for application programming interface and you can think of this essentially as the language that different services and apps use on the internet to communicate with each other and send data back and forth now if you're somebody that's into trading cryptocurrency or a digital marketer that wants to analyze keywords or look up seo and different things like that apis are useful for you but if you don't have a developer background it might be a little bit intimidating trying to figure out how to use these different uh data sources so basically when a human or a user is using some kind of app or website and they want to get access or to some data for example in this case a burrito right the api will essentially convert that human language into application understandable language here so he takes this and kind of abbreviates it right and this is essentially what the api is doing so an api looks a lot like a website but the information that it returns is we could consider raw data so let's go ahead and head to coingecko for an example on coingecko's website we can access the raw data that's being piped up here to the main home page by going here to resources and clicking on crypto api once inside here if we scroll down we can see uh the coin gecko api version three and when we open this part of the documentation we can see there's some a little bit technical looking documentation um but basically let's just say for example we don't get a list of all the coins that are on coin gecko we can go here to the endpoint coins slash markets and when we open this we can actually try this out ourselves so the only thing that we're required to add here is us dollar so we're going to be asking coingecko what is the price of all of your coins with respect to us dollar now when we scroll here to the bottom and hit execute so if we grab this url that's here in the documentation we can paste that in a new tab to kind of look and see what that raw data looks like and if you're like me this is very hard to read so how can we get this probably useful data into something that's more friendly right we can see like oh there's something about the total volume there's some market cap rank for something right what even is this right neo ah here we go so we can kind of check it out but this again this is hard to read so how do we get this into something human readable i'm going to introduce you now to a tool known as api connector which can take this kind of data and parse it into google sheets for you with no need to code you simply copy the url over put in any query parameters that are necessary and boom your data is in a flat nice tabular format so if we head over to mixedanalytics.com we can access this extension by going here and installing from the google workspace marketplace i already have a copy so once you have a copy head over to google sheets and you can access the add-on by clicking here on add-ons going to api connector and then pressing open now to get this data in the flat format of google sheets what we'll do is head back over to the documentation and then we'll we'll grab just this url here the https that's here in quotes we'll copy that and then we'll paste it over here in the api url path here in the sidebar we don't need to set up anything else other than the destination sheet so we'll just set current and then we'll give this a name that's consistent with what we're doing which we're getting exchange rates and then when we save and run this request we will get the data pulled back into a nice flat tabular format just like this so we can see that we have ids symbols name we have the image of that particular asset linked here as well so this is fantastic right and even if you're not a this makes the data really easy to see and i as a developer also use this tool a lot because i find it very useful when i'm wanting to look at data from a particular domain or service and i just want to get a quick look and i don't want to sit around and code up a solution in python or javascript that can pull this data into a human viewable format it's great it just makes it a lot easier for development purposes and again for people that aren't developers this is useful if you want to get a look at data that you normally would have to go through some other service or route to get access to now if you're less of a crypto person and more of somebody that's into doing say digital marketing instagram i'll show you something that might be relevant for you if we head over to rapid api which we can just google search and then head into the website here rapid api is a huge api marketplace that has thousands of apis as they say here on the front page and in particular let's pull up um a api known as hashtagging so hashtag is able to generate hashtags based on a keyword um so if we click here on get related hashtags instagram let's go ahead and set up our request over an api connector and pull this into sheets to start what we can do is we can create a new sheet and we'll just call this maybe something like keywords for example right um and then i've already opened a new request but i'll do it again just to show you so we go to add new and then under now that we've got our new request open we'll want to go here to copy this url so we'll see it has the request here and we can paste that in like this and then we'll want to add our query parameters so we can do that by starting with a question mark and then we will add a keyword equals travel here and then the only other thing we'll need to include is these uh headers here and there's a special spot inside the api connector sidebar for that so we can paste in that key there and then copy our api key please don't share this with other people consider this kind of like your password um and keep it safe and then we can also grab that x rapid api host as another header and then grab the domain that we're pulling from and then when we paste in that value we'll set current as our destination sheet and then we'll keep the naming consistent so we're going to look for keywords save and then run the request and then shortly we should see the sheet populate with a list of keywords that are related to travel so we can see nature lovers photo of the day um we have even like a relevant score that tells us like how similar it is so yeah that's pretty much it for the digital content stuff all right and lastly there might be situations where you want to scrape data from a given service that you're unable to find an api for so let's say for example you're a computer geek like me and you're using the website leak code for example leeco doesn't have a publicly accessible api at least readily available on their front page right we don't see anything about an api here even searching api nothing comes up so let's say for example that i am inside of lee code and i'm wanting to get data on all of these problems that i see here right i want to see the acceptance rate and the difficulty and so on and so forth right how do i get this data over into google sheets you can use ctrl shift i to inspect inside the website and here at the top you'll want to navigate over to network and then refresh the page and shortly you should see a couple of endpoints pop up here just based on experience i'm going to guess that the data is contained inside all and if we head over here to headers we can see the request url is http code api problems all so i'm guessing that's probably it so we can go ahead and copy that request url head back to google sheets open a new sheet and we'll just call it something like the code problems and then we want to go ahead and make a new request and we should be able to paste this url path in directly and i'm assuming it's public so we can set the current destination sheet and then we'll say leak code problems and save and give it a run and shortly we should see google sheets populate with the data from the code's problem set or maybe not see how long it takes maybe it's going to take a while up and there it is um so there's the data for all of the problems inside leak code we can see the total actions total submitted we can see the question id um yeah we can see the relative difficulty level uh so that's pretty much it so for those of you that are new to apis and understand it now you have kind of a basic idea of what's happening behind the scenes and there's a lot more to it there's different kind of requests we were only focusing on get requests which are the most common but you can also post input which we will talk about in future videos if you have any feedback for me or have any questions feel free to drop a comment below or reach out to us on a reddit account to take a look at some of the different projects people are working on or get help on any issues you're having using this add-on thanks have a good one

Channel: mixed analytics

Views: 3,583

Keywords:

Id: 9VBxRZgY8_4

Length: 10min 26sec (626 seconds)

Published: Mon Mar 15 2021