Import CoinMarketCap Data to Google Sheets

hey youtube are you looking for a good way to keep the price of bitcoin ethereum teller whatever you're trading constantly updated in something like google sheets uh let me introduce you today to a tool known as api connector so instead of basically just copying and pasting this stuff into google sheets which does work but you have to go back and forth and do this every single day to keep it updated or hourly or however often it is there's a simple no code way to programmatically update this data directly from coinmarketcap and we'll do that by accessing a tool on the website mixedanalytics.com so if you head over there in your browser once we get over to mixed analytics website you'll see there's a tool called api connector here on the bottom right corner so if we open that we're going to head over to the google workspace marketplace from here you can install it i already have it installed in my account so i'm not going to worry about that so go ahead and get a copy of that and when you head over to google sheets you should see a copy of that here api connector just like that um now what we're going to do now is head over to coinmarketcap and learn what an api is an api stands for an application programming interface and essentially you can think of an api as a way that different services and websites on the internet communicate with each other um so if you want to be able to pull this raw data at any time without having to manually copy and paste it over we'll connect directly to the api and be able to update it every time we run so let's go ahead and look for that now typically if there's a public api it would be here at the bottom uh so we can see here it says crypto api let's go ahead and pop that open um and this account will require a login i already have one um so it's not going to be too difficult for me um since i already have it it's going to pop me right over once you have your account set up you should be directed to a screen like this and you'll be able to have a certain amount of calls per day right so every time you refresh that api you're going to use one of these calls note this is not connected to your free limit on api connector side currently api connector offers eight calls for free per day or as you can see this service gives you roughly 333 so there's a little bit of a mismatch there so if you're running into a request submit it's probably from our site if you're using the free account now what can we start doing with this let's go ahead and start by heading over to the api documentation this is essentially going to show us all the different information and services that we can connect to to pull data from coin market cap now in general with most apis there's going to be a unique id that links to the given um data point or in this case a crypto project or product or whatever it happens to be so for example instead of calling the api using btc or bitcoin you would use a unique number or hash value to access them so let's go ahead and take a look for that um if we check cryptocurrency we can see here it says coinmarketcapidmap so if we open that up this returns a mapping of all cryptocurrencies to unique coin market cap ids right so we're going to get name symbol token address if you saw our coin gecko video this is probably related to that okay now what is this going to give us right so for example we could see bitcoin's id is number one not surprisingly um so in order to use any of these other endpoints we're going to need to know the id for our given token so let's go ahead and call this over in api connector and we'll start by copying this endpoint or we can actually go ahead and copy this entire url should work um and we'll head over here to our google sheets and we'll open api connector here uh we can go ahead and paste in our url path here and there are no headers or parameters that are necessary for this particular endpoint so we can run this directly as is and we'll call the sheet something like ids and we'll set the destination c as current and then we'll go ahead and say ids just keep the names kind of consistent we'll save and when we run this we're going to see that we get an error message which happens don't worry too much about it we can pop that open and see we have a timestamp error ah the api key is missing rookie mistake so let's go ahead and head back over to the login and i'm going to snag that api key that was over here earlier so you can copy that and then i believe this is going to be a header but we can find out if the api key requires a header or parameter by heading back over to the documentation and we can see there's some query parameters i don't see anything about a key and i don't see any parameters [Music] yeah so if we scroll up here we can see authentication it says using your api key right so the preferred method is to use via a custom header named x dash cmc pro api key blah blah blah right so if we copied that we can put that in here under headers as a key go back to our account and copy this one and then we can paste in our api key here now it should work so if we hit save run and momentarily we should populate with a list of different names and ids and symbols and we'll have an idea with what okay so we can see we have a list now it's quite a lengthy list from the looks of it roughly 4 000 something crypto projects so this is the number we need to know to access the different cryptos uh using different various endpoints yeah so let's say for example we want to get the the latest market quotes for a given crypto right we don't want to get all of them but we just want to get a select view from our previous uh ids that we saw so let's we can we can get to that by clicking on quotes latest and we can head here and we can copy this whole url the quote slash latest and then we can head over here and paste that in as our url path now we're going to need to make a new sheet to put all this data so we'll call this something like latest quotes with just a regular u not a big u and then we'll set that as the current and remember we're going to need our api key but we also need to specify what the id is now we can if we see here in the query parameters we're going to have id and we can make it one or more comma separated cryptocurrency ids right so example one and two let's go ahead and set that up whenever you're setting a query parameter you'll start with a question mark if it's the first one with the name followed by whatever value you need so let's just say we want to get uh bitcoin and [Music] i don't know fray coin but i don't know how to pronounce that uh so 1 and 10 for example right so if we put this in 110 um that should just return these two cryptocoins now next we'll need to grab our api key again and i don't remember what the header was so let's just check again the xcmc pro api key place that in here and copy that right so it looks good and we'll want to give this the same name we'll just call it something like latest quotes just to keep it consistent you can call it whatever you want to but i do it that way just because it's easier to find the requests later anyway so we hit save and then when we hit run we should see just these two quotes returned okay um so we can see it looks like it's coming in a little bit of a flat format which is not a big deal um we can see there's some data on bitcoin and then we have the fray go in over here that means anyways um so if you get this kind of weird structure don't panic uh we have a tool under output options that allows you to twist the data into the structure that you need so by default the single row works 99 of the time but there's going to be cases where this happens so you can deal with that by pressing compact usually it's the first one to go to and if we hit run we'll just hang out for a minute and boom we can see okay that that handled most of the problems so there's still a little bit of a space here um but with the premium account of api connector you're able to use james filtering or james path to be able to clear up some of these gaps and these different unwanted data points um but that's a little bit out of the scope of this tutorial just for your knowledge you can reach out to us and uh all right so yeah that's pretty much it yeah again if you have any questions you can reach out to our support account um keep an eye on this youtube channel we're going to be releasing more videos to help you stay up to date with uh how to connect to the latest api services for whatever your data needs are and get them in google sheets without having to code well so it's a little bit but don't worry too much um yeah so again if anything else let us know have a good one

Channel: mixed analytics

Views: 36,158

Keywords:

Id: n84bsqaer-g

Length: 10min 14sec (614 seconds)

Published: Thu Jan 14 2021

Reddit Comments

[u/vajrapani1]

Hello, I'm trying to use an API from coingecko to update my google sheets. I'm using API-connector as an add-on.

https://api.coingecko.com/api/v3/simple/price?ids=apeswap-finance,auto,pancakeswap-token,tezos,solana,matic-network,binancecoin,kylinnetwork,zenon,enjincoin,filecoin,cosmos,hedera-hashgraph,vechain,polkadot,cardano,bitcoin&vs_currencies=usd

I get an error code

Completed with errors
- Server responded with an error (429) error code: 1015

I can't troubleshoot this through trial and error as I'm only allowed to run this API a few times a day. Could someone help, please? I know nothing about programming.