How to Merge Multiple Google Spreadsheets into One

Managing multiple Google Sheets across projects, departments, or clients can get messy fast. Whether you're consolidating monthly reports, combining survey responses, or unifying scattered data sources, merging multiple Google Sheets into one central file can save time, reduce errors, and streamline collaboration.

This guide will show you how to merge data from multiple Google Spreadsheets into one master sheet—both manually and automatically using Google Apps Script, so you can choose the method that suits your workflow.

✅ When Would You Need to Merge Google Sheets?

You might want to merge spreadsheets if:

• Each team member maintains their own Google Sheet

• Monthly data is stored in separate files

• You’re combining Google Form responses from multiple sources

• You want to create a central dashboard or report

Method 1: Manual Copy-Paste (Best for One-Time Merges)

If you only need to combine data once, manual copy-paste is a fast, simple method.

Steps:

1. Open each source spreadsheet in a new tab.

2. Copy the relevant data (e.g., from Sheet1).

3. Paste it into the destination spreadsheet (your master sheet).

4. Ensure columns match across sheets before merging.

Pros:

• No setup required

• Instant results

Cons:

• Tedious for more than a few files

• Easy to introduce errors or misalignment

• Not scalable

Method 2: Use the IMPORTRANGE() Function

IMPORTRANGE() lets you link data from another Google Sheet, and updates in real time.

Syntax:

=IMPORTRANGE("spreadsheet_url", "Sheet1!A1:Z1000")

Steps:

1. In your master sheet, choose a tab to pull data into.

2. In A1, enter the formula:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/abc123XYZ/edit", "Sheet1!A1:Z")

3. Authorize access when prompted.

Repeat for each sheet and stack the imported data in separate tabs or side-by-side.

Pros:

• Data stays updated in real-time

• No coding required

Cons:

• No automatic consolidation into one table (unless you use queries)

• Becomes complex with many files

Method 3: Merge with Google Apps Script (Best for Automation)

If you need to merge multiple spreadsheets into a single sheet, especially from different Google Drive locations, use Apps Script to automate the process.

Use Case: Combine data from several spreadsheets into one master sheet

Step-by-Step Script:

1. Open your destination spreadsheet.

2. Go to Extensions > Apps Script

3. Replace default code with:

function mergeSheetsFromMultipleFiles() {
  // List of spreadsheet URLs
  var fileUrls = [
    "https://docs.google.com/spreadsheets/d/FILE_ID_1/edit",
    "https://docs.google.com/spreadsheets/d/FILE_ID_2/edit",
    "https://docs.google.com/spreadsheets/d/FILE_ID_3/edit"
  ];
  
  var destinationSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Master") || SpreadsheetApp.getActiveSpreadsheet().insertSheet("Master");
  destinationSheet.clear(); // optional: start fresh
  var headerWritten = false;

  fileUrls.forEach(function(url) {
    var file = SpreadsheetApp.openByUrl(url);
    var sheet = file.getSheets()[0]; // get the first sheet in the file
    var data = sheet.getDataRange().getValues();

    if (!headerWritten) {
      destinationSheet.getRange(1, 1, data.length, data[0].length).setValues(data);
      headerWritten = true;
    } else {
      destinationSheet.getRange(destinationSheet.getLastRow() + 1, 1, data.length - 1, data[0].length).setValues(data.slice(1));
    }
  });

  SpreadsheetApp.flush();
}

4. Replace FILE_ID_1, FILE_ID_2, etc., with your actual spreadsheet IDs or URLs.

5. Click Run, then authorize access when prompted.

Pros:

• Fully automated merging

• Can be scheduled or triggered

• Combines all sheets into one clean table

Cons:

• Requires minor coding

• Sheet structures must match (same columns)

Method 4: Use Google Drive Folder (Bulk Merge from Folder)

If all your spreadsheets are stored in the same Drive folder, you can fetch and merge them by folder ID.

Script:

function mergeFromFolder() {
  var folder = DriveApp.getFolderById("YOUR_FOLDER_ID");
  var files = folder.getFilesByType(MimeType.GOOGLE_SHEETS);
  var masterSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Master") || SpreadsheetApp.getActiveSpreadsheet().insertSheet("Master");
  masterSheet.clear();
  var headerWritten = false;

  while (files.hasNext()) {
    var file = files.next();
    var sheet = SpreadsheetApp.open(file).getSheets()[0];
    var data = sheet.getDataRange().getValues();

    if (!headerWritten) {
      masterSheet.getRange(1, 1, data.length, data[0].length).setValues(data);
      headerWritten = true;
    } else {
      masterSheet.getRange(masterSheet.getLastRow() + 1, 1, data.length - 1, data[0].length).setValues(data.slice(1));
    }
  }
}

Replace "YOUR_FOLDER_ID" with the actual ID from your Google Drive folder URL.

Benefits:

• Easily scales with 10, 50, or 100 files

• Zero need to hardcode URLs

• Reusable and adjustable

Method 5: Use Google Sheets Add-ons

If you prefer no-code solutions, use a trusted add-on:

• Sheetgo – Connect, merge, and automate spreadsheets

• Coupler.io – Import data from multiple sources

• Merge Sheets by Ablebits – Powerful merging options

Steps:

1. Go to Extensions > Add-ons > Get add-ons

2. Search for and install one of the tools

3. Follow in-app instructions to link and merge files

Pros:

• User-friendly

• Powerful configuration options

• Handles complex merging

Cons:

• Some features are paid

• Limited automation unless upgraded

Final Tips

Conclusion

Merging multiple Google Spreadsheets into one doesn't have to be a tedious, error-prone task. Whether you're working with a handful of files or hundreds of spreadsheets across folders, Google Sheets and Apps Script offer flexible, powerful options to bring everything together efficiently.