Google form: create two rows with different information

user_17686371 0
Viewed 49

Im trying to create a google form with 2 fields, one for "item" and the other for "quantity" Since user might need to send miltiple items I want to create 1 form only and sort the information.

my Google form

So far I have managed to add a script that splits the information submitted in "item" into many rows, however, Im not able to do the same with the field "quantity"

I got this information from this post

This is my script:

    function onFormSubmit(e) {
  var ss = SpreadsheetApp.openByUrl("URL_here");
  var sheet = ss.getSheetByName("FormResponses");
  // Form Response retrieved from the event object
  const formResponse = e.response;
  var itemResponses = formResponse.getItemResponses();
  
  // Add responses comma-separated included
  var rowData = itemResponses.map(item => item.getResponse().toString());
  rowData.splice(0, 0, formResponse.getTimestamp());


  // Split into different rows afterwards
  if (rowData[1].includes(',')) {
    rowData[1].split(',').forEach(instanceName => {
      let tmpRow = rowData.map(data => data);
      tmpRow[1] = instanceName;
      sheet.appendRow(tmpRow);

       // Append to the sheet
    });

  } 
  else {
    sheet.appendRow(rowData); // Append to the sheet
  }

Current results:

Click here to see image

What I want to get:

Click here to see image

Thanks

google-forms google-apps-script google-sheets
1 Answers

When I saw your script, only the 2nd element of rowData is split with ,. I thought that this might be the reason for your issue. And, when appendRow is used in a loop, the process cost will become high. So, in your situation, how about the following modification?

From:

rowData.splice(0, 0, formResponse.getTimestamp());


// Split into different rows afterwards
if (rowData[1].includes(',')) {
  rowData[1].split(',').forEach(instanceName => {
    let tmpRow = rowData.map(data => data);
    tmpRow[1] = instanceName;
    sheet.appendRow(tmpRow);

     // Append to the sheet
  });

} 
else {
  sheet.appendRow(rowData); // Append to the sheet
}

To:

var date = formResponse.getTimestamp();
var values = rowData.map(v => v.includes(',') ? v.split(",") : [v]);
var res = values[0].map((_, c) => [date, ...values.map(r => r[c] || "")]);
sheet.getRange(sheet.getLastRow() + 1, 1, res.length, res[0].length).setValues(res);

Reference:

user_71086530
Related