google-apps-script - Google Scripts Concatenate Cells in different columns

Question

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

标签： google-apps-scriptgoogle-sheetsgoogle-sheets-formula

Answer 1

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))

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);
}