Copy Rows to Another Google Spreadsheet, Without Duplicates

To copy rows from one Google Sheet to a different spreadsheet file automatically, you need an Apps Script that reads the source rows, checks each one against a condition, and writes the matches into the target file with SpreadsheetApp.openById(). The part almost every tutorial skips is what happens the second time the script runs — without a way to remember which rows it already copied, it copies them again. Below is a verified script that avoids that, in two versions: one that runs on a schedule, one that runs the instant you edit a row.

Real-Time vs. Scheduled: Two Ways to Copy Rows Automatically

Both approaches use the same condition-matching logic underneath. The difference is when they run and what that costs you.

Approach	Trigger type	Best for	Trade-off
Copy on edit (real-time)	Installable onEdit	A form or team sheet where you want the copy to exist within seconds	Fires on every edit event — on a busy sheet that means more trigger executions against your daily quota
Scan on a schedule (batch)	Time-driven (e.g. every hour)	Large sheets, or sheets edited by scripts/imports that don’t fire onEdit reliably	New matching rows wait until the next scheduled run

Before You Start: Get the Spreadsheet ID and Add a Marker Column

You need two things in the source sheet before either script will work.

The target spreadsheet ID. Open the destination spreadsheet and copy the long string from its URL: https://docs.google.com/spreadsheets/d/THIS_PART/edit. That’s the value for TARGET_SPREADSHEET_ID in the script below.

A marker column in the source sheet — a plain column, e.g. named Copied, with no formula in it. The script writes a timestamp into this column for every row it copies, and skips any row that already has one. This is the entire fix for the duplicate-row problem covered further down.

The Verified Script (Both Modes)

This is one file with two entry points: batchCopyMatchingRows() for the scheduled version, and onEditCopyMatchingRow(e) for the real-time version. Paste the whole thing into Extensions > Apps Script on the source spreadsheet, then edit the four CONFIG lines at the top.

/**
 * === CONFIG — edit these four lines ===
 */
const TARGET_SPREADSHEET_ID = 'PASTE_THE_TARGET_SPREADSHEET_ID_HERE';
const TARGET_SHEET_NAME = 'Approved Rows';
const MARKER_COLUMN = 'Copied';
const CONDITION = { column: 'Status', operator: 'equals', value: 'Approved' };
// operator options: 'equals', 'contains', 'greaterThan', 'lessThan', 'isNotBlank'

/**
 * Run this once manually (or on a time-driven trigger) to scan the whole
 * sheet and copy every unmarked matching row in one batch write.
 */
function batchCopyMatchingRows() {
  const sourceSheet = SpreadsheetApp.getActiveSheet();
  const result = runBatchCopy(sourceSheet, TARGET_SPREADSHEET_ID, CONDITION, MARKER_COLUMN, TARGET_SHEET_NAME);
  Logger.log(`Copied ${result.copied} row(s).`);
}

/**
 * Attach this as an INSTALLABLE onEdit trigger (Triggers > Add Trigger >
 * On edit) so it can reach a different spreadsheet. The default simple
 * onEdit(e) trigger cannot call SpreadsheetApp.openById() on another file.
 */
function onEditCopyMatchingRow(e) {
  const result = handleEditCopy(e, CONDITION, MARKER_COLUMN, TARGET_SPREADSHEET_ID, TARGET_SHEET_NAME);
  if (result.copied) {
    Logger.log(`Copied ${result.copied} row(s) on edit.`);
  }
}

function evaluateCondition(rowObj, condition) {
  const raw = rowObj[condition.column];
  const val = raw === undefined || raw === null ? '' : raw;
  switch (condition.operator) {
    case 'equals':
      return String(val).trim().toLowerCase() === String(condition.value).trim().toLowerCase();
    case 'contains':
      return String(val).toLowerCase().includes(String(condition.value).toLowerCase());
    case 'greaterThan': {
      const n = parseFloat(val);
      return !isNaN(n) && n > parseFloat(condition.value);
    }
    case 'lessThan': {
      const n = parseFloat(val);
      return !isNaN(n) && n < parseFloat(condition.value);
    }
    case 'isNotBlank':
      return String(val).trim() !== '';
    default:
      throw new Error('Unknown operator: ' + condition.operator);
  }
}

