Connect Coin Market Cap API to Google Sheet

Question

I want to make a script to get Crypto prices updated in a Google Spreadsheets. I don't want to use API connector, rather do it myself end to end. I found a blog post on CMC blog but it doesn't work, I get an error.

Here is the code:

function ohlc_price() {
var sh1=SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“IDs″); 
var requestOptions = {method: ‘GET’, uri: ‘https://pro-api.coinmarketcap.com/v1/cryptocurrency/ohlcv/historical’, qs: {‘id’: ‘1’,’time_period’:’daily’,’interval’:’2d’, ‘start_date’: ‘2019-08-15’, ‘end_date’: ‘2019-08-18’}, 
headers: {‘X-CMC_PRO_API_KEY’: ‘MY APY KEY IS HERE’}, 
json: true, gzip: true};
var url=”https://pro-api.coinmarketcap.com/v1/cryptocurrency/ohlcv/historical?id=1&time_start=2019-06-01&interval=weekly”; 
var result= UrlFetchApp.fetch(url, requestOptions); 
var txt= result.getContentText();
var d=JSON.parse(txt); for (var i=0; i<10;i++) {sh1.getRange(i+2, 1).setValue(d.data.quotes[i].quote.USD.timestamp); sh1.getRange(i+2, 2).setValue(d.data.quotes[i].quote.USD.low); sh1.getRange(i+2, 3).setValue(d.data.quotes[i].quote.USD.open); sh1.getRange(i+2, 4).setValue(d.data.quotes[i].quote.USD.close); sh1.getRange(i+2, 5).setValue(d.data.quotes[i].quote.USD.high);}

Here is the error message I get: SyntaxError: Invalid or unexpected token (ligne : 2, fichier : Coin price.gs)

I tried to follow CMC instructions but nothing works...

Can someone explain me what isn't working and why ?

Thx

Answer 1

I expect there is some lacks of quotes or apostrophes, especially after the uri/url. Try this and complete :

function ohlc_price() {
  var sh1=SpreadsheetApp.getActiveSpreadsheet().getSheetByName('IDs'); 
  var requestOptions = {
    'method' : 'GET', 
    'uri': 'https://pro-api.coinmarketcap.com/v1/cryptocurrency/ohlcv/historical&#8217', 
    'qs': {'id': '1',
      'time_period':'daily',
      'interval':'2d', 
      'start_date': '2019-08-15', 
      'end_date': '2019-08-18'
    }, 
    'headers' : {'X-CMC_PRO_API_KEY': 'MY APY KEY IS HERE'}, 
    'json': true, 
    'gzip': true};
  var url = 'https://pro-api.coinmarketcap.com/v1/cryptocurrency/ohlcv/historical?id=1&time_start=2019-06-01&interval=weekly&#8221'; 
  var result = UrlFetchApp.fetch(url, requestOptions); 
  var txt= result.getContentText();
}