Example: Unpaid Invoices Report

A comprehensive step-by-step guide to building a complete Pyodide report from scratch, including development workflow, debugging techniques, and JSON data modeling.

---

Development Workflow Overview

┌─────────────────────────────────────────────────────────────────────────────┐
│                         DEVELOPMENT WORKFLOW                                 │
└─────────────────────────────────────────────────────────────────────────────┘

  STEP 1                    STEP 2                    STEP 3
  ┌─────────────────┐      ┌─────────────────┐      ┌─────────────────┐
  │ Design JSON     │      │ Test in Jupyter │      │ Build Suitelet  │
  │ Data Structure  │ ───▶ │ Notebook (VS    │ ───▶ │ + HTML Template │
  │                 │      │ Code)           │      │                 │
  └─────────────────┘      └─────────────────┘      └─────────────────┘
         │                        │                        │
         ▼                        ▼                        ▼
  • Define fields          • Load sample JSON       • Create SuiteQL query
  • Plan data types        • Test pandas logic      • Build form UI
  • Mock sample data       • Debug transformations  • Connect HTML template
                           • Verify Excel output    • Pass JSON data

  STEP 4                    STEP 5
  ┌─────────────────┐      ┌─────────────────┐
  │ Deploy to       │      │ Test & Debug    │
  │ NetSuite        │ ───▶ │ in Browser      │
  │                 │      │                 │
  └─────────────────┘      └─────────────────┘
         │                        │
         ▼                        ▼
  • Upload files           • Console.log JSON
  • Create script record   • Verify data structure
  • Deploy suitelet        • Test Excel export

---

Step 1: Design JSON Data Structure

Before writing any code, design your JSON data structure. This is critical for smooth development.

1.1 Identify Required Fields

For an Unpaid Invoices report, we need:

Field	Type	Description	Example
internalid	number	NetSuite internal ID	12345
invoice_number	string	Transaction number	"INV-001"
invoice_date	string	Date in DD/MM/YYYY	"15/01/2025"
due_date	string	Due date	"15/02/2025"
customer_name	string	Customer display name	"PT. ABC Company"
status	string	Invoice status	"Open"
currency	string	Currency code	"IDR"
total_amount	number	Total invoice amount	15000000
amount_unpaid	number	Remaining unpaid	5000000
amount_paid	number	Already paid	10000000
unpaid_percentage	number	% still unpaid	33.33
days_overdue	number	Days past due date	30

1.2 Create Sample JSON Data

Create a file sample_invoices.json for testing:

[
  {
    "internalid": 12345,
    "invoice_number": "INV-2025-001",
    "invoice_date": "05/01/2025",
    "due_date": "05/02/2025",
    "customer_name": "PT. Maju Jaya",
    "status": "Open",
    "currency": "IDR",
    "total_amount": 15000000,
    "amount_unpaid": 15000000,
    "amount_paid": 0,
    "unpaid_percentage": 100,
    "days_overdue": 45
  },
  {
    "internalid": 12346,
    "invoice_number": "INV-2025-002",
    "invoice_date": "10/01/2025",
    "due_date": "10/02/2025",
    "customer_name": "CV. Berkah Sentosa",
    "status": "Open",
    "currency": "IDR",
    "total_amount": 25000000,
    "amount_unpaid": 10000000,
    "amount_paid": 15000000,
    "unpaid_percentage": 40,
    "days_overdue": 40
  },
  {
    "internalid": 12347,
    "invoice_number": "INV-2025-003",
    "invoice_date": "15/02/2025",
    "due_date": "15/03/2025",
    "customer_name": "PT. Maju Jaya",
    "status": "Open",
    "currency": "IDR",
    "total_amount": 8500000,
    "amount_unpaid": 8500000,
    "amount_paid": 0,
    "unpaid_percentage": 100,
    "days_overdue": 5
  }
]

1.3 Best Practices for JSON Data Modeling

┌─────────────────────────────────────────────────────────────────────────────┐
│                    JSON DATA MODELING BEST PRACTICES                         │
└─────────────────────────────────────────────────────────────────────────────┘

  ✅ DO                                    ❌ DON'T
  ─────────────────────────────────────    ─────────────────────────────────────
  • Use snake_case for field names         • Use camelCase (Python prefers snake)
  • Keep numbers as numbers, not strings   • Store "15000000" as string
  • Use consistent date format             • Mix date formats
  • Include all calculated fields          • Calculate in multiple places
  • Use null for missing values            • Use empty string "" for numbers
  • Flatten nested objects when possible   • Over-nest simple data

  FIELD NAMING CONVENTION:
  ────────────────────────
  invoice_number     ✅  (snake_case)
  invoiceNumber      ❌  (camelCase - avoid)
  InvoiceNumber      ❌  (PascalCase - avoid)
  invoice-number     ❌  (kebab-case - avoid)

---

Step 2: Develop & Debug in Jupyter Notebook

What is Jupyter Notebook?

