You need to enable JavaScript to run this app.
优惠活动
大模型
产品
解决方案
定价
更多

Python操作Google Sheets：B列时间格式设置失效问题求助

avatar
AI 小北
2026-7-28
Google Sheets B列时间格式设置失效问题排查与解决

问题描述

我编写了一个抓取Forex Factory日历数据并导入Google Sheets的Python脚本，想要将B列设置为hh:mm AM/PM的时间格式，但尝试的代码始终无效，B列会自动变回"自动格式"。

尝试的格式设置代码：

# Set the time format for the second column of the sheet
date_column = sheet.range('B:B')
for cell in date_column:
    cell.number_format = 'hh:mm AM/PM'
sheet.update_cells(date_column)

完整脚本：

import random
import gspread
from selenium import webdriver
from selenium.webdriver.chrome.options import Options
from selenium.webdriver.common.by import By
from google.oauth2.service_account import Credentials
import time

# Define your Google Sheets credentials
creds = Credentials.from_service_account_file('keys.json')
scopes = ['https://www.googleapis.com/auth/spreadsheets', 'https://www.googleapis.com/auth/drive']
creds = creds.with_scopes(scopes)

# Authenticate with Google Drive API
gc = gspread.authorize(creds)

# Open the Google Sheets workbook
workbook = gc.open('forexfactorycalendar')
worksheet = workbook.worksheet('Sheet1')


# Specify the name of the sheet you want to write to
sheet_name = 'forexfactorycalendar'

# Open the specified sheet
sheet = gc.open(sheet_name).sheet1

def create_driver():
    user_agent_list = [
        'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/92.0.4515.131 Safari/537.36',
        'Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:90.0) Gecko/20100101 Firefox/90.0',
        'Mozilla/5.0 (Macintosh; Intel Mac OS X 11.5; rv:90.0) Gecko/20100101 Firefox/90.0',
        'Mozilla/5.0 (Windows NT 10.0) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/92.0.4515.131 Safari/537.36',
        'Mozilla/5.0 (Macintosh; Intel Mac OS X 11_5_1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/92.0.4515.131 Safari/537.36',
        'Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:90.0) Gecko/20100101 Firefox/90.0',
        'Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/92.0.4515.131 Safari/537.36'
    ]
    user_agent = random.choice(user_agent_list)

    browser_options = webdriver.ChromeOptions()
    browser_options.add_argument("--no-sandbox")
    browser_options.add_argument("--headless")
    browser_options.add_argument("start-maximized")
    browser_options.add_argument("window-size=1900,1080")
    browser_options.add_argument("disable-gpu")
    browser_options.add_argument("--disable-software-rasterizer")
    browser_options.add_argument("--disable-dev-shm-usage")
    browser_options.add_argument(f'user-agent={user_agent}')

    driver = webdriver.Chrome(options=browser_options, service_args=["--verbose", "--log-path=test.log"])

    return driver

def parse_data(driver, url):
    driver.get(url)

    data_table = driver.find_element(By.CLASS_NAME, "calendar__table")
    value_list = []

    for row in data_table.find_elements(By.TAG_NAME, "tr"):
        row_data = list(filter(None, [td.text for td in row.find_elements(By.TAG_NAME, "td")]))
        if row_data:
            impact_element = row.find_element(By.CLASS_NAME, "calendar__impact")
            impact_class = impact_element.get_attribute("class")
            if "impact--low" in impact_class:
                impact = "low"
            elif "impact--medium" in impact_class:
                impact = "medium"
            elif "impact--high" in impact_class:
                impact = "high"
            else:
                impact = ""
            row_data.append(impact)
            value_list.append(row_data)
    return value_list

driver = create_driver()
url = 'https://www.forexfactory.com/calendar'
value_list = parse_data(driver=driver, url=url)

# Clear existing data from sheet
sheet_range = sheet.range('A1:I' + str(len(value_list) + 1))
for cell in sheet_range:
    cell.value = ''
if sheet_range:
    sheet.update_cells(sheet_range)
    
# Set the time format for the first column of the sheet
date_column = sheet.range('B:B')
for cell in date_column:
    cell.number_format = 'hh:mm AM/PM'
sheet.update_cells(date_column)    

# Write the new data to the sheet
for value_index, value in enumerate(value_list):
    if '\n' in value[0]:
        date_str = value.pop(0).replace('\n', ' - ')
    row_index = value_index + 1

    
    # Write the impact value to column I
    worksheet.update_cell(row_index, 9, value[-1])
    
    # Write the remaining values to columns A-H
    worksheet.update(f'A{row_index}:I{row_index}', [[date_str] + value])
    
    # Add a delay of 1 second between requests
    time.sleep(1)

预期效果：B列显示为hh:mm AM/PM格式的时间（如08:30 AM）

问题原因

  1. 格式设置顺序错误：先设置B列格式再写入数据，gspread的update方法默认使用USER_ENTERED模式，Google Sheets会自动根据输入字符串推断单元格格式，直接覆盖之前设置的时间格式。
  2. 工作表对象混淆：脚本同时使用sheet和worksheet两个对象指向同一工作表，写入数据用worksheet、设置格式用sheet，逻辑冗余且加剧格式覆盖问题。
  3. 全列格式设置效率低：遍历整个B列（含空白单元格）设置格式，后续写入数据时空白单元格格式会被新内容覆盖。

