TypeError on line 9 when running function: getData

[ghost]

The following error message is returned when calling function getData:

TypeError: Cannot call method "getSheetByName" of null. (line 9, file "Code")

Below is line 9 from my copy of the script (latest commit c56800d)

var ssRates = ss.getSheetByName('rates');

And the full script: (Note sheet "rates" exists.)

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 DOGE = getRate(dogecoin);
  var XVG = getRate('verge');

  //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 XXX = getWebRate('xxx');
 // var YYY = getWebRate('yyy');

  //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(DOGE);
  ssRates.getRange('B2').setValue(XVG);

  //VTC wallet balances
  //Add more as needed with different variable names

  var VtcWallet = getVtcBalance("yourAddressHere");

  //Change the value in getRange() to match the cell in spreadsheet
  //Change the value in setValue() to match the variable above

  ssRates.getRange('E3').setValue(VtcWallet);

  //Ethereum Wallet Balances
  //Create an account on Etherscan.io
  //Create an API key at https://etherscan.io/myapikey
  //Put your API key in below, replacing yourEtherscanApiKey
  //Add Ethereum address, replacing yourEthAddress

  var EthApiKey = "XXX";
  var EthWallet = getEthBalance(EthApiKey,"XXX");
  var EthExodus = getEthBalance(EthApiKey, "XXX");

  //Putting this value in spreadsheet
  //Change the value in setValue() to match the variable above

  ssRates.getRange('E1').setValue(EthWallet);
  ssRates.getRange('E2').setValue(EthExodus);
}

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

function getEthBalance(ethApiKey,ethAddress) {

  var obj = JSON.parse (UrlFetchApp.fetch("https://api.etherscan.io/api?module=account&action=balance&address="+ethAddress+"&tag=latest&apikey="+ethApiKey));
  var data = (obj.result);

  return data * Math.pow(10,-18);
}

function getVtcBalance(vtcAddress) {

  var obj = UrlFetchApp.fetch("http://explorer.vertcoin.info/ext/getbalance/"+vtcAddress);

  return obj;
}

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

[ghost]

I do have a correctly named spreadsheet in Google Drive, also renamed page1 renamed to 'rates' (without the quotes) It didn't change the error.

[Project-42]

I tried your code and works once you add single quotes to dogecoin var DOGE = getRate(dogecoin);

btw, dogecoin?? really?? :P

Edit: The name of the sheet should be rates no 'rates' or "rates", the name of the spreadsheet (the file) should not be an issue

[ghost]

Hmm... I Fixed the quotes on 'dogecoin', double-checked the rates sheet, it's ok. Error persists however :/

And Why dogecoin? That's an easy one, I joined way back for the memes, and stayed for the profits, which were actually pretty decent over time :+1:

[Project-42]

I dont understand..

Can you try to recreate rates sheet? or even copy and past the code you posted here?

I created a new spredseet, copied "your" code, and it works fine (after adding quotes to dogecoin as we mentioned earlier)

`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 DOGE = getRate('dogecoin'); var XVG = getRate('verge');

//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 XXX = getWebRate('xxx'); // var YYY = getWebRate('yyy');

//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(DOGE); ssRates.getRange('B2').setValue(XVG);

//VTC wallet balances //Add more as needed with different variable names

var VtcWallet = getVtcBalance("yourAddressHere");

//Change the value in getRange() to match the cell in spreadsheet //Change the value in setValue() to match the variable above

ssRates.getRange('E3').setValue(VtcWallet);

//Ethereum Wallet Balances //Create an account on Etherscan.io //Create an API key at https://etherscan.io/myapikey //Put your API key in below, replacing yourEtherscanApiKey //Add Ethereum address, replacing yourEthAddress

var EthApiKey = "XXX"; var EthWallet = getEthBalance(EthApiKey,"XXX"); var EthExodus = getEthBalance(EthApiKey, "XXX");

//Putting this value in spreadsheet //Change the value in setValue() to match the variable above

ssRates.getRange('E1').setValue(EthWallet); ssRates.getRange('E2').setValue(EthExodus); }

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

function getEthBalance(ethApiKey,ethAddress) {

var obj = JSON.parse (UrlFetchApp.fetch("https://api.etherscan.io/api?module=account&action=balance&address="+ethAddress+"&tag=latest&apikey="+ethApiKey)); var data = (obj.result);

return data * Math.pow(10,-18); }

function getVtcBalance(vtcAddress) {

var obj = UrlFetchApp.fetch("http://explorer.vertcoin.info/ext/getbalance/"+vtcAddress);

return obj; }

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; }`

[Project-42]

@sergeimikhailov771 can you check the current code and close the issue if it works for you?

[ddarbs]

Still broken?

[LesterCovax]

no user update