Sorting data in Google spreadsheet

Question

I'm collecting data with a Google Form, and storing it in a spreadsheet.

I would like to write a script to create separate spreadsheet, sorted by name.

E.g.

I have three users: Daniel, Gilles and Jean Pierre.

I want to create a separate sheet from the main one, listing only the data submitted.

So, if the name is Daniel in row 2, I want to copy all the data in the row to the second sheet

So far:

function tri() 
{
     var ss = SpreadsheetApp.getActiveSpreadsheet();
     var data = ss.getDataRange().getValues();
     var sheet2 = ss.getSheets()[1]; 

     for (var t=1; t<data.length; t++)
     {   
         if (data ='Daniel')
         {   
             sheet2.appendRow([data]);    
         }
     }  
}

Mogsdad Mogsdad · Accepted Answer · 2013-07-30T18:00:33

No need for code, you can do this in the spreadsheet. The solution I'd prefer would be to create named tabs for each of your users, put their name in cell A1 of their sheet, then use the spreadsheet QUERY function to populate their sheet. Something like this in cell A3, say:

=query('Form Responses'!A1:Z,"select * where B = '" &A1 & "'")

Adjust the source to match your reality ('Form Responses'!A1:Z).

But if you must use script, try this:

function tri() 
{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Form Responses");
  var data = sheet.getDataRange().getValues();
  var users = ['Daniel', 'Gilles', 'Jean Pierre'];

  for (var user=0; user<users.length; user++) {
    // Open sheet with user's name; create if needed
    var dest = ss.getSheetByName(users[user]);
    if (!dest) dest = ss.insertSheet(users[user], user+1);

    var newData = [];
    newData.push(data[0]); // copy headers
    for (var row=1; row < data.length; row++) {
      // If row is for this user, copy it
      if (data[row][1] === users[user]) newData.push(data[row]);
    }
    // Clear user's sheet, then save collected data
    dest.clearContents();
    dest.getRange(1, 1, newData.length, newData[0].length).setValues(newData);
  }
}

Sorting data in Google spreadsheet

2 Answers