How to set Google Form response destination to two different spreadsheets in Google Apps Script?

Question

Each group has a Google Form to fill out. The responses for each group form is sent to a spreadsheet for each group. I want to have a "Master Spreadsheet" that would collect all responses from all the groups. In Google Apps Scripts, I tried:

form.setDestination(FormApp.DestinationType.SPREADSHEET, responseSpreadsheet.getId());
form.setDestination(FormApp.DestinationType.SPREADSHEET, masterSpreadsheet.getId());

The responses were only sent to "masterSpreadsheet" and "responseSpreadsheet" got ignored.

Answer 1

I briefly looked for documentation explicitly stating that Google Forms only support one external destination, but didn't find any. However, you can assume this from the UI only supporting one as well getDestinationId() only returning a single ID.

Probably the most straightforward approach to getting the responses to another spreadsheet is to use a form submit trigger on the primary destination spreadsheet. That trigger provides the values property in the event object, which you can simply pass to appendRow().

function onFormSubmit(e) {
  const alternateDestinations = [
    { url: 'https://docs.google.com/spreadsheets/d/SS_ID/edit', sheetName: 'Sheet1' }
  ];
  for (let i = 0; i < alternateDestinations.length; i++) {
    let dest = alternateDestinations[i];
    SpreadsheetApp.openByUrl(dest.url).getSheetByName(dest.sheetName).appendRow(e.values);
  }
}

A more complex approach would be to use the form submit trigger on the form itself. Rather than setting a specific spreadsheet destination, you could have it write the responses to all of the spreadsheets you specify.