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:

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:

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.

CodePudding user response：

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.