Google Scripts Concatenate Cells in different columns

user_14517039 0
Viewed 543

I have the following scenario where I want to concatenate cells in different columns but the quantity of columns differs from case to case.

example1: In cell A2 there is the value of 70286 & and the cell B2 there is the value of playmobil. The end result in F2 should be +70286 +playmobil

example2: In cell A4 there is the value of 70666, in the cell B4 there is the value of lego and in the cell C4 there is the value of ninjago. The end result in F2 should be +70666 +lego +ninjago

example

google-sheets-formula google-apps-script google-sheets
1 Answers

Solutions:

Google Sheet Formula:

You can use TEXTJOIN and if you want a plus sign in front of the string you can use CONCAT to add it.

Use this formula in column F:

=CONCAT("+",textjoin(" +", 1, A2:E2))

example

If you don't know how many columns you want to use, then you can select the full row:

=CONCAT("+",textjoin(" +", 1, A2:2))

Google Apps Script:

  • We get the data of the second row as a 2D array using getValues().
  • Then we apply flat() to convert it to 1D.
  • The next step is to remove the empty cells by using filter().
  • Finally, we use reduce() to get the final string as the sum of all strings.

The logic can be easily adjusted. You can do this logic for multiple cells etc.

Code snippet:

function myFunction() {
  const sh = SpreadsheetApp.getActive().getSheetByName('Sheet1');
  const row = 2;
  const data=sh.getRange(row,1,1,sh.getMaxColumns()).
                   getValues().
                   flat().
                   filter(c=>c!='').
                   reduce( (a, b) => `${a} +${b}`);      
  const result = `+${data}`;
  sh.getRange('F1').setValue(result);
  Logger.log(result);
}
user_112252910
Related