CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetscryptocurrency
NiphitAe
NiphitAe

Reputation: 405

Connect Coin Market Cap API to Google Sheet

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

Upvotes: 0

Views: 1263

Answers (2)

Tanaike
Tanaike

Reputation: 201378

Modification points:

  • There are no properties of uri and qs in the parameter of UrlFetchApp.
  • In your URL, id=1&time_start=2019-06-01&interval=weekly is used as the query parameter. But in your qs, {‘id’: ‘1’,’time_period’:’daily’,’interval’:’2d’, ‘start_date’: ‘2019-08-15’, ‘end_date’: ‘2019-08-18’} is used. So unfortunately, I cannot understand which do you want to use.
    • In this modification, interval=weekly, time_start=2019-06-01, and time_end=2019-08-18 are used. When you want to use other values, please modify the script.
  • In your script, for (var i = 0; i < 10; i++) {} is used for var d = JSON.parse(txt). In this case, when the data length is less than 10, an error occurs.

When above points are reflected to your script, it becomes as follows.

Modified script:

function ohlc_price() {
  // This is from https://gist.github.com/tanaikech/70503e0ea6998083fcb05c6d2a857107
  String.prototype.addQuery = function (obj) {
    return this + Object.keys(obj).reduce(function (p, e, i) {
      return p + (i == 0 ? "?" : "&") +
        (Array.isArray(obj[e]) ? obj[e].reduce(function (str, f, j) {
          return str + e + "=" + encodeURIComponent(f) + (j != obj[e].length - 1 ? "&" : "")
        }, "") : e + "=" + encodeURIComponent(obj[e]));
    }, "");
  }

  var url = "https://pro-api.coinmarketcap.com/v1/cryptocurrency/ohlcv/historical";
  var query = { 'id': '1', 'time_period': 'daily', 'interval': 'weekly', 'time_start': '2019-06-01', 'time_end': '2019-08-18' };
  var endpoint = url.addQuery(query);
  var requestOptions = {
    method: 'GET',
    headers: { 'X-CMC_PRO_API_KEY': 'MY APY KEY IS HERE' },
  };
  var result = UrlFetchApp.fetch(endpoint, requestOptions);
  var txt = result.getContentText();
  var d = JSON.parse(txt);

  var sh1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("IDs");
  var values = d.data.quotes.map(({ quote: { USD } }) => [USD.timestamp, USD.low, USD.open, USD.close, USD.high]);
  sh1.getRange(2, 1, values.length, values[0].length).setValues(values);
}

Note:

  • In this answer, it supposes that your value of X-CMC_PRO_API_KEY is valid value. Please be careful this.

References:

Upvotes: 0

Mike Steelson
Mike Steelson

Reputation: 15308

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

Upvotes: 1

Related Questions