reference does not exist with custom function - google script

Question

I'm trying to call this API response using a custom function where I can change the parameters. This is my code:

function callCandles(pair, start, end) {
  
  var response = UrlFetchApp.fetch("https://api-pub.bitfinex.com/v2/candles/trade:1D:" + "pair" + "/hist?limit=1000&start=" + "start" +"&end=" +"end" +"&sort=-1");
  
  var fact = JSON.parse(response.getContentText()); //parse the data from the API and store it in the variable data and convert response to text format//
  
  return fact;
  
}

This is what I'm typing in the spreadsheet:

=callCandles(tBTCUSD,"1577841154000","1606785154000")

But when I do it I get a "reference does not exist" error.

Thank you.

Tanaike · Accepted Answer · 2020-12-13 01:08:22Z

1

Please modify as follows and test it again.

Modified script:

function callCandles(pair, start, end) {
  
  var response = UrlFetchApp.fetch("https://api-pub.bitfinex.com/v2/candles/trade:1D:" + pair + "/hist?limit=1000&start=" + start + "&end=" + end + "&sort=-1");  // Modified
  
  var fact = JSON.parse(response.getContentText()); //parse the data from the API and store it in the variable data and convert response to text format//
  
  return fact;
  
}

Please use pair, start and end as the variables.
I thought that the reason of your error message is due to this. And also, please modify as follows.

Modified formula:

=callCandles("tBTCUSD","1577841154000","1606785154000")

Please use "tBTCUSD" as a string enclosed by ".

Note:

For example, when pair is used as "pair", pair is the string value. By this, even when =callCandles("tBTCUSD","1577841154000","1606785154000") is used, "tBTCUSD" is not used and "pair" is used.
When =callCandles(tBTCUSD,"1577841154000","1606785154000") is used, in this case, tBTCUSD is used as the named range. By this, when there is no named range of tBTCUSD, #NAME? is returned. By this, pair of function callCandles(pair, start, end) { is #NAME?. Please be careful this.

edited Dec 13, 2020 at 1:08

answered Dec 13, 2020 at 0:45

Tanaike

203k12 gold badges126 silver badges222 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jf12 Over a year ago

This worked! I changed pair, start and end to variables and modified the formula to have tBTC in quotation makrs and removed the quotation marks from start and end in the formula. Thanks a lot.

Dharman · Accepted Answer · 2020-12-13 00:55:29Z

0

Can you be more specific, in which line does the error occur?

Maybe the problem is that the variable you are passing the function are actually not used:

var response = UrlFetchApp.fetch("https://api-pub.bitfinex.com/v2/candles/trade:1D:" + "pair" + "/hist?limit=1000&start=" + "start" +"&end=" +"end" +"&sort=-1");

The parameter pair, start and end should be added without quotation marks. You should also use the newer syntax for some cleaner code:


var response = UrlFetchApp.fetch(`https://api-pub.bitfinex.com/v2/candles/trade:1D:${pair}/hist?limit=1000&start=${start}&end=${end} &sort=-1`);

And as @tanaike pointed out, use tBTCUSD with quotation mark like this "tBTCUSD"

edited Dec 13, 2020 at 0:55

Dharman♦

33.9k27 gold badges106 silver badges157 bronze badges

answered Dec 13, 2020 at 0:49

Michele Midali

844 bronze badges

1 Comment

jf12 Over a year ago

It works now. I changed pair, start and end to variables and modified the formula to have tBTC in quotation makrs and removed the quotation marks from start and end in the formula. Thanks a lot

Collectives™ on Stack Overflow

reference does not exist with custom function - google script

2 Answers 2

Modified script:

Modified formula:

Note:

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Modified script:

Modified formula:

Note:

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related