0

I have a spreadsheet in Google Sheets, where I am currently running a script.

There are two tabs, the first tab is the following:enter image description here

The second tab is the following: enter image description here

This is the script I am running:

function result(range) {
  var output2 = [];
  for(var i=0, iLen=range.length; i<iLen; i++) {
    var s = range[i][6].split(", ");
    var t = range[i][7].split(", ");
    var u = range[i][8].split(", ");
    
    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 == 6) 
        {
          output1.push(s[j]);
        }
        else if(k == 7)
        {
          output1.push(t[j]);
        }
        else if(k == 8)
        {
          output1.push(u[j]);
        }
        else 
        {
          output1.push(range[i][k]);
        }
      }
      output2.push(output1);
    }    
  }
  return output2;
}

In the second tab on the spreadsheet, in cell A1 I have the following function:

=result(Data!A1:AA)

What I am trying to get out of the script is that the names listed in Columns G-I (in the first tab) split where there is a comma and all the surrounding data is kept the same.

As you can see in the second tab it only splits the names in column G, but column H & I it only keeps the first attendee.

Is there a way to modify the script to get it to split all attendees in columns G-I?

7
  • You asked this same question a few days ago - How to split cells in multiple columns in Google Sheets, but keep surrounding data?. Why are you re-submitting the question?
    – Tedinoz
    Jun 24, 2020 at 11:19
  • @Tedinoz The script is different from the one that I used in that question!!! Therefore, I am not re-submitting the question........
    – Clarissa
    Jun 24, 2020 at 15:13
  • Have you considered a query instead of a function?
    – Tedinoz
    Jun 25, 2020 at 8:32
  • @Tedinoz I have not..how would I go about doing that?
    – Clarissa
    Jun 25, 2020 at 16:06
  • A query for this example is possible, I think, though not as straightforward as the function. There are users (@player0 springs to mind) who seem to be able to knock out complex queries faster than I can code the same thing in a function. But I was just interested to know whether you had made a conscious choice to go with the function vs query. If I can help with the function, then I'll have a shot at a query to show the alternative.
    – Tedinoz
    Jun 25, 2020 at 23:13

2 Answers 2

0

Query-based

Again, there are many ways to answer your question. Please consider this as one option for a solution.

This answer is NOT perfect but I am including it because it opens up the scope to look at options for displaying your data.

Props to @player0 for the answer to Comma separated list into matched columns pairings and infoinspired Split to Column and Categorize – Google Sheets Formula which gave the clue about adding further columns into the query.

I wrote three queries:

Restaurant Attendees =Query(ArrayFormula(TRIM(split(transpose(split(textjoin("|",1,if(len(G1:G),REGEXREPLACE(G1:G&",",",","|"&TEXT(A1:A,"dd/mm/yyyy hh:mm:ss")&"|"&TEXT(B1:B,"dd/mm/yyyy")&"|"&C1:C&"|"&D1:D&"|"&E1:E&"|"&F1:F&"|"&H1:H&"|"&I1:I&","),)),",")),"|"))),"Select Col2,Col3, Col4,Col5,Col6,Col7,Col1,Col8,Col9",0)


Restaurant query


School Attendees =Query(ArrayFormula(TRIM(split(transpose(split(textjoin("|",1,if(len(H1:H),REGEXREPLACE(H1:H&",",",","|"&TEXT(A1:A,"dd/mm/yyyy hh:mm:ss")&"|"&TEXT(B1:B,"dd/mm/yyyy")&"|"&C1:C&"|"&D1:D&"|"&E1:E&"|"&F1:F&"|"&G1:G&"|"&I1:I&","),)),",")),"|"))),"Select Col2,Col3, Col4,Col5,Col6,Col7,Col8, Col1, Col9 ")


School Attendees


Park Attendees =Query(ArrayFormula(TRIM(split(transpose(split(textjoin("|",1,if(len(I1:I),REGEXREPLACE(I1:I&",",",","|"&TEXT(A1:A,"dd/mm/yyyy hh:mm:ss")&"|"&TEXT(B1:B,"dd/mm/yyyy")&"|"&C1:C&"|"&D1:D&"|"&E1:E&"|"&F1:F&"|"&G1:G&"|"&H1:H&","),)),",")),"|"))),"Select Col2,Col3, Col4,Col5,Col6,Col7,Col8,Col9,Col1")


Parks


Combined

I combined the queries by wrapping them in curly brackets}

={Query(ArrayFormula(TRIM(split(transpose(split(textjoin("|",1,if(len(I1:I),REGEXREPLACE(I1:I&",",",","|"&TEXT(A1:A,"dd/mm/yyyy hh:mm:ss")&"|"&TEXT(B1:B,"dd/mm/yyyy")&"|"&C1:C&"|"&D1:D&"|"&E1:E&"|"&F1:F&"|"&G1:G&"|"&H1:H&","),)),",")),"|"))),"Select Col2,Col3, Col4,Col5,Col6,Col7,Col8,Col9,Col1");Query(ArrayFormula(TRIM(split(transpose(split(textjoin("|",1,if(len(G1:G),REGEXREPLACE(G1:G&",",",","|"&TEXT(A1:A,"dd/mm/yyyy hh:mm:ss")&"|"&TEXT(B1:B,"dd/mm/yyyy")&"|"&C1:C&"|"&D1:D&"|"&E1:E&"|"&F1:F&"|"&H1:H&"|"&I1:I&","),)),",")),"|"))),"Select Col2,Col3, Col4,Col5,Col6,Col7,Col1,Col8,Col9",0);Query(ArrayFormula(TRIM(split(transpose(split(textjoin("|",1,if(len(H1:H),REGEXREPLACE(H1:H&",",",","|"&TEXT(A1:A,"dd/mm/yyyy hh:mm:ss")&"|"&TEXT(B1:B,"dd/mm/yyyy")&"|"&C1:C&"|"&D1:D&"|"&E1:E&"|"&F1:F&"|"&G1:G&"|"&I1:I&","),)),",")),"|"))),"Select Col2,Col3, Col4,Col5,Col6,Col7,Col8, Col1, Col9 ",0)}


Combined three queries

You'll notice that the column headers are repeated. I do know how to remove column headers, but nothing that I tried in this case worked, so I left them in place as something for you to research/troubleshoot. ;-)


Edit 8 Sept 2020

This revision includes the "edit Url" column, and also takes out the duplicate Column Headers.

={Query(ArrayFormula(TRIM(split(transpose(split(textjoin("|",1,if(len(G1:G),REGEXREPLACE(G1:G&",",",","|"&TEXT(A1:A,"dd/mm/yyyy hh:mm:ss")&"|"&TEXT(B1:B,"dd/mm/yyyy")&"|"&C1:C&"|"&D1:D&"|"&E1:E&"|"&F1:F&"|"&H1:H&"|"&I1:I&"| "&"| "&"| "&J1:J&","),)),",")),"|"))),"Select Col2,Col3, Col4,Col5,Col6,Col7,Col1,Col8,Col9,Col10 label Col10 'edit Url'",1);Query(ArrayFormula(TRIM(split(transpose(split(textjoin("|",1,if(len(H2:H),REGEXREPLACE(H2:H&",",",","|"&TEXT(A2:A,"dd/mm/yyyy hh:mm:ss")&"|"&TEXT(B2:B,"dd/mm/yyyy")&"|"&C2:C&"|"&D2:D&"|"&E2:E&"|"&F2:F&"|"&G2:G&"|"&I2:I&"| "&"| "&"| "&J2:J&","),)),",")),"|"))),"Select Col2,Col3, Col4,Col5,Col6,Col7,Col8, Col1, Col9,Col10 ");Query(ArrayFormula(TRIM(split(transpose(split(textjoin("|",1,if(len(I2:I),REGEXREPLACE(I2:I&",",",","|"&TEXT(A2:A,"dd/mm/yyyy hh:mm:ss")&"|"&TEXT(B2:B,"dd/mm/yyyy")&"|"&C2:C&"|"&D2:D&"|"&E2:E&"|"&F2:F&"|"&G2:G&"|"&H2:H&"| "&"| "&"| "&J2:J&","),)),",")),"|"))),"Select Col2,Col3, Col4,Col5,Col6,Col7,Col8,Col9,Col1,Col10")}


3
  • Is there a way to still include the edit url, using this query-based formula?
    – Clarissa
    Sep 7, 2020 at 23:42
  • How does this revision suit.?
    – Tedinoz
    Sep 8, 2020 at 11:53
  • Works. Thank you
    – Clarissa
    Sep 10, 2020 at 2:02
0

You want to break out the names of attendees for School, Restaurant and Park Locations. The resulting output should be a row for each attendee.

Your script is intended to run as a custom function. You said that your script splits the names in column G, but column H & I it only keeps the first attendee.

