Spreadsheets in NetSuite SuiteScripts

NetSuite is a suite of cloud-based software systems for Enterprise Resource Planning (ERP). It has a robust scripting interface.¹

SheetJS is a JavaScript library for reading and writing data from spreadsheets.

This demo explores the SuiteScript scripting features in NetSuite. We'll explore how to use SheetJS in SuiteScripts for reading and writing files in NetSuite.

Tested Deployments

This demo was verified by NetSuite consultants in the following deployments:

@NScriptType	@NApiVersion	Date
ScheduledScript	2.1	2024-05-01
Restlet	2.1	2024-05-01
Suitelet	2.1	2024-05-01
MapReduceScript	2.1	2024-05-01

See issue #3058 in the issue tracker for more examples submitted by NetSuite consultants.

Installation

In SuiteScript parlance, third-party scripts are "Custom Modules"².

The SheetJS AMD script can be uploaded to the file cabinet and referenced in the define call in SuiteScripts.

SheetJS scripts have been tested against the Rhino JavaScript engine³ and work in both SuiteScript 2.0 and SuiteScript 2.1 deployments.

Adding SheetJS Scripts

The SheetJS standalone script should be uploaded to the File Cabinet.

It is strongly recommended to keep the original filename xlsx.full.min.js.

JSON Configuration

Assuming the uploaded file was named xlsx.full.min.js, the paths object in the JSON configuration should reference xlsx.full.min. The reference can be absolute or relative⁴.

For example, if the script xlsx.full.min.js was placed in the SuiteScripts top-level directory, the config should use "/SuiteScripts/xlsx.full.min":

JsLibraryConfig.json

{
  "paths": {
    "xlsx": "/SuiteScripts/xlsx.full.min"
  }
}

Relative references are also supported. If the entire project is stored in one folder, the config can use "./xlsx.full.min":

JsLibraryConfig.json

{
  "paths": {
    "xlsx": "./xlsx.full.min"
  }
}

SuiteScript Usage

The JSON configuration file should be referenced in SuiteScripts using @NAmdConfig. The path alias "xlsx" should be passed to define:

/**
* @NApiVersion 2.x
* @NAmdConfig  ./JsLibraryConfig.json
* ... more options ...
*/
define(['N/file', 'xlsx'], function(file, XLSX) {
  // ... use XLSX here ...
});

Sheets in the File Cabinet

The NetSuite File Cabinet⁵ is the primary feature for storing documents.

N/file is the primary module for interacting with the File Cabinet⁶. This section assumes that N/file is bound to the variable file:

define(
  ['N/file', 'xlsx'],
  function(
    file, // 'N/file'
    XLSX  // 'xlsx'
  ) {
    // ...
  }
);

Reading Files

There are three steps to reading files:

1. Pull files from the file cabinet using file.load⁷. The method returns a file.File object which represents the file metadata.

2. Read raw data from the file using File#getContents⁸. The method returns the data as a Base64-encoded string.

3. Parse the data with the SheetJS read method⁹. This method returns a SheetJS workbook object.

file.load expects an id property, which can be the internal ID (displayed in the File Cabinet web interface) or an absolute or relative path string.

/* file ID or path */
var id_of_file = 7262; // Internal ID 7262
/* load file */
var f = file.load({ id: id_of_file });
/* read file */
var b64 = f.getContents();
/* parse */
var workbook = XLSX.read(b64, { type: "base64" });

At this point, standard SheetJS utility functions¹⁰ can extract data from the workbook object.

Writing Files

There are three steps to writing files:

1. Write the data with the SheetJS write method¹¹. Using the base64 output type¹², the method will return a Base64 string.

2. Create a new file using file.create¹³. The recommended file type is file.Type.EXCEL. The method returns a file.File object.

3. Upload data to the File Cabinet with File#save¹⁴

/* write XLSX workbook as Base64 string */
var out = XLSX.write(workbook, { bookType: "xlsx", type: "base64" });
/* create file */
var newfile = file.create({
  name: 'SheetJSCabinetExport.xlsx', // replace with desired name
  fileType: file.Type.EXCEL,
  contents: out
});
/* save */
newfile.save();

Sheets in Suitelet Requests

Suitelets are driven by an exported onRequest method¹⁵.

The request property of the argument is a ServerRequest object¹⁶. The files property of the request¹⁷ is an object whose values are file objects.

