How to use an array and a single string in the same custom function

Question

I'm trying to make a custom function that will receive an array and a string as parameters, for example:

The script so far:

function VISITAS(CRM,CICLO) {

  if(CRM.map){
    return CRM.map(VISITAS);
  }
  else{
    CICLO = (CICLO).toString();
   return CICLO;
  }

}

If i use the function like VISITAS(A1;D1) it works and return "CICLO" as needed but when using a range and a single cell, the values turns to "0" like the first image. The script so far is a test, the second parameter will be used in some conditional operations. How can i use a range and keep the value of the single cell?

EDIT: I'm sorry, i will provide the whole need.

This function will need to use the second parameter as a conditional to search and return data that is in another sheet and return this data for each row in the first array.

For example, i have a sheet with "CICLOS" from 1 to 7, i need it to return information of "CICLO 2" for example based on each line of the array

I have a scenario like this:

and i need to use the function one time per column using the column header and the "CLIENT" column as parameter, the output should be something like this:

With the first row below the header being the function

Does this answer your question? Supporting arrays in custom functions with multiple inputs — TheMaster
– TheMaster, Commented Dec 10, 2019 at 20:13
One thing, the arguments for custom functions are not ranges, but rather arrays and values. Please see developers.google.com/apps-script/guides/sheets/…. — IMTheNachoMan
– IMTheNachoMan, Commented Dec 11, 2019 at 3:32
And what exactly do you need the code to ultimately do? On the first call, CRM will be a 2D array and CICLO will be a value. And, as @anton-dementiev mentioned below, when VISITAS is called in the CRM.map, it won't know the CICLO value. If you could provide more detail of what you're trying to do it would help. — IMTheNachoMan
– IMTheNachoMan, Commented Dec 11, 2019 at 3:36
Can you explain what you want this function to do? You talked about what the input has to be, but didn't say anything about the output. — Iamblichus
– Iamblichus, Commented Dec 11, 2019 at 8:12
Does it have to be a custom function, or it can be a regular Apps Script function? — Iamblichus
– Iamblichus, Commented Dec 11, 2019 at 13:42

Anton Dementiev · Accepted Answer · 2019-12-10 20:54:35Z

2

You are trying to call the 'toString()' method of something that isn't even there. When you make multiple recursive calls inside 'map()', the callback function VISITAS doesn't receive the 2nd parameter (CICLO). Since this new callback function has its own scope, CICLO is actually undefined.

The following code returns 0s:

function CUSTOM_FUNC(range, param) {
  if (range.map) {
    return range.map(CUSTOM_FUNC);
  } else {
    param = param.toString();
    return param;
  }

This snipped works correctly:

function CUSTOM_FUNC(range, param) {
  if (range.map) {
    return range.map(CUSTOM_FUNC);
  } else {
     range = range.toString();
    return range;
  }

Also, why would you use recursive calls in the first place? It's not like you have a rabbit hole in each cell containing the unspecified number of nested arrays.

edited Dec 10, 2019 at 20:54

answered Dec 10, 2019 at 20:49

Anton Dementiev

5,7344 gold badges23 silver badges36 bronze badges

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

2 Comments

IMTheNachoMan Over a year ago

Doesn't map send the index of the item in the array as the second parameter?

Iago Losada Alibune Over a year ago

Hello Anton! Thanks for the answer, I've edited my question and provided more detail

Iamblichus · Accepted Answer · 2019-12-11 15:33:26Z

1

Assuming that:

The parameters that you pass are (1) the array of clients, and (2) the ciclo to look for.
The data to look for in another sheet is not passed as a parameter of the custom function.

If all this is correct, then, what about this:

function visitasCustom(clients, ciclo) {
  var ss = SpreadsheetApp.getActive();
  var origin = ss.getSheetByName("Origin");
  var firstRow = 2;
  var firstCol = 1;
  var numRows = origin.getLastRow() - 1;
  var numCols = 3;
  var values = origin.getRange(firstRow, firstCol, numRows, numCols).getValues();
  var result = [];
  for (var i = 0; i < clients.length; i++) {
    var found = false;
    for (var j = 0; j < values.length; j++) {
      if (clients[i] == values[j][0] && ciclo == values[j][1]) {
        result.push(values[j][2]);
        found = true;
      }
    }
    if (!found) result.push("");
  }
  return result;
}

This custom function looks for data in a sheet called Origin and adds all the dates that correspond to that specific Ciclo, for each column in which you use this function.

I hope this is of any help.

edited Dec 11, 2019 at 15:33

answered Dec 11, 2019 at 14:01

Iamblichus

19.5k2 gold badges14 silver badges27 bronze badges

1 Comment

Iago Losada Alibune Over a year ago

Thank you! I just needed to change a little bit to concatenate results when the "CICLO" and "CLIENT" are the same, but it worked just like i needed!

Collectives™ on Stack Overflow

How to use an array and a single string in the same custom function

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related