How to Verify GST Numbers in Excel Using an API

You can verify a GST number in Excel two ways without installing an add-in: a Power Query query that calls the API with Web.Contents and expands the JSON response into columns, or a VBA macro that uses MSXML2.XMLHTTP60 to fill a results column across an entire sheet. Both talk to the same underlying GST verification API — the difference is refreshability versus one-click bulk runs.

Power Query vs VBA: which to use

Both routes call GET https://gstinapi.com/api/get-taxpayer-info/{gstin} with an X-API-Key header and get back a JSON object. Power Query is the natural choice if you want a live table that a colleague can refresh with one click, and it needs no macro security exception to run. VBA is the better choice if you already have a column of GSTINs in an existing sheet and just want a one-time button that fills a Status column next to them — no separate query object to maintain.

Power Query with Web.Contents

In Excel, go to Data → Get Data → From Other Sources → Blank Query, open the Advanced Editor, and paste an M query that calls the API and expands the response record into columns. Add Content-Type is not required for a GET request — only the API key header matters here.

let
    Gstin = "27AAACR5055K1Z7",
    Source = Json.Document(
        Web.Contents(
            "https://gstinapi.com/api/get-taxpayer-info/" & Gstin,
            [Headers = [#"X-API-Key" = "YOUR_API_KEY"]]
        )
    ),
    Expanded = Table.FromRecords({Source}),
    Columns = Table.ExpandRecordColumn(
        Expanded, "data",
        {"legal_name", "trade_name", "status", "registration_date"},
        {"Legal Name", "Trade Name", "Status", "Registration Date"}
    )
in
    Columns

To run this across a whole column of GSTINs rather than one hard-coded value, replace the Blank Query approach with a query parameterized off an existing table: select your GSTIN column, use Add Column → Invoke Custom Function, and point it at a function query built from the let block above with Gstin turned into a function parameter. Power Query then re-runs the call once per row and expands each result inline.

The Power Query privacy-level gotcha

The first time you add a Web.Contents call to gstinapi.com, Power Query will prompt you to set that source's privacy level — Public, Organizational, or Private. This is not optional metadata: Power Query uses it to decide whether it is safe to fold data from different sources together in the same query, and a query mixing sources of different privacy levels can fail outright with a Formula.Firewall: Query '...' (step '...') references other queries or steps, so it may not directly access a data source error, or simply refuse to load.

VBA with MSXML2.XMLHTTP60

If you would rather run a macro against an existing sheet than maintain a Power Query object, a short VBA routine using MSXML2.XMLHTTP60 — the modern, TLS 1.2-capable XML HTTP object — can loop over a column of GSTINs and write the status and legal name into adjacent columns.

Sub VerifyGstinColumn()
    Dim ws As Worksheet
    Dim http As Object
    Dim lastRow As Long, i As Long
    Dim url As String, gstin As String, jsonResponse As String

    Set ws = ThisWorkbook.Sheets("Vendors")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    For i = 2 To lastRow
        gstin = Trim(ws.Cells(i, 1).Value)
        If gstin <> "" Then
            Set http = CreateObject("MSXML2.XMLHTTP60")
            url = "https://gstinapi.com/api/get-taxpayer-info/" & gstin

            http.Open "GET", url, False
            http.setRequestHeader "X-API-Key", "YOUR_API_KEY"
            http.send

            If http.Status = 200 Then
                jsonResponse = http.responseText
                ws.Cells(i, 2).Value = ExtractJsonValue(jsonResponse, "status")
                ws.Cells(i, 3).Value = ExtractJsonValue(jsonResponse, "legal_name")
            Else
                ws.Cells(i, 2).Value = "Error " & http.Status
            End If

            ' Be polite to the API on large sheets
            Application.Wait Now + TimeValue("0:00:01")
        End If
    Next i
End Sub

ExtractJsonValue is a small helper you write once with basic string parsing, or you can drop in a lightweight open-source JSON parser (such as VBA-JSON) if you want proper nested-object handling — the filing-history array in a full response is nested deeply enough that hand-written string parsing gets unwieldy fast. See the API documentation for the full response schema before deciding how much of it you actually need in the sheet.

Do not paste your API key into a shared workbook

Both approaches above have the API key sitting in plain text — in an M query step or a VBA line. That is fine for a workbook only you ever open. It is not fine for a workbook you email to a colleague, upload to a shared drive, or attach to a vendor onboarding workflow email. An .xlsx file is a zip archive of XML and text; a query step or macro source is trivially readable by anyone who opens it, and version history on a shared drive can retain old keys even after you rotate them.

If several people need to run GST checks from spreadsheets, prefer a small internal proxy endpoint that holds the real key server-side, or issue each person their own API key from your account so a leaked key is easy to identify and revoke without breaking everyone else's workbook. For higher-volume, ongoing bulk checks, a proper integration is usually a better fit than a spreadsheet at all — see our bulk GSTIN verification guide.

One thing spreadsheets can catch even without a live call: a GSTIN's fifteenth character is a checksum over the preceding fourteen, computed with a Luhn mod-36 style algorithm. A quick =LEN() and pattern check in Excel filters out obviously malformed entries before you spend an API call on them — but only a live lookup, via either route above, tells you whether the number was ever actually issued and is currently Active. See GSTIN format explained for the full anatomy of a GSTIN.

Frequently asked questions