Skip to content

Instantly share code, notes, and snippets.

@erajanraja24

erajanraja24/Coinmarketcap API

Last active May 28, 2024 14:31
Show Gist options
  • Save erajanraja24/117bf3de60970ff78d1671153d4eab73 to your computer and use it in GitHub Desktop.
Save erajanraja24/117bf3de60970ff78d1671153d4eab73 to your computer and use it in GitHub Desktop.
Download ZIP
Configure Coinmarketcap API on Google sheets
Raw
Coinmarketcap API
function getCryptoPrice() {
var sh1=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
var sh2=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");
//Make sure that you got the API key from Coinmarketcap API dashboard and paste it in sheet_1 on cell B1
var apiKey=sh1.getRange(1, 2).getValue();
var url="https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?symbol=BTC"
var requestOptions = {
method: 'GET',
uri: 'https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest',
qs: {
start: 1,
limit: 5000,
convert: 'USD'
},
headers: {
'X-CMC_PRO_API_KEY': apiKey
},
json: true,
gzip: true
};
var httpRequest= UrlFetchApp.fetch(url, requestOptions);
var getContext= httpRequest.getContentText();
var parseData=JSON.parse(getContext);
sh2.getRange(1, 2).setValue(parseData.data.BTC.quote.USD.price)
}
@mihhailtserkassov
Copy link

Hello! thank you for fantastic explanation and code - works very well for me.
Just now I have found an obstackle - I need to get value of 1INCH crypto.

It is not working well, because of Number

sh2.getRange(1, 2).setValue(parseData.data.1INCH.quote.USD.price) - is not woking.

How it could be repaired?

@intsdev
Copy link

intsdev commented Feb 15, 2021 •
edited
Loading

@mihhailtserkassov
try
sh2.getRange(1, 2).setValue(parseData.data['1INCH'].quote.USD.price)

@mihhailtserkassov
Copy link

@mihhailtserkassov
try
sh2.getRange(1, 2).setValue(parseData.data['1INCH'].quote.USD.price)

Thank you very much! It is finally working =) going to buy 1INCH again )))))

@seanli1
Copy link

seanli1 commented Mar 13, 2021

This is awesome!

@alexis3290
Copy link

Hi,
Is there a ay to get the price of the 100 or 1000 first listed coin in one sheet ?

@Masdjaevel
Copy link

Masdjaevel commented May 27, 2021 •
edited
Loading

I have the same question as @alexislevrai.

I tried making new script files for each coin but the result was that as I was going along, creating a file, testing it, saving, and creating a new, the script would only update the last script file in the project - not all of them. Now I've created a separate project for each coin instead (all pointing to the same spreadsheet). Not as an elegant solution as I would've liked though.

@Priyamomer
Copy link

//To fetch price for multiple coins use this
function myFunction() {
var sh1=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("F10%");

var url ="https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?symbol=BTC,ADA,MATIC,LINK,DOT,KSM,BNB,SOL,ONE,ZRX,THETA,GRT,ETH"
var requestOptions = {
method: 'GET',
uri: 'https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest',
qs: {
'start': '1',
'limit': '5000',
'convert': 'USD'
},
headers: {
'X-CMC_PRO_API_KEY': your api
},
json: true,
gzip: true
};
var httpRequest=UrlFetchApp.fetch(url,requestOptions);
var getContext=httpRequest.getContentText();
var parseData=JSON.parse(getContext);
sh1.getRange(1,2).setValue(parseData.data['BTC'].quote.USD.price);
sh1.getRange(2,4).setValue(parseData.data['ETH'].quote.USD.price);
sh1.getRange(2,2).setValue(parseData.data['ADA'].quote.USD.price);
sh1.getRange(2,4).setValue(parseData.data['MATIC'].quote.USD.price);
sh1.getRange(2,6).setValue(parseData.data['LINK'].quote.USD.price);
sh1.getRange(2,8).setValue(parseData.data['DOT'].quote.USD.price);
sh1.getRange(2,10).setValue(parseData.data['KSM'].quote.USD.price);
sh1.getRange(2,12).setValue(parseData.data['BNB'].quote.USD.price);
sh1.getRange(3,2).setValue(parseData.data['SOL'].quote.USD.price);
sh1.getRange(3,4).setValue(parseData.data['ONE'].quote.USD.price);
sh1.getRange(3,6).setValue(parseData.data['ZRX'].quote.USD.price);
sh1.getRange(3,8).setValue(parseData.data['THETA'].quote.USD.price);
sh1.getRange(3,10).setValue(parseData.data['GRT'].quote.USD.price);
}

