Google Sheets AI：智能表格应用

# 模拟 Google Sheets AI 公式生成管线def generate_sheets_formula(user_input, sheet_context):    col_desc = ', '.join([f"{c['name']}({c['type']})" for c in sheet_context['columns']])    context_prompt = f"将以下自然语言请求转换为 Google Sheets 公式:\n    请求: {user_input}\n    可用列: {col_desc}\n    仅输出公式。"    if '销售' in user_input and '总和' in user_input:        formula = '=SUM(FILTER(D2:D100, A2:A100="华东区"))'    elif 'QUERY' in user_input:        formula = '=QUERY(A1:F1000, "SELECT A, D WHERE B = \\"华东\\" ORDER BY D DESC", 1)'    else:        formula = '=AVERAGEIF(B2:B100, "完成", D2:D100)'    valid_functions = {'SUM', 'FILTER', 'QUERY', 'AVERAGEIF', 'IF', 'VLOOKUP'}    is_valid = any(f in formula for f in valid_functions)    explanation = f"此公式使用{formula.split('(')[0].replace('=','')}函数进行条件计算"    return {'formula': formula, 'is_valid': is_valid, 'explanation': explanation} ctx = {'columns': [{'name': '地区', 'type': 'text'}, {'name': '产品', 'type': 'text'}, {'name': '销售额', 'type': 'number'}]}result = generate_sheets_formula('计算华东区销售总和', ctx)print(result)

# 模拟 Apps Script 调用 Gemini API 的自动化数据管道import jsonfrom datetime import datetime class AppsScriptGeminiPipeline:    def __init__(self, api_key='mock_key'):        self.api_key = api_key        self.sheet_data = []        def fetch_crm_data(self):        self.sheet_data = [            {'date': '2024-01-15', 'region': '华东', 'amount': 150000},            {'date': '2024-01-15', 'region': '华北', 'amount': 89000},            {'date': '2024-01-16', 'region': '华东', 'amount': 23000},            {'date': '2024-01-16', 'region': '华南', 'amount': 175000},        ]        return self.sheet_data        def call_gemini_analysis(self, data):        total = sum(d['amount'] for d in data)        avg = total / len(data)        anomaly = [d for d in data if d['amount'] < avg * 0.5]        summary = f"共{len(data)}条记录，总额{total}元，平均{avg:.0f}元"        if anomaly:            summary += f"，发现{len(anomaly)}条异常低值记录"        return {'summary': summary, 'anomalies': anomaly}        def generate_report(self, analysis):        return f"日报 - {datetime.now().strftime('%Y-%m-%d')}\n{analysis['summary']}" pipeline = AppsScriptGeminiPipeline()data = pipeline.fetch_crm_data()analysis = pipeline.call_gemini_analysis(data)report = pipeline.generate_report(analysis)print(report)

import jsonfrom collections import defaultdict class SheetsAIFormulaEngine:    SHEETS_FUNCTIONS = {        'SUM': {'min_args': 1, 'max_args': None},        'FILTER': {'min_args': 2, 'max_args': None},        'QUERY': {'min_args': 1, 'max_args': 3},        'AVERAGEIF': {'min_args': 2, 'max_args': 3},        'VLOOKUP': {'min_args': 3, 'max_args': 4},        'IF': {'min_args': 2, 'max_args': 3},    }        def __init__(self):        self.generation_log = []        self.column_context = {}        def set_sheet_context(self, columns):        self.column_context = {col['name']: col for col in columns}        return self        def generate_formula(self, natural_language_input):        if '总和' in natural_language_input or '合计' in natural_language_input:            formula = '=SUM(D2:D100)'            func_used = 'SUM'        elif '筛选' in natural_language_input or '过滤' in natural_language_input:            formula = '=FILTER(A2:D100, B2:B100="华东")'            func_used = 'FILTER'        elif '查询' in natural_language_input or 'QUERY' in natural_language_input:            formula = '=QUERY(A1:F1000, "SELECT * WHERE B = \\"华东\\"", 1)'            func_used = 'QUERY'        elif '查找' in natural_language_input:            formula = '=VLOOKUP("产品A", A2:D100, 4, FALSE)'            func_used = 'VLOOKUP'        else:            formula = '=AVERAGEIF(B2:B100, "完成", D2:D100)'            func_used = 'AVERAGEIF'        self.generation_log.append({'input': natural_language_input, 'formula': formula})        return {'formula': formula, 'function': func_used}        def validate_formula(self, formula_result):        func = formula_result['function']        if func in self.SHEETS_FUNCTIONS:            return {'valid': True, 'function': func, 'message': '函数验证通过'}        return {'valid': False, 'function': func, 'message': f'未知函数: {func}'}        def generate_explanation(self, formula_result):        explanations = {            'SUM': '对指定范围的数值求和',            'FILTER': '根据条件筛选数据行',            'QUERY': '使用类SQL语法查询和转换数据',            'VLOOKUP': '在表格首列查找值并返回同行其他列的数据',            'AVERAGEIF': '对满足条件的单元格计算平均值',        }        func = formula_result['function']        return explanations.get(func, '执行计算操作') engine = SheetsAIFormulaEngine()engine.set_sheet_context([    {'name': '地区', 'type': 'text'},    {'name': '产品', 'type': 'text'},    {'name': '状态', 'type': 'text'},    {'name': '销售额', 'type': 'number'}])result1 = engine.generate_formula('计算华东区销售总和')valid1 = engine.validate_formula(result1)expl1 = engine.generate_explanation(result1)print(f'公式: {result1["formula"]}')print(f'验证: {valid1["message"]}')print(f'解释: {expl1}')result2 = engine.generate_formula('QUERY查询华东区数据')valid2 = engine.validate_formula(result2)print(f'公式: {result2["formula"]}')print(f'验证: {valid2["message"]}')print(f'生成日志: {len(engine.generation_log)}条')