CODEWITHSUNDEEP
javascriptgoogle-apps-scriptsplit
xyz333
xyz333

Reputation: 768

Split strings in multiple columns into multiple rows with a Google Script/JavaScript

I have multiple columns filled with semicolon delimited strings.

I need to split the strings on these columns into rows. The columns to be split in my example data are

B, C, D

In my actual data, these are columns.

X, Y, AA, AB with a last column of AZ

The column header are the actual column headers.

I can do this with one column but not with multiple columns.

Thank you for your assistance

A Google sheet with data

https://docs.google.com/spreadsheets/d/1ZTVydSDpQegbj7qZElrJKeaIeWT5Wwna62NDPkJ_ZGs/edit?usp=sharing

Have

ID Authors Authors Raw Affiliation Corresponding Authors Authors Affiliations GRID IDs
1 AAA; BBB; CCC Stuff1; Stuff2; Stuf3 oStuff1; oStuff2; oStuf3 AAA, (University of Oxford); BBB (University of Bonn); CCC (University of Zurich) QQQ1
2 RRR; GGG Stuff4; Stuff5 oStuff4;oStuff5 RRR, (University of Oxford); GGG(University of Bonn)
3 MMM Stuff6 oStuff6 MMM, (University of Cambridge) QQQ3

Need

ID Authors Authors Raw Affiliation Corresponding Authors Authors Affiliations GRID IDs
1 AAA Stuff1 oStuff1 AAA, (University of Oxford) QQQ1
1 BBB Stuff2 oStuff2 BBB (University of Bonn) QQQ1
1 CCC Stuff3 oStuff3 CCC (University of Zurich) QQQ1
2 RRR Stuff4 oStuff4 RRR, (University of Oxford)
2 GGG Stuff5 oStuff5 GGG(University of Bonn)
3 MMM Stuff6 oStuff6 MMM, (University of Cambridge) QQQ3

I did find this by @Tanaike, but it has no example data, and I do not understand it well enough to apply it to my data

function sample1() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const srcSheet = ss.getSheetByName("Sheet1"); // Please set the source sheet name.
  const dstSheet = ss.getSheetByName("Sheet2"); // Please set the destination sheet name.
  const values = srcSheet.getDataRange().getValues();
  const res = values.flatMap(([a, b, c, d, e, f, g, ...v]) => {
    const { vv, len } = v.reduce((o, c) => {
      const t = typeof c != "string" ? c.toString().split(",") : c.split(",");
      o.vv.push(t);
      o.len = o.len < t.length ? t.length : o.len;
      return o;
    }, { vv: [], len: 0 });
    const temp = vv.map(e => e.concat(Array(len - e.length).fill("")));
    return temp[0].map((_, i) => [...(i == 0 ? [a, b, c, d] : Array(4).fill("")), e, f, g, ...temp.map(r => isNaN(r[i].trim()) ? r[i].trim() : r[i].trim() && Number(r[i]))]);
  });
  dstSheet.getRange(1, 1, res.length, res[0].length).setValues(res);
}

Upvotes: 0

Views: 107

Answers (2)

doubleunary
doubleunary

Reputation: 18819

To do that with a spreadsheet formula, use query() and flatten(), like this:

=arrayformula( 
  lambda( 
    authors, rawAffiliations, correspondents, authorAffiliations, 
    query( 
      { 
        From!A1:F1; 
        flatten(iferror(authors/0, From!A2:A)), 
        flatten(authors), 
        flatten(rawAffiliations), 
        flatten(correspondents), 
        flatten(authorAffiliations), 
        flatten(iferror(authors/0, From!F2:F)) 
      }, 
      "where Col1 is not null and Col2 is not null", 1 
   ) 
  )( 
    trim(split(From!B2:B, ";")), 
    trim(split(From!C2:C, ";")), 
    trim(split(From!D2:D, ";")), 
    trim(split(From!E2:E, ";")) 
  )
)

See your sample spreadsheet.

Upvotes: 1

TheWizEd
TheWizEd

Reputation: 8606

Even for me the original script proposed is too complicated for my simple brain. Here is an example of how I would do it. Less cryptic.

I fill each cell of each row with an array of values split from the semicolon delimited string. Then loop through those arrays to see how many elements in each row and create a seperate row for each.

function splitColumns() {
  try {
    let spread = SpreadsheetApp.getActiveSpreadsheet();
    let sheet = spread.getSheetByName("Sheet1");
    let values = sheet.getRange(2,1,sheet.getLastRow()-1,sheet.getLastColumn()).getValues();
    let results = [];
    values.forEach( row => {
        let newRow = [];
        let length = 0;
        row.forEach( cell => {
            let newCell = cell.toString().split(";");
            newRow.push(newCell);
            if( newCell.length > length ) length = newCell.length;
          }
        );
        for( let i=0; i<length; i++ ) {
          let row = [];
          newRow.forEach( cell => {
              let j = cell.length > i ? i : cell.length-1;
              row.push(cell[j].trim());
            }
          );
          results.push(row);
        }
      }
    );
    sheet.getRange(sheet.getLastRow()+10,1,results.length,results[0].length).setValues(results);
  }
  catch(err) {
    console.log("Error in splitColumns: "+err)
  }
}

enter image description here

References

Upvotes: 2

Related Questions