Run Batch Elasticsearch Queries From a Excel and Write Results Back

The Scenario

It's Tuesday afternoon and you've got 50 product SKUs in column A of your "Enrichment" worksheet. Your job is to fill in the product name, current price, and stock status for each one before the merchandising team's 4 PM standup — and right now columns B, C, and D are blank.

The data all lives in your Elasticsearch 'products' index. It's queryable. You just have to get it out, row by row, and into the workbook.

The bad version:

• Open Kibana, write a term query for the first SKU, locate the title and price fields buried inside the _source object of the response, copy them
• Paste into row 2, manually format the price field because it came back as a string with trailing decimals, move to the next SKU
• Repeat 49 more times, losing your place at row 23 when you accidentally overwrite a value and have to re-query to recover it

Your afternoon is not long enough for 50 rounds of that. More to the point: enriching a column of identifiers against a search index is not a task that should require any manual steps at all.

The Easy Way: One Prompt in SheetXAI

SheetXAI is an AI agent that lives inside your Excel workbook. It reads your data, talks to Elasticsearch on your behalf, and writes results back without you touching the API. Open the SheetXAI sidebar, paste in your prompt, and it handles the loop.

For each SKU in column A of my "Enrichment" worksheet, query my Elasticsearch 'products' index for that SKU and write the matching product name, price, and stock level into columns B, C, and D

What You Get

• Column B fills with the product name returned from the _source.title field for each match
• Column C receives the price, formatted as a number
• Column D shows the stock status — "in_stock", "low_stock", or "out_of_stock" depending on what the index returns
• Rows where the SKU returns no match get a "not found" note in column B so you know which ones to follow up on

What If the Data Is Not Quite Ready

The SKUs have a prefix that doesn't match the index

Your workbook has SKUs formatted as "PRD-001042" but the index stores them as "001042". SheetXAI can strip the prefix before querying.

For each SKU in column A of "Enrichment", remove the "PRD-" prefix, query the Elasticsearch 'products' index, and write the product name, price, and stock level into columns B, C, and D

The price field is nested inside a sub-object

If the index returns price inside a pricing.retail nested field rather than at the top level:

Query the 'products' Elasticsearch index for each SKU in column A and write the value from pricing.retail into column C — if the field path doesn't exist for a row, write "no price" instead

You need to join the result against a second worksheet

You have a "Thresholds" worksheet listing the minimum stock level per product category. You want to flag items that are below threshold after enrichment.

After querying Elasticsearch for each SKU in column A of "Enrichment" and writing stock levels into column D, check the minimum stock level for each product category in the "Thresholds" worksheet and write "below threshold" or "ok" into column E

Clean up the SKU column, enrich it, and flag the mismatches in one pass

Some SKUs in column A have trailing spaces, some have mixed case, and a handful are duplicates.

In my "Enrichment" worksheet, trim and lowercase each SKU in column A, remove duplicate rows, then query the Elasticsearch 'products' index for each unique SKU and write the product name, price, and stock level into columns B, C, and D — flag any rows where the index returned no match with "MISSING" in column E

The pattern: ask for the cleanup and the enrichment together. SheetXAI runs the normalization inline before it hits the index.

Try It

Get the 7-day free trial of SheetXAI and open the Excel workbook where you've been manually enriching SKUs from Elasticsearch — paste in that 50-row column and ask it to fill in the product fields in one shot. You can also explore exporting your cluster index inventory or pulling an index schema into a data dictionary.