Custom function fails when passing range into it

Question

Trying to extract the first word from each cell in a range passed into the custom function. However, getting an error when using the function in the test spreadsheet.

/**
* @customfunction
*/
function test(alt_activities_range)
{
  var ss = SpreadsheetApp.getActive().getActiveCell();
  var sheet = SpreadsheetApp.getActive().getActiveSheet();
  var alt_activities_array = alt_activities_range[0]; 

  var alt_activities_array = alt_activities_range.map(str => {
    let arr = str.split(' ');
    return arr[0];
  });

  console.log(alt_activities_array);
  
}

The following error is produced: TypeError: str.split is not a function.

Any help is greatly appreciated.

Answer 1

When I saw your provided Spreadsheet, it seems that the values of "B1:I1" of =test(B1:I1) are [["Chess - 6","Pilates - 6","Elevate - 6","Kundalini - 6","Scrabble - 6","Gardening - 6","Darning - 6","Crossword - 6"]]. So, in the case of your script, alt_activities_range is [["Chess - 6","Pilates - 6","Elevate - 6","Kundalini - 6","Scrabble - 6","Gardening - 6","Darning - 6","Crossword - 6"]].

In your script, the 1st element is retrieved by var alt_activities_array = alt_activities_range[0]. But, at var alt_activities_array = alt_activities_range.map(str => {, alt_activities_range is used. In this case, it is a 2-dimensional array. By this, str is an array. I guessed that this is the reason for your current issue of TypeError: str.split is not a function..

If you want to remove this issue, please modify it as follows.

From:

var alt_activities_array = alt_activities_range.map(str => {
  let arr = str.split(' ');
  return arr[0];
});

To:

var alt_activities_array = alt_activities_range[0].map(str => { // or alt_activities_array.map(str => {
  let arr = str.split(' ');
  return arr[0];
});

• By this modification, alt_activities_array is ["Chess","Pilates","Elevate","Kundalini","Scrabble","Gardening","Darning","Crossword"].