A GST invoice workflow for a small shop, without paid software

If you issue thirty or forty invoices a month, you need four things: somewhere to type invoice data, rules that catch typos before the invoice is printed, a printable PDF, and an archive you can search two years later when a notice arrives. A spreadsheet plus a few free browser tools covers all four. What it does not cover is e-invoicing, e-way bills, stock accounting or multiple simultaneous users, and I will be specific about where that line sits.

This is about formats and workflow, not tax advice. Rate applicability, the exact fields your business must show, and whether e-invoicing applies to you are questions for a chartered accountant. Get the workflow confirmed once, at the start, and then it is mechanical.

The chain

1. Type line items into one sheet.
2. Validation formulas flag bad GSTIN, bad HSN, duplicate invoice numbers.
3. Render a printable PDF per invoice.
4. File the PDF into a month folder with a predictable name.
5. At filing time, reconcile the sheet against the portal export.

1. One sheet, one row per line item

The mistake almost everyone makes on the first attempt is one row per invoice, with columns like item1, item2, item3. It breaks the moment someone buys four things. Use one row per line item and let the invoice number repeat.

Keep a second tab, Masters, with one row per regular customer (name, address, GSTIN, state code) and one row per item (description, HSN, default rate, default tax percent). The Invoices tab then pulls from Masters with XLOOKUP, so a customer's GSTIN is typed once in its life rather than once per invoice. That single change removes most of the errors this whole article is about.

2. Validation that catches the real mistakes

Three checks are worth automating, because these are the ones that survive a human read-through.

GSTIN shape. Fifteen characters: two-digit state code, ten-character PAN, an entity number, the letter Z, then a check character.

^[0-9]{2}[A-Z]{5}[0-9]{4}[A-Z][1-9A-Z]Z[0-9A-Z]$

In Google Sheets, =REGEXMATCH(E2, "^[0-9]{2}[A-Z]{5}[0-9]{4}[A-Z][1-9A-Z]Z[0-9A-Z]$") in a helper column, conditionally formatted red on FALSE. Microsoft 365 has REGEXTEST now; older Excel does not, and you either write a short VBA function or fall back to a LEN plus CODE formula that is not worth the trouble. Test the pattern against your own data first in the Regex Tester: paste twenty real GSTINs from your customer list and confirm all twenty pass before you trust the column.

The honest limit: regex checks shape, not truth. 27AAAAA1111A1ZX has a perfect shape and a wrong check character. The check digit is a mod-36 calculation, which no regex can do:

import re
GSTIN_RE = r"^[0-9]{2}[A-Z]{5}[0-9]{4}[A-Z][1-9A-Z]Z[0-9A-Z]$"
CH = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"

def gstin_ok(g: str) -> bool:
    if not re.match(GSTIN_RE, g or ""):
        return False
    total = 0
    for i, c in enumerate(g[:14]):
        v = CH.index(c) * (2 if i % 2 else 1)
        total += v // 36 + v % 36
    return CH[(36 - total % 36) % 36] == g[14]

gstin_ok("27AAAAA1111A1ZW")   # True
gstin_ok("27AAAAA1111A1ZX")   # False, shape fine, check character wrong

A valid check character still does not prove the GSTIN is active or belongs to that party. Only the portal's search can tell you that, and for a new customer it is worth the thirty seconds.

HSN shape. Goods use 4, 6 or 8 digits; services use a 6-digit SAC that starts with 99. The number of digits you are required to show depends on your turnover, which is the CA's call, but the shape check is easy:

^[0-9]{4}(?:[0-9]{2}(?:[0-9]{2})?)?$

That accepts 1704, 170490 and 17049090 and rejects 170 and 1704909. A seven-digit HSN is always a typo.

Duplicate and out-of-sequence invoice numbers. =COUNTIF($A:$A, A2) must equal the number of line items you expect, and a gap in the series is something you should be able to explain. A helper column =IF(COUNTIFS($A$2:A2, A2) = 1, "new", "") marks the first row of each invoice, which also makes the next step easier.

3. The printable PDF

Whatever you generate, it has to carry the fields a tax invoice requires: your name, address and GSTIN, a unique invoice number and date, the recipient's name, address and GSTIN, place of supply, HSN or SAC per line, taxable value, the rate and amount of CGST and SGST or IGST, the total, and a signature. Have your CA tick off that list against a sample invoice once.

