How to Directly Store Specific Data from Google Form Responses to a Second Sheet in Google Sheets?

Question

I have a Google Form that collects responses, and it's linked to a Google Sheets document where these responses are stored. I'd like to set up a second sheet within the same document to store specific data from these form responses for easier analysis. What's the best way to achieve this?

I've considered using formulas in the second sheet to reference data from the Form Responses sheet. For instance, using ='Form Responses 1'!A2 to pull data from the first row and first column. However, I'm not sure if this is the most efficient approach, especially if I want to store multiple pieces of specific data.

Suppose that we have three input fields in a google form, and I want to store the data of the first and second fields in "Form Responses 1" and store the data of the last field in "Sheet 2" of a google sheet.

Is there a more effective way to directly store specific data from the form responses in the second sheet? Additionally, could Google Apps Script be utilized to automate this process and potentially improve the data manipulation between the sheets?

Any guidance or examples would be greatly appreciated. Thank you in advance!

Answer 1

function onFormSubmit(e) {
  //posting form responses to another sheet
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  sh.getRange(sh.getLastRow() + 1, 1, 1, e.values.length).setValues([e.values]);
}