Guys I have little problem. I have output from other system to google spreadsheet.

I need to make a worksheet so that all data from this cell is divided into separate rows and the number next to it (D4) divided by the number of separate persons (a = first person, b=2nd person etc) (C4).

So far I have this script (other topic from stack ) -> Split comma delimited cell into multiple rows, keeping row details

/** 
 * Splits the array by commas in the column with given index, by given delimiter
 * @param {A2:B20}  range Range reference
 * @param {2}  colToSplit Column index
 * @param {","}  delimiter Character by which to split
 * @customfunction
 */


function advancedSplit(range, colToSplit, delimiter) {
    var resArr = [], row;
    range.forEach(function (r) {
        r[colToSplit-1].toString().split(delimiter)
            .forEach(function (s) {
                
                row = [];
                r.forEach(function (c, k) {               
                    row.push( (k === colToSplit-1) ? s.trim() : c);
                })
                resArr.push(row);
                
            })
     
    })
    
    return resArr.filter(function (r) {
        return r.toString()
        
            
    })

    
}

The problem with dividing a cell by ";" is solved, but not with dividing a cell with a number of persons (phrases)

Sample:

cell A1 contains {a,b,c,d,e,f} and B2 1000

output: a 200 b 200 c 200 d 200 e 200 f 200

This is dynamic data with 1000 rows. An appscript should be used in this case

CodePudding user response：

This is not intended to be a cell function but as a standard function it provides the desired output.

function myfunk() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const osh = ss.getSheetByName('Sheet1');
  const vs = sh.getRange(2,1,2,4).getValues();
  let a = [];
  vs.forEach((r,i) => {
    let c2 = r[2].toString().split(",");
    c2.forEach(e => {
      a.push([r[0],r[1],e,r[3]/c2.length]);
    });
  });
  osh.clearContents();
  osh.getRange(2,1,a.length,a[0].length).setValues(a);
}

input:

output:

Try it this way for more rows and columns

function myfunk() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const osh = ss.getSheetByName('Sheet1');
  const vs = sh.getRange(2,1,sh.getLastRow() - 1,sh.getLastColumn()).getValues();//gets the entire column
  let a = [];
  vs.forEach((r,i) => {
    let c2 = r[2].toString().split(",");
    c2.forEach(e => {
      let row = r.slice();//keeps each element of the output array independent
      row.splice(2,2,e,row[3]/c2.length);//replace elements 2 and 3
      a.push(row);
    });
  });
  osh.clearContents();
  osh.getRange(2,1,a.length,a[0].length).setValues(a);
}