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 AI to generate a structured table based on the prompt and optional header/source data.
 
    Args:
        prompt (str): Instruction for AI to create a table
        header (list, optional): 2D list containing table header (column names)
        source (list, optional): 2D list containing source data used to create the table
        temperature (float, optional): Controls response creativity (0-2). Default is 0.0
        max_tokens (int, optional): Maximum tokens for response generation. Default is 1500
        model (str, optional): ID of the model to use
        api_key (str, optional): API key for authentication (e.g. for Mistral AI)
        api_url (str, optional): OpenAI compatible URL. (e.g., https://api.mistral.ai/v1/chat/completions)
 
    Returns:
        list: 2D list representing the generated table data
    """
    # Demo fallback: Boardflare
    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 "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."
 
    # Validate temperature
    if not isinstance(temperature, (float, int)) or not (0 <= float(temperature) <= 2):
        raise ValueError("temperature must be a float between 0 and 2 (inclusive)")
    # Validate max_tokens
    if not isinstance(max_tokens, int) or not (5 <= max_tokens <= 5000):
        raise ValueError("max_tokens must be an integer between 5 and 5000 (inclusive)")
 
    # Construct the message
    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 the response does not have 'choices', treat as error
        if not isinstance(response_data, dict) or 'choices' not in response_data:
            # Try to extract a message from common error keys
            err_msg = None
            for k in ('error', 'message', 'detail'):  # common API error keys
                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 f"Error: Unable to parse response. Expected a 2D array."
        except (json.JSONDecodeError, ValueError):
            return f"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}"