解决方案

修复步骤

  1. 调整顺序：先写数据再设格式：确保数据写入完成后统一设置B列时间格式，避免被写入操作覆盖。
  2. 统一工作表对象：删除冗余定义，全程使用同一个对象操作。
  3. 优化格式范围：仅针对有数据的B列单元格设置格式，减少无效操作。

修复后的完整脚本 

import random
import gspread
from selenium import webdriver
from selenium.webdriver.chrome.options import Options
from selenium.webdriver.common.by import By
from google.oauth2.service_account import Credentials
import time

# 初始化Google Sheets凭证
creds = Credentials.from_service_account_file('keys.json')
scopes = ['https://www.googleapis.com/auth/spreadsheets', 'https://www.googleapis.com/auth/drive']
creds = creds.with_scopes(scopes)
gc = gspread.authorize(creds)

# 打开目标工作表（统一使用worksheet对象）
workbook = gc.open('forexfactorycalendar')
worksheet = workbook.worksheet('Sheet1')

def create_driver():
    user_agent_list = [
        'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/92.0.4515.131 Safari/537.36',
        'Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:90.0) Gecko/20100101 Firefox/90.0',
        'Mozilla/5.0 (Macintosh; Intel Mac OS X 11.5; rv:90.0) Gecko/20100101 Firefox/90.0',
        'Mozilla/5.0 (Windows NT 10.0) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/92.0.4515.131 Safari/537.36',
        'Mozilla/5.0 (Macintosh; Intel Mac OS X 11_5_1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/92.0.4515.131 Safari/537.36',
        'Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:90.0) Gecko/20100101 Firefox/90.0',
        'Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/92.0.4515.131 Safari/537.36'
    ]
    user_agent = random.choice(user_agent_list)

    browser_options = webdriver.ChromeOptions()
    browser_options.add_argument("--no-sandbox")
    browser_options.add_argument("--headless")
    browser_options.add_argument("start-maximized")
    browser_options.add_argument("window-size=1900,1080")
    browser_options.add_argument("disable-gpu")
    browser_options.add_argument("--disable-software-rasterizer")
    browser_options.add_argument("--disable-dev-shm-usage")
    browser_options.add_argument(f'user-agent={user_agent}')

    driver = webdriver.Chrome(options=browser_options, service_args=["--verbose", "--log-path=test.log"])
    return driver

def parse_data(driver, url):
    driver.get(url)
    data_table = driver.find_element(By.CLASS_NAME, "calendar__table")
    value_list = []

    for row in data_table.find_elements(By.TAG_NAME, "tr"):
        row_data = list(filter(None, [td.text for td in row.find_elements(By.TAG_NAME, "td")]))
        if row_data:
            impact_element = row.find_element(By.CLASS_NAME, "calendar__impact")
            impact_class = impact_element.get_attribute("class")
            if "impact--low" in impact_class:
                impact = "low"
            elif "impact--medium" in impact_class:
                impact = "medium"
            elif "impact--high" in impact_class:
                impact = "high"
            else:
                impact = ""
            row_data.append(impact)
            value_list.append(row_data)
    return value_list

# 抓取数据
driver = create_driver()
url = 'https://www.forexfactory.com/calendar'
value_list = parse_data(driver=driver, url=url)
driver.quit()  # 抓取完成后关闭浏览器，释放资源

# 清空现有数据
row_count = len(value_list)
if row_count > 0:
    sheet_range = worksheet.range('A1:I' + str(row_count))
    for cell in sheet_range:
        cell.value = ''
    worksheet.update_cells(sheet_range)

# 写入新数据
for value_index, value in enumerate(value_list):
    if '\n' in value[0]:
        date_str = value.pop(0).replace('\n', ' - ')
    row_index = value_index + 1
    # 写入整行数据（避免分开写入导致的格式问题）
    worksheet.update(f'A{row_index}:I{row_index}', [[date_str] + value])
    time.sleep(1)

# 数据写入完成后，设置B列有数据单元格的时间格式
if row_count > 0:
    date_column = worksheet.range(f'B1:B{row_count}')
    for cell in date_column:
        cell.number_format = 'hh:mm AM/PM'
    worksheet.update_cells(date_column)

额外说明

  • 如果抓取的时间字符串无法被Google Sheets识别为时间类型，需先将字符串转换为Python的datetime对象再写入，确保Google Sheets能自动识别为时间后再设置格式。
  • 新增driver.quit()在抓取完成后关闭浏览器，避免资源泄漏。

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

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分)， 限时 9.9 元起

产品介绍相关文档
相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分)， 限时 9.9 元起

产品介绍相关文档
火山引擎 最新活动
全面开放
Doubao-Seedance-2.5全新模型升级

模型再升级,30秒超长叙事, 模态参考扩容

HOT
方舟Codingplan

模型自由，工具不限，最新支持 Deepseek-V4 系列、GLM-5.3 系列

HOT
方舟Agent plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列

HOT
新用户特惠专场

大模型19元起，Al应用9.9元畅享，新人首购爆款尽享优惠

最近更新时间：2026.07.28 23:17:37