Google sheets split multi-line cell into new rows (+ duplicate surrounding row entries)

Question

I have a website for summer camp. Every registration automatically ends up in a google sheet. I would like to split the registrations into multiple rows.

The Google spreadsheet I've inherited for this project includes a column C - H, with multiple children info listed in the same cell, separated by line breaks (ie CHAR(10) ). There is one registration per row. The number of lines in (Column C to H) a cell varies row-by-row. My initial sheet looks like this:

I need to do the following to optimize this sheet:

Split each multi-line cell so each child appears on its own row. This requires that new row/s be inserted beneath the original row. duplicate the data from all other cells on the original row (i.e. from columns A & J:V), so that each new row contains the full data for a child. I need an automated process - I'll have about 3000 registrations to process so can't do this with any manual steps.

The sheet should then look like this:

I've tried using this custom script in google sheets but it didn't work:

function result(range) {
  var splitCol = 1; // split on column B
  var output2 = [];
  for(var i=0, iLen=range.length; i<iLen; i++) {
    var s = range[i][splitCol].split("\n");    
    for(var j=0, jLen=s.length; j<jLen; j++) {
      var output1 = []; 
      for(var k=0, kLen=range[0].length; k<kLen; k++) {
        if(k == splitCol) {
          output1.push(s[j]);
        } else {
          output1.push(range[i][k]);
        }
      }
      output2.push(output1);
    }    
  }
  return output2;
}

All help is welcome! Thanks.

You need to be more descriptive than "doesn't work"

TheMaster
– TheMaster

2021-12-07 12:47:33 +00:00
Commented Dec 7, 2021 at 12:47 — TheMaster
– TheMaster, Commented Dec 7, 2021 at 12:47

doubleunary · Accepted Answer · 2022-08-30 07:51:17Z

4

Try the below custom function. It loops through rows one by one and inserts as many rows as there are delimiter-separated values in the cell in the row that has the most such delimited values.

The function duplicates rows so that each split value is in its own row. Columns that do not contain the separator character are filled down using the value in the original row.

/**
* Expands data by splitting text strings that contain a separator character.
*
* Duplicates rows so that each split value is in its own row.
* Columns that do not contain the separator character are filled down
* using the value in the original row.
*
* @param {A2:D42} data The rows to expand.
* @param {char(10)} separator The character to split by.
* @customfunction
*/
function ExpandSplit(data, separator = '\n') {
  // version 1.1, written by --Hyde, 7 December 2021
  //  - support multiple split columns
  //  - see https://stackoverflow.com/q/70258670/13045193
  if (!Array.isArray(data)) {
    throw new Error('ExpandSplit expected data to be a range or multiple rows.');
  }
  let expanded = [];
  data.forEach(row => {
    const numDupRows = Math.max(1, ...row.map(column => typeof column === 'string' && column.split(separator).length));
    const duplicates = [];
    row.forEach((column, columnIndex) => {
      const splitString = String(column).split(separator);
      for (let dupRow = 0; dupRow < numDupRows; dupRow++) {
        if (!duplicates[dupRow]) {
          duplicates[dupRow] = [];
        }
        if (typeof column === 'string' && splitString.length > 1) {
          duplicates[dupRow][columnIndex] = splitString[dupRow] || null;
        } else {
          duplicates[dupRow][columnIndex] = column;
        }
      }
    });
    expanded = expanded.concat(duplicates);
  });
  return expanded;
}

To use the function, call it from a spreadsheet formula, like this:

=ExpandSplit(A2:D42, char(10))

edited Aug 30, 2022 at 7:51

answered Dec 7, 2021 at 12:48

doubleunary

21.9k13 gold badges38 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

serge Over a year ago

could you please provide an example how to use it?

doubleunary Over a year ago

Edited the answer to add a usage example.

Emel · Accepted Answer · 2021-12-10 09:27:28Z

You could use this code as a guide. It simply takes a given range and subdivides it into as many rows as it finds \n separators.

const splitData = () => {
  let ss = SpreadsheetApp.openById(SS_ID).getSheets()[0]
  let rangeToSplit = ss.getRange('C2:H2')
  let values = rangeToSplit.getValues()[0]
  for(let i = 0 ; i < values.length; i++){
    var cellValues = values[i].split('\n')
    for ( let j = 0 ; j < cellValues.length ; j++) {
      // 2+j refers to the rows 2,3,..
      // 3+i refers to columns C,D,...
      ss.getRange(2+j, 3+i).setValue(cellValues[j])
    }
  }
}

If you want to accommodate this code to copy that cells that does not containing a line break, you should try adding some kind of control mechanism like:

var content = ss.getRange(2 + j, 1 + i).getValue()
    if (content.split('\n').length != 1) {
      ss.getRange(2 + j, 3 + i).setValue(cellValues[j])
}

UPDATED 10/12/2021

Following @doubleunary's suggestion, I have decided to test batch operations.

/**
 * A workaround using batch operations
 * @param {Array} resulting of the getRange().getValues()
 * @returns {Array} filtered by \n
 */
const batchUpdate = (data) => {
  var result = []
  for (var i = 0; i < data.length; i++) {
    var check  = data[i].some(element=>element.includes('\n'))
    if (check) {
      var copyArr = data[i].slice()
      /// temp the cells to split
      var tempC = data[i][2].split('\n')
      var tempD = data[i][3].split('\n')
      var tempE = data[i][4].split('\n')
      var tempF = data[i][5].split('\n')
      var tempG = data[i][6].split('\n')
      var tempH = data[i][7].split('\n')
      for (var j = 0; j < tempC.length; j++) {
        copyArr[2] = tempC[j]
        copyArr[3] = tempD[j]
        copyArr[4] = tempE[j]
        copyArr[5] = tempF[j]
        copyArr[6] = tempG[j]
        copyArr[7] = tempH[j]
        result.push([data[i][0],data[i][1],temp[j],data[i][3],data[i][4]])
      }
    } else {
      result.push(data[i])
    }
  }
  return result
}

const testingBatch = () => {
  // 
  let rangeValues = ss.getRange('A2:J300').getValues()
  var newValues = batchUpdate(rangeValues)
  console.log(newValues)
  ss.getRange(2,1, newValues.length, newValues[0].length).setValues(newValues)
}

Documentation:

getRange(row, column)

Hmm... the OP specified "3000 registrations", so this code will take a long time to run because it gets and sets values one cell at a time. See Apps Script best practices.

Collectives™ on Stack Overflow

Google sheets split multi-line cell into new rows (+ duplicate surrounding row entries)

2 Answers 2

2 Comments

UPDATED 10/12/2021

Documentation:

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

UPDATED 10/12/2021

Documentation:

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related