在Google Apps Script中对比两行单元格内容并提取差异项

Got it, here's a straightforward solution to compare your two rows of comma-separated values and pull out the unique entries as you need.

Step-by-Step Solution Code #

function compareRows() {
  // Access the active sheet (replace with getSheetByName("YourSheetName") if needed)
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  
  // Fetch values from A1 and A2, clean whitespace, and split into arrays
  const rowA1 = sheet.getRange("A1").getValue().trim().split(",");
  const rowA2 = sheet.getRange("A2").getValue().trim().split(",");
  
  // Filter items that exist only in A1
  const onlyInA1 = rowA1.filter(item => !rowA2.includes(item.trim()));
  
  // Filter items that exist only in A2
  const onlyInA2 = rowA2.filter(item => !rowA1.includes(item.trim()));
  
  // Write results back to A3 and A4 as comma-separated strings
  sheet.getRange("A3").setValue(onlyInA1.join(","));
  sheet.getRange("A4").setValue(onlyInA2.join(","));
}

How to Use This Script #

1. Open your Google Sheet with the values in A1 and A2.
2. Click Extensions > Apps Script to launch the script editor.
3. Delete any default code, then paste the script above.
4. Save the project (name it something like "RowValueComparator").
5. Hit the run button ▶️, and authorize the script when prompted (it only accesses your current sheet, so it's safe).

Key Features #

• Whitespace Handling: The trim() method takes care of any accidental spaces around values (like " Shyam ") to prevent mismatches.
• Simple Filter Logic: Uses filter() and includes() to quickly check which items don't exist in the other row's array.
• Flexibility: If your sheet has a specific name, swap getActiveSheet() with getSheetByName("YourSheetName") for more consistent results.

Test Case Outcome #

For your provided values:

• A1 = Ram,shyam,Steve,Jack
• A2 = Ram,Tom,Steve,Michael,Linda

The script will output:

• A3: shyam,Jack
• A4: Tom,Michael,Linda

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