How to Query NetSuite with the REST API and SuiteQL
Read NetSuite data through the SuiteTalk REST API with SuiteQL queries (open orders, unpaid invoices, customer balances, stock by location) and use the results in any workflow, without building a RESTlet.
What This Integration Does
NetSuite's REST API has two ways to read data: record endpoints such as /record/v1/salesOrder/123, which return one record with all its fields, and SuiteQL, a SQL dialect sent to /query/v1/suiteql that returns rows across many records in one call. For reporting, syncing and lookups, SuiteQL is almost always the better choice: it is faster, it joins tables, and it costs far fewer API calls. Spojit's NetSuite connector signs every request with Token-Based Authentication, so you write the query and Spojit handles OAuth 1.0 signing, paging and errors. This tutorial sets up the connection, shows SuiteQL queries for the questions teams ask most, and turns one into a scheduled report.
SuiteQL is read-only, so running these queries never changes NetSuite data. The example workflow runs every morning and posts a summary; to write data back, use the connector's record tools such as upsert-record or update-record.
Prerequisites
- A NetSuite account with Token-Based Authentication and REST Web Services enabled (Setup -> Company -> Enable Features -> SuiteCloud).
- An integration record (Consumer Key and Secret) and an access token (Token ID and Secret) for a role with REST Web Services, SuiteAnalytics Workbook and view permission on the records you query.
- A NetSuite connection in Spojit with the Account ID and the four credentials. Run
verify-connectiononce to confirm it works.
Step 1: Discover Tables and Fields
NetSuite accounts are customised, so field names differ between accounts. Before writing queries, check what yours exposes: the NetSuite connector's list-record-types and get-record-metadata tools return the record types and fields for your account, and Oracle's Analytics Data Source Schema Browser lists the SuiteQL tables and columns. Custom fields appear with their script IDs, such as custbody_delivery_date.
Step 2: Write the Query
Add a Connector node on NetSuite in Direct Mode with run-suiteql. These queries cover the most common requests:
Open sales orders waiting to ship:
SELECT t.tranid, t.trandate, BUILTIN.DF(t.entity) AS customer,
BUILTIN.DF(t.status) AS status, t.foreigntotal AS total
FROM transaction t
WHERE t.type = 'SalesOrd'
AND t.status IN ('SalesOrd:B', 'SalesOrd:D', 'SalesOrd:E')
ORDER BY t.trandateUnpaid invoices past due:
SELECT t.tranid, BUILTIN.DF(t.entity) AS customer, t.duedate,
t.foreignamountremaining AS amount_due
FROM transaction t
WHERE t.type = 'CustInvc'
AND t.foreignamountremaining > 0
AND t.duedate < CURRENT_DATE
ORDER BY t.duedateCustomers changed since a date (for incremental syncs):
SELECT id, entityid, companyname, email, lastmodifieddate
FROM customer
WHERE lastmodifieddate >= TO_DATE('2026-10-01', 'YYYY-MM-DD')BUILTIN.DF() returns the display value of a list or record field (the customer's name instead of its internal ID, "Pending Fulfillment" instead of SalesOrd:B). Status codes and transaction type IDs vary slightly between accounts, so run a quick SELECT DISTINCT status FROM transaction WHERE type = 'SalesOrd' first to see yours.
Step 3: Page Through Large Results
SuiteQL returns rows in pages. run-suiteql takes limit (up to 1,000 rows per page) and offset. For larger results, wrap the step in a Loop in While mode that increases offset by the page size while the response says more rows exist. Name the collected rows rows.
Step 4: Use the Results
As an example, turn the overdue invoice query into a morning report. Add a Trigger with type Schedule (0 8 * * 1-5) at the start, then after the query:
- A Connector node in Agent Mode on the Text Tools connector (its tools only reshape text, so the step cannot change anything) that summarises
{{ rows }}: total overdue, the five largest balances and anything more than 60 days late. - A Slack step with
send-messageto your finance channel, or a Send Email node to the credit controller.
The same query output can feed a MySQL table, a PDF Tools create-pdf report, or a Power BI dataset.
Step 5: Fetch Full Records When You Need Them
SuiteQL rows are flat. When you need one record with its sublists (all lines of a sales order, for example), call get-sales-order, get-invoice or the generic get-record with the ID from the query. For endpoints the tools do not cover, raw-request sends any signed call relative to /services/rest, such as GET /record/v1/vendorBill/456.
Tips
- Select only the columns you need: SuiteQL is fast, but wide selects over
transactionare slow. Name columns rather than usingSELECT *. - Join lines when you need items:
transactionlineholds item lines; join ontransactionline.transaction = transaction.idand filtermainline = 'F'to skip the header line. - Let AI write the first draft: an Agent Mode step on the NetSuite connector can turn "show me unpaid invoices over $5,000" into SuiteQL and run it. Copy the query it used into a Direct Mode step for scheduled runs.
- RESTlets are optional: SuiteQL and the record API cover most needs. Keep RESTlets for logic that must run inside NetSuite.
Common Pitfalls
- Role permissions: SuiteQL only returns rows the token's role can see. An empty result usually means a missing permission, not missing data.
- Timezones:
lastmodifieddateandtrandateuse the account's timezone. Convert your window boundaries to it, or records near midnight fall into the wrong day. - TBA and NetSuite 2027.1: Oracle has said no new TBA authorisations can be created from NetSuite 2027.1 onward; existing tokens keep working. Create the token you need before your account upgrades.
- Concurrency limits: NetSuite limits concurrent web service requests per account. Avoid running many SuiteQL steps in parallel against the same account.
Testing
Run each query from a Manual workflow with FETCH FIRST 10 ROWS ONLY added, and compare the rows with a saved search in NetSuite. When the numbers agree, remove the limit, add the schedule, and check the first scheduled run's output.