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)

}

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