自动化填充Google表单：基于关键字驱动与动态数据的方法

import pandas as pd
from selenium import webdriver
from selenium.webdriver.common.by import By
from selenium.webdriver.support.ui import WebDriverWait
from selenium.webdriver.support import expected_conditions as EC
import time

# --- 配置参数 ---
FORM_URL = "YOUR_GOOGLE_FORM_URL_HERE"  # 替换为您的Google表单链接
EXCEL_FILE = "form_data.xlsx"           # 您的Excel数据文件路径
WEBDRIVER_PATH = "path/to/your/chromedriver" # 如果WebDriver不在PATH中，请指定路径

# 建议使用ChromeOptions来配置浏览器，例如无头模式
options = webdriver.ChromeOptions()
# options.add_argument("--headless")  # 启用无头模式，浏览器不会显示界面
options.add_argument("--disable-gpu")
options.add_argument("--no-sandbox")
options.add_argument("--disable-dev-shm-usage")

# --- 表单字段映射（关键部分） ---
# 将Excel列名映射到Google表单的HTML元素定位器。
# 这里使用name属性作为示例，您可能需要根据实际表单调整为ID、XPath或CSS选择器。
# 格式: { "Excel列名": { "locator_type": By.NAME, "locator_value": "entry.YOUR_FORM_FIELD_ID" } }
# 对于多选框和单选框，locator_value通常是其name属性，但值需要单独处理。
FORM_FIELD_MAPPING = {
    "姓名": {"locator_type": By.NAME, "locator_value": "entry.1234567890"}, # 替换为实际name属性
    "邮箱": {"locator_type": By.NAME, "locator_value": "entry.0987654321"},
    "性别": {"locator_type": By.NAME, "locator_value": "entry.1122334455"}, # 单选按钮组的name
    "爱好 (多选)": {"locator_type": By.NAME, "locator_value": "entry.6677889900"}, # 复选框组的name
    "城市": {"locator_type": By.NAME, "locator_value": "entry.5544332211"}, # 下拉菜单的name
}

# 提交按钮的定位器
SUBMIT_BUTTON_LOCATOR = {"locator_type": By.XPATH, "locator_value": "//div[@role='button']//span[text()='提交']"} # 示例XPath，可能需要调整

