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.

You could have an onFormSubmit() trigger that on a new form response entry will copy it to the other spreadsheets. See [Intallalbe Triggers](https://developers.google.com/apps-script/guides/triggers/installable) — TheWizEd, Sep 11 '22 at 14:19
If this answered your question, please click the accept button on the left (check icon). By doing so, other people in the community, who may have the same concern as you, will know that theirs can be resolved. If the accept button is unavailable to you, feel free to tell me. [How to accept answer](https://stackoverflow.com/help/accepted-answer) — Fernando Lara, Sep 16 '22 at 13:51

score 1 · Accepted Answer · answered Sep 15 '22 at 16:03

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.

How to store google form responses into multiple spreadsheets?

1 Answers1

Trigger:

Result:

References: