Why this custom Google Spreadsheet function does not work?

Question

I've writen the following code to implement a custom Google Spreadsheet function; the goal is to write all possible games between teams whose names are listed in 'names' parameter:

function ALLGAMES( names )
{
    var nbTeams = names.length;
    var gameList = [];

    for( var t1=0; t1<nbTeams-1; t1++ ) {
        for( var t2=t1+1; t2<nbTeams; t2++ ) {
            gameList.push( [ new String(names[t1]), new String(names[t2]) ] );
            //A. gameList.push( [ t1, t2 ] );
        }
    }

    // B. return JSON.stringify(gameList)
    // C. return [[ 'a', 'b' ], ['c', 'd']]; //using the value returned by JSON.stringify
    return gameList;
}

When I use the function in the spreadsheet, it fills cells with blank values instead of the teams names.

However, the behaviour is as expected in any of the following cases:

If I use the A. line (pushing numbers instead of strings), it displays all the numbers correctly
If I use the B. line (returning the JSON string for the array), it displays a correct JSON string
If I use the C. line (returning the array in full), it works as expected.

Where is the problem?

Edit 1:

Here is the call to ALLGAMES I make to test the function:

I would suggest to print (log) new String(names[t1]), new String(names[t2]) before push to debug the issue — Eugene, Sep 06 '16 at 14:47
Add the formula that you are using to call the custom function. If you are using a reference as argument, add the values of the reference. — Rubén, Sep 06 '16 at 18:24
@Eugene the log shows correct strings, which is consistent with the case where I uncomment line B. and get the JSON string of the array. — Silverspur, Sep 07 '16 at 09:44

score 2 · Accepted Answer · edited May 23 '17 at 10:33

2

The ALLGAMES function will receive a 2-dimensional array from your selection. You should first map it into a 1-dimensional array. In the loop you should leave out new String(..). In javascript you almost never use this notation, see this discussion

function ALLGAMES( names )
{
  names = names.map(function(item) { return item[0]; }); // adjusted

  var nbTeams = names.length;
  var gameList = [];

  for( var t1=0; t1<nbTeams-1; t1++ ) {
    for( var t2=t1+1; t2<nbTeams; t2++ ) {
      gameList.push( [ names[t1], names[t2] ] ); // adjusted
    }
  }

  return gameList;
}

edited May 23 '17 at 10:33

Community

1
1

answered Sep 06 '16 at 15:43

Wim den Herder

1,197
9
13

Same for me, when using a `test` function that calls `ALLGAMES`. But this still does not work when using `ALLGAMES` directly from the spreadsheet, which is what I want. – Silverspur Sep 07 '16 at 09:42
I gave my answer an update, and now it works also directly from the spreadsheet. – Wim den Herder Sep 07 '16 at 11:06
Arf, indeed. I assumed the `names` array was unidimensional... My bad. – Silverspur Sep 07 '16 at 12:20

score 1 · Answer 2 · answered Sep 07 '16 at 14:10

Short answer

Replace

gameList.push( [ new String(names[t1]), new String(names[t2]) ] );

by

gameList.push( [names[t1][0], names[t2][0]])

Explanation

Range references passed as arguments of custom functions are parsed as 2D arrays, so something like reference[i][j] could be used instead of reference[i]. By the other hand, new String(string) returns an object, but custom functions should return values, otherwise Google Sheets could not display the result properly.

Why this custom Google Spreadsheet function does not work?

2 Answers2

Short answer

Explanation