Accessing stored values in Google Scripts from Google Spreadsheet

Question

I'm using a Google script to poll CoinMarketCap's api via a Google Spreadsheet to get the latest prices, market caps, supplies etc for a number of crypto coins.

My spreadsheet looks like this...

Ticker | Coin | Price | MarketCap | Supply etc
BTC | Bitcoin | 9,000 | 130,000,000,000 | 20,000,000
ETH | Ethereum | 700 | 60,000,000,000 | 80,000,000,000
etc

Then I hit a refresh button and each cell that needs updating makes a call (simultaneously) to repetitive functions (with its ticker/coinID as a parameter identifying the row) as follows...

function getMarketCap(coinID) {
  var url1 = 'https://api.coinmarketcap.com/v1/ticker/';
  var full_url = url1 + coinID;
  var response = UrlFetchApp.fetch(full_url);
  var data = JSON.parse(response.getContentText());
  return Number(data[0]['market_cap_usd']);
}
function getTotalSupply(coinID) {
  //repetitive code for api call
  return Number(data[0]['total_supply']);
}
//about 6 more similar functions making same call but returning different data

Apart from being inelegant, the problem is that the CoinMarketCap api has a limit of somewhere around 30 requests per minute, and although I'm querying less than 30 coins, by multiplying them by each column I'm exceeding the limit.

I know it's a super basic question, but I'm new to calling Google scripts through a spreadsheet and am struggling to see how I could in theory make a single call to the API per coin, and store the individual columns/attributes (i.e. price, marketCap, Supply etc) to be accessed by the other cells. Or perhaps it's possible to 'push' spreadsheet cell updates for the whole row from the Google script after making the first call for the row?

Try not using a custom function and run with time based trigger — Cooper
@Cooper - thanks, but there's no way to do it without losing the ability to manually trigger a complete data refresh? — d3wannabe

Ed Nelson Ed Nelson · Accepted Answer · 2018-05-31T16:07:34

Ypu can just get everything with one call and then pick out the data you need from the symbols in column A. Run it from a custom menu like this:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Current Prices')
      .addItem('Run', 'getMarketCap')
      .addToUi();
}
function getMarketCap() {
  var ss=SpreadsheetApp.getActiveSpreadsheet()
  var s=ss.getSheetByName("Sheet1")//get Sheet1
  var lr=s.getLastRow()
  var url1 = 'https://api.coinmarketcap.com/v1/ticker/';// get all data
  var response = UrlFetchApp.fetch(url1);
  var data = JSON.parse(response.getContentText());
  var sym=s.getRange(2,1,lr,1).getValues()//get list of symbols in col A
  for(var i=0;i<sym.length;i++){//for eacch symbol in col A
    for(var j=0;j<data.length;j++){
       if(sym[i][0]==data[j]['symbol']){//find matching symbol in data
          s.getRange(i+2,2,1,1).setValue(data[j]['name'])
          s.getRange(i+2,3,1,1).setValue(data[j]['price_usd'])
          s.getRange(i+2,4,1,1).setValue(data[j][['market_cap_usd']])
          s.getRange(i+2,5,1,1).setValue(data[j]['total_supply'])
      }
    }
  }
}

Accessing stored values in Google Scripts from Google Spreadsheet

1 Answers