TypeError: Impossible to call the "getRange" method of null

[angristan]

I created a sheet called Rates and gave access the script access to it.

var queryString = Math.random();

function getData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  //
  //IMPORTANT: Create a sheet called 'Rates'.  This is where the values will be written
  //
  var ssRates = ss.getSheetByName('Rates');

  //Grabbing values from CoinMarketCapAPI
  //Change the variable name to match the trading symbol
  //Change the name in the quotes (e.g. are-bees-carebears) to match the 'id' field from https://api.coinmarketcap.com/v1/ticker/
  //Copy/paste to add more lines as needed

  var BTC = getRate('bitcoin');
  var ETH = getRate('ethereum');
  var XRP = getRate('ripple');

  //Grabbing values that are on CoinMarketCap but not in the API
  //Change the variable name to match the trading symbol
  //Go to the CoinMarketCap page for the currency (e.g. https://coinmarketcap.com/currencies/zeeyx)
  //Change the name in quotes (e.g. zeeyx) to match the end of the URL for your currency
  //Copy/paste to add more lines as needed

  var BCH = getWebRate('bitcoin-cash');
  var LTC = getWebRate('litecoin');
  var MIOTA = getWebRate('iota');

  //Setting values in a sheet called 'Rates' (defined at the top)
  //Change the values in getRange() to match the cells in the 'Rates' sheet you want to userAgent
  //Use the coin symbols from above in setValue()

  ssRates.getRange('B1').setValue(BTC);
  ssRates.getRange('B2').setValue(ETH);
  ssRates.getRange('B3').setValue(XRP);

  ssRates.getRange('B4').setValue(BCH);
  ssRates.getRange('B5').setValue(LTC);
  ssRates.getRange('B6').setValue(MIOTA);

}

  //
  // DON'T TOUCH ANYTHING BELOW
  // IT MAKES THE MAGIC HAPPEN
  //

function getRate(currencyId) {

  var url = 'https://api.coinmarketcap.com/v1/ticker/' + currencyId + '/';
  var response = UrlFetchApp.fetch(url, {'muteHttpExceptions': true});
  var json = response.getContentText();
  var data = JSON.parse(json);

  return parseFloat(data[0]['price_usd']);
}

function getWebRate(currencyId) {
  //Example Output: 
  // '=IMPORTXML("https://coinmarketcap.com/currencies/zeeyx?3908288283","//span[@id=\'quote_price\']")';   

  var coinScrape1 = '=IMPORTXML("https://coinmarketcap.com/currencies/';
  var coinScrape2 = '","//span[@id=\'quote_price\']")';

  return coinScrape1 + currencyId + '?' + queryString + coinScrape2;
}

What am I doing wrong?

Thanks

[Project-42]

Double check that the sheet is called Rates and not the spreadsheet file.. Had that error if you don't have the sheet created

[angristan]

@solifugo I'm sorry, what's the difference between the two?

Thanks for the help

[RJMoise]

@Angristan The top left of Google Docs displays the file name, this can be whatever you want. The bottom of the page will have 'Sheet1' by default, this is what you rename to Rates.

[Project-42]

On this image, the spreadsheet is called "inspection checklist" and the sheet "sheet1"

Make sure your sheet is called Rates

https://s3.amazonaws.com/cdn.freshdesk.com/data/helpdesk/attachments/production/13015973984/original/GUlVucKdpGvbWfaQ3xxN2ce21uSnbd8ToQ.png?1482832755

[angristan]

Ah! I understand now, thanks!

[angristan]

BTW, does one of you know I can do this

Transform this line :

ssRates.getRange('B1').setValue(BTC);

Into something that would put the value of BTC into the cell of column, say D (or whatever, I want to define it), and line were the cell of one column would be = to BTC ?

eg:

Here the price would go on D6, but if I sort lines in alphabetical order based on column A, my BTC price would go on D2, but the script would still write on D6 if I set ssRates.getRange('B1').setValue(BTC);.

I don't know if I'm clear enough :p

Thanks!

[angristan]

Nevermind... https://chrome.google.com/webstore/detail/cryptofinance/bhjnahcnhemcnnenhgbmmdapapblnlcn?hl=en