AI.QUERY

Users can pass specified parameters to the AI.QUERY function to obtain query results from the AI model.

Syntax

AI.QUERY(prompt1, [data1], [prompt2], [data2] ...)

Arguments

The AI.QUERY function can accept any number of prompts and data, which will be organized into a single text block and sent to the AI model for processing.

This function has these arguments:

Argument	Description
prompt1	[Required] Text describing the task or asking a question to the AI model. Each prompt parameter is merged with the context in order to form a complete prompt message.
data1	[Optional] Table reference providing context or data for the AI model; can be a single cell or a range.
prompt2, data2, …	[Optional] Additional text and table references for further context to the AI model. Unlimited in number; they can be used in pairs or just specify prompt.

Remarks

The prompt sent to the AI model is generated by concatenating all prompt and data parameters. For example, when using the formula =COPILOT("Classify", B1:B10, "into one of the following categories ", A1:A4), the final content sent to the AI model is: "Classify [the values from B1:B10] into the following categories [the values from A1:A4]".

Examples

Example 1

The example below demonstrates how to pass multiple prompt parameters to the AI.QUERY function so that the AI can intelligently analyze the given text based on the prompts.

// To use this example, add the following dependency to your project: com.openai:openai-java:4.6.1
// Configure the model request handler and choose different large model providers as needed. Here the example uses OpenAI GPT-4.1; replace with your API key when using.
Workbook.setAIModelRequestHandler(new OpenAIModelRequestHandler("https://api.openai.com/v1", "sk-xxxx", "gpt-4.1"));
// DeepSeek model.
// Workbook.setAIModelRequestHandler(new OpenAIModelRequestHandler("https://api.deepseek.com/v1", "sk-xxxx", "deepseek-chat"));
// Qwen model.
// Workbook.setAIModelRequestHandler(new OpenAIModelRequestHandler("https://dashscope.aliyuncs.com/compatible-mode/v1", "sk-xxxx", "qwen-plus"));

// Initialize the workbook and set data.Workbook workbook = new Workbook();
IWorksheet sheet = workbook.getWorksheets().get(0);
sheet.getColumns().get(0).setColumnWidth(57);
sheet.getColumns().get(1).setColumnWidth(55);
sheet.getColumns().get(2).setColumnWidth(42);
sheet.getRange("A1:C1").merge();
sheet.getRange("A1").setValue("Example1: Customer Product Reviews and Classification");
sheet.getRange("A1").getFont().setBold(true);
sheet.getRange("A1").getFont().setSize(16);
sheet.getRange("A1").getFont().setColor(Color.GetWhite());
sheet.getRange("A1").getInterior().setColor(Color.FromArgb(90, 126, 158));
sheet.getRange("A1").setHorizontalAlignment(HorizontalAlignment.Center);
sheet.getRange("A1").setVerticalAlignment(VerticalAlignment.Center);
sheet.getRange("A1").setRowHeight(35);
sheet.getRange("A3").setValue("Formula:");
sheet.getRange("A3").getFont().setBold(true);
sheet.getRange("A3").getFont().setSize(11);
sheet.getRange("A3").getInterior().setColor(Color.FromArgb(217, 225, 242));
sheet.getRange("B3").setValue("=AI.QUERY(\"evaluate these reviews\", A6:A13, \"based on these categories\",B5:C5)");
sheet.getRange("B3").getFont().setItalic(true);
sheet.getRange("B3").getFont().setColor(Color.FromArgb(68, 114, 196));
sheet.getRange("A5:C5").setValue(new Object[][]{
        {"Taco Truck Reviews", "Positive or negative", "Topics"}
});
sheet.getRange("A5:C5").getFont().setBold(true);
sheet.getRange("A5:C5").getInterior().setColor(Color.FromArgb(155, 194, 230));
sheet.getRange("A5:C5").setHorizontalAlignment(HorizontalAlignment.Center);
sheet.getRange("A6:A13").setValue(new Object[][]{
        {"Great tacos with fresh ingredients! Definitely coming back for more."},
        {"The service was slow, but the food was worth the wait."},
        {"Not impressed. The tacos were bland and lacked flavor."},
        {"Amazing variety of salsas and toppings. Loved it!"},
        {"The truck was clean and the staff was friendly."},
        {"Overpriced for the portion size. Won't be returning."},
        {"The tortillas were soggy and the meat was dry."},
        {"Best taco truck in town! Highly recommend the carne asada."}
});

