How to Distribute Google Form Responses to Multiple Spreadsheets

Google Forms is one of the most popular tools for collecting online data. Its seamless integration with other Google apps, especially Google Sheets, makes it a standout choice among form builders.

By default, each form you create links to just one Google Sheet. However, challenges arise when you need to share form responses with multiple people without granting access to the primary spreadsheet.

This situation is common in workplaces, where multiple divisions might need to access the same form but require separate spreadsheets. Sharing separate sheets ensures data accuracy and restricts access to sensitive information in the main spreadsheet.

If you’re looking to set up a form that distributes responses to multiple spreadsheets, here’s an enthusiastic, step-by-step guide:

1. Add the Script Editor

Click the three-dot menu in the top-right corner of your form, then select "Script Editor".

2. Insert the Following Code

Once the Script Editor opens, paste this code:

function onFormSubmit(e) {
  const itemResponses = e.response.getItemResponses();
  var responses = e.response.getItemResponses(); // Get the submitted responses
  
  // Construct an array of response values
  var formResponse = responses.map(function(response) {
    return response.getResponse();
  });

  // Get the timestamp of the form submission
  var timestamp = e.response.getTimestamp();
  
  // Add timestamp to the beginning of the formResponse array
  formResponse.unshift(timestamp);
  
  const pertanyaan_kedua = itemResponses[1].getResponse();
  // var main_spreadsheet = SpreadsheetApp.openById('SPREADSHEETID');
  // var main_sheet = main_spreadsheet.getActiveSheet();
  var second_spreadsheet = SpreadsheetApp.openById('SPREADSHEETID');
  var second_sheet = second_spreadsheet.getActiveSheet();

  if (pertanyaan_kedua === 'Opsi 1') {
    second_sheet.appendRow(formResponse);
  }
  // else {
  //   main_sheet.appendRow(formResponse);
  // }
}

Step 3: Create a New Spreadsheet and Share Access

Create a separate Google Spreadsheet and ensure the necessary team members have Editor access.

Step 4: Update the Spreadsheet ID in the Code

To find your spreadsheet’s ID, open it and copy the part of the URL between /d/ and /edit.

Step 5: Set Up a Trigger

Next, configure a trigger to determine when the script will run. This step ensures your script executes every time a new form submission is received.

Step 6: Adjust the Trigger Settings

Customize the trigger to match your needs, such as running the script on every form submission.

Step 7: Test the Setup

Now for the fun part—testing! Fill out and submit your form. Then, check the linked spreadsheets to confirm that the responses are correctly distributed.

That’s It!

And there you have it—a simple way to distribute Google Form responses to multiple spreadsheets. This setup is perfect for collaborative environments, ensuring data is shared efficiently while maintaining control over sensitive information.

Good luck, and happy form building!