Custom function (UDF): how to input values as a range?

Question

I need to get OHLC data from CEX.IO and put in in necessary cell under the pair name. To do this I have some simple custom function in Google Spreadsheets for retrieve OHLC from CEX.IO:

function OHLCV_CEXIO_1d(date,pair){  
 var site = "https://cex.io/api/ohlcv/hd/";
 var url = site + date + "/" + pair;
 var response = UrlFetchApp.fetch(url);
 var json = response.getContentText();
 var data = JSON.parse(json);
 var result = data.data1d;

 return result;
}

This script is working good when I specify pair as a single cell (for example, "BTC/USD" or cell "B1").

But when I need to specify pair as a range I see "Loading..." and then empty cell. For example, I have such table:

          A      B       H       N
1              BTC/USD ETH/USD BCH/USD
2     20190712

If I use formula in =OHLCV_CEXIO_1d("20190712",B1) evertything will be ok. But if I try to use ARRAYFORMULA like this: =ARRAYFORMULA(IF(B1:1>0,OHLCV_CEXIO_1d("20190712",B1:1),"")) I'll receive empty cell B2. As I understand, I need to change my script to recognize input values as array (or range?).

As a result I expect that cells H2 and N2 will be filled automatically as array

Can you please give an advise how to fix my issue?

UPD: Thanks' for all who replied with links. But I have already red all of them and many other. Now I'm here:

function OHLCV_CEXIO_1d(date,pair){
    if (date.map) {
     return date.map(OHLCV_CEXIO_1d);
    } else {
       if (pair.map) {
        return pair.map(OHLCV_CEXIO_1d);
       } else {
        var url = "https://cex.io/api/ohlcv/hd/" +date +"/" +pair +"/";
        var response = UrlFetchApp.fetch(url);
        var json = response.getContentText(); 
        var data = JSON.parse(json);
        var result = data.data1d;
           return result; 
       }  
    } 
}

Now I have the formula: =ARRAYFORMULA(IF(A2:A10>0;IF(B1:1>0;OHLCV_CEXIO_1d(A2:A10; B1:1);"");"")) Everything works ok till var data = JSON.parse(json); At this point I receive an error: SyntaxError: Unexpected token: <

Could anybody to revise my script and help to fix this problem? Thank you in advance!

Have you read [How to pass a range into a custom function in Google Spreadsheets?](https://webapps.stackexchange.com/q/10629/196152) and/or [Passing cell references to spreadsheet functions](https://stackoverflow.com/q/12206399/1330560)? — Tedinoz, Oct 19 '19 at 14:26

score 0 · Answer 1 · answered Oct 24 '19 at 13:12

It seems you pass the hard objects (aka Array) to the recursion. It's not good.

Try flat arguments instead

/**
 *
 * @param {(string | Array.<string>)} date
 * @param {(string | Array.<string>)} pair
 */
function OHLCV_CEXIO_1d(date, pair) {
  try {
    date = Array.isArray(date) ? date.flat(2) : [date];
    pair = Array.isArray(pair) ? pair.flat(2) : [pair];
    return date.map(function(date) {
      return pair.map(function(pair) {
        var url =
          'https://cex.io/api/ohlcv/hd/' +
          date +
          '/' +
          pair +
          '/';
        var response = UrlFetchApp.fetch(url, {
          muteHttpExceptions: true
        });
        var json = response.getContentText();
        var data = JSON.parse(json);
        var result = data.data1d;
        return result;
      });
    });
  } catch (err) {
    return Utilities.formatString(
      '%s\n%s',
      err.message,
      err.stack
    );
  }
}

You can find code for the polyfill there jonathantneal/array-flat-polyfill

Custom function (UDF): how to input values as a range?

1 Answers1