Google Active Script: using map in an array with 2 arguments

Question

This works:

Suppose I want to use in an ArrayFormula() the indirect() function, which doesn't work. That is, supposed I type this in cell E1, =ArrayFormula(indirect(address(row(E:E),column(A:A)))), this will return the value of cell A1 in all the cells in column E. To circumvent this, I created the custom function below:

function retValue(cell){
  if(cell.map) {
    return cell.map(retValue);
  } else {
    var cellRang = SpreadsheetApp.getActive().getRange(cell);
    return cellRang.getValue();
  }
}

Now, when I enter this =ArrayFormula(retValue(address(row(E:E),column(A:A)))) in cell E1, each cell in column E will have the corresponding value of the cell in the same row of column A.

My problem:

What I need is to have a custom function that receives 2 arguments, like function retValue2(cell, anotherRange) but I only care if cell is an array, as anotherRange must be an array anyway. What happens is that when I iteratively call cell.map(retValue2) the argument anotherRange is lost and I'm not entirely sure how to go about this.

I tried to come up with this:

function retValue2(cell, anotherRange) {
  if (cell.map) {
    return cell.map(retValue2);
  } else {
    var range = SpreadsheetApp.getActive().getRange(anotherRange);
    var nrRows = range.getNumRows();
    var nrCols = range.getNumColumns();
    return cell + ',' + nrRows + ',' + nrCols;
  }
}

But it fails because anotherRange is not recognized inside the iteration I think. How do I solve this?

PS.: in the example that works, why exactly does it work at all? I understand that when I do this return cell.map(retValue); it will use my own function as the callback, which would return all the values in the array, but in the spreadsheet it shows only the one on the same row. What is the magic here?

EDIT:

My end goal is to create my own lookup function where I pass a search key and a 2-dimensional array (rows and columns) and then, it locates the coordinates of that key in the array.

Look here:

function retCoord(sKey, sIRange) {
  try {
    var key = SpreadsheetApp.getActive().getRange(sKey).getValue();
  }
  catch(e) {
    var key = sKey;
  }
  var range = SpreadsheetApp.getActive().getRange(sIRange).getValues();
  nbRow = range.length;
  nbColumn = range[0].length;
  for(var i = 0; i<nbRow; i++){
    for(var j = 0; j<nbColumn; j++){
      if(range[i][j] == key){
        return i + ", " + j;
      }
    }
  }
}

If in my spreadsheet I enter something like =retCoord("K4","A:L") it will search the content of cell K4 in my 2-dimensional array A:L and return where in the array the value is, 1, 2 for example. It also works if I use =retCoord(K4,"A:L") or =retCoord("term searched","A:L"), and in this latter case I enter directly the term searched. This works fine until I use it in an ArrayFormula().

First, instead of =retCoord("K4","A:L") I could very well use =retCoord(address(4, 11),"A:L") for instance and my .getRange() method would get the cell K4 just fine.

Now, here is the big problem. I want to use my function in an ArrayFormula(), and, positioning my cursor in O1 and hoping to search the items from column D in the columns E through L I want to pass as one of the inputs of address(), row(O:O), like this: =ArrayFormula(retCoord(address(row(O:O),4,4),"E:L")), meaning that for each row, a new address is passed. that is, in O1 cell, it should return the result of retCoord(D1,"E:L"), in O2 should be retCoord(D2,"E:L"), in O3 should be retCoord(D3,"E:L") and so on.

The issue happens because in my function, sKey is an array and if I try to use the same approach as my function retValue (here above in the This works: section) it fails because now, in retCoord, I have 2 inputs, and the introspection function calling from before fails because of the second input. Of course I'm missing something and there is always a better and more elegant way to approach a problem. But for now, can anyone help me with this one?

EDIT2:

I changed the code a little and it seems I moved forward but not quite yet. Check comment below for line indicated by (*):

function retCoord(sKey, sIRange) {
  var key = '';
  try {
    key = SpreadsheetApp.getActive().getRange(sKey).getValue();
    return key;
  }
  catch(e) {
    if (sKey.map) {
      var objKey = sKey.map(retCoord);
      return objKey; // (*) <--- comments below
      key = objKey;
    } else {
      key = sKey;
    }
  }
  var range = SpreadsheetApp.getActive().getRange(sIRange).getValues();
  nbRow = range.length;
  nbColumn = range[0].length;
  for(var i = 0; i<nbRow; i++){
    for(var j = 0; j<nbColumn; j++){
      if(range[i][j] == key){
        return key + " = "+ i + ", " + j;
      }
    }
  }
}

