Python调用Google Apps Script实现免认证自动化执行

import os.path
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 import errors

# 定义API权限范围（Scopes）。请根据您的Apps Script实际操作选择最小必要的权限。
# 示例中包含了常见的Apps Script、Drive和Sheets操作权限。
SCOPES = [
    "https://www.googleapis.com/auth/script.projects",         # 管理Apps Script项目
    "https://www.googleapis.com/auth/script.external_request", # 允许Apps Script进行外部请求
    "https://www.googleapis.com/auth/drive.readonly",          # 读取Google Drive文件
    "https://www.googleapis.com/auth/drive",                   # 修改Google Drive文件
    "https://www.googleapis.com/auth/spreadsheets",            # 操作Google Sheets
    # 根据Apps Script的实际功能，可能还需要其他权限，例如：
    # "https://www.googleapis.com/auth/script.scriptapp",      # 允许Apps Script访问自身环境
    # "https://www.googleapis.com/auth/script.processes",      # 管理Apps Script进程
]

def authenticate_and_run_apps_script(script_id: str, function_name: str):
    """
    处理Google Apps Script的认证流程并执行指定函数。
    如果token.json不存在或过期，将引导用户进行认证并保存凭据。
    """
    creds = None
    token_file = "token.json"
    credentials_file = "credentials.json" # 从Google Cloud下载的客户端密钥文件

    # 1. 尝试从token.json加载已保存的凭据
    if os.path.exists(token_file):
        creds = Credentials.from_authorized_user_file(token_file, SCOPES)

    # 2. 如果凭据不存在、无效或已过期，则进行认证流程
    if not creds or not creds.valid:
        if creds and creds.expired and creds.refresh_token:
            # 凭据过期但有刷新令牌，尝试自动刷新
            print("凭据已过期，尝试使用刷新令牌更新...")
            try:
                creds.refresh(Request())
            except Exception as refresh_error:
                print(f"刷新令牌失败: {refresh_error}。将进行重新认证。")
                # 刷新失败，需要重新进行交互式认证
                flow = InstalledAppFlow.from_client_secrets_file(credentials_file, SCOPES)
                creds = flow.run_local_server(port=0)
        else:
            # 首次认证或刷新令牌无效，进行交互式认证
            print("进行首次认证或重新认证，请在浏览器中完成授权...")
            flow = InstalledAppFlow.from_client_secrets_file(credentials_file, SCOPES)
            creds = flow.run_local_server(port=0)

        # 3. 将新获取或刷新的凭据保存到token.json，以便后续使用
        with open(token_file, "w") as token:
            token.write(creds.to_json())
        print(f"凭据已成功保存到 {token_file}")

    try:
        # 4. 构建Apps Script API服务客户端
        service = build("script", "v1", credentials=creds)

        # 5. 准备Apps Script函数的执行请求
        request_body = {"function": function_name}

        print(f"正在执行Apps Script函数: '{function_name}' (部署ID: {script_id})...")
        # 6. 执行Apps Script函数
        response = service.scripts().run(body=request_body, scriptId=script_id).execute()

        # 7. 处理Apps Script函数的执行结果
        if 'error' in response:
            # Apps Script内部执行失败
            error_details = response['error']['details']
            print(f"Apps Script执行失败: {error_details}")
            # 可以在这里解析error_details以获取更详细的错误信息
            for detail in error_details:
                print(f"  错误类型: {detail.get('errorType')}, 消息: {detail.get('errorMessage')}")
        else:
            # Apps Script函数执行成功
            print(f"Apps Script函数 '{function_name}' 执行成功！")
            # 如果Apps Script函数有返回值，可以通过response['response']['result']获取
            if 'response' in response and 'result' in response['response']:
                print(f"函数返回结果: {response['response']['result']}")

    except errors.HttpError as error:
        # Google API调用本身发生错误（例如权限不足、API不可用、script_id错误等）
        print(f"Google API调用发生错误: {error}")
        print(f"错误内容: {error.content.decode('utf-8')}")
    except Exception as e:
        # 捕获其他未知异常
        print(f"发生未知错误: {e}")

if __name__ == "__main__":
    # 请替换为您的Apps Script部署ID和要执行的函数名
    YOUR_SCRIPT_ID = "AKfycbxtDnDYa2mTZKB6WoqK_D9PDsLZyqb7GQAh7pvER-K-rMFXYNa6oVOhzXHsyfyl8vLz" # 示例ID
    YOUR_FUNCTION_NAME = "helloWorld" # 示例Apps Script函数名

    # 首次运行时，会弹出浏览器窗口进行认证。认证成功后，会在当前目录生成token.json。
    # 后续运行将直接使用token.json进行认证，无需人工干预。
    authenticate_and_run_apps_script(YOUR_SCRIPT_ID, YOUR_FUNCTION_NAME)

function helloWorld() {
  Logger.log("Hello from Apps Script!");
  return "Hello from Apps Script!";
}