Convert GST JSON to Excel: Complete Guide for GSTR-1, 2A, and 2B
The GST portal lets you download your return data as JSON files. This guide shows how to convert them into clean Excel spreadsheets for reconciliation, auditing, and filing.
Why convert GST JSON to Excel?
The JSON files from the GST portal are structured for machine consumption, not human reading. Converting them to Excel lets you sort by supplier, filter by tax rate, sum up ITC amounts, compare with your purchase register, and prepare for filing.
What the converter does
When you upload a GST JSON file, the converter:
**Detects the format**: Identifies whether it is a GSTR-2A, 2B, or 1 file by looking for the gstin field and section keys.
**Unwraps envelopes**: If the file is wrapped in API response metadata (common with GSP downloads), the converter digs through nested data keys to find the actual return document.
**Creates sheets**: One sheet per section (b2b, cdnr, impg, etc.). Each sheet has its own column structure matching the section's data.
**Flattens invoices**: Nested structures like supplier > invoices > items are flattened into rows. A B2B section with 3 suppliers, 5 invoices, and 8 line items produces 8 rows with all context fields repeated.
**Adds context columns**: Your GSTIN, return period, generation date, and file code appear on every row for easy filtering.
Supported formats
GSTR-2A: Inward supplies auto-populated from suppliers' GSTR-1. Sections: b2b, cdnr, impg.
GSTR-2B: ITC statement. Sections: b2b (under docdata), cdnr, b2ba, plus summary sections (cpsumm, itcsumm).
GSTR-1: Outward supplies. Sections: b2b, b2cl, b2cs, cdnr, exp, hsn, doc_issue.
GSP API envelopes: Wrapped responses like {"code": 200, "data": {"data": {...}}} are automatically unwrapped.
Tips for reconciliation in Excel
Filter the b2b sheet by CGST + SGST vs IGST to separate intra-state and inter-state purchases.
Use SUMIF to total ITC by supplier GSTIN.
Compare invoice numbers against your purchase register using VLOOKUP.
Use conditional formatting to highlight mismatches in taxable values.
Sort by date to identify any invoices outside the filing period.