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.
CodePudding user response：
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;
}
CodePudding user response：
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])
}