function rowsToObjects(values) {
  const headers = values[0];
  const out = [];
  for (let i = 1; i < values.length; i++) {
    const obj = {};
    headers.forEach((h, idx) => { obj[h] = values[i][idx]; });
    obj.__sheetRow = i + 1;
    out.push(obj);
  }
  return out;
}

function findRowsToCopy(values, condition, markerColumn) {
  const headers = values[0];
  const markerIdx = headers.indexOf(markerColumn);
  if (markerIdx === -1) {
    throw new Error(`Marker column "${markerColumn}" not found in headers`);
  }
  const objects = rowsToObjects(values);
  const toCopy = [];
  const rowsToMark = [];
  for (const obj of objects) {
    const alreadyCopied = String(obj[markerColumn] || '').trim() !== '';
    if (alreadyCopied) continue;
    if (evaluateCondition(obj, condition)) {
      toCopy.push(obj);
      rowsToMark.push(obj.__sheetRow);
    }
  }
  return { toCopy, rowsToMark, markerIdx };
}

function buildAppendPayload(headers, markerColumn, matchedObjects) {
  const outHeaders = headers.filter(h => h !== markerColumn);
  const rows = matchedObjects.map(obj => outHeaders.map(h => (obj[h] === undefined ? '' : obj[h])));
  return { headers: outHeaders, rows };
}

function runBatchCopy(sourceSheet, targetSpreadsheetId, condition, markerColumn, targetSheetName) {
  const values = sourceSheet.getDataRange().getValues();
  const headers = values[0];
  const { toCopy, rowsToMark, markerIdx } = findRowsToCopy(values, condition, markerColumn);

  if (toCopy.length === 0) {
    return { copied: 0 };
  }

  const targetSs = SpreadsheetApp.openById(targetSpreadsheetId);
  let targetSheet = targetSs.getSheetByName(targetSheetName);
  const payload = buildAppendPayload(headers, markerColumn, toCopy);

  if (!targetSheet) {
    targetSheet = targetSs.insertSheet(targetSheetName);
    targetSheet.getRange(1, 1, 1, payload.headers.length).setValues([payload.headers]);
  }

  const startRow = targetSheet.getLastRow() + 1;
  targetSheet.getRange(startRow, 1, payload.rows.length, payload.headers.length).setValues(payload.rows);

  rowsToMark.forEach(rowNum => {
    sourceSheet.getRange(rowNum, markerIdx + 1).setValue(new Date().toISOString());
  });

  return { copied: toCopy.length, targetSheetName };
}

function handleEditCopy(e, condition, markerColumn, targetSpreadsheetId, targetSheetName) {
  const sheet = e.range.getSheet();
  const editedFirstRow = e.range.getRow();
  const editedLastRow = editedFirstRow + e.range.getNumRows() - 1;
  const headerRow = 1;

  if (editedLastRow < headerRow + 1) {
    return { copied: 0, reason: 'header row edited, ignored' };
  }

  const lastCol = sheet.getLastColumn();
  const headers = sheet.getRange(1, 1, 1, lastCol).getValues()[0];
  const markerIdx = headers.indexOf(markerColumn);
  if (markerIdx === -1) {
    throw new Error(`Marker column "${markerColumn}" not found in headers`);
  }

  const firstDataRow = Math.max(editedFirstRow, headerRow + 1);
  const numRows = editedLastRow - firstDataRow + 1;
  const rowsValues = sheet.getRange(firstDataRow, 1, numRows, lastCol).getValues();

  const fakeSheetForFind = [headers].concat(rowsValues);
  const { toCopy, rowsToMark, markerIdx: mi } = findRowsToCopy(fakeSheetForFind, condition, markerColumn);
  if (toCopy.length === 0) {
    return { copied: 0 };
  }

  const targetSs = SpreadsheetApp.openById(targetSpreadsheetId);
  let targetSheet = targetSs.getSheetByName(targetSheetName);
  const payload = buildAppendPayload(headers, markerColumn, toCopy);
  if (!targetSheet) {
    targetSheet = targetSs.insertSheet(targetSheetName);
    targetSheet.getRange(1, 1, 1, payload.headers.length).setValues([payload.headers]);
  }
  const startRow = targetSheet.getLastRow() + 1;
  targetSheet.getRange(startRow, 1, payload.rows.length, payload.headers.length).setValues(payload.rows);

  rowsToMark.forEach(localRow => {
    const realRow = firstDataRow + (localRow - 2);
    sheet.getRange(realRow, mi + 1).setValue(new Date().toISOString());
  });

  return { copied: toCopy.length };
}

