CODEWITHSUNDEEP
google-sheetsgoogle-forms
Bogdan Webb
Bogdan Webb

Reputation: 45

Google spreadsheet formating based on form responses

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

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

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

main form parts entry form responses formatted result

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

Upvotes: 0

Views: 179

Answers (1)

Iamblichus
Iamblichus

Reputation: 19339

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:

Upvotes: 1

Related Questions