如何使用Google Apps Script按条件拆分列并将Scan It区域数据分流至Commercial与Manufacturing区域

First, let's break down the key issues in your original script:

• You tried comparing an entire 2D array (scanvalues) directly to a string, which won't work—you need to iterate through each row in the Scan It range
• The methods for getting the last row of data were incorrect (e.g., getRange("L2:N").getLastRow() isn't valid syntax)
• There were typo errors (like sheets instead of sheet, getLastrow instead of getLastRow())
• You didn't account for avoiding duplicate entries when new data comes in via IMPORTRANGE

Here's a working script that handles the data routing correctly, with comments explaining each step:

function routeScanData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet1');
  
  // Define ranges: Scan It (L2:N), Commercial (A3:C), Manufacturing (E3:G), and a status column (O) to track processed rows
  const scanRange = sheet.getRange(2, 12, sheet.getLastRow() - 1, 3); // L2 to N (3 columns total)
  const scanValues = scanRange.getValues();
  const statusRange = sheet.getRange(2, 15, sheet.getLastRow() - 1, 1); // Column O to mark if a row is processed
  const statusValues = statusRange.getValues();
  
  // Get the next empty row for Commercial (A3:C)
  const commLastRow = sheet.getRange("A:A").getValues().filter(String).length + 1;
  const commNextRow = commLastRow < 3 ? 3 : commLastRow;
  
  // Get the next empty row for Manufacturing (E3:G)
  const mfgLastRow = sheet.getRange("E:E").getValues().filter(String).length + 1;
  const mfgNextRow = mfgLastRow < 3 ? 3 : mfgLastRow;
  
  let currentCommRow = commNextRow;
  let currentMfgRow = mfgNextRow;
  
  // Iterate through each row in Scan It
  for (let i = 0; i < scanValues.length; i++) {
    const item = scanValues[i][0]; // Column L: Item #
    const vendor = scanValues[i][1]; // Column M: Vendor
    const quantity = scanValues[i][2]; // Column N: Quantity
    
    // Skip empty rows or already processed rows
    if (!item || statusValues[i][0] === "Processed") continue;
    
    // Route to Manufacturing if Vendor matches your target value (adjust "IMMCO" to "Company" if needed)
    if (vendor === "IMMCO") {
      // Write data to Manufacturing range
      sheet.getRange(currentMfgRow, 5, 1, 3).setValues([[item, vendor, quantity]]);
      currentMfgRow++;
    } else {
      // Write data to Commercial range
      sheet.getRange(currentCommRow, 1, 1, 3).setValues([[item, vendor, quantity]]);
      currentCommRow++;
    }
    
    // Mark this row as processed to avoid duplicates on next run
    statusValues[i][0] = "Processed";
  }
  
  // Update the status column with processed markers
  statusRange.setValues(statusValues);
}

Key Notes for This Script: #

• Avoid Duplicates: We added a status column (Column O) to mark rows as "Processed" once they're routed—this prevents reprocessing the same data when new entries come in via IMPORTRANGE.
• Dynamic Last Rows: Instead of relying on getLastRow() alone (which can include blank rows from IMPORTRANGE), we filter non-empty values to find the actual last row of data in each target column.
• Row Iteration: We loop through each row in the Scan It range, check the Vendor value, and write the data to the correct target range.
• Adjust Condition: If your original rule was to route rows with "Company" to Manufacturing, just replace vendor === "IMMCO" with vendor === "Company".

How to Use: #

1. Add a header "Status" to cell O1 in your Sheet1 (optional, but helps with clarity)
2. Paste this script into your Google Sheet's script editor (Extensions > Apps Script)
3. Run the script manually first to test, or set up a time-driven trigger to run it automatically as new data comes in.

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