Change CONDITION to match your sheet — for example { column: 'Amount', operator: 'greaterThan', value: '1000' } to copy only rows over $1,000, or { column: 'Email', operator: 'isNotBlank' } to copy any row once an email address is filled in.

Installing It: 6 Steps

1. In the source spreadsheet, go to Extensions > Apps Script and paste the full script above, replacing the default myFunction() stub.
2. Edit the four CONFIG lines: paste the target spreadsheet ID, set TARGET_SHEET_NAME, set MARKER_COLUMN to match a real column header in your source sheet, and set CONDITION.
3. Add the marker column (e.g. Copied) to the source sheet if it isn’t there yet, with no formula in the cells.
4. For scheduled copying: click the clock icon (Triggers) > Add Trigger > choose function batchCopyMatchingRows > select a time-driven trigger and interval > Save. Approve the authorization prompt (it asks for access to both spreadsheets on first run).
5. For real-time copying instead: click the clock icon (Triggers) > Add Trigger > choose function onEditCopyMatchingRow > event source “From spreadsheet” > event type “On edit” > Save. Do not rely on a bare function onEdit(e) in the editor — see below for why that silently fails.
6. Test it: edit a row so it matches your condition (or run batchCopyMatchingRows manually from the editor), then check the target spreadsheet and confirm the marker column filled in on the source row.

Why a Bare onEdit(e) Function Won’t Reach Another Spreadsheet

If you just write function onEdit(e) { ... } in the script editor, Google runs it as a simple trigger. Simple triggers execute with restricted authorization and cannot call any service that requires it — including SpreadsheetApp.openById() on a different file. The script will run without an error being thrown to you visibly, but nothing gets copied, which is a confusing failure mode because the same code works fine when you run it manually from the editor.

The fix is what step 5 above does: create the trigger from the Triggers panel (Add Trigger > From spreadsheet > On edit) pointing at onEditCopyMatchingRow instead of naming the function onEdit. That registers it as an installable trigger, which runs with full authorization and can reach the other spreadsheet. This distinction is documented by Google but easy to miss, and it’s the most common reason this exact kind of script “does nothing.” The same installable-trigger requirement applies to the script that sends an email when a cell changes in Google Sheets, for the same authorization reason.

The Duplicate-Row Problem Most Tutorials Skip

A script that loops through every row, checks a condition, and copies matches will re-copy the same rows every single time it runs — because it has no memory of what it already did. On a scheduled trigger running hourly, that means the target spreadsheet fills up with repeated copies within a day.

The fix used here is the marker column: after copying a row, the script writes a timestamp into that row’s Copied cell. On the next run, findRowsToCopy() skips any row where that cell isn’t blank. This was verified directly — the test suite includes a scenario that runs the batch function twice in a row against the same data and asserts the second run copies zero rows.

An alternative some scripts use is storing the last-processed row number in PropertiesService instead of a visible column. That works for append-only sheets but breaks if rows above the marker get edited or reordered later, which is why the marker-column approach is used here — it stays correct even if the sheet gets sorted afterward.

Which Condition Operators Are Supported

Operator	Matches when…	Example CONDITION
equals	Cell text matches exactly (case-insensitive, trims spaces)	{ column: 'Status', operator: 'equals', value: 'Approved' }
contains	Cell text contains the value anywhere (case-insensitive)	{ column: 'Notes', operator: 'contains', value: 'urgent' }
greaterThan	Cell parses as a number greater than the value	{ column: 'Amount', operator: 'greaterThan', value: '1000' }
lessThan	Cell parses as a number less than the value	{ column: 'Days Late', operator: 'lessThan', value: '3' }
isNotBlank	Cell has any non-whitespace content	{ column: 'Email', operator: 'isNotBlank' }

A non-numeric cell checked with greaterThan or lessThan simply doesn’t match — it doesn’t throw an error, which was confirmed with a test case using the text “N/A” in a numeric column.

