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.
or maybe:
={"JOIN VALUE WITH COMMA";
INDEX(REGEXREPLACE(TRIM(FLATTEN(QUERY(TRANSPOSE(
IF(A2:H="";;A1:H1&": "&A2:H&","));;9^9))); ",$"; ))}