Duplicate Form responses to two Google Sheets programmatically

Question

I need to save my Google Form in two different sheets.

The first sheet will be the one for history and the other one will be exploited by the logistic services (who may delete some rows when the clients receive the shipped stuff).

I really need to keep all the responses on the first sheet whatever the logistic services do on the second.

The fact is; I was using formRat, but is not working anymore and I don't see any complementary module that does exactly the same thing. I'm not good enough in programming to write the script by myself.

I tried to write this in the second sheet:

=ArrayFormula('first_sheet_name'!A:W)

But when I try to delete a row on the second sheet, it reappears a few seconds later because Google Sheets recalculates it.

Mogsdad Mogsdad · Accepted Answer · 2015-10-22T01:35:48

A form submission trigger script attached to the Form Response spreadsheet can easily copy responses to the second sheet, as they arrive. Any modifications made later on the second sheet will survive.

Here is a very simple example of such an Installable Trigger Function. You need to declare ss2ID with the Sheet ID of spreadsheet 2. The script assumes that the responses are to be copied to the first sheet in spreadsheet 2, and that all form answers are populated.

function copyResponse( event ) {
  fixFormEvent( event );  // From https://stackoverflow.com/a/26975968/1677912
  var ss2Id = "---sheet-id---";
  var sheet2 = SpreadsheetApp.openById( secondSheetId ).getSheets()[0];
  sheet2.appendRow( [event.values] );
}

This function uses fixFormEvent( event ) from e.values in google forms skips empty answers, is there a workaround? to ensure the columns in the new sheet align with the original questions.

Duplicate Form responses to two Google Sheets programmatically

1 Answers