How to store google form responses into multiple spreadsheets?

Question

I am trying to store google from response into multiple spread sheets, tries "IMPORTRANGE" but it does not actually copies the cell data, but instead creates a reference for the same.

What I want is whenever there is new response on google sheets, it send the response to 2 diffrent google sheets instead of just one. Reason being, I want to make modifications in one of the sheets, and delete some responses. And keep one with all the data intact.

Answer 1

As mentioned by TheWizEd you can use Google Apps Script Installable Triggers to get the values from a form submission and copy those values to a different spreadsheet of your choice.

You may use the following code:

function copyFormResponses(e) {
  let ss = SpreadsheetApp.openByUrl("Insert Spreadsheet URL here").getSheetByName("Insert the Sheet name here");
  let range = e.range;
  let values = e.values;
  let lastRow = ss.getLastRow();
  for(let i=1; i<=values.length; i++)
  {
      ss.getRange(lastRow+1, i).setValue(values[i-1]);
  }
}

Just make sure to change the URL and the name of the sheet to the ones you are going to be using, and add the code to the main spreadsheet where the form responses are being saved.

In addition to that, remember to add the trigger to the code so that it runs with every new form submission.

Trigger:

Result:

References:

• Installable triggers
• Event Objects
• setValue()