The Scenario
It's Tuesday afternoon and you've got 50 product SKUs in column A of your "Enrichment" sheet. 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 sheet.
The bad version:
- Open Kibana, write a term query for the first SKU, locate the title and price fields buried inside the
_sourceobject 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 Google Sheet. 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" sheet, 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.titlefield 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 sheet 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.retailinto 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 tab
You have a "Thresholds" tab 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" tab 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" sheet, 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 Google Sheet 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.