The response property of the argument is a ServerResponse object¹⁸. The writeFile method¹⁹ of the response can respond with a file object. For the examples in this section, the argument will be named context:

/**
 * @NApiVersion 2.1
 * @NAmdConfig ./JsLibraryConfig.json
 * @NScriptType Suitelet
 */
define(['N/file', 'xlsx'], function (file, XLSX) {
  function onRequest(context) {
    /* ServerRequest object */
    var request = context.request;

    /* ServerResponse object */
    var response = context.response;

    // ... do work here ...
  }

  return { onRequest: onRequest };
});

Importing Sheet Data

There are three steps to importing data from Suitelet requests:

1. Pull files from the request.files object.²⁰. Each value in the object is a file.File object which represents the file metadata.

2. Read raw data from the file using File#getContents²¹. The method returns the data as a Base64-encoded string.

3. Parse the data with the SheetJS read method²². This method returns a SheetJS workbook object.

/* form element ID or field name */
var id_of_file = "uploaded_file"
/* get file from request */
var f = context.request.files[id_of_file];
/* read file */
var b64 = f.getContents();
/* parse */
var workbook = XLSX.read(b64, { type: "base64" });

At this point, standard SheetJS utility functions²³ can extract data from the workbook object.

When programmatically creating a form with N/ui/serverWidget, the keys of the files object are determined by the id properties of the field.

var form = serverWidget.createForm({ title: "Upload Spreadsheet" });
var field = form.addField({
  id: "uploaded_file",
  label: "Choose Spreadsheet",
  type: serverWidget.FieldType.FILE
});

Since the id of the file field is uploaded_file, the request handler can access the file at at context.request.files["uploaded_file"]

Exporting Files

There are three steps to generating downloadable files:

1. Write the data with the SheetJS write method²⁴. Using the base64 output type²⁵, the method will return a Base64 string.

2. Create a new file using file.create²⁶. The recommended file type is file.Type.EXCEL. The method returns a file.File object.

3. Initiate download with response.writeFile²⁷.

/* write XLSX workbook as Base64 string */
var out = XLSX.write(workbook, { bookType: "xlsx", type: "base64" });
/* create file */
var newfile = file.create({
  name: 'SheetJSSuiteletExport.xlsx', // replace with desired name
  fileType: file.Type.EXCEL,
  contents: out
});
/* initiate download */
context.response.writeFile(newfile);

Troubleshooting

NetSuite users reported errors using SheetJS scripts:

Fail to evaluate script: com.netsuite.suitescript.scriptobject.GraalValueAdapter@68d0f09d

NetSuite is incorrectly treating xlsx as a reserved word. As this behavior is not documented, it is believed to be a NetSuite bug.

Exasperated users concluded that Oracle will not be addressing this bug:

Oracle is not going to do anything with this

The "Oracle Bugs" warning in the NetSuite installation page includes a workaround that involves manually patching the library.

Footnotes

1. See "SuiteScript 2.x API Introduction" in the NetSuite documentation. ↩

2. See "SuiteScript 2.x Custom Modules" in the NetSuite documentation. ↩

3. See "Java + Rhino" demo ↩

4. See "Module Dependency Paths" in the NetSuite documentation. ↩

5. See "File Cabinet Overview" in the NetSuite documentation. ↩

6. See N/file Module in the NetSuite documentation. ↩

7. See file.load in the NetSuite documentation. ↩

8. See File.getContents() in the NetSuite documentation. ↩

9. See read in "Reading Files" ↩

10. See "Utility Functions" ↩

11. See write in "Writing Files" ↩

12. See "Supported Output Formats" ↩

13. See file.create(options) in the NetSuite documentation. ↩

14. See File.save() in the NetSuite documentation. ↩

15. See onRequest(params) in the NetSuite documentation. ↩

16. See http.ServerRequest in the NetSuite documentation. ↩

17. See ServerRequest.files in the NetSuite documentation. ↩

18. See http.ServerResponse in the NetSuite documentation. ↩

19. See ServerResponse.writeFile(options) in the NetSuite documentation. ↩

20. See ServerRequest.files in the NetSuite documentation. ↩

21. See File.getContents() in the NetSuite documentation. ↩

22. See read in "Reading Files" ↩

23. See "Utility Functions" ↩

24. See write in "Writing Files" ↩

25. See "Supported Output Formats" ↩

26. See file.create(options) in the NetSuite documentation. ↩

27. See ServerResponse.writeFile(options) in the NetSuite documentation. ↩