@pf1991
Copy link

pf1991 commented Aug 18, 2021

Hi Guys! Thank you so much for your scripts.

Here is my version where you can specify also which properties you would like to print.

Headers (Line 1 on 'Data' Sheet):
cmc_rank | name | symbol | max_supply | circulating_supply | total_supply | platform.name | platform.token_address | tags | quote.USD.price | quote.USD.market_cap | quote.USD.fully_diluted_market_cap

`function getCryptoPrice() {
var sh1=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data");
var sh2=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("_");

//Make sure that you got the API key from Coinmarketcap API dashboard and paste it in sheet_1 on cell B1
var apiKey=sh2.getRange(1, 2).getValue();
var coins=sh2.getRange("C1:C").getValues().filter(v => v != "");
var header=sh1.getRange("A1:Z1").getValues()[0].filter(v => v != "");

var url ="https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?symbol=" + coins.join(",")
var requestOptions = {
method: 'GET',
uri: 'https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest',
qs: {
start: 1,
limit: 5000,
convert: 'USD'
},
headers: {
'X-CMC_PRO_API_KEY': apiKey
},
json: true,
gzip: true
};

var readValue = function(data, headerLevels) {
let value = data[headerLevels[0]];
for(k = 1; k < headerLevels.length; k++) {
value = value[headerLevels[k]]
}
if(Array.isArray(value)) {
return value.join(",\n");
}
return value;
}

var httpRequest= UrlFetchApp.fetch(url, requestOptions);
var getContext= httpRequest.getContentText();

var parseData=JSON.parse(getContext);
sh2.getRange(1, 5).setValue(parseData);

for(let i = 0; i < coins.length; i++) {
for(let j = 0; j < header.length; j++) {
let cell = sh1.getRange((2+i), 1+j);
try {
let data = parseData.data[coins[i]];
let headerLevels = header[j].split(".");
cell.setValue(readValue(data, headerLevels));
} catch (ignore) {
cell.setValue("-")
}
}
}`

@crisskumar
Copy link

What am I missing, I created two sheets, then added this script, added table with desired name and symbol
but it spits error:
Error
(Exception: Request failed for https://pro-api.coinmarketcap.com returned code 400. Truncated server response: {"status":{"timestamp":"2021-08-24T01:21:44.479Z","error_code":400,"error_message":"Invalid values for "symbol": "STROJ,SYMBOL"","elapsed":0,"c... (use muteHttpExceptions option to examine full response)
getCryptoPrice @ getCryptoPrice.gs:37
)

code:
function getCryptoPrice()
{
var sh1=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data");
var sh2=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("key");

//Make sure that you got the API key from Coinmarketcap API dashboard and paste it in key on cell B1
var apiKey=sh2.getRange(1, 2).getValue();
var coins=sh2.getRange("C1:C").getValues().filter(v => v != "");
var header=sh1.getRange("A1:Z1").getValues()[0].filter(v => v != "");

var url ="https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?symbol=" + coins.join(",")
var requestOptions = {
method: 'GET',
uri: 'https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest',
qs: {
start: 1,
limit: 5000,
convert: 'USD'
},
headers: {
'X-CMC_PRO_API_KEY': apiKey
},
json: true,
gzip: true
};

var readValue = function(data, headerLevels){
let value = data[headerLevels[0]];
for(k = 1; k < headerLevels.length; k++) {
value = value[headerLevels[k]]
}
if(Array.isArray(value)) {
return value.join(",\n");
}
return value;}

var httpRequest= UrlFetchApp.fetch(url,requestOptions);
var getContext= httpRequest.getContentText();

var parseData=JSON.parse(getContext);
sh2.getRange(1, 5).setValue(parseData);

for(let i = 0; i < coins.length; i++) {
for(let j = 0; j < header.length; j++) {
let cell = sh1.getRange((2+i), 1+j);
try {
let data = parseData.data[coins[i]];
let headerLevels = header[j].split(".");
cell.setValue(readValue(data, headerLevels));
} catch (ignore) {
cell.setValue("-")
}}}}

@pf1991
Copy link

pf1991 commented Aug 24, 2021 •
edited
Loading

What am I missing, I created two sheets, then added this script, added table with desired name and symbol
but it spits error:
Error
(Exception: Request failed for https://pro-api.coinmarketcap.com returned code 400. Truncated server response: {"status":{"timestamp":"2021-08-24T01:21:44.479Z","error_code":400,"error_message":"Invalid values for "symbol": "STROJ,SYMBOL"","elapsed":0,"c... (use muteHttpExceptions option to examine full response)
getCryptoPrice @ getCryptoPrice.gs:37
)

code:
function getCryptoPrice()
{
var sh1=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data");
var sh2=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("key");

//Make sure that you got the API key from Coinmarketcap API dashboard and paste it in key on cell B1
var apiKey=sh2.getRange(1, 2).getValue();
var coins=sh2.getRange("C1:C").getValues().filter(v => v != "");
var header=sh1.getRange("A1:Z1").getValues()[0].filter(v => v != "");

var url ="https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?symbol=" + coins.join(",")
var requestOptions = {
method: 'GET',
uri: 'https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest',
qs: {
start: 1,
limit: 5000,
convert: 'USD'
},
headers: {
'X-CMC_PRO_API_KEY': apiKey
},
json: true,
gzip: true
};

var readValue = function(data, headerLevels){
let value = data[headerLevels[0]];
for(k = 1; k < headerLevels.length; k++) {
value = value[headerLevels[k]]
}
if(Array.isArray(value)) {
return value.join(",\n");
}
return value;}

var httpRequest= UrlFetchApp.fetch(url,requestOptions);
var getContext= httpRequest.getContentText();

var parseData=JSON.parse(getContext);
sh2.getRange(1, 5).setValue(parseData);

for(let i = 0; i < coins.length; i++) {
for(let j = 0; j < header.length; j++) {
let cell = sh1.getRange((2+i), 1+j);
try {
let data = parseData.data[coins[i]];
let headerLevels = header[j].split(".");
cell.setValue(readValue(data, headerLevels));
} catch (ignore) {
cell.setValue("-")
}}}}

The error suggests that you are putting the value SYMBOL on the column where you should put only the target coins.

Usage example:
https://docs.google.com/spreadsheets/d/1gl_xhWMLLVxIjGap8OWuAXFkW8JKyVo00-_z07A0GbM/edit?usp=drivesdk

@crisskumar
Copy link

What am I missing, I created two sheets, then added this script, added table with desired name and symbol
but it spits error:
Error
(Exception: Request failed for https://pro-api.coinmarketcap.com returned code 400. Truncated server response: {"status":{"timestamp":"2021-08-24T01:21:44.479Z","error_code":400,"error_message":"Invalid values for "symbol": "STROJ,SYMBOL"","elapsed":0,"c... (use muteHttpExceptions option to examine full response)
getCryptoPrice @ getCryptoPrice.gs:37
)
code:
function getCryptoPrice()
{
var sh1=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data");
var sh2=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("key");
//Make sure that you got the API key from Coinmarketcap API dashboard and paste it in key on cell B1
var apiKey=sh2.getRange(1, 2).getValue();
var coins=sh2.getRange("C1:C").getValues().filter(v => v != "");
var header=sh1.getRange("A1:Z1").getValues()[0].filter(v => v != "");
var url ="https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?symbol=" + coins.join(",")
var requestOptions = {
method: 'GET',
uri: 'https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest',
qs: {
start: 1,
limit: 5000,
convert: 'USD'
},
headers: {
'X-CMC_PRO_API_KEY': apiKey
},
json: true,
gzip: true
};
var readValue = function(data, headerLevels){
let value = data[headerLevels[0]];
for(k = 1; k < headerLevels.length; k++) {
value = value[headerLevels[k]]
}
if(Array.isArray(value)) {
return value.join(",\n");
}
return value;}
var httpRequest= UrlFetchApp.fetch(url,requestOptions);
var getContext= httpRequest.getContentText();
var parseData=JSON.parse(getContext);
sh2.getRange(1, 5).setValue(parseData);
for(let i = 0; i < coins.length; i++) {
for(let j = 0; j < header.length; j++) {
let cell = sh1.getRange((2+i), 1+j);
try {
let data = parseData.data[coins[i]];
let headerLevels = header[j].split(".");
cell.setValue(readValue(data, headerLevels));
} catch (ignore) {
cell.setValue("-")
}}}}

The error suggests that you are putting the value SYMBOL on the column where you should put only the target coins.

Usage example:
https://docs.google.com/spreadsheets/d/1gl_xhWMLLVxIjGap8OWuAXFkW8JKyVo00-_z07A0GbM/edit?usp=drivesdk

Thank you much, It worked, was missing symbol columns in key sheet.

@Gia-1989
Copy link

Gia-1989 commented Sep 2, 2021

