Google Sheets Integration

Calculate U.S. surplus lines taxes directly in your spreadsheets with custom functions. No automation platform required—just use our pre-configured template!

What's Included in the Template?

• ✅ All 8 Custom Functions Pre-Installed — Calculate taxes, get rates, fetch state data, look up historical rates
• ✅ 5 Ready-to-Use Worksheets — Quick start, bulk calculator, rate lookup, detailed breakdown, reference guide
• ✅ Works Like Built-in Functions — Familiar spreadsheet formula syntax
• ✅ No Code Copying Required — Just add your API key and go
• ✅ Perfect for Manual Workflows — One-off calculations and bulk processing
• ✅ Same Pricing — $0.38 per calculation (same as REST API)
• ✅ 100 Free Calculations — Included when you sign up

Prerequisites

Before installing, ensure you have:

1. Google Account

   Access to Google Sheets via your Google/Workspace account

2. API Key

   Get your API key at app.surpluslinesapi.com. New accounts include 100 free queries.

Quick Start (Using Template)

Get started in just 2 minutes with our pre-configured template!

Step 1: Copy the Template

1. Click the button below to open our template
2. Click File → Make a copy
3. Give your copy a name (e.g., "Surplus Lines Calculator 2026")

Step 2: Get Your API Key

1. Go to app.surpluslinesapi.com
2. Sign in or create an account (100 free queries included)
3. Copy your API key from the dashboard

Step 3: Add Your API Key to Your Copy

1. In your Google Sheet copy, click Extensions → Apps Script
2. Find line 22:

   const API_KEY = 'YOUR_API_KEY_HERE';

3. Replace YOUR_API_KEY_HERE with your actual API key
4. Click the Save icon (💾)
5. Close the Apps Script tab

Step 4: Start Calculating!

Explore the 5 pre-configured worksheets:

• Quick Start — Basic tax calculations
• Bulk Calculator — Process multiple states at once
• Rate Lookup — All 53 states with tax rates
• Detailed Breakdown — Premium + Tax + Total Due
• Reference Guide — Function examples and syntax

Alternative: Manual Installation

Prefer to install the script manually? Skip to manual installation instructions

Testing Your Setup

Using the Template (Automatic)

The template includes pre-configured examples that will automatically calculate once you add your API key:

1. Open the "Quick Start" tab
2. The formulas should automatically run
3. If you see #ERROR!, click the cell and authorize:
   • Click "Review Permissions"
   • Select your Google account
   • Click "Allow"
4. Formulas should now work across all worksheets!

Test in Apps Script Editor (Optional)

1. In the Apps Script editor, find the function testApiConnection()
2. Select it from the function dropdown
3. Click the Run button (▶️)
4. Check the Execution log at the bottom
5. You should see "✅ SUCCESS! API is working correctly"

Quick Test in Any Sheet

Try this formula in any cell:

=CALCULATE_TAX("California", 10000)

Expected Result: 318

CALCULATE_TAX(state, premium)

Calculate total tax amount for a state and premium. Returns a single number.

Parameters

Returns

Total tax amount (single number). API response includes state_code (e.g., "CA").

Example

=CALCULATE_TAX("California", 10000)
=CALCULATE_TAX("CA", 10000)   // State codes work too!

Returns: 318

API Response (Internal)

{
  "success": true,
  "state": "California",
  "state_code": "CA",
  "premium": 10000,
  "total_tax": 318,
  "total_due": 10318,
  "account": {
    "balance": "0.00",
    "free_queries_remaining": 99,
    "was_free_query": true
  }
}

CALCULATE_TAX_DETAILS(state, premium, [multiline])

Get detailed breakdown including state, premium, total tax, and total due.

Parameters

Returns

Tax breakdown (horizontal by default or vertical if multiline=TRUE). Includes state_code in API response.

Example (Single Row - Default)

=CALCULATE_TAX_DETAILS("California", 10000)
=CALCULATE_TAX_DETAILS("CA", 10000)   // State codes work too!

Returns (4 cells horizontally):

California    10000    318    10318

Example (Multi-Row)

=CALCULATE_TAX_DETAILS("California", 10000, TRUE)

Returns (4 cells vertically):

State: California
Premium: 10000
Total Tax: 318
Total Due: 10318

CALCULATE_WITH_PREMIUM(state, premium)

Calculate tax and return premium, total tax, and total due in a horizontal row.

Parameters

Returns

Array with [premium, total_tax, total_due] in 3 horizontal columns

Example

=CALCULATE_WITH_PREMIUM("California", 10000)

Returns (3 cells horizontally):

10000    318    10318

(Premium, Total Tax, Total Due)

GET_TAX_RATE(state)

Get just the tax rate percentage for a state.

Parameters

Returns

Tax rate as a percentage (e.g., 3 for 3%). API response includes state_code.

Example

=GET_TAX_RATE("California")
=GET_TAX_RATE("TX")   // State codes work too!

