I have this google apps script that's supposed to act as a custom function, meaning you invoke it by typing = and then the name of the function in a cell of a sheet, and then provide an input value that's passed into the function. You then use return in the script to send the output back to the cell.

The code below is a proof of concept. It simply takes an array of email addresses and loops through it, pushing any addresses that do not contain a given substring (in this case "yahoo.com") back to an array array, which is then returned. This script works as expected.

function test_no_sheet()
{
 var message_objs = ['[email protected]','[email protected]','[email protected]','[email protected]','[email protected]','[email protected]','[email protected]']; 
 var array = [];
for (var i = 0; i < message_objs.length; i  ) {
  if (!message_objs[i].includes("yahoo.com")){array.push(message_objs[i])} else {continue}
    console.log(message_objs[i]);
    console.log("test"   array)
    //Do something
    
}
return array
}

The problem arrises when I attempt to use an array contained within a cell as input. The sheet function adaptation of the proof script is simply changed to pass the input parameter to the message_objs.

function test_yes_sheet(input)
{
 var message_objs = input
 var array = [];
for (var i = 0; i < message_objs.length; i  ) {
  if (!message_objs[i].includes("yahoo.com")){array.push(message_objs[i])} else {continue}
    console.log(message_objs[i]);
    console.log("test"   array)
    //Do something
    
}
return array
}

I've provided a publicly available sheet to demonstrate the problem (I believe you can access the underlying google apps script as well under extensions). Basically when the sheet returns the array, it posts this godawful list of garbage to the target cell, where it clearly simply takes the input array, separates every single character, and posts each character to a new row in the sheet. I expect it to just post the loop output array to the target cell. Any ideas what is going on?

CodePudding user response：

In your script of test_yes_sheet(input) using =test_yes_sheet(A1), the value of input is given to message_objs as the string value. On the other hand, your test_no_sheet(), I thought that message_objs is an array. I thought that this is the reason of your issue. When you want to use test_yes_sheet(input), how about the following modification?

From:

var message_objs = input

To:

var message_objs = input.replace(/'/g, "").split(",").map(e => e.trim());

• By this modification, the string value of input is converted to an array and put to message_objs.

Note:

• By the way, in your script, I thought that the following modification can be also used using filter. Ref

    function sample(input) {
      var message_objs = input.replace(/'/g, "").split(",").map(e => e.trim());
      var array = message_objs.filter(e => !e.includes("ampion.net"));
      return array;
    }
  

References:

• split()
• map()