Google spreadsheet formating based on form responses

Question

I'm trying to make a Google form populate a spreadsheet with multiple rows based on 1 form entered data like this:

Form is simple with minimal customer information's https://docs.google.com/forms/d/1LrKlVuI7kxVU0lxz70Uu-2Obj4x3qIwe6nS-ErzbCAg/
Based on selected number of parts needed by customer (at the moment 1, 2 or 3) Form goes to sections where 1, 2 or 3 are entered by parts name and identification number https://docs.google.com/spreadsheets/d/1kAsgrB-2swL_tWYyXF5GLLX2PkVrkHYkcKr9eaQ8gnI/edit?usp=sharing

After entry i need for Forms to enter data in Sheet (or formatted sheet) as follows: -Each parts entered (1, 2 or 3) should be on individual row with the same client name in common

Sheet editors can then enter data in extra rows such as "Delivery date" and "Item price"

short: 1 form submitted, 3 questions in form, 3 rows

p.s. cell coloring is used only to point out the common data between rows

By default, each form submission is a new row. You can re-structure the form data in a new tab and use it. — Abhishek Boorugu
What research did you do until now? Did you try something? I'm thinking about three options: (1) install an onFormSubmit that will add the submitted data to the spreadsheet directly with the desired format, (2) install an onFormSubmit that will take the data from Form Responses and copy it to the desired sheet with the desired format, and (3) use sheets formulas to copy the data to the desired format. What do you think would be the most appropriate for your situation? Are you open to using Apps Script? — Iamblichus
Also, the Form you provided is not public. Can you provide a publicly accessible copy, free of sensitive information? — Iamblichus
done. i've made the form public (sorry about that)... i've tried to use a similar question posted on stackexchange [link]webapps.stackexchange.com/questions/107100/… but didn't quite made it. I'm not using a "Apps script" and i think i must achieve something more "automative" (on form submission) — Bogdan Webb
Well, Apps Script is a tool for achieving "something more automative", like the onFormSubmit I mentioned before. In this case, would any of the options I mentioned before would be appropriate for you? — Iamblichus

Iamblichus Iamblichus · Accepted Answer · 2020-09-01T13:24:03

One option would be to do the following:

#1. Install onFormSubmit trigger:

Install an onFormSubmit trigger attached to your spreadsheet, so that a function runs every time the form attached to the spreadsheet is submitted (this assumes that your Form is attached to your spreadsheet).

The trigger can be installed either manually, following these steps, or programmatically. To install the trigger programmatically, open a script bound to your spreadsheet by clicking Tools > Script editor, and copy an execute this function once:

function createTrigger() {
  var ss = SpreadsheetApp.getActive();
  ScriptApp.newTrigger("submitData")
    .forSpreadsheet(ss)
    .onFormSubmit()
    .create();
}

This will fire a function called submitData every time the form is submitted. Next, then, would be to write function, which should append the submitted data in the format you desire.

#2. Function to append submitted data to sheet:

In order to append the data submitted through the Form on the sheet called Formated responses, you need to use the corresponding event object, which contains the submitted data. You can use this to check how many parts are submitted and the values for its corresponding fields. Then, the method appendRow could be used to append this data to the sheet.

It could be something like this:

function submitData(e) {
  var sheet = SpreadsheetApp.getActive().getSheetByName("Formated responses");
  var res = e.namedValues;
  var numberOfParts = res["Number of parts"][0];
  var mainFields = [res["Timestamp"][0], res["Client name"][0], numberOfParts];
  switch (numberOfParts) {
    case '1':
      var fieldsOne = [res["Part identification number"][0], res["Part name"][0]];
      sheet.appendRow(mainFields.concat(fieldsOne));      
      break;
    case '2':
      var fieldsTwo = [res["#1 part identification number"][0], res["#1 part name"][0]];
      sheet.appendRow(mainFields.concat(fieldsTwo));
      fieldsTwo = [res["#2nd part identification number"][0], res["#2nd part name"][0]];
      sheet.appendRow(mainFields.concat(fieldsTwo));
      break;
    case '3':
      var fieldsThree = [res["#1st part identification number"][0], res["#1st part name"][0]];
      sheet.appendRow(mainFields.concat(fieldsThree));
      fieldsThree = [res["#2nd part identification number"][1], res["#2nd part name"][1]];
      sheet.appendRow(mainFields.concat(fieldsThree));
      fieldsThree = [res["#3rd part identification number"][0], res["#3rd part name"][0]];
      sheet.appendRow(mainFields.concat(fieldsThree));
      break;
  }
}

Note:

The function submitData could be made simpler (the switch could probably be removed, and a for loop used instead), but the names of the Form fields are not consistent with each other, hindering this option. Because of this, the function has a fair amount of repetition. I'd recommend you to fix the field names and rewrite the function a bit.

Reference:

Installable Triggers