使用列表/字典更新Google Sheets每行数据时仅覆盖首行的问题

我尝试用列表/字典中的元素更新表格的每一行，但所有值仅覆盖更新了指定区域的第一行单元格（A2:G2），希望将数据以列表/表格形式逐行写入。以下是当前代码：

from selenium.webdriver.support.ui import WebDriverWait
from selenium.webdriver.support import expected_conditions as EC
from selenium.webdriver.common.by import By
import gspread
import time
import datetime
import re

driver = uc.Chrome(use_subprocess=True)
    

while True:

    gc = gspread.service_account(filename='creds.json')

    sh = gc.open('Luciana-2022')

    oos = sh.worksheet("OOS")
    
    time.sleep(10)

    walmartLinks = ["https://www.walmart.com/ip/Motioneaze-Motion-Sickness-Relief-Topical-Oil-08-fl-Oz-20-application/12346124", "https://www.walmart.com/ip/Penn-Championship-Extra-Duty-Tennis-Ball-Pack-6-Cans-18-Balls-Pressurized-Suitable-for-Hard-Tennis-Courts/11186275", "https://www.walmart.com/ip/Woolite-Damage-Defense-Liquid-Laundry-Detergent-66-Loads-Regular-and-HE-Washers-100-Fl-Oz-Packaging-may-vary/15610733", "https://www.walmart.com/ip/Mott-s-100-Original-Apple-Juice-8-fl-oz-bottles-6-pack/36874000", "https://www.walmart.com/ip/Butternut-Mountain-Farm-100-Pure-Vermont-Maple-Syrup-32-fl-oz/49058126", "https://www.walmart.com/ip/Queen-Helene-Cocoa-Butter-Hand-Body-Lotion-16-oz/25991731"]
    data_list = []

    for index, item in enumerate(walmartLinks):
        driver.get(item)
        time.sleep(5)

        try:
            time.sleep(10)
            WebDriverWait(driver, 30).until(EC.presence_of_element_located((By.XPATH, '//div[@data-testid="fulfillmentLabel-1P" and contains(., "arrives by")]')))
            stockValue = "In Stock"
        except:
            stockValue = "Out of Stock"
            pass

        try:
            soldby = driver.find_element(By.XPATH, '//div[@class="ml2"]')
            soldbyValue = soldby.get_attribute("innerText")
        except:
            soldbyValue = "No seller"
            pass

        try:
            title = driver.find_element(By.XPATH, '//h1[@itemprop="name"]')
            titleValue = title.get_attribute("innerText")
        except:
            titleValue = "Product not found"
            pass
        
        try:
            upc = driver.find_element(By.XPATH, '//script[contains(text(),"@type")][1]')
            upcValue = upc.get_attribute("innerText")
            gtin = re.sub('^(.*"gtin13":"',"", upcValue).split('"')[0]
        except:
            gtin = "UPC Not Available"
            pass

        search_it_on_Amazon = "https://www.amazon.com/s?k=" + gtin + "&s=review-rank"
        url = driver.current_url
        dateUpdated = datetime.datetime.now().strftime("%c")

        fields = {
                "stock": stockValue,
                "soldby": soldbyValue,
                "title": titleValue,
                'url': url,
                'date': dateUpdated,
                'upc': gtin,
                'search_it_on_Amazon': search_it_on_Amazon
            }
        data_list.append(fields)

        # 计算当前行号：从第2行开始，第index个元素对应index+2行
        row_num = index + 2
        # 生成当前行的范围，比如第2行是A2:G2，第3行是A3:G3
        cell_range = f'A{row_num}:G{row_num}'
        cell_list = oos.range(cell_range)
        
        cell_values = [fields['title'], fields['stock'], fields['soldby'], fields['url'], fields['upc'], fields['search_it_on_Amazon'], fields['date']]

        for i, val in enumerate(cell_values):
            cell_list[i].value = val
        oos.update_cells(cell_list)   
       
    continue

• 核心问题：原代码每次循环都固定选择A2:G2范围，导致所有数据都覆盖到第一行。
• 修复方案：
  1. 用enumerate遍历walmartLinks，获取每个元素的索引index
  2. 根据索引计算当前写入行号：row_num = index + 2（从第2行开始写入）
  3. 动态生成当前行的范围字符串，比如f'A{row_num}:G{row_num}'
  4. 把update_cells移到单元格赋值循环外，一次性更新整行，减少不必要的API调用

如果数据量较大，建议先收集所有行数据，再批量写入，提升效率：

# 收集所有行数据
all_rows = []
for item in walmartLinks:
    # ... 省略爬取数据的代码 ...
    fields = { ... }
    all_rows.append([fields['title'], fields['stock'], fields['soldby'], fields['url'], fields['upc'], fields['search_it_on_Amazon'], fields['date']])

# 批量写入从第2行开始的区域
oos.update(f'A2:G{len(all_rows)+1}', all_rows)

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