┌─────────────────────────────────────────────────────────────────────────────┐
│                      WHAT IS JUPYTER NOTEBOOK?                               │
└─────────────────────────────────────────────────────────────────────────────┘

  Jupyter Notebook is an interactive coding environment where you can:

  ┌─────────────────────────────────────────────────────────────────────────┐
  │  CELL 1: Import libraries                          [▶ Run]  [Output ✓] │
  │  ─────────────────────────────────────────────────────────────────────  │
  │  import pandas as pd                                                    │
  │  import json                                                            │
  │                                                                         │
  │  Output: (no output - imports successful)                               │
  ├─────────────────────────────────────────────────────────────────────────┤
  │  CELL 2: Load data                                 [▶ Run]  [Output ✓] │
  │  ─────────────────────────────────────────────────────────────────────  │
  │  data = [{"name": "Test", "amount": 100}]                               │
  │  df = pd.DataFrame(data)                                                │
  │  print(df)                                                              │
  │                                                                         │
  │  Output:                                                                │
  │     name  amount                                                        │
  │  0  Test     100                                                        │
  ├─────────────────────────────────────────────────────────────────────────┤
  │  CELL 3: Process data                              [▶ Run]  [Error ❌] │
  │  ─────────────────────────────────────────────────────────────────────  │
  │  df['total'] = df['amount'] * df['qty']  # ERROR: 'qty' doesn't exist  │
  │                                                                         │
  │  Output: KeyError: 'qty'  ◄── You see the error immediately!           │
  └─────────────────────────────────────────────────────────────────────────┘

  WHY USE JUPYTER?
  ────────────────
  • Run code CELL BY CELL (not all at once)
  • See output IMMEDIATELY after each cell
  • FIX errors before moving to next step
  • EXPERIMENT with different approaches
  • KEEP working code, delete failed attempts

2.1 Set Up Jupyter in VS Code

Step 1: Install Jupyter Extension

1. Open VS Code
2. Click Extensions icon (left sidebar) or press Ctrl+Shift+X
3. Search for "Jupyter"
4. Install Jupyter by Microsoft (the one with millions of downloads)

Step 2: Create a Notebook File

1. Press Ctrl+Shift+P to open Command Palette
2. Type "Create: New Jupyter Notebook"
3. Press Enter
4. Save the file as unpaid_invoices_dev.ipynb

Step 3: Select Python Kernel

1. Click "Select Kernel" in top-right corner
2. Choose "Python 3.x.x" (your installed Python version)

┌─────────────────────────────────────────────────────────────────────────────┐
│  VS CODE WITH JUPYTER                                                        │
├─────────────────────────────────────────────────────────────────────────────┤
│  ┌─────────────────────────────────────────────────────────────────────┐   │
│  │ unpaid_invoices_dev.ipynb                    [Python 3.11.0] ▼      │   │
│  ├─────────────────────────────────────────────────────────────────────┤   │
│  │ + Code   + Markdown                                                 │   │
│  ├─────────────────────────────────────────────────────────────────────┤   │
│  │ [1]: import pandas as pd                              [▶] Run Cell  │   │
│  │      import json                                                    │   │
│  │                                                                     │   │
│  │      ✓ Cell executed successfully                                   │   │
│  ├─────────────────────────────────────────────────────────────────────┤   │
│  │ [2]: # Your next code here...                         [▶] Run Cell  │   │
│  │      |                                                              │   │
│  └─────────────────────────────────────────────────────────────────────┘   │
└─────────────────────────────────────────────────────────────────────────────┘

2.2 Jupyter Workflow: Cell by Cell Development

