How to Fetch a Trial Balance from NetSuite via API (SOAP vs. SuiteQL)

If you've ever tried to pull a NetSuite trial balance via API, you've probably discovered that NetSuite doesn't expose a single endpoint called /trialBalance. There's no REST resource that hands you a tidy debit/credit listing the way the Reports > Financial > Trial Balance screen does in the UI. Instead, you have to reconstruct the trial balance yourself, either by calling a summarized SOAP method or by aggregating the underlying general ledger tables with SuiteQL.

This post walks through both approaches, with sample code, and explains when to use each. If you'd rather skip the integration work entirely, we'll also show how CloseCore automates trial balance and GL detail extraction from NetSuite for month-end close.

What is a trial balance, and why is it hard to get from NetSuite?

A trial balance is a snapshot of every general ledger account's net balance as of a point in time (for balance sheet accounts) or over a period (for income statement accounts). Accountants use it as the starting point for reconciliations, flux analysis, financial statement preparation, and audit support.

In NetSuite, the trial balance is derived from postings on transaction, transactionline, and transactionaccountingline. A naive "give me every transaction and I'll sum it on my side" approach blows up quickly: a mid-sized NetSuite tenant easily has millions of GL lines per period. You will hit timeouts, governance limits, or paging hell long before you finish.

The two viable approaches are:

1. SuiteTalk SOAP getPostingTransactionSummary — a server-side aggregation that returns summed amounts per account per period. This is what NetSuite's own reports use, and it is the most reliable way to get a trial balance.
2. SuiteQL on transactionaccountingline — a SQL-like query that lets you SUM(amount) GROUP BY account. More flexible, but you own the correctness (period boundaries, posting flag, account type rules).

NetSuite's REST Record API is not a viable option here. There is no record type for "trial balance" or "GL summary." The closest REST option is to run SuiteQL over the REST query/v1/suiteql endpoint, which is what we'll do below.

Before you begin: Required permissions

This guide assumes you already have a working way to authenticate to the NetSuite API. NetSuite's preferred method is Token-Based Authentication (TBA) using OAuth 1.0 — you'll need an integration record, a user token, and the consumer key/secret + token ID/secret pair to sign requests. OAuth 2.0 is also supported for REST. Setting any of this up is out of scope for this post; if you don't have it yet, see Oracle's Token-Based Authentication docs.

You'll also need the right role permissions for whichever path you choose. Permissions are assigned on the role, not on the integration record or user directly, so check the role the integration runs under.

For the SuiteTalk SOAP path (getPostingTransactionSummary):

• Reports > Financial Statements — View level
• Setup > SOAP Web Services — Full level

For the SuiteQL path (REST query/v1/suiteql):

• Reports > Financial Statements — View level
• Setup > REST Web Services — Full level

You'll also want read access to the subsidiaries you intend to query.

Option 1: SOAP — getPostingTransactionSummary

getPostingTransactionSummary is part of the SuiteTalk Platform Web Services schema. You tell it which dimensions to summarize on (account is what we want here) and which filters to apply (subsidiary, period, account list), and it returns one row per dimension combination with the summed amount. This is the method NetSuite's own Trial Balance report uses under the hood, so it's the closest you can get to a 1:1 reproduction of the UI report.

Building the SOAP request

The shape of the request is small. You need:

• a <fields> block listing the dimension you want grouped by (account),
• a <filters> block with subsidiary, the accounting periods to include, and the optional account list,
• and a <pageIndex> (the response is paginated).

Don't forget to include your SOAP authentication header. Read the NetSuite documentation for SOAP authentication.

One important subtlety: NetSuite represents the opening balance as a synthetic period with internalId="-1". For balance sheet accounts, you must include this period in the filter alongside your real period IDs, or you'll only see activity inside your range. For P&L accounts, leave it out.