def fill_form(driver, data_row):
    """
    根据给定的数据行填充Google表单。
    :param driver: Selenium WebDriver实例
    :param data_row: Pandas Series，包含当前行的数据
    """
    print(f"正在填充数据: {data_row['姓名']}")

    for excel_col_name, field_info in FORM_FIELD_MAPPING.items():
        locator_type = field_info["locator_type"]
        locator_value = field_info["locator_value"]
        field_data = str(data_row.get(excel_col_name, "")).strip() # 获取Excel数据，并转为字符串

        if not field_data: # 如果Excel中该字段为空，则跳过
            continue

        try:
            # 处理文本输入框和文本区域
            if excel_col_name in ["姓名", "邮箱"]: # 根据Excel列名判断类型
                element = WebDriverWait(driver, 10).until(
                    EC.presence_of_element_located((locator_type, locator_value))
                )
                element.clear()
                element.send_keys(field_data)

            # 处理单选按钮 (Radio Buttons)
            elif excel_col_name == "性别":
                # Google Forms的单选按钮通常通过value属性来区分选项
                radio_button_xpath = f"//input[@type='radio' and @name='{locator_value}' and @value='{field_data}']"
                radio_element = WebDriverWait(driver, 10).until(
                    EC.presence_of_element_located((By.XPATH, radio_button_xpath))
                )
                if not radio_element.is_selected():
                    radio_element.click()

            # 处理复选框 (Checkboxes)
            elif excel_col_name == "爱好 (多选)":
                # 假设Excel数据用逗号分隔，如 "阅读,编程"
                selected_hobbies = [h.strip() for h in field_data.split(',')]
                for hobby in selected_hobbies:
                    # Google Forms的复选框通常通过value属性来区分选项
                    checkbox_xpath = f"//input[@type='checkbox' and @name='{locator_value}' and @value='{hobby}']"
                    checkbox_element = WebDriverWait(driver, 10).until(
                        EC.presence_of_element_located((By.XPATH, checkbox_xpath))
                    )
                    if not checkbox_element.is_selected():
                        checkbox_element.click()

            # 处理下拉菜单 (Dropdowns)
            elif excel_col_name == "城市":
                # Google Forms的下拉菜单通常是div模拟的，需要点击div，然后点击对应的选项
                # 找到下拉菜单的父元素（通常是一个div，点击它会展开选项）
                dropdown_parent_xpath = f"//div[@role='listbox' and @aria-labelledby='{locator_value}_label']" # 示例XPath，需要根据实际调整
                # 或者直接点击带有name属性的div，如果它直接是下拉菜单

                # 简单处理：找到所有选项并点击匹配的
                # 这是一个通用的方法，但可能需要根据Google Forms的具体实现调整
                # 尝试点击下拉菜单的容器，使其展开
                try:
                    # 尝试定位并点击下拉菜单的显示区域
                    dropdown_display_area = WebDriverWait(driver, 5).until(
                        EC.element_to_be_clickable((By.XPATH, f"//div[@aria-labelledby='{locator_value}_label']"))
                    )
                    dropdown_display_area.click()
                    time.sleep(0.5) # 等待选项展开

                    # 找到所有选项并点击匹配的
                    option_xpath = f"//div[@role='option']//span[text()='{field_data}']"
                    option_element = WebDriverWait(driver, 5).until(
                        EC.element_to_be_clickable((By.XPATH, option_xpath))
                    )
                    option_element.click()
                except Exception as e:
                    print(f"处理下拉菜单 '{excel_col_name}' 失败: {e}")
                    # 如果上述方法不行，可能需要更精确的定位
                    # 例如，直接找到并点击隐藏的select/option元素，或通过JavaScript
                    pass # 继续尝试其他方法或跳过

        except Exception as e:
            print(f"填充字段 '{excel_col_name}' 失败: {e}")
            # 可以选择在这里记录错误，或者跳过当前表单

    # 提交表单
    try:
        submit_button = WebDriverWait(driver, 10).until(
            EC.element_to_be_clickable((SUBMIT_BUTTON_LOCATOR["locator_type"], SUBMIT_BUTTON_LOCATOR["locator_value"]))
        )
        submit_button.click()
        print(f"表单提交成功 for {data_row['姓名']}")
        time.sleep(2) # 等待提交完成或跳转到确认页面

        # 如果Google表单提交后会显示“提交另一份回复”的链接，可以点击它准备下一次提交
        try:
            submit_another_response_link = WebDriverWait(driver, 5).until(
                EC.element_to_be_clickable((By.LINK_TEXT, "提交另一份回复"))
            )
            submit_another_response_link.click()
            time.sleep(1) # 等待页面加载
        except:
            # 如果没有找到“提交另一份回复”链接，则直接重新加载表单URL
            driver.get(FORM_URL)
            time.sleep(1)

    except Exception as e:
        print(f"提交表单失败 for {data_row['姓名']}: {e}")
        # 如果提交失败，可以尝试重新加载页面或跳过
        driver.get(FORM_URL)
        time.sleep(1)


def main():
    try:
        # 读取Excel数据
        df = pd.read_excel(EXCEL_FILE)
        print(f"成功读取 {len(df)} 条数据。")

        # 初始化WebDriver
        # driver = webdriver.Chrome(executable_path=WEBDRIVER_PATH, options=options) # 如果指定了路径
        driver = webdriver.Chrome(options=options) # 如果chromedriver在PATH中

        for index, row in df.iterrows():
            driver.get(FORM_URL) # 每次循环都打开表单链接，确保从头开始
            WebDriverWait(driver, 10).until(EC.presence_of_element_located((By.TAG_NAME, "body"))) # 等待页面加载
            fill_form(driver, row)
            time.sleep(1) # 每次提交后稍作等待

    except FileNotFoundError:
        print(f"错误：Excel文件 '{EXCEL_FILE}' 未找到。请检查文件路径。")
    except Exception as e:
        print(f"发生错误: {e}")
    finally:
        if 'driver' in locals() and driver:
            driver.quit()
            print("浏览器已关闭。")

if __name__ == "__main__":
    main()