Split Google Form response to rows

Question

I have a google form with multiple sections. Section 1 is just a date input.

Section 2 - 13 are exactly the same as each other, they have the following example questions:

Colour
Size
Age
City
Another? [yes/no]

Saying yes on the final question proceeds to the next section, therefor allowing you to capture another "set" of data. Saying no submits the form.

My form submission thus looks something like this in the Responses sheet:

Timestamp	Date	Colour	Size	Age	City	Another	Colour	Size	Age	City	Another	etc..
25/07/2022 21:09:19	21/07/2022	Blue	XL	25	NY	Yes	Red	S	22	TX	Yes

The columns Colour, Size, Age, City and Submit thus repeat in the columns, either with data or blank - depending on how many times Yes was selected at the end of the sections.

What I am trying to do is turn every "set" of Colour, Size, Age and City into a row of it's own on another sheet. So the final sheet would look like this:

Timestamp	Date	Colour	Size	Age	City
25/07/2022 21:09:19	21/07/2022	Blue	XL	25	NY
25/07/2022 21:09:19	21/07/2022	Red	S	22	TX
etc...

The timestamp and date should be at the start of every row, while the respective sets of Colour, Size, Age and City are appended to the bottom of this new Data sheet with every form submission.

I would also want to avoid blank rows, so only append to the Data sheet if there are values in the sets of responses.

Any assistance for how this google script should look would be greatly appreciated.

*"...looks something like this..."* Please read how to and [share a test sheet](https://webapps.stackexchange.com/a/138383/117311) so as you can be easier helped. — marikamitsos, Jul 25 '22 at 20:14
Is it a requirement that this result be achieved through Google Apps Script? If not, [this question](https://stackoverflow.com/questions/72749390/how-can-we-stack-rows-of-data-excluding-blank-under-one-header-column/72749807#72749807) might be helpful to you. — Sebastian Smiley, Jul 25 '22 at 21:17
If you share spreadsheets, make sure to also add images of your sheet to avoid closure of your question, as questions here must be [self](https://meta.stackoverflow.com/a/260455/) [contained](https://meta.stackexchange.com/a/149892). [Your email address can also be accessed by the public](https://meta.stackoverflow.com/questions/394304/), when you share Google files. — TheMaster, Jul 26 '22 at 07:57

score 2 · Accepted Answer · answered Jul 25 '22 at 21:45

You can try this in Apps Script:

function splitFormResponses() {
  const rs = SpreadsheetApp.getActive().getSheetByName("formReponses");
  const responses = rs.getRange(2, 1, rs.getLastRow() -1, rs.getLastColumn()).getValues();

  const splittedSheet = SpreadsheetApp.getActive().getSheetByName("splittedResponses");
  const splittedResponses = [];
  var newResponse = [];
  var timestamp;
  var date;

  //Creating a 2D array using the amount of rows in the responses.
  for(i=0; i < responses.length; i++){
    
    //Loop through each cell in the row.
    for (j = 0; j < responses[i].length; j++) { 
      timestamp = responses[i][0];
      date = responses[i][1];

      //If there is "another" it will add the created row to the 2D array and restart the "newResponse" variable with the current row's timestamp and date
      if (responses[i][j] == "Yes") {

        splittedResponses.push(newResponse);
        newResponse = [timestamp, date];

      //If there is no "another" it will add the created row to the 2D array and jump to the next row in the original responses.
      } else if (responses[i][j] == "No"){

        splittedResponses.push(newResponse);
        newResponse = [];
        break;     

      //Otherwise, it will add the value of the current cell to the new row.
      } else { 

      newResponse.push(responses[i][j]);
      }
    }
  }

  //Printing the array.
  splittedSheet.getRange(2, 1, splittedResponses.length, splittedResponses[0].length).setValues(splittedResponses);
}

Using this example data:

You get this result in a new "splittedResponses" sheet at "A2".

Hope this helps.

This worked perfectly, thank you. I will just look at running this on new form submission, then it's perfect. — WarrenG, Jul 26 '22 at 08:40

Split Google Form response to rows

1 Answers1