如何实现Python脚本写入Google Sheets前检查重复行并更新最高价

Hey Ben, let's tackle that duplicate entry problem and add the price update logic you need. Your original check wasn't working because you were converting the listing into a mismatched dictionary that didn't align with the sheet's structure—let's fix that with a more reliable approach.

The Core Issues with Your Original Check #

• Your dict(zip(i,i)) code was pairing consecutive list items as key-value pairs (e.g., title as key, img['src'] as value), which doesn't match the structured records from get_all_records() (which uses the sheet's headers as keys).
• You weren't using a unique identifier (like VIN) to detect duplicates, which is critical because some fields (like auction timing) might change even for the same vehicle.

Step-by-Step Solution #

We'll use VIN (Vehicle Identification Number) as the unique key (since it's guaranteed unique per vehicle) to check for existing entries. For duplicates, we'll compare prices and update only if the new price is higher.

1. Pre-Fetch Existing Sheet Data for Quick Lookup

Before processing new vehicles, grab the sheet's headers and existing records, then build a lookup dictionary for fast duplicate checks:

# Get headers and existing records from the sheet (run this once before the loop)
headers = sheet2.row_values(1)
existing_records = sheet2.get_all_records()

# Create a lookup dict: key = VIN, value = {row index, current price}
existing_vehicles = {}
for row_idx, record in enumerate(existing_records, start=2):  # rows start at 2 (header is row 1)
    vehicle_vin = record.get('VIN', '')
    if vehicle_vin:  # Only use vehicles with a valid VIN
        current_price = record.get('Price', '')
        existing_vehicles[vehicle_vin] = {
            'row_index': row_idx,
            'current_price': current_price
        }

2. Modify Your Vehicle Processing Loop

Replace your existing print/insert logic with this code that handles duplicates and price updates:

# Inside your for loop processing each vehicle:

# Process the new price (convert to numeric for comparison)
new_price_str = '£' + bid.attrs['amount'][:-3]
new_price = float(new_price_str.replace('£', ''))

# Check if the vehicle exists via VIN
if vin in existing_vehicles:
    # Vehicle exists—check if we need to update the price
    existing_price_data = existing_vehicles[vin]
    existing_price_str = existing_price_data['current_price']
    
    # Convert existing price to numeric (handle empty cases)
    existing_price = float(existing_price_str.replace('£', '')) if existing_price_str else 0
    
    if new_price > existing_price:
        # Update the price in the existing row
        price_column_idx = headers.index('Price') + 1  # gspread uses 1-based indexing
        sheet2.update_cell(existing_price_data['row_index'], price_column_idx, new_price_str)
        print(f"Updated price for {title} (VIN: {vin}): {existing_price_str} → {new_price_str}")
    else:
        print(f"No update needed for {title} (VIN: {vin})—current price is not lower than new price")
else:
    # Vehicle is new—insert at the top (row 2)
    row = [title, img['src'], video, vin, loc, exterior_colour, interior_colour, 'N/A', mileage, gearbox, 'N/A', 'Live', auction_date, '', new_price_str, 'The Market', '', '', '', '', year, make, model, variant]
    sheet2.insert_row(row, 2)
    print(f"Added new vehicle: {title} (VIN: {vin})")

Key Improvements #

• Fast Duplicate Checks: Using a dictionary lookup (O(1) time) instead of looping through all rows (O(n) time) makes the script much faster as your sheet grows.
• Reliable Duplicate Detection: VIN is a unique identifier, so we won't miss duplicates even if other fields (like auction date) change.
• Price Update Logic: We convert prices to numeric values to accurately compare and only update when the new price is higher.
• Clear Feedback: The print statements let you track what's being added or updated.

Notes #

• Make sure your sheet's header for the price column is exactly Price (case-sensitive) for headers.index('Price') to work. Adjust the string if your header has a different name (e.g., Current Bid).
• If some vehicles don't have a VIN, you can fall back to a combination of title + mileage as a secondary unique key, but VIN is always preferable.

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