Three ways to produce it, in increasing order of effort and reliability:

• Spreadsheet template. A second workbook with a formatted invoice layout that pulls one invoice by number. Print to PDF, or use Excel to PDF if your print margins keep fighting you. Fine for five invoices a day, tedious for twenty.
• Word mail merge. Point a Word template at the sheet, merge, export one PDF per record. No code, and it handles multi-line-item invoices badly unless you nest tables.
• A short script. Thirty lines of Python that group the sheet by inv_no, fill an HTML template, and let the browser or weasyprint turn each into a PDF. This is the version that scales, because adding a field is a one-line change rather than re-doing a layout.

import pandas as pd
from string import Template

tpl = Template(open("invoice.html").read())
df = pd.read_excel("invoices-2026-04.xlsx", dtype={"inv_no": str, "hsn": str})

for inv_no, g in df.groupby("inv_no", sort=True):
    g = g.assign(taxable=(g["qty"] * g["rate"]).round(2))
    rows = "".join(
        f"<tr><td>{r.desc}</td><td>{r.hsn}</td><td>{r.qty}</td>"
        f"<td>{r.rate:.2f}</td><td>{r.taxable:.2f}</td></tr>" for r in g.itertuples()
    )
    html = tpl.substitute(inv_no=inv_no, date=g["inv_date"].iloc[0].strftime("%d-%m-%Y"),
                          party=g["party_name"].iloc[0], gstin=g["party_gstin"].iloc[0],
                          rows=rows, taxable=f"{g['taxable'].sum():,.2f}")
    open(f"out/{inv_no}.html", "w", encoding="utf-8").write(html)

Round at the line level, sum the rounded values, and never re-round the total. Doing it the other way round is where the one-rupee differences in step 5 come from.

4. Archiving

A folder convention beats a clever system, because you will follow it under pressure:

GST/2026-27/04-Apr/INV-2627-0142_NagpurTraders.pdf
GST/2026-27/04-Apr/invoices-2026-04.xlsx
GST/2026-27/04-Apr/gstr1-export-2026-04.xlsx

The month's source sheet lives beside the PDFs it produced. When a query comes two years later you open one folder, not four. Back it up to two places, a cloud drive and an external disk, and once a year actually open a file from the backup. Records have to be kept for years; a drive does not care about your filing deadline.

5. Reconciling against the portal export

Download the B2B export from the portal after filing and compare it against your sheet. Portal exports are messy in predictable ways: merged header rows, trailing spaces in GSTIN, invoice numbers stored as text in one column and as numbers in another, and blank rows between sections. Run it through Clean & Deduplicate Data first to trim whitespace, drop empty rows and remove exact duplicates, then match on inv_no plus party_gstin.

Compare values with a tolerance: abs(mine - portal) <= 1 flags real errors and ignores rounding noise. Anything larger than that is a data-entry problem worth chasing.

When to stop and buy software

Be honest with yourself about this. A spreadsheet chain is cheap and transparent, and it stops paying off sooner than people expect.

The e-invoicing line is the hard one. An IRN has to be generated by the invoice registration portal and the signed QR code printed on the invoice. A spreadsheet cannot do that, and no amount of scripting makes it compliant. If your turnover crosses the threshold, the decision is made for you.

If the middle row is where you are and you would rather have the script than the subscription, that is the kind of work described under automation services. If you would rather build it yourself, the pandas parts of it are covered in /learn/pandas/.

Frequently asked questions

Is a spreadsheet-generated invoice legally acceptable? The format is what matters, not the software, provided every required field is present and your numbering is unique and sequential. Have a CA review one sample invoice and your numbering scheme before you issue the hundredth one.

Can I validate GSTIN inside Google Sheets without any scripting? You can validate the shape with REGEXMATCH. The check character needs arithmetic, so that part goes in Apps Script or in the Python function above. Shape validation alone still catches most typos.

What is the single highest-value check to add first? The Masters lookup, so GSTIN and HSN are never retyped. Most reconciliation mismatches start as a one-character typo in a field that was copied by hand.

How long should I keep the PDFs and sheets? Longer than you think, and in two places. Tax record retention runs to several years after the relevant financial year, so treat the archive as permanent and confirm the exact period with your CA.