const body = `
  <getPostingTransactionSummary xmlns="urn:messages_2024_2.platform.webservices.netsuite.com">
    <fields>
      <platformCore:account>true</platformCore:account>
    </fields>
    <filters>
      <platformCore:period>
        ${includeOpeningBalance ? '<platformCore:recordRef internalId="-1" type="accountingPeriod"/>' : ''}
        ${periodIds.map((id) => `<platformCore:recordRef internalId="${id}" type="accountingPeriod"/>`).join('')}
      </platformCore:period>
      <platformCore:subsidiary>
        <platformCore:recordRef internalId="${subsidiaryId}" type="subsidiary"/>
      </platformCore:subsidiary>
      ${accountIds?.length
        ? `<platformCore:account>
             ${accountIds.map((id) => `<platformCore:recordRef internalId="${id}" type="account"/>`).join('')}
           </platformCore:account>`
        : ''}
    </filters>
    <pageIndex>${pageIndex}</pageIndex>
  </getPostingTransactionSummary>
`;

Walking the pages

getPostingTransactionSummary returns totalRecords, pageSize, and totalPages on every response. Send page 1, check totalRecords, then loop pages 2..N and concatenate rows. Each row gives you an account internalId and an amount — join back against your account list to attach account numbers and names.

To see the full SOAP schema, including the getPostingTransactionSummary operation definition, visit the NetSuite WSDL documentation.

Balance sheet vs. income statement accounts

Balance sheet accounts (assets, liabilities, equity) carry their balance forward forever, so for a TB you sum every posting from inception through your period end. Income statement accounts (revenue, expense, COGS) reset each fiscal year, so for a TB you sum only the postings within the period range you care about.

The cleanest implementation is to split the call: one invocation for balance-sheet accounts using all periods up to and including period end (with the opening-balance period included), and a second invocation for income-statement accounts using only periods inside your range (with the opening balance excluded).

We'll assume you have methods to fetch accounts and periods.

const balanceSheetAccounts   = await getAllAccounts({ type: 'balance_sheet' })     || [];
const incomeStatementAccounts = await getAllAccounts({ type: 'income_statement' }) || [];

const periodsThroughEnd  = await getAllPeriods({ to: periodEnd });
const periodsInRange     = await getAllPeriods({ from: periodStart, to: periodEnd });

const results = [];

if (balanceSheetAccounts.length) {
  results.push(
    ...(await getPostingTransactionSummary({
      subsidiaryId,
      periodIds: periodsThroughEnd.map((p) => p.id),
      accountIds: balanceSheetAccounts.map((a) => a.id),
      includeOpeningBalance: true,
    })),
  );
}

if (incomeStatementAccounts.length) {
  results.push(
    ...(await getPostingTransactionSummary({
      subsidiaryId,
      periodIds: periodsInRange.map((p) => p.id),
      accountIds: incomeStatementAccounts.map((a) => a.id),
      includeOpeningBalance: false,
    })),
  );
}

return results;

Skip this split and you'll either understate balance sheet accounts or grossly misstate P&L accounts (by summing every year of history into the current period).

Option 2: SuiteQL on transactionaccountingline

SuiteQL lets you run SQL-like queries against NetSuite's internal tables via the REST endpoint POST /services/rest/query/v1/suiteql. It's well suited to trial balance work because the raw GL is already in a queryable shape: transactionaccountingline (TAL) holds the per-account amounts, transaction and transactionline hold the headers, and accountingperiod holds the calendar.

A minimal call looks like:

await fetch('/services/rest/query/v1/suiteql?limit=1000', {
  method: 'POST',
  headers: { 'Content-Type': 'application/json', Prefer: 'transient' },
  body: JSON.stringify({ q: query }),
});

Pagination is via the Link response header — wrap it in an auto-paginate helper so callers don't have to think about it.

Trial balance query — income statement accounts

Sum GL amounts for P&L account types over the periods that fall inside your range, restricted to a single subsidiary:

SELECT SUM(TAL.amount)  AS glamount,
       A.acctnumber     AS acctnumber,
       A.id             AS id
FROM   transactionaccountingline TAL
       LEFT JOIN transaction       T  ON T.id  = TAL.transaction
       LEFT JOIN account           A  ON A.id  = TAL.account
       LEFT JOIN transactionline   TL ON TL.transaction = TAL.transaction
                                     AND TL.id = TAL.transactionline
       LEFT JOIN accountingperiod  AP ON AP.id = T.postingperiod
