The Scenario
You are an inventory controller. It is the last week of the month and the monthly inventory audit is due Friday. You have 80 item codes sitting in a Google Sheet, the same 80 SKUs you audit every month. You need the current stock balance for each one from ERPNext's "Main Store" warehouse so you can compare them against your physical count sheets.
The way you have been doing this: open ERPNext, go to Stock Balance report, search each item code one at a time, read the quantity, type it into the sheet. Or, if you are feeling ambitious, export the full Stock Balance report as CSV, open it in Google Sheets, use a VLOOKUP to pull the quantity against your 80 items, fix the VLOOKUP errors for items where the name format doesn't match, and debug the #N/A returns until you know which ones are zero vs. genuinely missing.
On a good month this takes ninety minutes. On a month where someone renamed an item in ERPNext, it takes longer.
The bad version of audit week:
- Export the ERPNext Stock Balance report for all items
- Open the CSV in a separate sheet
- Write VLOOKUP formulas against 80 item codes
- Debug the 12
#N/Areturns — some are zero stock, some are name mismatches - Fix the mismatches manually
- Copy-paste the quantities into the audit sheet
- Friday is here and you are still reconciling three items.
The fast version is one prompt.
The Easy Way: One Prompt in SheetXAI
SheetXAI reads your item code list and fetches the current ERPNext stock balance for each one, writing the quantity directly into the sheet next to the item code.
Open the SheetXAI sidebar and type:
For each item code in column A of my 'Inventory Audit' sheet, fetch the current ERPNext stock balance from warehouse 'Main Store' and write the quantity on hand into column B. If the item is not found in ERPNext, write "NOT FOUND" into column B.
SheetXAI queries the ERPNext stock API for each item code, writes the current quantity into column B, and flags any item it could not find. You see the quantities inline, next to your physical count column, without a VLOOKUP in sight.
What You Get
An updated audit sheet with 80 rows of stock balance data:
- Quantity on hand — pulled from ERPNext's "Main Store" as of the moment you ran the prompt
- NOT FOUND flags — any item code that returned no result, so you investigate those specifically
- Variance column ready — add a formula in column C to subtract your physical count (column B minus your manual entry) and the audit view is complete
No VLOOKUP, no CSV, no cross-referencing. The quantities are in the sheet and the audit can proceed.
For items flagged as NOT FOUND, check whether the item code was renamed in ERPNext and re-run just those rows with the corrected codes.
What If the Data Is Not Quite Ready
Inventory audits have a few reliable complications. SheetXAI handles them in the same prompt.
When item codes in the sheet have inconsistent formatting
Some codes are uppercase, some mixed case. ERPNext is case-sensitive.
Normalize all item codes in column A to uppercase before fetching the ERPNext stock balance. Write the quantity on hand into column B. Write "NOT FOUND" if the item returns no result.
When you need balances across two warehouses, not just one
The audit covers "Main Store" and "Finished Goods" separately. You need both quantities in the same row.
For each item code in column A, fetch the ERPNext stock balance from 'Main Store' and write the quantity into column B. Then fetch the balance from 'Finished Goods' and write it into column C. Leave blank if the item has no stock in that warehouse.
When you want to flag items below their reorder level automatically
Reorder levels are in column C. You want column D to show a flag whenever current stock is below reorder.
Fetch the ERPNext stock balance for each item code in column A from warehouse 'Main Store' and write the quantity into column B. Then compare each value in column B against the reorder level in column C. Write "REORDER" into column D for any item where stock is at or below the reorder level. Leave column D blank otherwise.
When you need to run the audit, flag discrepancies, and summarize it all in one pass
Your physical count is in column B already. You want ERPNext stock in column C, the variance in column D, and a summary paragraph at the top of the sheet.
Fetch the ERPNext stock balance from 'Main Store' for each item code in column A and write the quantity into column C. Calculate the variance (column B minus column C) and write it into column D. Flag any row where the variance is more than 10 units by writing "INVESTIGATE" into column E. Then write a one-paragraph audit summary at the top of the sheet listing the total items audited, total items with variances, and the three largest discrepancies by absolute value.
The pattern: instead of fetching the data and then analyzing it separately, you fetch, compare, flag, and summarize in one instruction.
Try It
Get the 7-day free trial of SheetXAI and run it against your audit item list before the next physical count. The ERPNext integration is included in every SheetXAI plan. For other inventory and operations workflows, see how to pull open Sales Orders for fulfillment planning or the ERPNext in Google Sheets overview.