📋 Merge Multiple Google Spreadsheets into One

1. Overview

\[ \begin{array}{l} \textbf{You can combine data from multiple Google Spreadsheets into one --} \\ \text{by using Google Apps Script to import and merge data automatically.} \\ \textbf{This is ideal for consolidating team sheets, survey results, or logs --} \\ \text{from many sources into a master dashboard.} \end{array} \]

2. Use Cases

            
                - Combine monthly reports from multiple teammates
                - Merge Google Form responses from different forms
                - Consolidate department or regional data into one summary sheet
                - Aggregate Google Sheets shared by external collaborators
            
        

This technique is widely used in project management, operations, HR onboarding, and financial reporting scenarios.

3. Preparation

            
                - Create a new Google Spreadsheet as your "Master" sheet
                - Collect the spreadsheet IDs (or URLs) of all source sheets
                - Ensure each sheet has the same column structure (headers in row 1)
            
        

If needed, you can extract spreadsheet IDs from URLs using string manipulation or manually.

4. Google Apps Script to Merge Sheets

            
                function mergeSpreadsheets() {
                  const sourceIds = [
                    'SPREADSHEET_ID_1',
                    'SPREADSHEET_ID_2',
                    'SPREADSHEET_ID_3'
                  ];

                  const targetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Master");
                  targetSheet.clearContents();

                  let isFirst = true;

                  sourceIds.forEach(id => {
                    const source = SpreadsheetApp.openById(id);
                    const sheet = source.getSheets()[0];
                    const data = sheet.getDataRange().getValues();

                    // Add headers only once
                    if (isFirst) {
                      targetSheet.getRange(1, 1, data.length, data[0].length).setValues(data);
                      isFirst = false;
                    } else {
                      targetSheet.getRange(
                        targetSheet.getLastRow() + 1,
                        1,
                        data.length - 1,
                        data[0].length
                      ).setValues(data.slice(1)); // skip headers
                    }
                  });
                }
            
        

This script copies data from the first sheet of each spreadsheet, excluding repeated headers.

5. Extracting Spreadsheet ID from URL

            
                // Sample spreadsheet URL:
                // https://docs.google.com/spreadsheets/d/1abcDEFghiJKLmnopQRsTuvWxyz1234567890/edit#gid=0

                // Extract the ID between `/d/` and `/edit`
                Spreadsheet ID: 1abcDEFghiJKLmnopQRsTuvWxyz1234567890
            
        

Use only the ID string inside the `openById()` function, not the full URL.

6. Optional Enhancements

            
                - Add timestamp columns to track when each row was imported
                - Include a source column showing the origin spreadsheet name
                - Schedule the script with triggers to auto-update daily
            
        

These enhancements make the system robust, traceable, and hands-free.

7. Conclusion

            
                Merging multiple Google Spreadsheets is simple with Apps Script. 
                Whether you’re managing team data or automating monthly reports, 
                this approach saves time and improves consistency.
            
        

Combine this with filters, charts, or pivot tables to create a powerful real-time dashboard.