Consolidating Data from Multiple Tabs into a Master Sheet Advanced QUERY or Script Google Sheet

If you’ve ever worked with multiple tabs of data in Google Sheets — maybe tracking monthly sales or gathering survey responses from different teams — you know how frustrating it can be to manually combine everything into one master sheet. That’s where advanced functions like QUERY and Google Apps Script come in. With just a few smart formulas or a little code, you can automate this process and save hours of work. This guide is perfect for beginners who want to understand how to consolidate data like a pro — without the headache.

Why Consolidating Data Matters

Whether you’re managing finances, attendance, inventory, or project updates, keeping everything in one place allows you to analyze trends, create summaries, and build dashboards more easily. Consolidation avoids errors from copy-pasting and makes your spreadsheet workflows cleaner and faster.

Real-Life Scenario: Monthly Sales Tracking

Let’s say you have a separate tab for each month’s sales — “January”, “February”, “March”, etc. Each tab contains data like this:

Date	Product	Units Sold	Revenue
01/01/2024	Product A	20	$200
01/01/2024	Product B	15	$150

Instead of copying each sheet into a “Master” tab, you can use formulas or scripts to automatically combine all the data.

Method 1: Using the QUERY + ARRAYFORMULA + INDIRECT

Step-by-Step Instructions

In your “Master” sheet, list the tab names vertically in column A (e.g., A2:A4 = January, February, March).
In B1 (or another column), use this formula:


=ARRAYFORMULA(
  QUERY({
    INDIRECT("'"&A2&"'!A2:D");
    INDIRECT("'"&A3&"'!A2:D");
    INDIRECT("'"&A4&"'!A2:D")
  },
  "SELECT Col1, Col2, Col3, Col4 WHERE Col1 IS NOT NULL", 0)
)

Note: INDIRECT works only if the sheet names don’t change and are spelled exactly the same. It also doesn’t work across different files.

Method 2: Using Google Apps Script (Dynamic, More Scalable)

If you want a more powerful, flexible solution, here’s a script that pulls data from all sheets into one master tab:


function consolidateToMaster() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const master = ss.getSheetByName("Master") || ss.insertSheet("Master");
  master.clear(); // Clear previous data
  master.appendRow(["Date", "Product", "Units Sold", "Revenue"]);

  const sheets = ss.getSheets().filter(s => s.getName() !== "Master");

  sheets.forEach(sheet => {
    const data = sheet.getRange(2, 1, sheet.getLastRow() - 1, 4).getValues();
    master.getRange(master.getLastRow() + 1, 1, data.length, data[0].length).setValues(data);
  });
}

How to Use the Script:

Go to Extensions > Apps Script
Paste the code and click the disk icon to save
Run consolidateToMaster using the ▶️ button

Pro Tips

Name all data sheets consistently (e.g., avoid spaces or typos)
Add a column for “Source Sheet” in your script to identify where each row came from
Set up a button in your sheet to trigger the script for non-techy users

Key Benefits

No more manual copy-paste across sheets
Always up-to-date data in your master sheet
Works great with dashboards and pivot tables
One-click update with a script

Quick-Reference Cheat Sheet

Tool	Use Case	Pros	Limitations
QUERY + INDIRECT	Basic merging from known sheet names	Fast, no coding needed	Manual tab references, doesn’t scale well
Apps Script	Auto-combine all sheet data dynamically	Fully automated, scalable	Requires script access

Whether you’re consolidating a year’s worth of sales, department reports, or student records, combining multiple tabs into a master sheet will streamline your workflow and reduce human error. Start with QUERY for simple cases, and upgrade to Apps Script when you need more control. Either way, your spreadsheets will be smarter and easier to manage.