This (*) line I added only to see what was returning from the map. Surprisingly (in a way), it is an object with all the elements of that column and that is expected. What I didn't expect was that if I return that object to my spreadsheet cell, it brings back only the value of that specific cell (as I wanted but not really as expected). But the problem is I cannot use that object to compare against a string as it will expand and become something else and will never match. Look:

If I do return objKey in my custom function, over cell O1 it returns "a", in O2 it returns "b", in O3 it returns "c" as expected because those are the values of my cells D1, D2, D3 respectively.
If I do return "-> " + objKey in in my custom function, instead of returning -> a, -> b, -> c in O1, O2, O3 respectively, it returns -> =A:A,a,b,c,d,e,f for all the cells in column O, which seems it did some type of objKey.toString() under the hood before concatenating with "-> "

Conclusion: how do I "coerce" the apparent result of objKey into string keeping the apparent result when you return the object without changing it? Simply put, I want the concatenation "some string" + to be equal to "some string" + "one string representing the value in that row instead of an object". In other words, what the hell is happening here? How does Google Sheets now that in that row, that element is the one representing the one I want? This is what I asked in the "PS." in the first part of this post.

Maybe I misunderstood, but wouldn't it be easier to use in E1 =Arrayformula(A:A) to show the contents of column A in column E ? — JPV
– JPV, Commented Sep 24, 2019 at 10:21
I believe that using Apps Script your issue can be solved in a more elegant way, without cell formulas. For this, could you explain a little bit more in detail what your general intention is? — ziganotschka
– ziganotschka, Commented Sep 24, 2019 at 10:46
Possible duplicate of Supporting arrays in custom functions with multiple inputs — TheMaster
– TheMaster, Commented Sep 24, 2019 at 11:31
@JPV, if I wanted to have just the value of the column A, yes. But that was ludic. Imagine that instead of A:A I wanted column(A:A)+1 (=B:B), or depending on some circustance, it would be column(A:A)+1, else column(A:A)+2, and so on and so forth. I will explain in my question what I used it for. — Adriano_Pinaffo
– Adriano_Pinaffo, Commented Sep 24, 2019 at 12:08
@ziganotschka, I will try to explain a little better in my question — Adriano_Pinaffo
– Adriano_Pinaffo, Commented Sep 24, 2019 at 12:08

ziganotschka · Accepted Answer · 2019-09-25 11:19:54Z

0

You want to search all keys in the sKey column within the Range sIRange and note the position of each key within the range into the corresponding row in a destination column ?

This is how you can do it with Apps Script without formulas:

function retCoord(sKeyColumn, destinationColumn, sIRange) {
  var key = sKeyColumn;
  var range=SpreadsheetApp.getActive().getRange(sIRange);
  var rangeValues = SpreadsheetApp.getActive().getRange(sIRange).getValues();
  var nbRow = rangeValues.length;
  var nbColumn = rangeValues[0].length;
  var sKeyRange=SpreadsheetApp.getActive().getRange(sKeyColumn);
  var destinationRange=SpreadsheetApp.getActive().getRange(destinationColumn);
  var sKeyValues=sKeyRange.getValues();
  for(var k=0;k<sKeyValues.length;k++){
    for(var i = 1; i<=nbRow; i++){
      for(var j = 1; j<=nbColumn; j++){
        if(range.getCell(i, j).getValue() == sKeyValues[k][0]){
          destinationRange.getCell(k+1, 1).setValue(range.getCell(i, j).getA1Notation());
        }
      }
    }
  }
}

Sample call:

function myFunction(){
retCoord('A1:A6','B1:B6','C1:J7');
}

retCoord('A:A','B:B','C:J'); would also work but would take very long, since the code would also loop through empty rows

answered Sep 25, 2019 at 11:19

ziganotschka

26.9k2 gold badges18 silver badges34 bronze badges

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

1 Comment

Adriano_Pinaffo Over a year ago

Sorry, but this is not actually what I want. I do want to use a formula and not only that, I want an ArrayFormula().

Collectives™ on Stack Overflow

Google Active Script: using map in an array with 2 arguments

1 Answer 1

This is how you can do it with Apps Script without formulas:

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

This is how you can do it with Apps Script without formulas:

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related