Silverspur Silverspur - 3 months ago 6
Javascript Question

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:


  • 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:
Google Spreadsheet call to custom function ALLGAMES

Answer

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