How do I automatically take data from a Google form and generate a new spreadsheet from each respons-CodePudding

So basically I want to take the form response data in "Form Responses 1" and then feed the data into "John Doe Response". So every google form response would:

A) Generate a new sheet

B) Be automatically formatted into the format in the John Doe Sheet

C) Automatically name the tabs (ie John Doe Response) with the students name from question 1.

Here is my Google Form: https://docs.google.com/spreadsheets/d/11NW0OFf_jWLelKjw4i-F-holjCInZSluIeZCr0v9aZk/edit?usp=sharing

Unsure if this is possible but if so, that would be incredible! We are a school and definitely need the help on this.

Here is the code I am trying to work with, it errors out with: TypeError: Cannot read properties of undefined (reading 'values'). I think i need to substitute in the names of the spreadsheets? Please be explicit, I have little programming knowledge..

function onFormSubmit(e) {
  const [timestamp, name, homephone, cellphone, counselor, email, hobbies, activity, transport] = e.values;
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const template = ss.getSheetByName('Template');
  const sheetname = `${name} Response`;
  let sheet = ss.getSheetByName(sheetname);

  if (!sheet) {
    sheet = template.copyTo(ss).setName(sheetname);
  }

  sheet.getRange('B1:B5').setValues([
    [name],
    [homephone],
    [cellphone],
    [email],
    [counselor],
  ]);

  sheet.getRange('A7').setValue(hobbies);
  sheet.getRange('A11').setValue(activity);

  if (transport === 'Yes') {
    sheet.getRange('C14').check();
  }
}

CodePudding user response：

TypeError: Cannot read properties of undefined (reading 'values')

The code you show seems fine. It is designed to run on an installable trigger. If you run the code in the script editor, the code will error out, because the event object e will be undefined in that context.

TypeError: Cannot read properties of null (reading 'copyTo')

The second error you have received, but did not mention in the question, comes because there is no sheet named 'Template' in the spreadsheet.

To make your code work, first create a template sheet whose name matches that specified in the code. Then create a trigger from left-hand pane in the spreadsheet's script editor. Finally, submit a new response to make the code run. To view any errors, open the project's execution log.

I need to point out that it is usually a bad idea to scatter your data to many sheets. It is almost always better to keep all the data in one sheet, and use filter views to sort rows and choose which rows are displayed at a given time. This approach lets you and other users only view and edit one type of data at a time, without disturbing others. You can view all the data easily, and let others focus on just the data that is relevant to them, while keeping just one master copy of everything.

For additional ease of use, you can insert links in the frozen section of the sheet to easily switch between filter views, instead of having to go to Data > Filter views to switch. See the Filter views example spreadsheet for an illustration.