// Define AI formula; the concatenated prompt is: "Evaluate these reviews [values in A6:A13], based on these categories [values in B5:C5]"
sheet.getRange("B6").setFormula2("=AI.QUERY(\"evaluate these reviews\", A6:A13, \"based on these categories\",B5:C5)");
sheet.getRange("A6:C13").getFont().setSize(11);
sheet.getRange("A6:C13").setHorizontalAlignment(HorizontalAlignment.Center);
sheet.getRange("A6:C13").getBorders().setLineStyle(BorderLineStyle.Medium);
sheet.getRange("A6:C13").getBorders().setColor(Color.FromArgb(200, 200, 200));

// The AI function works as an asynchronous calculation function; you need to wait for its calculation to complete.
workbook.calculate();
workbook.waitForCalculationToFinish();

// Set the page to print as a single page.
sheet.getPageSetup().setFitToPagesTall(1);
sheet.getPageSetup().setFitToPagesWide(1);
sheet.getPageSetup().setIsPercentScale(false);

// Save as PDF file.workbook.save("AIQueryDataAnalysis.pdf");

/**
 * Implementation of IAIModelRequestHandler for OpenAI API.
 */
public class OpenAIModelRequestHandler implements IAIModelRequestHandler {
    private String _apiEndpoint;
    private String _apiKey;
    private String _model;
    public OpenAIModelRequestHandler(String apiEndpoint, String apiKey, String model) {
        if (apiEndpoint == null || apiEndpoint.trim().isEmpty())
            throw new IllegalArgumentException("API endpoint cannot be null or empty.");
        if (apiKey == null || apiKey.trim().isEmpty())
            throw new IllegalArgumentException("API key cannot be null or empty.");
        _apiEndpoint = apiEndpoint.replaceAll("/$", "");
        _apiKey = apiKey;
        _model = model;
    }
    @Override
    public CompletableFuture<AIModelResponse> sendRequestAsync(AIModelRequest request) {
        CompletableFuture<AIModelResponse> result = new CompletableFuture<>();
        if (request == null) {
            AIModelResponse modelResponse = new AIModelResponse();
            modelResponse.setSuccess(false);
            System.err.println("Request cannot be null.");
            result.complete(modelResponse);
            return result;
        }
        OpenAIClientAsync openAIClient = OpenAIOkHttpClientAsync.builder()
                .apiKey(_apiKey)
                .baseUrl(_apiEndpoint)
                .build();
        ChatCompletionCreateParams.Builder builder = ChatCompletionCreateParams.builder();
        for (AIMessage item : request.getMessages()) {
            switch (item.getRole().toLowerCase()) {
                case "system":
                    builder.addSystemMessage(item.getContent());
                    break;
                case "user":
                    builder.addUserMessage(item.getContent());
                    break;
                default:
                    throw new RuntimeException("Unknown message role:" + item.getRole());
            }
        }
        builder.model(_model);
        ChatCompletionCreateParams params = builder.build();
        CompletableFuture<ChatCompletion> chatCompletion = openAIClient.chat().completions().create(params);
        chatCompletion.whenComplete((response, exception) -> {
            try {
                if (exception != null) {
                    AIModelResponse errorResponse = new AIModelResponse();
                    errorResponse.setSuccess(false);
                    System.err.println("An error occurred: " + exception.getMessage());
                    result.complete(errorResponse);
                } else {
                    if (response != null && !response.choices().isEmpty()) {
                        StringBuilder contentBuilder = new StringBuilder();
                        for (ChatCompletion.Choice choice : response.choices()) {
                            choice.message();
                            if (choice.message().content().isPresent()) {
                                contentBuilder.append(choice.message().content().get());
                            }
                        }
                        AIModelResponse successResponse = new AIModelResponse();
                        successResponse.setSuccess(true);
                        successResponse.setContent(contentBuilder.toString());
                        result.complete(successResponse);
                    } else {
                        AIModelResponse noContentResponse = new AIModelResponse();
                        noContentResponse.setSuccess(false);
                        System.err.println("No content received from the model.");
                        result.complete(noContentResponse);
                    }
                }
            } finally {
                try {
                    openAIClient.close();
                } catch (Exception e) {
                    System.err.println("Error closing OpenAI client: " + e.getMessage());
                }
            }
        });
        return result;
    }
}

