Configure Coinmarketcap API on Google sheets

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)

}

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?

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

@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 )))))

This is awesome!

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

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.

//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);
}

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("-")
}
}
}`

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("-")
}}}}

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

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.

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.

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

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

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)

}

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