Google-sheets – Forms Data Manipulation In Google Sheets

formulasgoogle sheetsgoogle-formsworksheet-function

I'm trying to develop a solution using Google Forms with Google Sheets that will collect Google Forms data and then automatically divide that one form submission's data into multiple rows of data in another sheet.

I've learned how to QUERY data (thanks to many of you on here) from one sheet to another, but I don't know how to automatically divide 1 row of data into multiple rows.

Here is a YouTube video outlining the problem in a visual manner (I will not delete it).

Basically, I have one Google form that collects the following.

  1. Teacher Information (header data)
  2. Report Name (header data)
  3. Class Information (header data)
  4. Student#1 Data (record data: Multiple Form Fields)
  5. Student#2 Data (record data: Multiple Form Fields)
  6. Student#3 Data (record data: Multiple Form Fields)
  7. Student#4 Data (record data: Multiple Form Fields)
  8. Student#5 Data (record data: Multiple Form Fields)

I need an automated way to convert that single form response (1 row) into multiple rows (5 rows) each with the same 'header data' but with unique record date.

EXAMPLE Form Data

Header Data  -  Record Date (Student #1)  -  Record Date (Student #2)  -  Record Date (Student #3)  -  Record Date (Student #4)  -  Record Date (Student #5)

EXAMPLE Desired Manipulated Data

    Header Data  -  Record Date (Student #1)
    Header Data  -  Record Date (Student #2)
    Header Data  -  Record Date (Student #3)
    Header Data  -  Record Date (Student #4)
    Header Data  -  Record Date (Student #5)

Best Answer

Short answer

  1. Add a new sheet.
  2. Add the following formulas to the new sheet

Cell A2 -> Headers

=OFFSET('Form responses 1'!$A$2,ceiling((row()-1)/5,1)-1,0)

This could be repeated several times, one for each header column, just change the last parameter accordingly.

Cell B2 -> data

=OFFSET('Form responses 1'!$A$2,ceiling((row()-1)/5,1)-1,mod(row()-2,5)*2+1,1,2)

Fill down as necessary

Explanation

  • OFFSET() is used to get the values from the source data.
  • 'Form responses 1'!$A$2 is the start point to count columns and rows.
  • row() is used to increment the row and column iterators.
  • ceiling((row()-1)/5,1), row iterator, is used to set the row from the source data.
  • mod(row()-2,5)*2+1, column iterator, is used to set the column from the source data.
  • In the data formula the last parameter, indicates the number of data columns.

In case that are several "header data" columns, add 1 for each of them. I.E. if there are five "header data" columns, and six "data columns" the column iterator should be

mod(row()-2,5)*6+5

The final data formula to be added to the F2 cell is:

=OFFSET('Form responses 1'!$A$2,ceiling((row()-1)/5,1)-1,mod(row()-2,5)*6+5,1,6)

In the row and column iterators, 5 is used because there are 5 columns for "record data".

Demo

Spreadsheet

Related Topic