A CoinMarketCap App-Script modification to read multiple tickers in google sheets

So, I have this script from CoinMarketCap to lookup crypto prices. I want to modify this script where I can apply it to a cell and then drag and drop it down the column to read the ticker symbol in another cell column. I am a complete noob at App Scripts.

function getLatestBTCPrice() {

var url = "https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest";
var apiKey = 'YOUR_API_KEY'; // Replace with your API key
var headers = {
"X-CMC_PRO_API_KEY": apiKey,
"Accept": "application/json"
};

var parameters = {
"symbol": "BTC"
};

var response = UrlFetchApp.fetch(url + "?" + Object.keys(parameters).map(key => key + '=' +
parameters[key]).join('&'), {'headers': headers});
var json = JSON.parse(response.getContentText());
var btcPrice = json.data.BTC.quote.USD.price;

return btcPrice;
}

This is what I've tried so far with no success. The line I'm having issues with is var tickerPrice = json.data.ETC.quote.USD.price; I want to ETC to update as it read a new ticker but I can't seem to figure it out.

function getTickerPrice() {


var url = "https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest";
var apiKey = 'YOUR_API_KEY'; // Replace with your API key
var headers = {
"X-CMC_PRO_API_KEY": apiKey,
"Accept": "application/json"
};

var sheet = SpreadsheetApp.getActiveSpreadsheet();
var ticker = sheet.getRange('A3').getValue();
var parameters = {
"symbol": ticker
};

var response = UrlFetchApp.fetch(url + "?" + Object.keys(parameters).map(key => key + '=' +
parameters[key]).join('&'), {'headers': headers});
var json = JSON.parse(response.getContentText());
var tickerPrice = json.data.ETC.quote.USD.price;

return tickerPrice;

The goal is to have a formula and apply it to a cell and then drag and drop it down the column to read the ticker symbol in another cell column.

Here's a modified version of your script that should achieve what you'd like:

function getTickerPrice(ticker) {
  var url = "https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest";
  var apiKey = 'YOUR_API_KEY';
  var headers = {
    "X-CMC_PRO_API_KEY": apiKey,
    "Accept": "application/json"
  };
  var parameters = {
    "symbol": ticker
  };
  var response = UrlFetchApp.fetch(url + "?" + Object.keys(parameters).map(key => key + '=' +
    parameters[key]).join('&'), { 'headers': headers });
  var json = JSON.parse(response.getContentText());
  var tickerPrice = json.data[ticker].quote.USD.price;
  return tickerPrice;
}

I removed var sheet = SpreadsheetApp.getActiveSpreadsheet(); and var ticker = sheet.getRange('A3').getValue(); in the script since it only captures A3 and changed var tickerPrice = json.data.ETC.quote.USD.price; to var tickerPrice = json.data[ticker].quote.USD.price; so that it reads off the ticker from the cell you'll be pointing to it.

OUTPUT

Note: To make this work, you'll need to call the function like =getTickerPrice(A3) in Google Sheets and then drag the formula down.

REFERENCE

• Custom Functions in Google Sheets