1. Introduction
2. Option 1: Using a FILTER Formula (Simple, No Code)
   1. Example 1: Sales Data
   2. Example 2: Email Filtering
3. Option 2: Using Google Apps Script (More Flexible, Works Across Files)

Introduction

The goal is to create a mirror sheet that automatically shows all rows or only the rows from a source sheet based on a criterion (for example, where the column “IS DONE” is set to “DONE”).

This is useful if you want to keep a clean view of completed tasks without manually copying rows.

You can do this either inside the same spreadsheet (simpler, using a formula) or across different spreadsheets (more flexible, using Google Apps Script).

You can do this in Google Sheets without manual duplication, using a FILTER formula or an Apps Script depending on how much control you want. Here are two approaches:

Option 1: Using a FILTER Formula (Simple, No Code)

To use the Google Sheets FILTER formula to populate multiple sheets based on criteria, follow these steps:

1. Create a source sheet that contains the data you want to filter and distribute to other sheets. The source sheet should have a consistent structure, with a header row and one or more data rows.
2. Create the destination sheets you want to populate with the filtered data. The destination sheets should have the same structure as the source sheet, with a header row and the same number of columns.
3. In the first cell of the first column of the destination sheets, enter the FILTER formula. The formula should reference the range of data in the source sheet and specify the criteria to filter by.
4. Copy the formula to the rest of the cells in the first column of the destination sheets. The formula will automatically adjust to reference the correct rows in the source sheet and return the filtered data for each destination sheet.
5. Repeat steps 3–4 for each column in the destination sheets if necessary.
6. Save and close the Google Sheets file. The destination sheets will automatically update with the filtered data from the source sheet based on the criteria specified in the FILTER formula.

Note: The key to using the FILTER formula to populate multiple sheets is to reference the correct range of data in the source sheet and specify the correct criteria to filter by. The formula will update automatically if you add new rows to the source sheet that meet the filtering criteria.

Example 1: Sales Data

Here’s a simple example that demonstrates how to use the FILTER formula to populate multiple sheets based on criteria:

Suppose you have a source sheet named “Sales Data” with the following data:

And you want to create two destination sheets named “East Sales” and “West Sales” to show the sales data for each region.

To do this, follow these steps:

1. Create the “East Sales” and “West Sales” sheets with the same structure as the “Sales Data” sheet, including a header row and the same number of columns.
2. In the first cell of the first column of the “East Sales” sheet, enter the following FILTER formula:

   =FILTER('Sales Data'!A:C,'Sales Data'!B:B="East")
   

   This formula filters the data in columns A–C of the “Sales Data” sheet to show only the rows where the value in column B (Region) is “East”.

Based on the example, the “East Sales” sheet will show the sales data for products in the East region. The content of the “East Sales” sheet should look like this:

This is because the FILTER formula used in the “East Sales” sheet filters the data in columns A–C of the “Sales Data” sheet to show only the rows where the value in column B (Region) is “East”. Therefore, only the rows for products with sales data in the East region are shown in the “East Sales” sheet.

Based on the example, the “West Sales” sheet will show the sales data for products in the West region. In the first cell of the first column of the “West Sales” sheet, enter the following FILTER formula:

=FILTER('Sales Data'!A:C,'Sales Data'!B:B="West")

The content of the “West Sales” sheet should look like this:

This is because the FILTER formula used in the “West Sales” sheet filters the data in columns A–C of the “Sales Data” sheet to show only the rows where the value in column B (Region) is “West”. Therefore, only the rows for products with sales data in the West region are shown in the “West Sales” sheet.

Now, when you add new rows to the “Sales Data” sheet and the region is “East” or “West”, the “East Sales” and “West Sales” sheets will automatically update with the filtered data based on the criteria specified in the FILTER formula.

Here is a sample Google Sheet: here

Example 2: Email Filtering

=FILTER('Email Messages'!A2:H, 'Email Messages'!C2:C="name1@company1.com")

Here is a sample Google Sheet: here

Option 2: Using Google Apps Script (More Flexible, Works Across Files)

If your mirror needs to be in a different spreadsheet, use Apps Script:

1. Open your mirror spreadsheet.
2. Go to Extensions → Apps Script.
3. Paste this script:

   
   function syncFilteredRows() {
     // === CONFIGURATION ===
     const SOURCE_SPREADSHEET_ID = "SOURCE_SHEET_ID";   // Source spreadsheet ID
     const SOURCE_SHEET_NAME = "SourceSheet";           // Name of source sheet
     const TARGET_SHEET_NAME = "MirrorSheet";           // Name of target (mirror) sheet
     
     const FILTER_COLUMN = "IS DONE";                   // Column header to check
     const FILTER_VALUE = "DONE";                       // Value to match in that column
     // =====================
   
     // Open sheets
     const sourceSpreadsheet = SpreadsheetApp.openById(SOURCE_SPREADSHEET_ID); 
     const sourceSheet = sourceSpreadsheet.getSheetByName(SOURCE_SHEET_NAME);
     const targetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(TARGET_SHEET_NAME);
   
     // Clear old data
     targetSheet.clearContents();
     
     // Get all data
     const data = sourceSheet.getDataRange().getValues();
     const headers = data[0];
     const filterIndex = headers.indexOf(FILTER_COLUMN);
     
     if (filterIndex === -1) {
       throw new Error(`Column "${FILTER_COLUMN}" not found in source sheet.`);
     }
   
     // Filter rows (keep header + matching rows)
     const filtered = data.filter((row, i) => i === 0 || row[filterIndex] === FILTER_VALUE);
     
     // Write to mirror sheet
     targetSheet.getRange(1, 1, filtered.length, 
       filtered[0].length).setValues(filtered);
   }
   
   

   Replace the config section at the top with:

   1. SOURCE_SHEET_ID → The ID of your source spreadsheet (from the URL).
   2. SourceSheet → The source sheet’s name.
   3. MirrorSheet → The mirror sheet’s name.
   4. FILTER_COLUMN → The column header you want to check.
   5. FILTER_VALUE → The value you want to filter on.
4. After the script is created, save it. Then go to Triggers → Add Trigger:
   1. Choose syncFilteredRows.
   2. Event source: Time-driven.
   3. Pick a schedule (e.g., every 5 minutes).