CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Silverspur
Silverspur

Reputation: 923

Why this custom Google Spreadsheet function does not work?

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:

Where is the problem?

Edit 1:

Here is the call to ALLGAMES I make to test the function: Google Spreadsheet call to custom function ALLGAMES

Upvotes: 0

Views: 80

Answers (2)

Wicket
Wicket

Reputation: 38425

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.

Upvotes: 1

Wim den Herder
Wim den Herder

Reputation: 1305

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;
}

Upvotes: 2

Related Questions