Returns: 3 (for California), 4.85 (for Texas)

GET_STATES()

Get list of all supported states and territories as 2-letter postal codes.

Parameters

None

Returns

Vertical array of 53 postal codes (one per cell)

Example

=GET_STATES()

Returns (1 column, 53 rows):

AL
AK
AZ
...
TX
...
WY

GET_RATES()

Get tax rates for all states.

Parameters

None

Returns

3-column array with state names, state codes, and tax rates (53 rows × 3 columns)

Example

=GET_RATES()

Returns (3 columns, 53 rows):

Texas         TX    4.85
California    CA    3
New York      NY    3.6
...

GET_RATES_DETAILS()

Get detailed rate information for all states including tax rate, stamping fee, filing fee, service fee, surcharge, regulatory fee, fire marshal tax, SLAS clearinghouse fee, flat fee, and legislative source.

Parameters

None

Returns

Multi-column array with all rate details (53 rows × 12 columns, including state_code)

Example

=GET_RATES_DETAILS()

Returns (53 rows with 12 columns each):

State         | Code | Tax Rate | Stamping Fee | Filing Fee | Service Fee | Surcharge | Regulatory Fee | Fire Marshal Tax | SLAS Fee | Flat Fee | Legislative Source
Texas         | TX   | 4.85%    | 0.04%        | null       | null        | null      | null           | null             | null     | null     | https://...
California    | CA   | 3%       | 0.18%        | null       | null        | null      | null           | null             | null     | null     | https://...
New York      | NY   | 3.6%     | 0.15%        | null       | null        | null      | null           | null             | $25      | null     | https://...
...

GET_HISTORICAL_RATE(state, date)

Look up tax rates that were in effect on a specific date. Perfect for policy audits, renewals, and historical compliance verification.

Parameters

Returns

Tax rate that was in effect on the specified date (single number as percentage). API response includes state_code.

Example

=GET_HISTORICAL_RATE("Iowa", "2025-06-15")
=GET_HISTORICAL_RATE("IA", "2025-06-15")   // State codes work too!

Returns: 0.95 (representing 0.95% tax rate in effect on that date)

Use Cases

• Policy Audits — Verify taxes were calculated correctly using rates in effect at policy inception
• Renewals — Compare current rates to prior year rates for renewal quotes
• Compliance — Document historical rate information for regulatory filings
• Retroactive Calculations — Calculate taxes for backdated policies

Usage Examples

Common use cases for the Google Sheets functions:

// Add 5% markup to tax
=CALCULATE_TAX(A2, B2) * 1.05

// Total all taxes
=SUM(C2:C10)    // where C2:C10 contain CALCULATE_TAX formulas

// Conditional calculation
=IF(B2>10000, CALCULATE_TAX(A2, B2), 0)

Pro Tips

• Drag Formulas Down — Copy formulas down to calculate taxes for multiple rows
• Copy Values Only — For large spreadsheets, copy calculated values and paste as "Values only" to avoid repeated API calls
• Performance — Functions make live API calls (1-2 seconds each)

Error Handling

Common Errors

API Error Codes

FAQ

How much does each calculation cost?

$0.38 per calculation (same as the REST API). New accounts include 100 free queries.

Do formulas recalculate automatically?

Yes. Functions run when the cell is created, sheet is opened/reloaded, referenced cells change, or manual recalculation (Ctrl+R or Cmd+R).

Can I use this in shared spreadsheets?

Yes. Shared spreadsheets use the owner's API key and credits. For advanced security, see the README for using Google Apps Script Properties Service.

What's the difference between CALCULATE_TAX and CALCULATE_TAX_DETAILS?

CALCULATE_TAX returns just the total tax number. CALCULATE_TAX_DETAILS returns state, premium, total tax, and total due in separate cells.

Can I process multiple policies at once?

Yes! Drag formulas down to calculate taxes for multiple rows. Each row makes one API call.

Where is my API key stored?

In the Apps Script code, accessible only to the spreadsheet owner. For shared sheets, consider using Properties Service for secure storage.

Manual Installation (Advanced)

If you prefer not to use the template, you can manually install the script:

Step 1: Get Your API Key

1. Go to app.surpluslinesapi.com
2. Sign in or create an account
3. Copy your API key from the dashboard

Step 2: Install the Script

1. Open a new Google Sheet or existing sheet
2. Click Extensions → Apps Script
3. Delete any existing code in the editor
4. Copy the script code from the documentation (surplus-lines-tax.gs)
5. Paste the entire script into the Apps Script editor
6. Find line 22: const API_KEY = 'YOUR_API_KEY_HERE';
7. Replace YOUR_API_KEY_HERE with your actual API key
8. Click the Save icon (💾)
9. Close the Apps Script tab

Step 3: Use the Functions

Start using the 7 custom functions in your spreadsheet. See the Functions Reference for examples.

Pricing

Google Sheets integration follows the same pricing as the REST API:

Get Your API Key View Full Documentation 📊 Get Free Template