AI_FILL

=AI_FILL(example_range, fill_range, [temperature], [model], [max_tokens])

=AI_FILL(A1:D4, A5:D7)

=AI_FILL(A1:D5, A6:D9)

=AI_FILL(A1:E3, A4:E6)

import requests
import json
 
def ai_fill(example_range, fill_range, temperature=0.0, model="mistral-small-latest", max_tokens=1500, api_key=None, api_url="https://api.mistral.ai/v1/chat/completions"):
    """
    Fills missing or incomplete data in a target range by learning patterns from an example range using a large language model.
 
    Args:
        example_range: 2D list containing complete data as examples for the AI to learn from.
        fill_range: 2D list containing data with missing values to be filled.
        temperature: Optional float. Controls response creativity (0.0-2.0). Default is 0.0.
        model: Optional string. ID of the model to use. Default is "mistral-small-latest".
        max_tokens: Optional int. Maximum tokens for response generation. Default is 1500.
        api_key: Optional string. API key for authentication.
        api_url: Optional string. OpenAI-compatible URL. Default is "https://api.mistral.ai/v1/chat/completions".
 
    Returns:
        2D list with missing data filled in, or a 2D list with an error message.
 
    This example function is provided as-is without any representation of accuracy.
    """
    # Validate temperature
    if not isinstance(temperature, (float, int)) or not (0 <= float(temperature) <= 2):
        return [["Error: 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):
        return [["Error: max_tokens must be an integer between 5 and 5000 (inclusive)"]]
    # Validate inputs
    if not isinstance(example_range, list) or not example_range:
        return [["Error: Example range is empty or invalid."]]
    if not isinstance(fill_range, list) or not fill_range:
        return [["Error: Fill range is empty or invalid."]]
    # Demo fallback logic (same as ai_ask)
    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."]]
    # Convert example_range and fill_range to JSON strings for the prompt
    example_json = json.dumps(example_range)
    fill_json = json.dumps(fill_range)
    # Construct a specific prompt for filling data
    fill_prompt = (
        "Fill in the missing values in the target data based on patterns in the example data.\n"
        f"Example data (complete): {example_json}\n\n"
        f"Target data (with missing values): {fill_json}\n\n"
        "Study the patterns in the example data and complete the target data by filling in missing values. Preserve all existing values in the target data."
    )
    fill_prompt += ("\n\nReturn ONLY a JSON object with a key 'items' whose value is a JSON array of arrays (2D array) with the completed target data. "
                    "Do not include any explanatory text, just the JSON object. "
                    "For example: {\"items\": [[\"row1col1\", \"row1col2\"], [\"row2col1\", \"row2col2\"]]}")
    # Prepare the API request payload
    payload = {
        "messages": [{"role": "user", "content": fill_prompt}],
        "temperature": 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()
        content = response_data["choices"][0]["message"]["content"]
        try:
            filled_data = json.loads(content)
            if isinstance(filled_data, dict) and "items" in filled_data:
                filled_data = filled_data["items"]
            elif isinstance(filled_data, dict):
                if "data" in filled_data:
                    filled_data = filled_data["data"]
                elif "filled_data" in filled_data:
                    filled_data = filled_data["filled_data"]
                elif "result" in filled_data:
                    filled_data = filled_data["result"]
            if isinstance(filled_data, list) and all(isinstance(row, list) for row in filled_data):
                if (len(filled_data) == len(fill_range) and all(len(row) == len(fill_range[i]) for i, row in enumerate(filled_data))):
                    return filled_data
                else:
                    return [["Error: AI response dimensions don't match the fill range."]]
            else:
                return [["Error: Unable to parse response. Expected a 2D array."]]
        except (json.JSONDecodeError, ValueError):
            return [["Error: Unable to fill data. The AI response wasn't in the expected format."]]
    except requests.exceptions.RequestException as e:
        return [["Error: API request failed.", str(e)]]