Pull NetSuite Data into Excel with SuiteQL

You don't need a paid iPaaS connector to get NetSuite data into Excel. With a SuiteQL query, a RESTlet, and Power Query's Advanced Editor, you can build a direct pipeline that authenticates with Token-Based Authentication (TBA) and refreshes on demand. This walkthrough shows the exact setup, including the OAuth 1.0 signature generation that most articles skip.

What You're Building

The approach has three moving parts:

1. A RESTlet that accepts a SuiteQL query and returns the result set as JSON.
2. A Power Query function in Excel that builds an OAuth 1.0 signature, posts the query, and parses the response.
3. A set of named parameters (account ID, consumer key, token ID, and the rest) so you never paste secrets into the query itself.

Think of the RESTlet as the bridge between Excel and the SuiteAnalytics data source. Excel never talks to NetSuite directly. It talks to your RESTlet, which runs the query inside NetSuite and hands back the rows.

Step 1: Set Up the Integration and Capture Credentials

Go to Setup > Integration > Manage Integrations > New and create a token-based authentication integration. During setup, note these five values:

• Account ID
• Consumer Key
• Consumer Secret
• Token ID
• Token Secret

The realm differs for sandbox. Take your account ID, replace the dash with an underscore, and uppercase the alpha characters. Account 12345-sb1 becomes realm 12345_SB1. This trips people up because the realm looks wrong if you leave it lowercase. The NetSuite Basics Guide confirms that sandbox account IDs contain an underscore while the account-specific domain URL uses a hyphen.

Then create a TBA token at Setup > Users/Roles > Access Tokens > New. Assign the token to a role with access to the RESTlet. If you use the Data Warehouse Integrator role, you can query the netsuite2 data source without building a custom role. Oracle's documentation on NetSuite account types covers the sandbox naming convention in more detail.

Step 2: Deploy the RESTlet

Install a SuiteQL RESTlet. Tim Dietrich's SuiteAPI is the common choice because it exposes a queryRun procedure that accepts a query string and returns records as JSON. Deploy it and note the script internal ID and deployment ID from the deployment page.

The RESTlet's queryRun procedure receives a body like this:

{
  "procedure": "queryRun",
  "query": "SELECT id, entityid, email FROM employee WHERE isinactive = 'F'"
}

It returns a JSON object with a records array. That array is what Power Query will expand into a table.

Step 3: Build the Power Query with OAuth 1.0 Signing

This is where most integrations break. NetSuite's RESTlet endpoints require an OAuth 1.0 authorization header, and the signature must be HMAC-SHA256 over the exact request method, URL, and parameter string. Excel's built-in Web.Contents does not sign requests for you, so you generate the signature in M.

Open Data > Get Data > From Other Sources > Blank Query, then open the Advanced Editor. Define your parameters first, either as named parameters in the query or in a parameter table:

• NSAccountID, e.g. 12345-sb1
• NSScriptInternalID, the RESTlet script ID, e.g. 676
• NSRealm, e.g. 12345_SB1
• NSConsumerKey, NSConsumerSecret, NSTokenId, NSTokenSecret
• NSQRYProcedure, queryRun
• NSQRYEeTable, your SuiteQL query, e.g. SELECT id FROM employee

The M query builds the base URL, computes the timestamp and nonce, constructs the signature base string, and calls a small HMAC-SHA256 helper to sign it. The signature uses your consumer secret and token secret as the key:

secret = Text.Combine({consumersecret, "&", tokensecret})

The authorization header then goes into Web.Contents alongside the JSON payload:

response = Web.Contents(url, [Headers = headers, Content = postData]),
jsonResponse = Json.Document(response),
records = jsonResponse[records]

From records, use Table.FromList and Table.ExpandRecordColumn to turn the JSON array into a proper Excel table. The column list in that last step must match the fields your query returns. If your SuiteQL selects id, entityid, email, expand exactly those three.

Validate the Handshake with a Single Record

Before you run a heavy query, test with one row. Set NSQRYEeTable to:

SELECT id, entityid, email FROM employee WHERE id = 1

Refresh the query. If the signature is wrong, you'll get a 401 or {"error": {"code": "INVALID_LOGIN_ATTEMPT"}}. The error response tells you exactly what went wrong. A successful run returns one row with the employee's ID, entity ID, and email. This confirms the OAuth handshake, the RESTlet deployment, and the JSON parsing all work end to end.

Filter Early, Not in Excel

The whole point of running SuiteQL server-side is to shrink the payload before it crosses the wire. Push your filters into the WHERE clause rather than pulling everything and filtering in Power Query. For example, limit to active customers or transactions in the last three years:

SELECT id, trandate, total
FROM transaction
WHERE trandate >= BUILTIN.RELATIVE_RANGES('TSTARTYR', 'START')
  AND trandate <= BUILTIN.RELATIVE_RANGES('TSTARTYR', 'END')

This keeps refresh times reasonable and avoids hitting the 100,000-row ceiling that SuiteQL enforces. Oracle's SuiteQL execution guide confirms this limit applies when queries run through REST web services. If you need more than that, switch the RESTlet to page through results with query.runSuiteQLPaged() and a limit and offset loop.

Where This Approach Breaks Down

Three failure points are common.

Expired or revoked tokens. If the token is disabled or the integration is deactivated, every refresh fails with an auth error. Store the five credential values in your password manager, not hard-coded in the query, so you can rotate them without editing the M code.

Column mismatch in Table.ExpandRecordColumn. If your query returns more fields than you expand, the extra columns are silently dropped. If it returns fewer, the expansion throws an error. Keep the query and the expand list in sync.

Signature drift from URL encoding. The base URL and the concatenated parameters both get Uri.EscapeDataString applied, and the order of the OAuth parameters in the signature base string is significant. Reorder anything and the signature fails.

A Cleaner Option: Keep the Query in NetSuite

One refinement worth adopting: don't embed the SuiteQL string in Excel at all. Store the query inside the RESTlet, keyed by a procedure name, and have Excel send only the procedure name plus any parameters. This keeps your SQL in version control in NetSuite, avoids pasting long queries into M, and means a query change does not require every user to update their workbook.

The trade-off is flexibility. A fixed-query RESTlet is safer but less ad hoc. If analysts need to write their own queries, the parameterized version above is the way to go.

Test this with a single record first, confirm the handshake, then scale the query up. Once the pipeline runs clean, you can schedule refreshes in Power Query and treat the RESTlet as your standard data source for ad hoc NetSuite analysis.