AI_TABLE

=AI_TABLE(prompt, [header], [source], [temperature], [max_tokens], [model], [api_key], [api_url])

=AI_TABLE("Create a table listing the features of 4 different smartphones including brand, model, camera quality, battery life.")

=AI_TABLE("Generate a table of top 5 tourist destinations.", A1:D1)

=AI_TABLE("Summarize the sales data by product category.", , A1:C8)

import requests
import json
 
def ai_table(prompt, header=None, source=None, temperature=0.0, max_tokens=1500, model="mistral-small-latest", api_key=None, api_url=None):
    """
    Uses an AI model to generate a structured table (2D list) based on a prompt, with optional header and source data.
 
    Args:
        prompt: Instruction for the AI to create a table.
        header: 2D list containing table header (column names), optional.
        source: 2D list containing source data for the table, optional.
        temperature: Controls response creativity (0-2). Default is 0.0.
        max_tokens: Maximum tokens for response generation. Default is 1500.
        model: ID of the model to use. Default is "mistral-small-latest".
        api_key: API key for authentication, optional.
        api_url: OpenAI-compatible API endpoint URL, optional.
 
    Returns:
        2D list representing the generated table data, or a 2D list with a single error string on failure.
 
    This example function is provided as-is without any representation of accuracy.
    """
    if api_key is None or api_url is None:
        if "idToken" in globals():
            api_key = globals()["idToken"]
            api_url = "https://llm.boardflare.com"
        else:
            return [["Error: Login on the Functions tab for limited demo usage, or sign up for a free Mistral AI account at https://console.mistral.ai/ and add your own api_key."]]
    if not isinstance(temperature, (float, int)) or not (0 <= float(temperature) <= 2):
        return [["Error: temperature must be a float between 0 and 2 (inclusive)"]]
    if not isinstance(max_tokens, int) or not (5 <= max_tokens <= 5000):
        return [["Error: max_tokens must be an integer between 5 and 5000 (inclusive)"]]
    table_prompt = f"Generate a well-organized table based on this request: {prompt}"
    if header is not None and header and len(header) > 0:
        header_str = ", ".join(str(col) for col in header[0])
        table_prompt += f"\nUse exactly these columns: {header_str}"
    if source is not None:
        source_str = json.dumps(source, indent=2)
        table_prompt += f"\n\nUse this source data to create the table:\n{source_str}"
    table_prompt += ("\nReturn ONLY a JSON object with a key 'items' whose value is a JSON array of arrays (2D array) with the table data. "
                     "The first row should contain column headers if not provided. "
                     "Each subsequent row should contain data that fits the columns. "
                     "Do not include any explanatory text, just the JSON object. "
                     "For example: {\"items\": [[\"Header1\", \"Header2\"], [\"Row1Col1\", \"Row1Col2\"]]}")
    payload = {
        "messages": [{"role": "user", "content": table_prompt}],
        "temperature": float(temperature),
        "model": model,
        "max_tokens": max_tokens,
        "response_format": {"type": "json_object"}
    }
    headers = {
        "Authorization": f"Bearer {api_key}",
        "Content-Type": "application/json",
        "Accept": "application/json"
    }
    try:
        response = requests.post(api_url, headers=headers, json=payload)
        if response.status_code == 429:
            return [["You have hit the rate limit for the API. Please try again later."]]
        response.raise_for_status()
        response_data = response.json()
        if not isinstance(response_data, dict) or "choices" not in response_data:
            err_msg = None
            for k in ("error", "message", "detail"):
                if k in response_data:
                    err_msg = response_data[k]
                    break
            if not err_msg:
                err_msg = str(response_data)
            return [[f"Error: {err_msg}"]]
        content = response_data["choices"][0]["message"]["content"]
        try:
            table_data = json.loads(content)
            if isinstance(table_data, dict) and "items" in table_data:
                table_data = table_data["items"]
            elif isinstance(table_data, dict):
                for key in ("data", "filled_data", "result"):
                    if key in table_data:
                        table_data = table_data[key]
                        break
            if isinstance(table_data, list) and all(isinstance(row, list) for row in table_data):
                return table_data
            else:
                return [["Error: Unable to parse response. Expected a 2D array."]]
        except (json.JSONDecodeError, ValueError):
            return [["Error: Unable to generate table. The AI response wasn't in the expected format."]]
    except Exception as e:
        msg = str(e)
        return [[f"Error: {msg}"]]