5 Ways Google Forms Splits Sections into Excel Sheets

1. Exporting Forms to Excel

Exporting Google Forms data directly to Excel might seem like the simplest solution, but how can you split different sections of your form into separate Excel sheets? Here’s how Google Forms handles this process:

• Responses Collection: Google Forms records responses in its own platform when a user submits a form.
• Data Export: You can export this data into various formats, including Microsoft Excel, through the “Responses” section of your form.
• Manual Split: After exporting, you can manually split different form sections into separate sheets within an Excel workbook.

Here is how you can proceed:

1. Open your Google Form and go to the "Responses" tab.
2. Click on the green Google Sheets icon to export the responses into a new spreadsheet.
3. Use Excel's functionality to split the data:
• Insert new sheets within the same workbook for each section.
• Sort and filter your data, then copy-paste relevant sections into their designated sheets.

2. Using Google Apps Script to Split Sections

For users comfortable with coding, Google Apps Script provides a more automated solution:

• Script Creation: You'll need to write or find a script to automate the process of splitting Google Forms data into separate sheets.
• Triggers Setup: Set up triggers to run the script each time a form is submitted or on a scheduled basis.
• Code Execution: The script will use Google Sheets' API to split the form responses automatically.

The following script snippet shows a basic example of how to achieve this:

function splitFormSections() {
  var form = FormApp.getActiveForm();
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var responses = form.getResponses();
  
  // Iterate through each section and sheet
  var sections = form.getItems().filter(item => item.getType() === 'SECTION_HEADER');
  
  sections.forEach(section => {
    var sheet = sheet.getSheetByName(section.getTitle());
    if (!sheet) sheet = sheet.insertSheet(section.getTitle());
    
    // Filter responses and append to the respective sheet
  });
}

⚠️ Note: This script requires editing to filter responses based on the form's structure and the section names.

3. Utilizing Third-Party Add-Ons

If coding isn’t your forte, third-party add-ons can automate the task for you:

• Form Publisher: Automates splitting Google Forms data into multiple sheets within Excel.
• autoCrat: While primarily for document merging, it can handle form data extraction into separate sheets.
• docAppender: Similar to autoCrat, it integrates with Google Sheets for exporting data into separate sheets.

These tools require little to no coding knowledge and can save you significant time:

4. Creating a Custom Formula

While Google Sheets doesn’t natively support splitting data into different sheets, you can create formulas to split data into columns, and then manually copy those columns into new sheets:

• IFS Formula: Use a formula like =IFS(B2=“Section 1”, B2, B2=“Section 2”, C2, B2=“Section 3”, D2) to categorize data into columns.
• Filter & Copy: Filter the data to show only responses from each section, then copy those columns into their respective sheets.

5. Manually Split Sections with Excel

The simplest approach for small or infrequent use is manual splitting in Excel:

• Sort and Filter: Use Excel’s sorting and filtering tools to separate different sections.
• Copy-Paste: Manually copy each section’s data into a new sheet.
• Save: Save your split workbook as desired, either as an Excel file or back to Google Sheets.

In the grand scheme of organizing form data, Google Forms doesn't inherently split responses into separate Excel sheets, but with various methods, this can be achieved. Each approach has its advantages, tailored to different levels of technical expertise and workflow requirements. The beauty of Google Forms is its flexibility, allowing users from beginners to advanced developers to manage and organize their data according to their needs.

Can I split Google Forms responses into separate Excel sheets without using code?

+

Yes, while Google Forms doesn’t have a native feature for this, you can use third-party add-ons or manually split the data in Excel.

Is it possible to automate the process of splitting form responses?

+

Yes, through Google Apps Script or third-party add-ons, you can set up triggers to split responses automatically as they are submitted.

What if my form changes? Will these methods still work?

+

If your form structure changes, you might need to update your scripts or add-ons to reflect the new sections, or manually adjust your sorting and copying methods in Excel.