If you're running multiple Survicate surveys and want to combine their responses into a single master sheet, here's a step-by-step guide to automate this process.
Prerequisites Steps
Set Up Survicate-to-Sheets Connections:
Open each Survicate survey you want to track
Go to Integrations → Google Sheets
Connect each survey to a separate Google Sheet
Note: Each survey needs its own individual connection to Google Sheets
Gather Your Sheet IDs:
Open each Google Sheet connected to Survicate
Copy the ID from the URL (it's the long string between /d/ and /edit)
You'll need IDs for all source sheets and your target sheet
Setting Up the Merger
Create a Master Sheet:
Create a new Google Sheet that will store all responses
Copy its ID from the URL
Set Up the Script:
Open your master Google Sheet
Go to Extensions → Apps Script
Delete any code in the editor
Paste the code below
function mergeSheets() {
// Get source spreadsheets
var sheet1 = SpreadsheetApp.openById('YOUR_FIRST_SURVICATE_SHEET_ID');
var sheet2 = SpreadsheetApp.openById('YOUR_SECOND_SURVICATE_SHEET_ID');
// Get target spreadsheet and its first sheet
var masterSheet = SpreadsheetApp.openById('YOUR_MASTER_SHEET_ID');
var targetSheet = masterSheet.getSheets()[0];
// Clear existing data
targetSheet.clear();
// Get the actual sheets from the spreadsheets
var sourceSheet1 = sheet1.getSheets()[0];
var sourceSheet2 = sheet2.getSheets()[0];
// Get the data range for each sheet
var data1 = sourceSheet1.getDataRange().getValues();
var data2 = sourceSheet2.getDataRange().getValues();
// Skip header row in data2 by slicing from index 1
var data2WithoutHeader = data2.slice(1);
// Log the data sizes for debugging
Logger.log("Sheet 1 rows: " + data1.length + ", columns: " + data1[0].length);
Logger.log("Sheet 2 rows (without header): " + data2WithoutHeader.length + ", columns: " + data2WithoutHeader[0].length);
// Write data1 (including header) to master sheet
targetSheet.getRange(1, 1, data1.length, data1[0].length).setValues(data1);
// Write data2 (without header) below data1
targetSheet.getRange(data1.length + 1, 1, data2WithoutHeader.length, data2WithoutHeader[0].length).setValues(data2WithoutHeader);
}
3. Replace the IDs:
Replace 'YOUR_FIRST_SURVICATE_SHEET_ID' with the ID of your first Survicate responses sheet
Replace 'YOUR_SECOND_SURVICATE_SHEET_ID' with the ID of your second Survicate responses sheet
Replace 'YOUR_MASTER_SHEET_ID' with your target sheet ID
4. Deploy
Click the Deploy button, select "New deployment"
Select type "Web app"
Click Deploy on the modal window again
Google will ask for permissions
Click "Review Permissions"
Choose your Google account
Click "Allow"
Setting Up Automatic Updates
Since Survicate continuously collects responses, you'll want to set up automatic merging:
In the Apps Script editor, click the clock icon (Triggers)
Click "Add Trigger"
Configure the trigger:
Choose function: mergeSheets
Choose which deployment: Head
Select event source: Time-driven
Select type of time: Daily (recommended)
Choose a time of day that works for your reporting needs
Click Save
Important Notes
Ensure each Survicate survey is properly connected to its own Google Sheet first
The script keeps headers from the first sheet only
The script will clear and update the master sheet each time it runs
Both source sheets should have matching column structures from Survicate
The master sheet will be refreshed daily (or according to your trigger settings)
Troubleshooting
If you encounter issues:
Make sure all sheet IDs are put inside single quotes, example:
var sheet2 = SpreadsheetApp.openById('1KHT0nNCzeDfAW--YYK2cYysPy7iCM11sB6CKfczVr1Q');Verify all Survicate-to-Sheets connections are working
Check that all sheet IDs are correct
Ensure you have proper access to all sheets
Look at the execution log in Apps Script for error messages
Check your Google Sheets quotas if working with large surveys
Need help? Contact Survicate support for integration issues or check Google's Apps Script documentation for script-related questions.