Copy submitted form data to new automatically generated Google sheet

Question

I have created a new sheet for every new name entered in the form using the following code.

function formSheet(e) {
  var sheetName = e.namedValues['Name'][0];
  SpreadsheetApp.getActiveSpreadsheet().insertSheet(sheetName);
}

You can find the details regarding the code in Auto-create and name new sheet in Google sheets from names being filled from a google form

Can I copy data while I make new sheet? I want to copy respective name data to its respective sheet as well. There can be multiple entries with the same name as well which should also be copied.

Answer 1

I have expanded the functionality of the script from the linked question accordingly. First it checks whether the sheet with given name already exists. If it doesn't, the sheet is created and the headers of the form responses sheet are copied there. Otherwise, the existing sheet it used. Either way, the newly submitted data is then copied to the sheet.

function copyData(e) {
  var sheetName = e.namedValues['Name'][0];
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(sheetName);
  if (!sheet) {
    sheet = ss.insertSheet(sheetName);
    var formSheet = e.range.getSheet();
    var headers = formSheet.getRange(1, 1, 1, formSheet.getLastColumn());
    headers.copyTo(sheet.getRange(1, 1));
  }
  sheet.appendRow(e.values);
}

The function must be equipped with a trigger using "Resources -> current project's triggers" in the Script Editor menu: the trigger options are "from spreadsheet", "on form submit".