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
 
# This function uses AI to fill in missing data in a target range by learning patterns from an example range.
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"):
    """
    Uses AI to fill in missing data in a target range by learning patterns from an example range.
 
    Args:
        example_range (list): 2D list containing complete data as examples for the AI to learn from
        fill_range (list): 2D list containing data with missing values to be filled
        temperature (float, optional): Controls response creativity (0-2). Default is 0.0
        model (str, optional): ID of the model to use
        max_tokens (int, optional): Maximum tokens for response generation. Default is 1500
        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 with missing data filled in, or a 2D list with an error message
    """
    # 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)")
 
    # 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)]]