Combine Range As CSV

Viewed 57

How Can I combine Range in a Cell with Comma Separate Value (CSV) without write one by one Cell Name ? I Use Concatenate formula, but it does not work because I can't add Comma to separate every Cell Value. Anyone can help how to make formula with just write Start colom and Finish colom ? Is this the sample, start colom was 'P' and finish colom was 'AC'.

  • My range ('P'+rowNo+':AC'+rowNo) -.

Script sheet.getRange(rowNo,colRekapSiswa).setFormula('P'+rowNo+'&","&Q'+rowNo+'&","&R'+rowNo+'&","&S'+rowNo+'&","&T'+rowNo+'&","&U'+rowNo+'&","&V'+rowNo+'&","&W'+rowNo+'&","&X'+rowNo+'&","&Y'+rowNo+'&","&Z'+rowNo+'&","&AA'+rowNo+'&","&AB'+rowNo+'&","&AC'+rowNo);

2 Answers

Not sure if I understand your goal. So here is a guess:

function to_csv(range) { 
  return range.join(',');
}

enter image description here

Which is actually the same as the native formula: =TEXTJOIN(",",TRUE,A1:C1)

Or:

function the_weird_way_to_set_a_formula() { 
  var row = 10; 
  var formula = 'TEXTJOIN(",",TRUE,' +
    [...'PQRSTUVWXYZ'.split(''), ...'ABC'.split('').map(letter => 'A' + letter)]
    .map(letter => letter + row).join(',') + ')';

  SpreadsheetApp.getActiveSheet().getRange('a1').setFormula(formula);
}

enter image description here

I'm not sure I understand the question but I'll give it a shot

function combineRangeValuesIntoOneCell() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const vs = sh.getRange(1,16,sh.getLastRow(),11).getValues();
  let o = vs.reduce((a,c,i) => {
    c.forEach(e => {
      a += e;
    });
    return a;
  },0);
  Logger.log(o);
}
A B C D E F G H I J K L M N O P Q R S T U V W X Y Z
1 2 3 4 5 6 7 8 9 10 11
2 3 4 5 6 7 8 9 10 11 12
3 4 5 6 7 8 9 10 11 12 13
4 5 6 7 8 9 10 11 12 13 14
5 6 7 8 9 10 11 12 13 14 15
6 7 8 9 10 11 12 13 14 15 16
7 8 9 10 11 12 13 14 15 16 17
8 9 10 11 12 13 14 15 16 17 18
9 10 11 12 13 14 15 16 17 18 19
10 11 12 13 14 15 16 17 18 19 20
Execution log
8:26:42 AM  Notice  Execution started
8:26:43 AM  Info    1155.0
8:26:44 AM  Notice  Execution completed
Related