如何用Python通过Google API获取用户认证邮箱并匹配Sheets数据

1. 安装兼容Python 3.4的依赖库 #

因为Python 3.4.10版本较老，需指定兼容的库版本：

pip install google-auth-oauthlib==0.4.6 google-api-python-client==2.31.0 google-auth-httplib2==0.1.0

2. 认证时获取用户邮箱 #

在OAuth2认证流程中，添加https://www.googleapis.com/auth/userinfo.email权限，即可获取已登录用户的邮箱信息。以下是完整认证+获取邮箱的代码：

import google.auth
from google_auth_oauthlib.flow import InstalledAppFlow
from googleapiclient.discovery import build
from google.auth.transport.requests import Request
import os.path
import pickle

# 定义所需权限：Sheets只读+用户邮箱获取
SCOPES = [
    'https://www.googleapis.com/auth/spreadsheets.readonly',
    'https://www.googleapis.com/auth/userinfo.email'
]

def get_user_email_and_credentials():
    creds = None
    # 加载已保存的凭证（如果存在）
    if os.path.exists('token.pickle'):
        with open('token.pickle', 'rb') as token:
            creds = pickle.load(token)
    # 无有效凭证则重新认证
    if not creds or not creds.valid:
        if creds and creds.expired and creds.refresh_token:
            creds.refresh(Request())
        else:
            flow = InstalledAppFlow.from_client_secrets_file(
                'credentials.json', SCOPES)
            creds = flow.run_local_server(port=0)
        # 保存凭证供下次使用
        with open('token.pickle', 'wb') as token:
            pickle.dump(creds, token)
    
    # 从id_token中解析用户邮箱
    id_info = creds.id_token
    user_email = id_info['email']
    return creds, user_email

3. 筛选Google Sheets中对应邮箱的行 #

拿到邮箱后，调用Sheets API获取数据并筛选匹配行：

def get_matching_row(spreadsheet_id, range_name, user_email):
    creds, _ = get_user_email_and_credentials()
    service = build('sheets', 'v4', credentials=creds)
    
    # 获取表格数据
    sheet = service.spreadsheets()
    result = sheet.values().get(spreadsheetId=spreadsheet_id, range=range_name).execute()
    values = result.get('values', [])
    
    if not values:
        return None
    # 遍历行匹配邮箱，返回对应列内容
    for row in values:
        if len(row) >= 2 and row[0] == user_email:
            return row[1]
    return None

# 示例调用
if __name__ == '__main__':
    # 替换为你的表格ID和数据范围
    SPREADSHEET_ID = '你的Google表格ID'
    RANGE_NAME = 'Sheet1!A:B'
    _, user_email = get_user_email_and_credentials()
    matching_value = get_matching_row(SPREADSHEET_ID, RANGE_NAME, user_email)
    print(matching_value)  # personB登录时会输出bananas

注意事项 #

• 确保credentials.json是从Google Cloud Console下载的OAuth2桌面应用客户端密钥
• 首次运行会弹出浏览器让用户登录授权，授权后凭证保存到token.pickle，后续无需重复登录
• 若表格数据量极大，建议改用Sheets API的spreadsheets.values.query方法在云端筛选，减少本地数据传输

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