NetSuite

Connection Dialog

Excel Add-in for NetSuite supports both Basic and Token-Based authentication. Connection dialog box currently allows only Basic authentication. Token-based authentication can be configured only via Advanced connection options. Note that 2-factor authentication is required for NetSuite users with highly privileged roles. This means that such NetSuite user account cannot be used for basic authentication. You can find more information about Token-based authentication below.

The following connection options are required for basic authentication. Only Account Id is required for both authentication types :

1.	Account Id - NetSuite account id. You can get the account id value in NetSuite Web Services Preferences. To open it, sign in to your NetSuite account. Then point to Setup, then to Integrations, and then click Web Services Preferences.

2.	Application Id - The NetSuite CRM application ID created by using the integration record.

3.	User Id - The NetSuite account email to login with;

4.	Password - NetSuite account password.

To generate the application id parameter value, you need to configure Integration Record in your NetSuite account settings. For this, perform the following actions:

1.	Sign in to your NetSuite account.

2.	Point to Setup, then to Integrations, then Manage Integrations, and then click New.

3.	On the opened page enter the Name for the application, for example, Excel Add-in.

4.	Make sure the Token Based Authentication check box is cleared and the User Authentication check box is selected.

5.	Click Save.

6.	Copy the generated Application ID. You may reuse this value if you need multiple connections to this NetSuite account.

Important Note

Excel Add-ins support for NetSuite is somewhat limited. Excel Add-in for NetSuite does not support NetSuite fields, storing array data. It cannot import data from such fields to Excel, and they are not displayed in the interface. If such field is required in the table (must be filled in order to create a new record), Excel Add-in for NetSuite cannot load data to such tables. Thus, such tables are read-only in Excel Add-in for NetSuite.

The list of such objects, for example, includes all the transactions objects, because Excel Add-in for NetSuite cannot import their items. Here is the list of such objects: BinWorksheet, CashRefund, CashSale, Check, CreditMemo, Estimate, Invoice, Opportunity, PurchaseOrder, ReturnAuthorization, SalesOrder, TransferOrder, VendorBill, VendorCredit, VendorReturnAuthorization, WorkOrder, Deposit, InventoryAdjustment, InventoryTransfer, ItemSupplyPlan, JournalEntry, VendorPayment, SerializedAssemblyItem, ExpenseReport, ManufacturingRouting, PaycheckJournal, ManufacturingOperationTask, CustomerRefund, InventoryAdjustment, InventoryCostRevaluation, ManufacturingCostTemplate.

Excel Add-in for NetSuite cannot load data to these tables, and cannot load certain data from them.

Advanced Connection Options

To open Advanced connection options, click the Advanced button in the Connection dialog box. Here you can set parameters for Token-based authentication and additional parameters to control connection behavior. For Token-based authentication, you need to set the Authentication parameter to TokenBased and specify values for the following parameters: Consumer Key, Consumer Secret, Token, Token Secret.

The following table describes the options that you can include in the Connection String using the Advanced Connection Editor dialog box:

