How to create a script to split and rejoin words in Google Sheets?-CodePudding

I am trying to write an Apps Script script in Google Sheets to split a cell containing words and re concatenate them in a different format. There may be any number of words in the input cell.

Input in cell A1: cat, dog, mouse, rat

Output in cell B2 : "CAT", "DOG", "MOUSE", "RAT"

I cannot get the for loop correct

function tags(data) {
  array1 = data.split(", ");
  a =[]
  for (i=1; i<21; i  ){
    a = a   char(34)   i   char(34)
    return a
  }
}

CodePudding user response：

In your script, I thought that you are using your script as a custom function. If my understanding is correct, how about the following modification?

Modified script 1:

When your script is modified, how about the following modification?

function tags(data) {
  var array1 = data.split(", ");
  var a = []
  for (var i = 0; i < array1.length; i  ) {
    a.push(String.fromCharCode(34)   array1[i].toUpperCase()   String.fromCharCode(34));
  }
  return a.join(", ");
}

Modification points:

char(34) cannot be directly used.
array1 is not used in your script.
In your script, the for loop is stopped at the 1st loop by return a.

Modified script 2:

In order to achieve your goal, you can also the following modification.

function tags(data) {
  if (!Array.isArray(data)) data = [[data]];
  return data.map(r => r.map(c => c.split(",").map(e => `"${e.trim().toUpperCase()}"`).join(", ")));
}

In this case, for example, you can use the custom function like =tags(A1), =tags(A1:A5) and =tags(A1:D5).

Note:

From your question, I used toUpperCase(). If you don't want to use it, please remove it.

References:

CodePudding user response：

function tags(data) {
  data = data.toUpperCase().split(",");

  for (var i = 0; i < data.length; i  )
    data[i] = ('"'   data[i]   '"');
  
  return data.join(", ");
}