The reason that the names in the other Columns are not being dissected can be are found in these two sets of lines:


jLen=s.length:

  • s is for Column G - the attendees for the Restaurant and this line is part of a loop; but there is no equivalent for the columns H & I

if(k == 6)     

 {
   output1.push(s[j]);
 }
 else
 {
   output1.push(range[i][k]);
  } 

  • this section is a bit more obvious. If k=6 (restaurant), then the split names are added to the row array, but if k<>6, then the UNSPLIT values for Columns H & I are added.

There are many ways to answer your question. Please consider this as one alternative. The answer is somewhat more verbose than yours, but you can fine-tune that at your leisure. The answer is also written as a stand-alone function; again, you can convert it to a custom function.


function wa14339503() {
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var dataname = "ejbData"
  var source = ss.getSheetByName(dataname);
  var outputname = "ejbOutput";
  var output = ss.getSheetByName(outputname);

  // get details of data sheet
  var lRow = source.getLastRow();
  var lCol = source.getLastColumn();
  var datarange = source.getRange(1,1,lRow,lCol);
  var datavalues = datarange.getValues();
  var dataLen = datavalues.length
  // Logger.log("DEBUG: length of data = "+dataLen+", data range = "+datarange.getA1Notation());

  // create temporary array for output
  var output2 = [];

  // loop though the rows
  for (var i=0; i<datavalues.length; i++) {
    
    // row 0 = headers
    if (i===0){
      // create a temporary array for the row data
      var rowarray = [];
      
      // push data values to rowarray
      rowarray.push([datavalues[i][0]]);
      rowarray.push([datavalues[i][1]]);
      rowarray.push([datavalues[i][2]]);
      rowarray.push([datavalues[i][3]]);
      rowarray.push([datavalues[i][4]]);
      rowarray.push([datavalues[i][5]]);
      rowarray.push([datavalues[i][6]]);
      rowarray.push([datavalues[i][7]]);
      rowarray.push([datavalues[i][8]]);            
      rowarray.push([datavalues[i][9]]);      
      
      // push the row data to the output array
      output2.push(rowarray);
      // Logger.log("DEBUG: i="+1+", header row, length of output2 = "+output2.length);
    }
    
    // start testing data rows
    if (i>0){
      
      // reset row data
      var rowarray = [];
      
      // test for location
      if (datavalues[i][5].length>0){
        
        // get location value
        var location = datavalues[i][5];
        
        // assign Attendee column
        if (location==="School"){
          var attendeedCol=7
        }else if (location==="Restaurant") {
          var attendeedCol=6
        } else if (location==="Park") {
          var attendeedCol=8
        }
            
        // Logger.log("DEBUG: the attendees = "+datavalues[i][attendeedCol])
        
        // split the attendees
        var locsplit = datavalues[i][attendeedCol].split(", ");
        
        //Logger.log("DEBUG: locsplit start")
        //Logger.log(locsplit); // DEBUG
        //Logger.log("DEBUG: locsplit end")

        // get the number of attendees
        var locLen = locsplit.length;
        // Logger.log("DEBUG: the location = "+location+", and the number of persons = "+locLen);

        // for this data row, loop through the number of attendees to create new rows.
        for (var l=0; l<locLen;l++){
          
          // clear the row array
          var rowarray = [];
          
          // Logger.log("DEBUG: split value = "+locsplit[l]);
          
          // push values to row array
          rowarray.push([datavalues[i][0]]);
          rowarray.push([datavalues[i][1]]);
          rowarray.push([datavalues[i][2]]);
          rowarray.push([datavalues[i][3]]);
          rowarray.push([datavalues[i][4]]);
          rowarray.push([datavalues[i][5]]);
          rowarray.push([datavalues[i][6]]);
          rowarray.push([datavalues[i][7]]);
          rowarray.push([datavalues[i][8]]);            
          rowarray.push([datavalues[i][9]]);  
          
          // substitute the attendee name for the attendee listlist
          rowarray.splice(attendeedCol, 1, locsplit[l]);
                   
          // push the new row onto the array
          output2.push(rowarray);
          
          // rinse and repeat for the number of attendees in the split
        }
        
      }
      
    }
    
  }
  // Logger.log(output2); DEBUG
  // Logger.log("DEBUG: length of output2 = "+output2.length);
  
  // create a range on the output sheet
  // set the values with the temporary array
  output.getRange(1,1,output2.length,lCol).setValues(output2)
  
}

Before

Before


After

After

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.