Option	Description
Account Date Format	Specifies the date format configured in your NetSuite account, so that date values are correctly interpreted when reading and writing data.
Account Id	Specifies the NetSuite account id.
Account Time Zone	Specifies the time zone configured in your NetSuite account, which is used to correctly interpret date and time values.
API Version	Determines the version of the NetSuite API to use.
Application Id	Specifies the NetSuite CRM application ID created by using the integration record. Required for Basic authentication.
Authentication Type	Determines authentication kind to use. It can be Basic or TokenBased. Note that highly privileged user accounts cannot be used for Basic authentication.
Client Id	Specifies the client ID of the NetSuite integration record. The value is used for OAuth 2.0 authentication.
Client Secret	Specifies the client secret of the NetSuite integration record. The value is used for OAuth 2.0 authentication.
Connection Lifetime	Specifies the time span (in seconds), compared against a connection's creation time when it is returned to the pool; the connection is destroyed if this span is exceeded. The default value is 0 (connection always returns to pool).
Connection Timeout	Specifies the length of time (in seconds) to wait for a connection to the server before terminating the attempt and generating an error. The default value is 60.
Consumer Key	Specifies the NetSuite consumer key from an integration record. It is required only for Token-Based authentication. Read how to obtain it below.
Consumer Key Secret	Specifies the NetSuite consumer secret from an integration record. It is required only for Token-Based authentication. Read how to obtain it below.
Default Command Timeout	Specifies the time in seconds to wait while trying to execute a command before terminating the attempt and generating an error.
Failover Retries	Specifies the number of retry attempts to automatically re-run a command before displaying an error message in case when the command failed because of an error that may be temporary.
Initialization Command	Specifies a database-specific command to execute after establishing the connection.
Language	Specifies the language used to retrieve translatable NetSuite field and record labels.
Local SQL Engine	Determines whether to use local SQL engine for SELECT queries, that are too complex to translate them directly to NetSuite API calls or to show an error message.
Max Pool Size	Specifies the maximum number of connections allowed in the pool. Setting the Max Pool Size value in the connection string can affect performance. The default value is 100.
Min Pool Size	Specifies the minimum number of connections allowed in the pool. The default value is 0.
NormalizeDbNames	Determines whether database object names are normalized when retrieved from the database.
Password	Specifies the NetSuite account password. Required for Basic authentication.
Persist Security Info	Indicates if security-sensitive information, such as the password, is not returned as part of the connection if the connection is open or has ever been in an open state.
Pooling	Draws the connection from the appropriate pool, or creates and adds it to the appropriate pool, when set to true. This is the default behavior.
Proxy Host	Specifies the host name or IP address of the proxy server.
Proxy Password	Specifies the password for the proxy server account.
Proxy Port	Specifies the port number of the proxy server. The default value is 3128.
Proxy User	Specifies the proxy server account name.
Readonly	Indicates whether the connection is read-only (allows only SELECT statements).
Refresh Token	Stores the OAuth 2.0 refresh token for NetSuite. The value is obtained automatically when you perform Web Login.
Role Id	Specifies the InternalId of the role for basic authentication that will be used to log in the user with limited permissions. If it is not specified, the user's default role will be used.
Run Once Command	Specifies a database-specific command to execute after establishing the connection. Unlike Initialization Command, it is not executed for connections obtained from the connection pool.
Token Id	Specifies the authentication token, generated by a NetSuite user. It is required only for Token-Based authentication. Read how to obtain it below.
Token Secret	Specifies the authentication token secret, generated by a NetSuite user. It is required only for Token-Based authentication. Read how to obtain it below.
Token Server	Is not used for NetSuite.
Use Custom Fields	Set this parameter to True if you need to work with custom fields of predefined NetSuite objects. However, please note that processing custom fields may take a substantial amount of time, and it's better to leave the default False value if you don't need to work with custom fields.
Use Custom Tables	Set this parameter to True if you need to work with custom NetSuite objects. However, please note that processing custom objects may take a substantial amount of time, and it's better to leave the default False value if you don't need to work with custom NetSuite objects.
Use Custom Lists	Set this parameter to True if you need to work with custom NetSuite lists. However, please note that processing them may take a substantial amount of time, and it's better to leave the default False value if you don't need to work with NetSuite custom lists.
Use Display Names	Determines whether to use NetSuite field display names instead of internal names as column names.
User Id	Specifies the NetSuite account email to login with. Required for Basic authentication.
UTC Dates	Converts dates to UTC format during import when set to True; the date values entered by the user (in SELECT statements, etc.) are then considered UTC dates when sending them to the data source.

To use token-based authentication in NetSuite, you need to perform the following actions:

1.	Enable token-based authentication in NetSuite. For this, perform the following actions:

a.	Point to Setup, then to Company, then click Enable Features.

b.	Then click Suite Cloud.

c.	Finally, in the Manage Authentication group, select the Token-based Authentication check box.

2.	Create a role with the necessary privileges for token-based authentication and access to the necessary objects and assign it to the user that will be used for authentication. You need to assign at least the “Web Services” and “User Access Tokens” privileges.

3.	Create an integration record with Token-Based authentication enabled. For this:

a.	Point to Setup, then to Integrations, then Manage Integrations, and then click New.

b.	In the opened page, enter Name for the application, for example, Skyvia.

c.	Make sure the Token Based Authentication check box is selected.

d.	Click Save.

From the confirmation screen, copy the generated Consumer Key, and Consumer Secret. Note that Consumer Key and Consumer Secret are displayed only once on this page. If you need them again, you will have to re-generate them, which make the old ones to stop working. You may reuse these values if you need multiple Skyvia connections to this NetSuite account.

4.	Create Access Token.

a.	Point to Setup, then to Users/Roles, then Access Tokens, and then click New.

b.	Select the Integration record, User, and Role created on the previous steps.

c.	From the confirmation screen, copy the generated Token ID and Token Secret. Note that Token ID and Token Secret are displayed only once on this page. If you need them again, you will have to re-generate them, which make the old ones to stop working.

NetSuite

Connection Dialog

Advanced Connection Options

Option

Description