Home > Blockchain >  How to create a scrip to split and rejoin words in Gsheets
How to create a scrip to split and rejoin words in Gsheets

Time:02-17

I am trying to write an app script in Gsheets 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(", ");
}
  • Related