Hi, I am having the same error: returned code 400. Truncated server response: {"status":{"timestamp":"2021-08-31T17:25:37.697Z","error_code":400,"error_message":""symbol" should only include comma-separated alphanumeric cry... (use muteHttpExceptions option to examine full response)

How can I fix it? I What do you mean by missing symbol columns in key sheet? I have Tokens instead of Symbols.

@joylay83
Copy link

joylay83 commented Sep 9, 2021

Hi, I am having the same error: returned code 400. Truncated server response: {"status":{"timestamp":"2021-08-31T17:25:37.697Z","error_code":400,"error_message":""symbol" should only include comma-separated alphanumeric cry... (use muteHttpExceptions option to examine full response)

How can I fix it? I What do you mean by missing symbol columns in key sheet? I have Tokens instead of Symbols.

I think you forgot to key in the coins at column C in the "key" or "_" sheet

@joylay83
Copy link

joylay83 commented Sep 9, 2021 •
edited
Loading

What am I missing, I created two sheets, then added this script, added table with desired name and symbol
but it spits error:
Error
(Exception: Request failed for https://pro-api.coinmarketcap.com returned code 400. Truncated server response: {"status":{"timestamp":"2021-08-24T01:21:44.479Z","error_code":400,"error_message":"Invalid values for "symbol": "STROJ,SYMBOL"","elapsed":0,"c... (use muteHttpExceptions option to examine full response)
getCryptoPrice @ getCryptoPrice.gs:37
)
code:
function getCryptoPrice()
{
var sh1=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data");
var sh2=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("key");
//Make sure that you got the API key from Coinmarketcap API dashboard and paste it in key on cell B1
var apiKey=sh2.getRange(1, 2).getValue();
var coins=sh2.getRange("C1:C").getValues().filter(v => v != "");
var header=sh1.getRange("A1:Z1").getValues()[0].filter(v => v != "");
var url ="https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?symbol=" + coins.join(",")
var requestOptions = {
method: 'GET',
uri: 'https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest',
qs: {
start: 1,
limit: 5000,
convert: 'USD'
},
headers: {
'X-CMC_PRO_API_KEY': apiKey
},
json: true,
gzip: true
};
var readValue = function(data, headerLevels){
let value = data[headerLevels[0]];
for(k = 1; k < headerLevels.length; k++) {
value = value[headerLevels[k]]
}
if(Array.isArray(value)) {
return value.join(",\n");
}
return value;}
var httpRequest= UrlFetchApp.fetch(url,requestOptions);
var getContext= httpRequest.getContentText();
var parseData=JSON.parse(getContext);
sh2.getRange(1, 5).setValue(parseData);
for(let i = 0; i < coins.length; i++) {
for(let j = 0; j < header.length; j++) {
let cell = sh1.getRange((2+i), 1+j);
try {
let data = parseData.data[coins[i]];
let headerLevels = header[j].split(".");
cell.setValue(readValue(data, headerLevels));
} catch (ignore) {
cell.setValue("-")
}}}}

The error suggests that you are putting the value SYMBOL on the column where you should put only the target coins.

Usage example:
https://docs.google.com/spreadsheets/d/1gl_xhWMLLVxIjGap8OWuAXFkW8JKyVo00-_z07A0GbM/edit?usp=drivesdk

May I know how do I get it to show multiple 'convert'? eg USD, XAU, EUR.
I tried convert: 'USD,XAU,EUR' but no avail. I added a quote.XAU.price but got "-"
the script had no errors during execution. It used 1 credit.

also, how do i pull the 24hr change and 7d change?

tq in advance

@PapCoin66
Copy link

Hello,
I have an error ode, can you help ?
thanks

Error
Exception: Request failed for https://pro-api.coinmarketcap.com returned code 403. Truncated server response:

here is my code

function getCryptoPrice() {
var sh1=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
var sh2=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");

//Make sure that you got the API key from Coinmarketcap API dashboard and paste it in sheet_1 on cell B1
var apiKey=sh1.getRange(1, 2).getValue();

var url="https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?symbol=BTC"
var requestOptions = {
method: 'GET',
uri: 'https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest',
qs: {
start: 1,
limit: 5000,
convert: 'USD'
},
headers: {
'X-CMC_PRO_API_KEY': apiKey
},
json: true,
gzip: true
};

var httpRequest= UrlFetchApp.fetch(url, requestOptions);
var getContext= httpRequest.getContentText();

var parseData=JSON.parse(getContext);
sh2.getRange(4, 2).setValue(parseData.data['BTC'].quote.USD.price)

}

@karug64
Copy link

karug64 commented Jan 13, 2022

Hi,
I’m new in this forum.
I’ve a free account in CoinMarketCap.
If possible, can you tell me why, for every call of function i call this

start: 1,
limit: 5000

although i want the price of one crypto ?

thank’s

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment