Within the scope of SuiteScript, there is no module/API that allows developer to interact with Excel files. One option for creating an Excel file is to generate the XML data from scratch but it is quite complex and hard to manage if you want to create report. Besides, any change on the format can lead analyzing the whole XML data. However, this option is only if we want to create an Excel file with limited options available. What if we want to create file in different format or import data from existing Excel file?

SheetJS comes into play to help developers in this challenge. It offers great modules and functionality around that makes it easy to play around. SheetJS comes in 2 different editions. Community Edition, which is open source but of course it does not allow developers to use advanced features. But it is still powerful enough to interact with Excel files and may meet your requirements. In case you want to add styling, pivot tables, formulas, etc. you should consider getting the Professional Edition.

There are Utility Functions provided that can convert array of array or JSON data to a Excel Sheet and Excel Sheet to JSON or CSV. With this conversion, it is easier to work with the data in JavaScript. Please see the SheetJS documentation for the available options. SheetJS uses open source JSZip library for processing Excel data. In our example, we will us jszip.js and xlsx.js libraries.

Going into the details of the example source code, I used NAmdConfig to point out the external libraries. Example code is a scheduled script which searches for customers created last month and writes the output to an excel file.

searchToAoa function is limited to return up to 4000 results. It can be modified to return more results by using paging. It can work with any type of search object but I didn’t perform explicit testing. This function would run a search and generate the results in array of arrays form which can be passed to the aoa_to_sheet utility function from SheetJS library.

createExcelFile accepts array of objects where objects contain sheetName and rows from the searchToAoa function. There can be more sheets added to the array before invoking the function. Function would create the excel file in the desired folder and return the Internal ID of the created file.

excelFileToJson function loads an excel file, reads the content and returns result as a JSON Object. It is possible to pass headers array that can be used to identify each column. If not, first line of the each sheet will be taken as header.

You can find the source code on our GitHub repository. I hope this example helps you to understand how to interact with Excel files in NetSuite using SuiteScript. There are of course more ways to do it but I find SheetJS easy to use and import into SuiteScript.

You can reach through the Contact Us page for any communication.