如何通过脚本将Google Sheets中Ventas表前3行S列为M的行移至V表

I've put together a Google Apps Script that exactly meets your requirement: it will move the first 3 rows from the "Ventas" tab where column S contains "M" to the "V" tab, while leaving all other matching rows (and non-matching rows) in the original "Ventas" tab.

The Script #

function moveTop3MatchingRows() {
  // Get the active spreadsheet and target sheets
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ventasSheet = ss.getSheetByName("Ventas");
  const vSheet = ss.getSheetByName("V");

  // Fetch all data from Ventas (assuming row 1 is the header)
  const fullData = ventasSheet.getDataRange().getValues();
  const headerRow = fullData[0];
  const dataRows = fullData.slice(1); // Remove header to process rows

  // Separate rows into matching (S = "M") and non-matching
  const matchingRows = dataRows.filter(row => row[18] === "M"); // Column S is index 18 (0-based)
  const nonMatchingRows = dataRows.filter(row => row[18] !== "M");

  // Split matching rows into top 3 and remaining ones
  const top3Matches = matchingRows.slice(0, 3);
  const remainingMatches = matchingRows.slice(3);

  // Reconstruct the new data for Ventas: header + non-matching + remaining matches
  const updatedVentasData = [headerRow, ...nonMatchingRows, ...remainingMatches];

  // Update Ventas sheet with the filtered data
  ventasSheet.clearContents();
  ventasSheet.getRange(1, 1, updatedVentasData.length, updatedVentasData[0].length)
             .setValues(updatedVentasData);

  // Append the top 3 matches to the V sheet
  const vSheetCurrentData = vSheet.getDataRange().getValues();
  // Add header to V sheet if it's empty
  if (vSheetCurrentData.length === 0) {
    vSheet.appendRow(headerRow);
  }
  // Append each of the top 3 rows
  top3Matches.forEach(row => vSheet.appendRow(row));
}

How to Use This Script #

• Open your Google Spreadsheet.
• Click on Extensions > Apps Script to open the script editor.
• Delete any existing code in the editor.
• Paste the script above into the editor.
• Save the script with a name (e.g., MoveTop3Matches).
• Run the function moveTop3MatchingRows—you'll need to authorize the script the first time you run it (follow the prompts to allow access).

Key Notes #

• Column Index: Column S is referenced as index 18 because Google Apps Script uses 0-based indexing (A=0, B=1, ..., S=18). If your sheet structure is different, adjust this number accordingly.
• Headers: The script assumes row 1 is a header row. If your sheet doesn't have headers, remove the lines related to headerRow and adjust dataRows to be fullData instead of fullData.slice(1).
• Edge Cases: If there are fewer than 3 matching rows, the script will just move all available matching rows to the "V" tab, and leave nothing extra in Ventas.
• Data Preservation: The script clears the contents of Ventas and rewrites the filtered data—this preserves formatting (like cell colors) as long as you don't use clear() (which clears formatting too).

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