Apps Script – Copy Data Between Google Sheets Based on Criteria

Ever wish you could automatically move data between sheets without doing it manually? With Google Apps Script, you can copy data from one Google Sheet to another based on specific rules or conditions — no formulas or add-ons required. It’s a game-changer for beginners looking to simplify their workflow in Google Sheets.

Whether you want to separate approved requests, extract completed tasks, or organize sales by region, this script helps you do it in a click — or even automatically behind the scenes!

What Is Google Apps Script?

Google Apps Script is a built-in tool that lets you write small bits of code to automate tasks in Google Sheets, Docs, Gmail, and other Google tools. It’s like a friendly assistant for your spreadsheets — especially when you’re dealing with repetitive work.

Real-Life Example: Move Approved Orders to a Tracking Sheet

Imagine you manage incoming customer orders in one sheet. You want to automatically copy only the rows where the status is marked as “Approved” to another sheet called “Approved Orders” for tracking. Here’s how your original sheet might look:

Sheet Name: “Order Requests”

Order ID	Customer Name	Item	Quantity	Status
1001	Alice	Laptop	1	Approved
1002	Ben	Monitor	2	Pending
1003	Charlie	Keyboard	1	Approved

Step-by-Step: Copy Data Based on Status

Open your Google Sheet and click on Extensions > Apps Script.

Delete any code in the editor and paste the script below:


function copyApprovedOrders() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sourceSheet = ss.getSheetByName("Order Requests");
  const targetSheet = ss.getSheetByName("Approved Orders");
  
  const data = sourceSheet.getDataRange().getValues();
  const headers = data[0];
  const approvedRows = [];

  for (let i = 1; i < data.length; i++) {
    if (data[i][4] === "Approved") {
      approvedRows.push(data[i]);
    }
  }

  if (approvedRows.length > 0) {
    targetSheet.clearContents();
    targetSheet.appendRow(headers);
    approvedRows.forEach(row => targetSheet.appendRow(row));
  } else {
    SpreadsheetApp.getUi().alert("No approved orders found.");
  }
}

Save your project and click the ▶️ Run button to test it.
When prompted, authorize the script to access your Sheets.

How It Works

It reads all the rows from the “Order Requests” sheet.
It checks if the Status column (column E) contains “Approved”.
Only matching rows are copied to the “Approved Orders” sheet.
Old data in the “Approved Orders” sheet is cleared first.

Key Benefits

Save Time: No need to manually filter or copy rows.
Accuracy: Reduce errors by letting the script handle the logic.
Scalable: Works even if you have hundreds of rows.

Pro Tips

You can schedule this script to run automatically using a time trigger.
Add a column in the source sheet to mark “Sent” or “Processed” after copying.
Use color coding in the destination sheet to highlight new entries.

Quick Cheat Sheet

Term	Meaning
`SpreadsheetApp.getActiveSpreadsheet()`	Grabs the current Google Sheets file
`getDataRange().getValues()`	Reads all the rows and columns with data
`appendRow()`	Adds a new row to the bottom of a sheet
`clearContents()`	Removes all values but keeps formatting

With just a few lines of Google Apps Script, you can automate powerful tasks like copying rows based on criteria. It’s simple, beginner-friendly, and saves you hours of manual work. Once you set it up, you’ll wonder how you ever managed without it!

So the next time you’re juggling spreadsheets full of orders, tasks, or requests — let Apps Script do the heavy lifting.