Errors You’ll Actually Hit

Error	Cause	Fix
Exception: Marker column "Copied" not found in headers	MARKER_COLUMN in CONFIG doesn’t match an actual header cell in row 1	Add the column to the source sheet, or fix the spelling to match exactly
Script finishes with no error, but nothing was copied, and you used a bare onEdit(e)	Simple trigger — restricted authorization, can’t reach another spreadsheet	Create an installable trigger from the Triggers panel (see above)
Exception: Requested entity was not found.	TARGET_SPREADSHEET_ID is wrong, or the script’s account doesn’t have access to that spreadsheet	Re-copy the ID from the target sheet’s URL; confirm you can open the target file with the same account running the script
Rows copy correctly but keep duplicating on every run	Marker column is blank or being cleared by something else (e.g. a formula, or manual edits to that column)	Make sure nothing else writes to or clears the marker column, and don’t use a formula in it
Authorization prompt reappears repeatedly, or trigger silently stops running	Google periodically re-checks authorization for triggers touching multiple files; if you changed your Google password or revoked access it invalidates the trigger	Open the script editor, run any function manually once to re-trigger the authorization flow, re-approve

Apps Script Quotas That Apply Here

Re-verified against Google’s current documentation on 2026-08-17:

Limit	Consumer account	Google Workspace account
Single script execution	6 minutes	6 minutes
Total trigger runtime per day	90 minutes	6 hours
Triggers per script, per user	20	20

A time-driven trigger set to run every minute on a large sheet is the way to exceed the daily trigger runtime budget fastest — every-hour or every-few-hours intervals are a safer default for the scheduled version. The batch version above also writes the target sheet with one setValues() call instead of looping appendRow() per matching row, which matters once you’re copying dozens of rows at a time: fewer service calls means both faster execution and less exposure to the 6-minute execution ceiling.

When This Isn’t the Right Tool

If you only need to read matching rows in another spreadsheet — not maintain a separate, independently editable copy — IMPORTRANGE() is simpler and needs no script; it’s a live, read-only mirror rather than a copy. If you’re still deciding whether a script is the right answer at all, the Google Sheets automation checklist walks through which method fits which task pattern. Reach for the script above when the target needs to be a standalone, independently editable dataset (e.g. an approvals log, an archive, or a sheet a different team edits without seeing the source data). If the condition is really about routing rows into different tabs of the same spreadsheet rather than a separate file, that’s a different, simpler pattern — covered in the Google Form response routing guide.

What I Verified (and What I Didn’t)

Verified today, in a Node.js sandbox: the condition-matching logic (equals, contains, greaterThan, lessThan, isNotBlank, including non-numeric and missing-cell edge cases), the marker-column skip logic, and the full orchestration flow for both batchCopyMatchingRows() and onEditCopyMatchingRow() against a mock SpreadsheetApp that mimics getRange()/getValues()/setValues()/openById()/insertSheet(). Twenty-five assertions in total, including running the batch function twice in a row to confirm zero duplicate copies on rerun, and a simulated multi-row paste to confirm only matching rows get copied. node --check confirmed the exact script above (the one you’d paste in) is syntactically valid. The simple-vs-installable-trigger authorization behavior is Google’s documented behavior, cited above, not something re-derived independently.

Not verified, because there’s no way to in a sandbox: the actual Google authorization consent screen, whether a real installable trigger fires correctly inside the Apps Script runtime, and whether SpreadsheetApp.openById() behaves identically against a real second spreadsheet as it does against the mock. The mock reproduces the documented API surface, but it is a mock — it cannot catch a live-only limitation Google hasn’t documented.

FAQ

Can I copy to a spreadsheet owned by someone else?
Only if the Google account running the script has at least edit access to that spreadsheet. Shared-with-you access is enough; you don’t need to own the file.

Will this work if the source sheet gets sorted or filtered?
Sorting is fine — the marker column travels with its row, so already-copied rows stay marked no matter where they land. A filter view doesn’t hide rows from the script; it still reads every row in the underlying data.

Can I copy to more than one target spreadsheet from the same source?
Yes, but you’ll need to duplicate batchCopyMatchingRows() (or add a loop) with a separate marker column per target, since one marker column can only track “copied to that one destination.”