Google Sheets API首次请求成功后后续返回空值的原因排查

核心问题：代码逻辑错误 #

你的代码中，API请求逻辑被嵌套在凭证更新的if分支内部。只有当凭证不存在、无效、需要刷新或重新获取时，才会执行拉取数据的操作；当token.json有效且未过期时，程序会跳过整个凭证更新的if块，根本不会执行API请求，所以后续运行时没有输出（看起来返回空值）。

修正后的代码 #

将API请求部分移到凭证处理逻辑的外部，确保每次运行main()都会执行数据拉取：

import os

from google.auth.transport.requests import Request
from google.oauth2.credentials import Credentials
from google_auth_oauthlib.flow import InstalledAppFlow
from googleapiclient.discovery import build
from googleapiclient.errors import HttpError

SCOPES = ["https://www.googleapis.com/auth/spreadsheets"]

SPREADSHEET_ID = "<Spreadsheet ID (verified and working)>"


def main():
    credentials = None
    if os.path.exists("token.json"):
        credentials = Credentials.from_authorized_user_file("token.json", SCOPES)
    # 处理凭证更新/获取逻辑
    if not credentials or not credentials.valid:
        if credentials and credentials.expired and credentials.refresh_token:
            credentials.refresh(Request())
        else:
            flow = InstalledAppFlow.from_client_secrets_file("credentials.json", SCOPES)
            credentials = flow.run_local_server(port=0)
        # 保存更新后的凭证
        with open("token.json", "w") as token:
            token.write(credentials.to_json())

    # ========== 移到外部，确保每次运行都执行 ==========
    try:
        service = build("sheets", "v4", credentials=credentials)
        sheets = service.spreadsheets()

        result = sheets.values().get(spreadsheetId=SPREADSHEET_ID, range="Installed!A2:C3").execute()

        values = result.get("values", [])
        
        if not values:
            print("未获取到数据，请检查数据范围或表格内容")
        else:
            for row in values:
                print(row)
                
    except HttpError as error:
        print(error)

def test():
    print('success')

if __name__ == "__main__":
    main()

额外排查建议 #

• 每次运行时可以打印凭证状态，确认是否有效：

  print(f"凭证有效状态: {credentials.valid}")
  print(f"凭证是否过期: {credentials.expired}")
  

• 打印完整的result响应，确认API返回的原始内容，排除表格数据为空的可能：

  print("API返回结果:", result)
  

• 确保Installed工作表名称拼写正确，且A2:C3范围内确实存在数据。

内容的提问来源于stack exchange，提问作者Michael Jason Stokes