┌─────────────────────────────────────────────────────────────────────────────┐
│                    JUPYTER DEVELOPMENT WORKFLOW                              │
└─────────────────────────────────────────────────────────────────────────────┘

  The key principle: RUN ONE CELL → CHECK OUTPUT → FIX IF NEEDED → NEXT CELL

  ┌──────────────┐     ┌──────────────┐     ┌──────────────┐
  │  Write Code  │────▶│  Run Cell    │────▶│ Check Output │
  │  in Cell     │     │  (Shift+Enter│     │              │
  └──────────────┘     └──────────────┘     └──────┬───────┘
                                                   │
                              ┌────────────────────┴────────────────────┐
                              │                                         │
                              ▼                                         ▼
                       ┌──────────────┐                          ┌──────────────┐
                       │  Output OK?  │                          │ Output OK?   │
                       │     YES      │                          │     NO       │
                       └──────┬───────┘                          └──────┬───────┘
                              │                                         │
                              ▼                                         ▼
                       ┌──────────────┐                          ┌──────────────┐
                       │  Move to     │                          │  FIX the     │
                       │  NEXT Cell   │                          │  code, re-run│
                       └──────────────┘                          └──────────────┘

2.3 Cell-by-Cell Development (Follow Along)

CELL 1: Import Libraries (Press Shift+Enter to run)

# CELL 1: Import libraries
# Run this cell first. If no error, imports are successful.

import pandas as pd
import json
from datetime import datetime

print("✓ Libraries imported successfully!")

Expected Output:

✓ Libraries imported successfully!

If Error: You need to install the library. Open terminal and run:

pip install pandas

---

CELL 2: Create Sample Data (Run after Cell 1 succeeds)

# CELL 2: Create sample data
# This simulates what the Suitelet will send to the HTML

invoice_data = [
    {
        "internalid": 12345,
        "invoice_number": "INV-2025-001",
        "invoice_date": "05/01/2025",
        "due_date": "05/02/2025",
        "customer_name": "PT. Maju Jaya",
        "status": "Open",
        "currency": "IDR",
        "total_amount": 15000000,
        "amount_unpaid": 15000000,
        "amount_paid": 0,
        "unpaid_percentage": 100,
        "days_overdue": 45
    },
    {
        "internalid": 12346,
        "invoice_number": "INV-2025-002",
        "invoice_date": "10/01/2025",
        "due_date": "10/02/2025",
        "customer_name": "CV. Berkah Sentosa",
        "status": "Open",
        "currency": "IDR",
        "total_amount": 25000000,
        "amount_unpaid": 10000000,
        "amount_paid": 15000000,
        "unpaid_percentage": 40,
        "days_overdue": 40
    },
    {
        "internalid": 12347,
        "invoice_number": "INV-2025-003",
        "invoice_date": "15/02/2025",
        "due_date": "15/03/2025",
        "customer_name": "PT. Maju Jaya",
        "status": "Open",
        "currency": "IDR",
        "total_amount": 8500000,
        "amount_unpaid": 8500000,
        "amount_paid": 0,
        "unpaid_percentage": 100,
        "days_overdue": 5
    }
]

print(f"✓ Created {len(invoice_data)} sample invoices")
print(f"✓ First invoice: {invoice_data[0]['invoice_number']}")

Expected Output:

✓ Created 3 sample invoices
✓ First invoice: INV-2025-001

---

CELL 3: Convert to DataFrame & Inspect

# CELL 3: Convert to DataFrame and inspect
# DataFrame is like an Excel spreadsheet in Python

df = pd.DataFrame(invoice_data)

# Show basic info
print("=" * 50)
print("DATA INSPECTION")
print("=" * 50)
print(f"\nTotal rows: {len(df)}")
print(f"Total columns: {len(df.columns)}")
print(f"\nColumn names:")
for col in df.columns:
    print(f"  - {col}")

print(f"\nData types:")
print(df.dtypes)

print(f"\nFirst 2 rows:")
df.head(2)

Expected Output:

==================================================
DATA INSPECTION
==================================================

Total rows: 3
Total columns: 12

Column names:
  - internalid
  - invoice_number
  - invoice_date
  - due_date
  - customer_name
  - status
  - currency
  - total_amount
  - amount_unpaid
  - amount_paid
  - unpaid_percentage
  - days_overdue

Data types:
internalid            int64
invoice_number       object
invoice_date         object
...

Why This Step Matters:

• Verify data loaded correctly
• Check column names match what you expect
• Confirm data types (numbers should be int64/float64, not object)

---

CELL 4: Check for Data Issues

# CELL 4: Check for common data issues
# This helps catch problems BEFORE they cause errors later

print("=" * 50)
print("DATA QUALITY CHECK")
print("=" * 50)

# Check for null values
print("\n1. NULL VALUES:")
null_counts = df.isnull().sum()
if null_counts.sum() == 0:
    print("   ✓ No null values found")
else:
    print("   ⚠ Found null values:")
    print(null_counts[null_counts > 0])

# Check numeric columns
print("\n2. NUMERIC COLUMNS:")
numeric_cols = ['total_amount', 'amount_unpaid', 'amount_paid']
for col in numeric_cols:
    print(f"   {col}: min={df[col].min():,.0f}, max={df[col].max():,.0f}")

# Check for negative values (shouldn't happen)
print("\n3. NEGATIVE VALUE CHECK:")
for col in numeric_cols:
    negatives = (df[col] < 0).sum()
    if negatives > 0:
        print(f"   ⚠ {col} has {negatives} negative values!")
    else:
        print(f"   ✓ {col}: no negative values")

print("\n" + "=" * 50)
print("Data quality check complete!")

Expected Output:

==================================================
DATA QUALITY CHECK
==================================================

1. NULL VALUES:
   ✓ No null values found

2. NUMERIC COLUMNS:
   total_amount: min=8,500,000, max=25,000,000
   amount_unpaid: min=8,500,000, max=15,000,000
   amount_paid: min=0, max=15,000,000

3. NEGATIVE VALUE CHECK:
   ✓ total_amount: no negative values
   ✓ amount_unpaid: no negative values
   ✓ amount_paid: no negative values

==================================================
Data quality check complete!

---

CELL 5: Create Date Parsing Function

# CELL 5: Create function to parse dates
# We need to convert date strings to datetime objects for grouping

def parse_date(date_str):
    """
    Parse date string to datetime object.
    Handles multiple date formats.
    """
    if not date_str or date_str == '0' or str(date_str).strip() == '':
        return None

    # Try different date formats
    formats_to_try = ['%d/%m/%Y', '%Y-%m-%d', '%d-%m-%Y']

    for fmt in formats_to_try:
        try:
            return datetime.strptime(str(date_str), fmt)
        except ValueError:
            continue

    # If none worked, return None
    print(f"⚠ Could not parse date: {date_str}")
    return None

# TEST the function with different inputs
print("Testing parse_date function:")
print("-" * 40)

test_dates = ["05/01/2025", "2025-01-05", "05-01-2025", "", None, "invalid"]
for test in test_dates:
    result = parse_date(test)
    status = "✓" if result else "✗"
    print(f"  {status} Input: '{test}' → Output: {result}")

Expected Output:

Testing parse_date function:
----------------------------------------
  ✓ Input: '05/01/2025' → Output: 2025-01-05 00:00:00
  ✓ Input: '2025-01-05' → Output: 2025-01-05 00:00:00
  ✓ Input: '05-01-2025' → Output: 2025-01-05 00:00:00
  ✗ Input: '' → Output: None
  ✗ Input: 'None' → Output: None
  ⚠ Could not parse date: invalid
  ✗ Input: 'invalid' → Output: None

Debug Point: If your dates aren't parsing:

• Check the actual format in your data
• Add the format to formats_to_try list

---

CELL 6: Apply Date Parsing & Create Month Column

# CELL 6: Apply date parsing to DataFrame
# Add new columns for grouping by month

# Parse invoice_date column
df['parsed_date'] = df['invoice_date'].apply(parse_date)

# Create month_year column (e.g., "January 2025")
df['month_year'] = df['parsed_date'].apply(
    lambda x: x.strftime('%B %Y') if x else 'Unknown'
)

# Create sort key (for correct chronological order)
df['month_sort'] = df['parsed_date'].apply(
    lambda x: x.strftime('%Y%m') if x else '999999'
)

# Show results
print("Date columns added successfully!")
print("-" * 50)
print(df[['invoice_number', 'invoice_date', 'month_year', 'month_sort']])

Expected Output:

Date columns added successfully!
--------------------------------------------------
  invoice_number invoice_date    month_year month_sort
0  INV-2025-001   05/01/2025  January 2025     202501
1  INV-2025-002   10/01/2025  January 2025     202501
2  INV-2025-003   15/02/2025 February 2025     202502

---

CELL 7: Test Groupby Aggregation

# CELL 7: Group data by month
# This is the core logic for creating subtotals

month_summary = df.groupby(['month_sort', 'month_year']).agg({
    'invoice_number': 'count',
    'total_amount': 'sum',
    'amount_unpaid': 'sum',
    'amount_paid': 'sum'
}).reset_index()

# Sort by month and drop sort column
month_summary = month_summary.sort_values('month_sort')
month_summary = month_summary.drop(columns=['month_sort'])

# Rename columns
month_summary.columns = ['Month', 'Invoice Count', 'Total Amount', 'Unpaid', 'Paid']

print("Monthly Summary:")
print("=" * 70)
print(month_summary.to_string(index=False))
print("=" * 70)

# Grand total
print(f"\nGRAND TOTAL:")
print(f"  Invoices: {month_summary['Invoice Count'].sum()}")
print(f"  Amount: {month_summary['Total Amount'].sum():,.0f}")
print(f"  Unpaid: {month_summary['Unpaid'].sum():,.0f}")

Expected Output:

Monthly Summary:
======================================================================
         Month  Invoice Count  Total Amount     Unpaid       Paid
  January 2025              2      40000000   25000000   15000000
 February 2025              1       8500000    8500000          0
======================================================================

GRAND TOTAL:
  Invoices: 3
  Amount: 48,500,000
  Unpaid: 33,500,000

---

CELL 8: Test Excel Generation

# CELL 8: Test Excel file generation
# This tests the XlsxWriter code before putting it in HTML

import xlsxwriter
import io

# Create Excel file in memory
bio = io.BytesIO()
workbook = xlsxwriter.Workbook(bio, {'nan_inf_to_errors': True})
worksheet = workbook.add_worksheet('Unpaid Invoices')

# Define formats
header_fmt = workbook.add_format({
    'bold': True,
    'bg_color': '#1a56db',
    'font_color': 'white',
    'border': 1,
    'align': 'center'
})

data_fmt = workbook.add_format({
    'border': 1,
    'align': 'left'
})

money_fmt = workbook.add_format({
    'border': 1,
    'num_format': '#,##0.00',
    'align': 'right'
})

# Set column widths
worksheet.set_column(0, 0, 15)  # Invoice #
worksheet.set_column(1, 1, 25)  # Customer
worksheet.set_column(2, 2, 12)  # Date
worksheet.set_column(3, 4, 15)  # Amounts

# Write headers
headers = ['Invoice #', 'Customer', 'Date', 'Total Amount', 'Unpaid']
for col, header in enumerate(headers):
    worksheet.write(0, col, header, header_fmt)

# Write data
for row_idx, row in df.iterrows():
    worksheet.write(row_idx + 1, 0, row['invoice_number'], data_fmt)
    worksheet.write(row_idx + 1, 1, row['customer_name'], data_fmt)
    worksheet.write(row_idx + 1, 2, row['invoice_date'], data_fmt)
    worksheet.write(row_idx + 1, 3, float(row['total_amount']), money_fmt)
    worksheet.write(row_idx + 1, 4, float(row['amount_unpaid']), money_fmt)

workbook.close()

# Save to file for visual inspection
bio.seek(0)
output_filename = 'test_output.xlsx'
with open(output_filename, 'wb') as f:
    f.write(bio.getvalue())

print(f"✓ Excel file created: {output_filename}")
print(f"✓ Open the file to verify formatting looks correct!")

Expected Output:

✓ Excel file created: test_output.xlsx
✓ Open the file to verify formatting looks correct!

Action Required: Open test_output.xlsx in Excel and verify:

• Headers are blue with white text
• Numbers are formatted with commas
• All data appears correctly

---

2.4 Debug Cycle: What to Do When Errors Occur

┌─────────────────────────────────────────────────────────────────────────────┐
│                         DEBUG CYCLE                                          │
└─────────────────────────────────────────────────────────────────────────────┘

  When you get an error in a cell:

       ┌─────────────────┐
       │   RUN CELL      │
       └────────┬────────┘
                │
                ▼
       ┌─────────────────┐
       │   ERROR!        │──────────────────────────────────────┐
       │   KeyError:     │                                      │
       │   'amount'      │                                      │
       └────────┬────────┘                                      │
                │                                               │
                ▼                                               │
  ┌──────────────────────────────┐                             │
  │ 1. READ the error message    │                             │
  │    "KeyError: 'amount'"      │                             │
  │    means column doesn't exist│                             │
  └──────────────┬───────────────┘                             │
                 │                                              │
                 ▼                                              │
  ┌──────────────────────────────┐                             │
  │ 2. ADD debug print statements│                             │
  │    print(df.columns)         │                             │
  │    # See actual column names │                             │
  └──────────────┬───────────────┘                             │
                 │                                              │
                 ▼                                              │
  ┌──────────────────────────────┐                             │
  │ 3. FIX the code              │                             │
  │    Change 'amount' to        │                             │
  │    'total_amount'            │                             │
  └──────────────┬───────────────┘                             │
                 │                                              │
                 ▼                                              │
  ┌──────────────────────────────┐                             │
  │ 4. RE-RUN the cell           │◄────────────────────────────┘
  │    (Shift+Enter)             │
  └──────────────────────────────┘

2.5 Common Errors and How to Fix Them

Error 1: KeyError (Column Not Found)

# ERROR:
df['amount']  # KeyError: 'amount'

# DEBUG - Print actual columns:
print(df.columns.tolist())
# Output: ['total_amount', 'amount_unpaid', ...]

# FIX - Use correct column name:
df['total_amount']  # ✓ Works!

Error 2: TypeError (Wrong Data Type)

# ERROR:
total = df['total_amount'] + "100"  # TypeError: can't add str to int

# DEBUG - Check data type:
print(type(df['total_amount'].iloc[0]))
# Output: <class 'int'>

# FIX - Convert string to number:
total = df['total_amount'] + 100  # ✓ Works!

Error 3: AttributeError (Method Doesn't Exist)

# ERROR:
df.groupby('month').summarize()  # AttributeError: no attribute 'summarize'

# DEBUG - Check available methods:
print([m for m in dir(df.groupby('month')) if not m.startswith('_')])

# FIX - Use correct method name:
df.groupby('month').agg({...})  # ✓ Works!

2.6 When to Copy Code to HTML

┌─────────────────────────────────────────────────────────────────────────────┐
│                    WHEN IS CODE READY FOR HTML?                              │
└─────────────────────────────────────────────────────────────────────────────┘

  Your code is ready to copy to HTML when ALL of these are true:

  ✅ CHECKLIST:
  ─────────────────────────────────────────────────────────────────────────────

  □ 1. DATA LOADING works without errors
       - JSON parses correctly
       - DataFrame creates successfully
       - All expected columns exist

  □ 2. DATA TRANSFORMATIONS work correctly
       - Date parsing handles all formats
       - Calculations produce expected results
       - No NaN or null values in output

  □ 3. GROUPING/AGGREGATION produces correct totals
       - Monthly totals match manual calculation
       - Grand total is correct

  □ 4. EXCEL FILE generates and opens correctly
       - File is created without errors
       - File opens in Excel without corruption
       - Formatting looks correct (fonts, colors, numbers)

  □ 5. CODE runs from TOP TO BOTTOM without stopping
       - Run all cells in order
       - No errors at any step

  ─────────────────────────────────────────────────────────────────────────────

  IF ALL ✅ → Copy the Python code to HTML

  IF ANY ❌ → Keep debugging in Jupyter until fixed

2.7 How to Copy Code to HTML

Once all checks pass:

Step 1: Combine all working cells into one code block

# In Jupyter - Create a new cell and combine all working code:

# ============== COPY THIS CODE TO HTML ==============
import io
import base64
import pandas as pd
import xlsxwriter
from xlsxwriter.utility import xl_range
import json
from datetime import datetime

from js import array_invoices, report_from_date, report_to_date

# Load data from JavaScript
invoice_data = json.loads(array_invoices)
df = pd.DataFrame(invoice_data)

if len(df) == 0:
    raise Exception("No data to export")

df = df.fillna(0)

# Parse dates
def parse_date(date_str):
    if not date_str or date_str == '0':
        return None
    for fmt in ['%d/%m/%Y', '%Y-%m-%d', '%d-%m-%Y']:
        try:
            return datetime.strptime(str(date_str), fmt)
        except:
            continue
    return None

df['parsed_date'] = df['invoice_date'].apply(parse_date)
df['month_year'] = df['parsed_date'].apply(lambda x: x.strftime('%B %Y') if x else 'Unknown')
df['month_sort'] = df['parsed_date'].apply(lambda x: x.strftime('%Y%m') if x else '999999')

# ... rest of Excel generation code ...

# Return base64 for download
bio.seek(0)
base64.b64encode(bio.getvalue()).decode()
# ============== END CODE TO COPY ==============

Step 2: In HTML, wrap the code in Pyodide:

// In HTML file
const pythonCode = `
... paste your Python code here ...
`;

const bytesData = pyodide.runPython(pythonCode);

2.8 Debug Back-and-Forth: Jupyter ↔ HTML

When errors occur after copying to HTML, use this workflow:

┌─────────────────────────────────────────────────────────────────────────────┐
│                    JUPYTER ↔ HTML DEBUG WORKFLOW                             │
└─────────────────────────────────────────────────────────────────────────────┘

  ERROR IN BROWSER (Pyodide)
  ──────────────────────────
         │
         ▼
  ┌─────────────────────────────────────────────────────────────────────────┐
  │ 1. CHECK BROWSER CONSOLE (F12)                                          │
  │    Look for error message, e.g.:                                        │
  │    "PythonError: KeyError: 'invoice_date'"                              │
  └───────────────────────────────┬─────────────────────────────────────────┘
                                  │
                                  ▼
  ┌─────────────────────────────────────────────────────────────────────────┐
  │ 2. CHECK DATA STRUCTURE IN BROWSER                                      │
  │    Add this to HTML before Pyodide runs:                                │
  │                                                                         │
  │    console.log('Data sample:', invoiceData[0]);                         │
  │    console.log('Field names:', Object.keys(invoiceData[0]));            │
  │                                                                         │
  │    Look at console output - maybe field is 'invoiceDate' not            │
  │    'invoice_date'                                                       │
  └───────────────────────────────┬─────────────────────────────────────────┘
                                  │
                                  ▼
  ┌─────────────────────────────────────────────────────────────────────────┐
  │ 3. COPY REAL DATA TO JUPYTER                                            │
  │    From browser console, copy the actual JSON data:                     │
  │                                                                         │
  │    > copy(JSON.stringify(invoiceData))                                  │
  │                                                                         │
  │    Paste into Jupyter:                                                  │
  │    invoice_data = [paste here]                                          │
  └───────────────────────────────┬─────────────────────────────────────────┘
                                  │
                                  ▼
  ┌─────────────────────────────────────────────────────────────────────────┐
  │ 4. DEBUG IN JUPYTER WITH REAL DATA                                      │
  │    - Run cells one by one                                               │
  │    - Find which step fails                                              │
  │    - Fix the code                                                       │
  │    - Test until all cells pass                                          │
  └───────────────────────────────┬─────────────────────────────────────────┘
                                  │
                                  ▼
  ┌─────────────────────────────────────────────────────────────────────────┐
  │ 5. COPY FIXED CODE BACK TO HTML                                         │
  │    - Update the Python code in HTML file                                │
  │    - Re-upload to NetSuite                                              │
  │    - Test again in browser                                              │
  └───────────────────────────────┬─────────────────────────────────────────┘
                                  │
                                  ▼
                           ┌──────────────┐
                           │  Still Error? │───── YES ──▶ Go back to step 1
                           └──────┬───────┘
                                  │ NO
                                  ▼
                           ┌──────────────┐
                           │    DONE!     │
                           └──────────────┘

Example: Real Debug Session

SCENARIO: Excel export fails in browser

Step 1: Browser console shows:
        "PythonError: KeyError: 'trandate'"

Step 2: Check data in browser:
        console.log(invoiceData[0]);
        // Output: {invoice_date: "05/01/2025", ...}
        // Aha! Field is 'invoice_date', not 'trandate'

Step 3: In browser console, copy data:
        copy(JSON.stringify(invoiceData))

Step 4: In Jupyter, paste and test:
        invoice_data = [{"invoice_date": "05/01/2025", ...}]
        df = pd.DataFrame(invoice_data)
        print(df.columns)  # See actual column names

Step 5: Fix code - change 'trandate' to 'invoice_date'

Step 6: Copy fixed code to HTML, re-upload, test again

---

Step 3: Build Suitelet + HTML Template

Now that your Python logic is tested, build the NetSuite integration.

3.1 How Suitelet Connects to HTML

┌─────────────────────────────────────────────────────────────────────────────┐
│                    SUITELET ↔ HTML DATA FLOW                                 │
└─────────────────────────────────────────────────────────────────────────────┘

  SUITELET (Server-Side JavaScript)
  ┌────────────────────────────────────────────────────────────────────────┐
  │                                                                        │
  │  1. Query Data using SuiteQL                                           │
  │     const results = query.runSuiteQL({query: sqlQuery}).asMappedResults()│
  │                                                                        │
  │  2. Convert to JSON                                                    │
  │     const jsonData = JSON.stringify(results);                          │
  │                                                                        │
  │  3. Inject as JavaScript variables                                     │
  │     fldHtml.defaultValue = `                                           │
  │         <script>                                                       │
  │             var invoiceData = ${jsonData};    ◄── JSON injected here   │
  │             var summaryData = ${summaryJson};                          │
  │         </script>                                                      │
  │     `;                                                                 │
  │                                                                        │
  │  4. Append HTML template                                               │
  │     const template = file.load({id: './template.html'}).getContents(); │
  │     fldHtml.defaultValue += template;                                  │
  │                                                                        │
  └────────────────────────────────────────────────────────────────────────┘
                                    │
                                    ▼
  HTML TEMPLATE (Client-Side)
  ┌────────────────────────────────────────────────────────────────────────┐
  │                                                                        │
  │  <!-- Variables are already available from Suitelet injection -->      │
  │  <script>                                                              │
  │      // invoiceData and summaryData are global variables               │
  │      console.log('Data received:', invoiceData);                       │
  │      console.log('Records:', invoiceData.length);                      │
  │                                                                        │
  │      // Use in Tabulator                                               │
  │      var table = new Tabulator("#table", {                             │
  │          data: invoiceData   ◄── Use the injected data                 │
  │      });                                                               │
  │                                                                        │
  │      // Pass to Pyodide                                                │
  │      window.array_invoices = JSON.stringify(invoiceData);              │
  │  </script>                                                             │
  │                                                                        │
  └────────────────────────────────────────────────────────────────────────┘

3.2 Suitelet Script

File: unpaid_invoices_sl.js

/**
 * @NApiVersion 2.1
 * @NScriptType Suitelet
 */
define(['N/ui/serverWidget', 'N/query', 'N/file', 'N/log'],
    (serverWidget, query, file, log) => {

        /**
         * Query unpaid invoices from NetSuite
         */
        function getUnpaidInvoices(fromDate, toDate) {
            const sqlQuery = `
                SELECT
                    t.id AS internalid,
                    t.tranid AS invoice_number,
                    t.trandate AS invoice_date,
                    t.duedate AS due_date,
                    BUILTIN.DF(t.entity) AS customer_name,
                    BUILTIN.DF(t.status) AS status,
                    BUILTIN.DF(t.currency) AS currency,
                    t.foreigntotal AS total_amount,
                    t.foreignamountunpaid AS amount_unpaid,
                    (t.foreigntotal - t.foreignamountunpaid) AS amount_paid,
                    ROUND((t.foreignamountunpaid / NULLIF(t.foreigntotal, 0)) * 100, 2) AS unpaid_percentage,
                    TO_DATE('${toDate}', 'DD/MM/YYYY') - TO_DATE(t.duedate) AS days_overdue
                FROM transaction t
                WHERE t.recordtype = 'invoice'
                  AND t.foreignamountunpaid > 0
                  AND t.trandate BETWEEN TO_DATE('${fromDate}', 'DD/MM/YYYY')
                                     AND TO_DATE('${toDate}', 'DD/MM/YYYY')
                ORDER BY t.duedate ASC
            `;

            // DEBUG: Log the query
            log.debug('SQL Query', sqlQuery);

            try {
                const results = query.runSuiteQL({query: sqlQuery}).asMappedResults();

                // DEBUG: Log result count and sample
                log.debug('Result Count', results.length);
                if (results.length > 0) {
                    log.debug('Sample Record', JSON.stringify(results[0]));
                }

                return results;
            } catch (e) {
                log.error('Query Error', e);
                return [];
            }
        }

        const onRequest = (context) => {
            const params = context.request.parameters;
            const fromDate = params.custpage_fromdate;
            const toDate = params.custpage_todate;

            // Create form
            const form = serverWidget.createForm({title: 'Unpaid Invoices Report'});

            // Add date fields
            const fldFromDate = form.addField({
                id: 'custpage_fromdate',
                type: serverWidget.FieldType.DATE,
                label: 'From Date'
            });
            fldFromDate.isMandatory = true;
            if (fromDate) fldFromDate.defaultValue = fromDate;

            const fldToDate = form.addField({
                id: 'custpage_todate',
                type: serverWidget.FieldType.DATE,
                label: 'To Date'
            });
            fldToDate.isMandatory = true;
            if (toDate) fldToDate.defaultValue = toDate;

            form.addSubmitButton({label: 'Generate Report'});

            // HTML field for template
            const fldHtml = form.addField({
                id: 'custpage_html',
                type: serverWidget.FieldType.INLINEHTML,
                label: ' '
            });

            // Process POST request
            if (context.request.method === 'POST' && fromDate && toDate) {
                // Get data
                const invoiceData = getUnpaidInvoices(fromDate, toDate);

                // Calculate summary
                const summary = {
                    totalInvoices: invoiceData.length,
                    totalUnpaid: invoiceData.reduce((sum, inv) =>
                        sum + (Number(inv.amount_unpaid) || 0), 0),
                    totalAmount: invoiceData.reduce((sum, inv) =>
                        sum + (Number(inv.total_amount) || 0), 0)
                };

                // CRITICAL: Inject data as JavaScript variables
                fldHtml.defaultValue = `
                    <script type="text/javascript">
                        var invoiceData = ${JSON.stringify(invoiceData)};
                        var summaryData = ${JSON.stringify(summary)};
                        var fromDate = '${fromDate}';
                        var toDate = '${toDate}';

                        // DEBUG: Log to browser console
                        console.log('Invoice Data:', invoiceData);
                        console.log('Summary:', summaryData);
                    </script>
                `;

                // Load and append HTML template
                const htmlTemplate = file.load({
                    id: './unpaid_invoices.html'
                }).getContents();

                fldHtml.defaultValue += htmlTemplate;
            }

            context.response.writePage(form);
        };

        return { onRequest };
    });

3.3 Key Connection Points

// SUITELET: Convert array to JSON string
fldHtml.defaultValue = `
    <script type="text/javascript">
        // These become global variables in the browser
        var invoiceData = ${JSON.stringify(invoiceData)};  // Array of objects
        var summaryData = ${JSON.stringify(summary)};       // Summary object
        var fromDate = '${fromDate}';                       // String
        var toDate = '${toDate}';                           // String
    </script>
`;

// HTML TEMPLATE: Use the variables directly
<script>
    // No need to fetch or parse - variables already exist!
    console.log(invoiceData);        // Array is ready to use
    console.log(invoiceData.length); // Access properties

    // Pass to Tabulator
    var table = new Tabulator("#my-table", {
        data: invoiceData  // Direct reference
    });

    // Pass to Pyodide (must stringify again for Python)
    window.array_invoices = JSON.stringify(invoiceData);
</script>

---

Step 4: Debugging Data Structure

4.1 Debug in Browser Console

Add debug logging in your HTML template:

<script type="text/javascript">
    // ============================================
    // DEBUG SECTION - Remove in production
    // ============================================

    console.group('📊 Report Data Debug');

    // Check if data exists
    console.log('invoiceData defined:', typeof invoiceData !== 'undefined');
    console.log('summaryData defined:', typeof summaryData !== 'undefined');

    // Log data structure
    if (typeof invoiceData !== 'undefined') {
        console.log('Total records:', invoiceData.length);
        console.log('First record:', invoiceData[0]);
        console.log('Field names:', Object.keys(invoiceData[0] || {}));

        // Check data types
        if (invoiceData[0]) {
            console.log('Data types:');
            for (let key in invoiceData[0]) {
                console.log(`  ${key}: ${typeof invoiceData[0][key]}`);
            }
        }
    }

    if (typeof summaryData !== 'undefined') {
        console.log('Summary:', summaryData);
    }

    console.groupEnd();

    // ============================================
    // END DEBUG SECTION
    // ============================================
</script>

4.2 Common Data Issues and Fixes

┌─────────────────────────────────────────────────────────────────────────────┐
│                    COMMON DATA ISSUES & SOLUTIONS                            │
└─────────────────────────────────────────────────────────────────────────────┘

  ISSUE                          SOLUTION
  ─────────────────────────────  ─────────────────────────────────────────────
  Numbers as strings             Use Number() or parseFloat() in JavaScript
  "15000000" → 15000000          df['amount'] = pd.to_numeric(df['amount'])

  Null values                    Check with || 0 or fillna(0)
  null + 100 = NaN               row.get('amount', 0) or 0

  Date format mismatch           Standardize in SuiteQL query
  "2025-01-15" vs "15/01/2025"   TO_CHAR(t.trandate, 'DD/MM/YYYY')

  Empty array                    Check length before processing
  invoiceData.length === 0       if len(df) == 0: raise Exception(...)

  Missing fields                 Use .get() with defaults
  row.customer_name undefined    row.get('customer_name', 'Unknown')

4.3 Validate Data in Python (Pyodide)

# Add at the start of your Pyodide code
import json

from js import array_invoices

# Parse JSON
invoice_data = json.loads(array_invoices)

# Validation checks
print(f"Records received: {len(invoice_data)}")

if len(invoice_data) == 0:
    raise Exception("No data received from Suitelet")

# Check first record structure
first_record = invoice_data[0]
print(f"Fields: {list(first_record.keys())}")

# Check required fields exist
required_fields = ['invoice_number', 'customer_name', 'total_amount', 'amount_unpaid']
for field in required_fields:
    if field not in first_record:
        raise Exception(f"Missing required field: {field}")

# Check data types
print(f"total_amount type: {type(first_record['total_amount'])}")

# Convert to DataFrame
df = pd.DataFrame(invoice_data)

# Fill NaN values
df = df.fillna(0)

# Ensure numeric columns
numeric_cols = ['total_amount', 'amount_unpaid', 'amount_paid']
for col in numeric_cols:
    df[col] = pd.to_numeric(df[col], errors='coerce').fillna(0)

print("Data validation passed!")

---

Step 5: Complete HTML Template

5.1 Template Structure

<!-- unpaid_invoices.html -->

<!-- 1. External Libraries -->
<link rel="stylesheet" href="https://cdn.jsdelivr.net/.../tabulator.min.css" />
<script src="https://cdn.jsdelivr.net/.../tabulator.min.js"></script>
<script src="https://cdn.jsdelivr.net/pyodide/v0.28.1/full/pyodide.js"></script>

<!-- 2. Styles -->
<style>
    /* Your CSS here */
</style>

<!-- 3. HTML Structure -->
<div class="report-container">
    <div class="summary-cards">...</div>
    <div id="invoice-table"></div>
    <button onclick="downloadReport()">Export to Excel</button>
</div>

<!-- 4. JavaScript Logic -->
<script type="text/javascript">
    // Data is already available from Suitelet injection:
    // - invoiceData (array)
    // - summaryData (object)
    // - fromDate, toDate (strings)

    // Initialize Tabulator
    var table = new Tabulator("#invoice-table", {
        data: invoiceData,
        columns: [
            {title: "Invoice #", field: "invoice_number"},
            {title: "Customer", field: "customer_name"},
            // ... more columns
        ]
    });

    // Pyodide export function
    async function downloadReport() {
        // Pass data to Python
        window.array_invoices = JSON.stringify(invoiceData);

        // Load Pyodide and run Python
        let pyodide = await loadPyodide();
        // ... Python code
    }
</script>

---

Download Complete Files

Download Suitelet (.js)

Download HTML Template

---

Deployment Checklist

┌─────────────────────────────────────────────────────────────────────────────┐
│                         DEPLOYMENT CHECKLIST                                 │
└─────────────────────────────────────────────────────────────────────────────┘

  □ 1. Upload files to File Cabinet
       SuiteScripts/Reports/
       ├── unpaid_invoices_sl.js
       └── unpaid_invoices.html

  □ 2. Create Script Record
       Customization → Scripting → Scripts → New
       - Name: Unpaid Invoices Report
       - Script File: unpaid_invoices_sl.js

  □ 3. Create Deployment
       - Status: Released
       - Log Level: Debug (testing) / Error (production)
       - Audience: Select roles

  □ 4. Test in Browser
       - Open Suitelet URL
       - Check browser console for errors
       - Verify data loads correctly
       - Test Excel export

  □ 5. Production Cleanup
       - Remove console.log statements
       - Change Log Level to Error
       - Test with production data volume

---

Key Takeaways

Step	What You Learn
Design JSON	Plan data structure before coding
Jupyter Testing	Test Python logic locally first
Suitelet→HTML	JSON.stringify injects data as JS variables
Browser Debug	Use console.log to verify data structure
Pyodide	Re-stringify data for Python processing

---

Next Steps

• Example: Sales Order Report - Advanced pandas with pivot tables
• Pandas Guide - Data manipulation reference
• Troubleshooting - Common issues and fixes