Home > Blockchain >  ArrayFormula For Join A Range Column
ArrayFormula For Join A Range Column

Time:10-25

I want to use ArrayFormula for JoinText for multiple columns, From Column A to Column H. I already have Google App Script for it, and it works.

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("DISPOSISI");
const rowNo = sheet.getLastRow();   
const colStaff1         =  1; 
const colStaff8         =  8; 
const colJoinStaff      =  9; 

// =============
// TEXT JOIN : 
// =============

const cellStaff1       =  sheet.getRange(rowNo,colStaff1).getA1Notation();
const cellStaff8       =  sheet.getRange(rowNo,colStaff8).getA1Notation();

sheet.getRange(rowNo,colJoinStaff).setValue(sheet.getRange(rowNo,colJoinStaff).setFormula('TEXTJOIN(", ";TRUE;' cellStaff1 ':' cellStaff8 ')').getValue());`

Every I added one new Row, I want the result will appear with ArrayFormula.

This is my formula :

=arrayformula(if(row(A:A)=1;"JOIN VALUE WITH COMMA";ARRAYFORMULA(IF((A:A)="";"";ARRAYFORMULA(TEXTJOIN(", ";TRUE;(A:A):(H:H)))))))

But it does not work.

enter image description here

or maybe:

={"JOIN VALUE WITH COMMA"; 
 INDEX(REGEXREPLACE(TRIM(FLATTEN(QUERY(TRANSPOSE(
 IF(A2:H="";;A1:H1&": "&A2:H&","));;9^9))); ",$"; ))}
  • Related