WHERE  TL.subsidiary = :subsidiaryId
  AND  TAL.posting   = 'T'
  AND  A.accttype IN ('Income', 'Expense', 'OthIncome', 'OthExpense', 'COGS')
  AND  AP.startdate >= TO_DATE(:periodStart, 'YYYYMMDD')
  AND  AP.enddate   <= TO_DATE(:periodEnd,   'YYYYMMDD')
GROUP BY A.id, A.acctnumber

Trial balance query — balance sheet accounts

Drop the start-date filter (you want everything from inception) and exclude the P&L account types:

SELECT SUM(TAL.amount)  AS glamount,
       A.acctnumber     AS acctnumber,
       A.id             AS id
FROM   transactionaccountingline TAL
       LEFT JOIN transaction       T  ON T.id  = TAL.transaction
       LEFT JOIN account           A  ON A.id  = TAL.account
       LEFT JOIN transactionline   TL ON TL.transaction = TAL.transaction
                                     AND TL.id = TAL.transactionline
       LEFT JOIN accountingperiod  AP ON AP.id = T.postingperiod
WHERE  TL.subsidiary = :subsidiaryId
  AND  TAL.posting   = 'T'
  AND  A.accttype NOT IN ('Income', 'Expense', 'OthIncome', 'OthExpense',
                          'COGS', 'NonPosting')
  AND  AP.enddate   <= TO_DATE(:periodEnd, 'YYYYMMDD')
GROUP BY A.id, A.acctnumber

Things to notice in both queries:

• TAL.posting = 'T' filters out non-posting lines (statistical journals, memo lines). Skip this and your numbers will not tie to the GL.
• The join TL.id = TAL.transactionline is mandatory — without it, transactions with multiple lines fan out and inflate balances.
• TL.subsidiary = :subsidiaryId is the canonical place to filter for a single subsidiary.

Run both queries, concatenate the results, and you have a full trial balance.

SOAP vs. SuiteQL: which one should you use?

Recommendation: If you only need the balance amounts, use getPostingTransactionSummary. It's much faster. If your NetSuite instance has a lot of transactions, pulling the trial balance via SuiteQL can be very slow. If you want a flexible reporting layer (custom groupings, ad-hoc slices) and drill-down into the transaction details, use SuiteQL. Many real integrations end up using both: SOAP for the canonical TB, SuiteQL for the drill-down.

Common pitfalls when pulling a NetSuite trial balance

• Forgetting the opening balance period. Balance sheet accounts need the magic internalId="-1" period included, or you'll only see activity inside your range.
• Mixing balance-sheet and P&L accounts in one query. Different period semantics — split them.
• Including non-posting lines. Always filter posting = 'T' (SuiteQL) or use getPostingTransactionSummary (which already excludes them).
• Forgetting to join TL.id = TAL.transactionline. Without this, journal entries with multiple lines fan out and inflate balances.
• Multi-currency. TAL.amount is in the subsidiary's base currency. If you need consolidated/parent currency or transaction currency, you'll need additional joins to currency and consolidatedexchangerate.
• Inactive accounts. Trial balances should normally include inactive accounts that still have balances. Default to including them when loading your account list.
• Period close timing. If you pull a TB while a period is still open, the numbers can change minute-to-minute. Make sure to re-fetch after period lock.
• Governance / rate limits. Both SOAP and SuiteQL count against NetSuite governance. Wrap calls with retries and timeouts; don't let them hang.

Skip the integration work: CloseCore for NetSuite

If you'd rather not maintain a NetSuite trial balance integration in-house — including handling OAuth 1.0 token-based authentication, SOAP envelopes, paging, the balance-sheet/P&L split, the opening-balance edge case, and the automatic refresh layer that makes any of this usable in production — CloseCore does it for you.

• Pre-built NetSuite connector. Trial balances, account dimensions, balance details, and journals sync automatically on a schedule (and on demand).
• Add-in for Excel. A single button to pull a NetSuite trial balance straight into any cell and drill into the transactions making up the balance, exactly like you'd pull a stock price with =STOCKHISTORY().
• Reconciliations, flux, and sign-off. Your trial balance feeds directly into reconciliations, flux analysis, and audit-ready sign-off workflows.

Whether you're a controller closing the books in five days, a SOX auditor pulling tie-out support, or an engineer trying to stop maintaining a brittle SuiteTalk script, CloseCore handles the integration so you can focus on the accounting.