How to Copy Google Form Responses to Another Google Sheet
Whenever you create a Google Form, the responses are stored in the default Google Sheet associated with the form. However, with the help of Document Studio, you can re-route your Google Form responses and store a copy of the responses in another spreadsheet - it could be a Microsoft Excel worksheet, a Zoho Spreadsheet, or even another Google Spreadsheet in your Drive.
For this example, we have a Google Form for collecting registrations for a business event from prospective attendees. Let’s see how we can use Document Studio to save the form responses to another Google Sheet in addition to the default Google Sheet.
Create Automation Workflow
Install Document Studio and open your Google Form to launch the add-on. Create a new workflow, give a descriptive name like Copy Rows to Google Sheet, and click Continue to move to the conditions page.
On the Conditions pane, specify if you want your workflow to run only if the form responses satisfy certain conditions. For instance, if the Google Form has a question asking for the customer’s location and you would like to copy responses specific to North America in another sheet, you can put that condition here. By default, the workflow will run for all the form responses.
Click Continue to move to the tasks section.
Connect to Google Sheets
Choose the Copy Row task from the list of available tasks and then select Google Sheets from the list of available spreadsheet services.
From the Google file picker window, choose the Google spreadsheet where you want the Google Form responses to be copied.
If you already have an existing sheet in the selected spreadsheet where you want the responses to be copied, specify its name in the Sheet Name field. You can also create a new sheet by specifying a new name in this field. You can also use dynamic markers for naming the sheet.
Field Mapping
The next section on the task page is Field Mapping where you define the form questions that should be mapped to the columns in the destination sheet. You may either copy all the form responses or only specific questions to the destination sheet.
Field Name: represents the heading (title) of the column that will be added in the destination spreadsheet.
Field Value: represents the values that will be stored in that column based on the answers in the Google Form response.
For instance, if a question in the source Google Form is titled Zip and you would like to store it as Postal Code in the destination spreadsheet, the mapping would look like this:
| Field Name | Field Value |
|---|---|
| Postal Code | {{ Zip }} |
You have a list of default fields available, including the questions in the form. You can edit the field names, delete an entire field, and add as many new fields as you want.
Click Done to save this task and proceed to the next step.
Activate Workflow
Click on Continue to proceed to the triggers screen. Check the Run on form submit option so that every time a new form response is submitted, the workflow will be triggered, and a new row will be added to the target Google Sheet.
Save your workflow. The workflow will also appear in the workflow dashboard where you can edit, delete, or manually run the workflow.
Open your Google Form and submit a sample response to test the workflow. It will copy the form responses to the default destination Google Sheet and the selected spreadsheet as per the field mapping you have defined.
You may copy responses to multiple Google Sheets in one go by creating multiple Copy Row tasks in the same workflow. Each task can have its own field mapping and destination sheet.