The output is shown below:

Example 2

The following example shows how to automatically generate text content in the spreadsheet using the AI.QUERY function.

// To use this example, add the following dependency to your project: com.openai:openai-java:4.6.1
// Configure the model request handler and choose different large model providers as needed. Here the example uses OpenAI GPT-4.1; replace with your API key when using.
Workbook.setAIModelRequestHandler(new OpenAIModelRequestHandler("https://api.openai.com/v1", "sk-xxxx", "gpt-4.1"));
// DeepSeek model.
// Workbook.setAIModelRequestHandler(new OpenAIModelRequestHandler("https://api.deepseek.com/v1", "sk-xxxx", "deepseek-chat"));
// Qwen model.
// Workbook.setAIModelRequestHandler(new OpenAIModelRequestHandler("https://dashscope.aliyuncs.com/compatible-mode/v1", "sk-xxxx", "qwen-plus"));

// Initialize the workbook and set data.Workbook workbook = new Workbook();
IWorksheet sheet = workbook.getWorksheets().get(0);
sheet.getColumns().get(0).setColumnWidth(28);
sheet.getColumns().get(1).setColumnWidth(35);
sheet.getColumns().get(2).setColumnWidth(18);
sheet.getColumns().get(3).setColumnWidth(18);
sheet.getColumns().get(4).setColumnWidth(25);
sheet.getRange("A1:D1").merge();
sheet.getRange("A1").setValue("Example 2: Content Generation - Ice Cream Flavors");

sheet.getRange("A1").getFont().setBold(true);
sheet.getRange("A1").getFont().setSize(14);
sheet.getRange("A1").getFont().setColor(Color.GetWhite());
sheet.getRange("A1").getInterior().setColor(Color.FromArgb(90, 126, 158));
sheet.getRange("A1").setHorizontalAlignment(HorizontalAlignment.Center);
sheet.getRange("A1").setVerticalAlignment(VerticalAlignment.Center);
sheet.getRange("A1").setRowHeight(30);

// Define AI formula to generate 5 unique ice cream flavors.
String QueryFormula3 = "=AI.QUERY(\"Generate 5 unique ice cream flavors, arranged vertically\")";
sheet.getRange("A3").setValue("Query:");
sheet.getRange("A3").getFont().setBold(true);
sheet.getRange("A3").getFont().setSize(11);
sheet.getRange("A3").getInterior().setColor(Color.FromArgb(217, 227, 242));
sheet.getRange("B3:D3").merge();
sheet.getRange("B3").setValue(QueryFormula3);
sheet.getRange("B3").getFont().setItalic(true);
sheet.getRange("B3").getFont().setColor(Color.FromArgb(68, 114, 196));
sheet.getRange("A5").setValue("Generated Flavors:");
sheet.getRange("A5").getFont().setBold(true);
sheet.getRange("A5").getFont().setSize(11);
sheet.getRange("A5").getInterior().setColor(Color.FromArgb(155, 194, 230));
sheet.getRange("B5").setFormula2(QueryFormula3);
sheet.getRange("B5:B5").getFont().setItalic(true);

// The AI function works as an asynchronous calculation function; you need to wait for its calculation to complete.
workbook.calculate();
workbook.waitForCalculationToFinish();

// Set the page to print as a single page.
sheet.getPageSetup().setFitToPagesTall(1);
sheet.getPageSetup().setFitToPagesWide(1);
sheet.getPageSetup().setIsPercentScale(false);

// Save as PDF file.
workbook.save("AIQueryTextGeneration.pdf");

The output is shown below: