Google Sheets技术求助：如何复制特定列值符合要求的整行？含列R值为‘open POs’的跨表复制需求

Hey there! Let's tackle your two questions—first the general approach to copying rows based on column criteria, then the specific Google Sheets solution you need.

When you need to copy rows where a column matches certain conditions, here's the core workflow you can apply across most spreadsheet tools:

• First, pinpoint the target column you'll use to check your condition (like Column R in your scenario).
• Define clear criteria that the column's value needs to hit (e.g., exactly 'open POs').
• Go through each row in your source sheet:
  • For every row, check if the value in your target column matches the criteria.
  • If it does, copy the entire row's data to your destination (another sheet, workbook, etc.).
• Depending on the tool, you can use built-in functions (like Filter), manual filtering, or scripting to automate this instead of doing it by hand.

I've got two solid options for you here—one uses built-in functions (no coding needed) and the other uses Apps Script for automation.

Option 1: Use the Filter Function (No Scripting, Auto-Updates) #

This is the easiest way if you want the destination sheet to automatically sync with changes in the source:

1. Open both your source sheet and the destination sheet where you want the rows to go.
2. In the first cell of your destination sheet (e.g., A1), paste this formula:

   =FILTER('Your Source Sheet Name'!A:Z, 'Your Source Sheet Name'!R:R="open POs")
   

   • Replace 'Your Source Sheet Name' with the actual name of your source worksheet (keep the single quotes if the name has spaces).
   • A:Z covers all columns in the source sheet—adjust this range (like A:R) if you only need specific columns copied.
   • Anytime the source sheet updates (new rows, changes to Column R), the destination sheet will automatically update too.

Option 2: Use Google Apps Script (Automated, Manual or Scheduled) #

If you need more control—like appending rows instead of overwriting, or running the copy on a schedule—use this script:

1. Open your source Google Sheet, go to Extensions > Apps Script to open the script editor.
2. Delete the default code and paste this:

   function copyOpenPORows() {
     // Customize these values to match your sheets
     const sourceSheetName = "Source";
     const destinationSheetName = "Open POs";
     const targetColumn = 18; // Column R is the 18th column (A=1, B=2, ...)
     
     const ss = SpreadsheetApp.getActiveSpreadsheet();
     const sourceSheet = ss.getSheetByName(sourceSheetName);
     const destinationSheet = ss.getSheetByName(destinationSheetName);
     
     // Grab all data from the source sheet
     const allData = sourceSheet.getDataRange().getValues();
     
     // Filter rows where Column R is exactly 'open POs'
     const matchingRows = allData.filter(row => row[targetColumn - 1] === "open POs");
     
     // Optional: Clear existing data in destination (remove this line to append new rows)
     destinationSheet.clearContents();
     
     // Paste the filtered rows into the destination sheet
     if (matchingRows.length > 0) {
       destinationSheet.getRange(1, 1, matchingRows.length, matchingRows[0].length).setValues(matchingRows);
     }
   }
   

3. Update the top variables (sourceSheetName, destinationSheetName) to match your actual sheet names.
4. Save the script (click the floppy disk icon) and name it something like "CopyOpenPORows".
5. Run the script by clicking the play button ▶️—you'll need to authorize it the first time (follow the prompts, it's secure since you're running your own custom script).
6. Optional: Set up a scheduled trigger (click the clock icon in the script editor) to run this automatically daily, hourly, or on whatever schedule you need.

内容的提问来源于stack exchange，提问作者Ovais Majid