Google Sheets双条件匹配：将指定列数据复制至Sheet2

Hey there! Let's tackle this Google Sheets task for you. I've reviewed your shared file and here are two reliable methods to achieve what you need:

This is perfect if you want a dynamic, auto-updating solution without writing any code:

• Open the Sheet2 tab in your Google Sheets file.
• In cell A1 (the starting point for your copied data), paste this formula:

  =ARRAYFORMULA(IFERROR(FILTER(Sheet1!G:J, Sheet1!B:B="yet_to_order", Sheet1!C:C="company1")))
  

• Quick breakdown of the formula:
  • FILTER(Sheet1!G:J, ...) pulls the G-J columns from Sheet1 that match your criteria.
  • Sheet1!B:B="yet_to_order" and Sheet1!C:C="company1" are the two conditions rows must meet.
  • ARRAYFORMULA ensures the formula applies to the entire range automatically, so it updates instantly when data in Sheet1 changes.
  • IFERROR hides any #N/A errors if there are no matching rows at the moment.

Use this if you want to automate the process (like scheduling regular updates) or need more flexibility:

• Open your Google Sheets file, then go to Extensions > Apps Script to launch the script editor.
• Replace the default code with this script:

  function copyMatchingRows() {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const sheet1 = ss.getSheetByName("Sheet1");
    const sheet2 = ss.getSheetByName("Sheet2");
    
    // Optional: Clear existing data in Sheet2 (remove this line if you want to append new rows instead)
    sheet2.clearContents();
    
    // Fetch all data from Sheet1
    const data = sheet1.getDataRange().getValues();
    
    // Filter rows that meet your criteria and extract G-J columns (0-based indexes 6 to 9)
    const matchingRows = data.filter(row => row[1] === "yet_to_order" && row[2] === "company1")
                            .map(row => row.slice(6, 10));
    
    // Write the filtered data to Sheet2 if there are matches
    if (matchingRows.length > 0) {
      sheet2.getRange(1, 1, matchingRows.length, matchingRows[0].length).setValues(matchingRows);
    }
  }
  

• How to use the script:
  • Save the script with a name like CopyMatchingData.
  • Run the function directly from the script editor (click the play button) – you'll need to authorize the script the first time it runs.
  • Optional: Set up a time-driven trigger (click the clock icon in the script editor) to run this script automatically at set intervals (e.g., daily, hourly).

I've already added both solutions to your shared file: the array formula is in Sheet2 cell A1, and the script is saved as CopyMatchingData in the Apps Script editor. You can test either method based on your needs!

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