Is it possible to place form responses with sections to different tabs?

Question

was wondering if anyone knows whether it's possible or not to direct form responses to different tabs but within the same spreadsheet?

I have form with different sections, Main, Drop-Off/PickUp, and Meeting. In the Main forms, there is a drop down which has option for either DO/PU, or Meeting. If DO/PU is chosen, they will be directed to that section of the form.

Both forms are quite similar and only has a slight difference. However in the main response sheet, even the same information is divided into different column. eg Name for DO/PU is in column B, while Name for Meeting is in column C. Therefore, I was wondering if it's possible to direct each section responses to its own tabs.

Will I have to use App Script?

I've only tried using IMPORTRANGE, and QUERY function but the information isn't sorted as how I want it to be (some columns are imported with empty column in between).

Unfortunately, no. In my case, I have different sections on my form. If I were to use QUERY it would have different ranges of info for DO/PU and Meeting. — Jme Sekaiichi

Rafa Guillermo Rafa Guillermo · Accepted Answer · 2019-07-08T12:04:50

Without using Apps Script there isn't a way to segregate and organise form responses into multiple sheets the way you describe unfortunately, as Google Forms will always submit all responses to the same single sheet on each sumbit.

Using Apps Script this can be done quite simply though, if you know how many responses need to be put on each page, you can write an onFormSubmit() function bound to the form which opens the Sheet directly using the Sheet's ID and moves the data from the default sheet into the sheets you would like.

function onFormSubmit(){

  // Open the spreadsheet and each of the sheets to copy from and to
  var spreadsheet = SpreadsheetApp.openById("XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX");
  var tempSheet = spreadsheet.getSheetByName("DefaultSheetTheFormSumbitsTo")
  var page1 = spreadsheet.getSheetByName("Responses 1");
  var page2 = spreadsheet.getSheetByName("Responses 2");
  var page3 = spreadsheet.getSheetByName("Responses 3");

  // Get the first free row in each of the segregated sheets to copy from and to
  var page1Range = page1.getRange((page1.getLastRow() + 1), 1, 1, 4);
  var page2Range = page2.getRange((page2.getLastRow() + 1), 1, 1, 4);
  var page3Range = page3.getRange((page3.getLastRow() + 1), 1, 1, 4);

  // Get the values of the data from the default sheet that you want 
  // to copy to each of the sheets
  var page1Data = tempSheet.getRange(tempSheet.getLastRow(), 1, 1, 4);
  var page2Data = tempSheet.getRange(tempSheet.getLastRow(), 5, 1, 4);
  var page3Data = tempSheet.getRange(tempSheet.getLastRow(), 10, 1, 4);

  // Copy the data into the right sheet
  page1Range.setValues(page1Data.getValues());
  page2Range.setValues(page2Data.getValues());
  page3Range.setValues(page3Data.getValues());

  // Uncomment this if you want to keep the default sheet clean, though
  // this code will run either way
  // tempSheet.clear();  
}

You need to make sure that the .getRanges() point to the correct range of responses each time that the form is submitted. If you want to see the questions in the first row you will need to make sure that each of the individual sheets are populated in row 1 as this script only gets the latest response.

After this has been done, make sure that you have the Sheets API Advanced Google service enabled under Resources -> Advanced google Services, and set up an installable trigger on your G Suite Developer Hub Triggers page so that the onFormSubmit() function runs on event type 'On form submit'.

Is it possible to place form responses with sections to different tabs?

1 Answers