Fetching a particular sheet from an Excel file

Requirement

We have an Excel file which contains multiple sheets that needs to be processed and need to fetch the single sheet using Suitescript.

Solution

We can fetch the details of a particular sheet by mentioning its name using Non AMD library.

Created a map reduce script which will extract the particular sheet details when specifying the sheet name in the script.

/**
 * @NApiVersion 2.1
 * @NModuleScope Public
 * @NScriptType MapReduceScript
 * @NAmdConfig /SuiteScripts/Jobin and Jismi IT Services/RSTP-292 Fetch Single Sheet from File/Excel Extracting/excel_extracting_path_specifier.json
 *
 * @module N/search
 * @module N/file
 * @module xlsx
 *
 */
define([ 'N/file', 'xlsx'],
    ( file, XLSX) => {
        /**
         * file Extract function
         * @param {Number} fileId internal ID
         * @return {Object} returnDate Json Object
         */
        function extractFileToJSON(fileId) {
            let excelFile = file.load({
                id: fileId
            });
            let workbook = XLSX.read(excelFile.getContents(), { type: 'base64' });
            let sheetName =  "Usage Details";
            let jsonData = XLSX.utils.sheet_to_json(workbook.Sheets[sheetName]);
            return jsonData;
        }

        /**
         * Defines the function that is executed at the beginning of the map/reduce process and generates the input data.
         * @param {Object} inputContext
         * @param {boolean} inputContext.isRestarted - Indicates whether the current invocation of this function is the first
         *     invocation (if true, the current invocation is not the first invocation and this function has been restarted)
         * @param {Object} inputContext.ObjectRef - Object that references the input data
         * @typedef {Object} ObjectRef
         * @property {string|number} ObjectRef.id - Internal ID of the record instance that contains the input data
         * @property {string} ObjectRef.type - Type of the record instance that contains the input data
         * @returns {Array|Object|Search|ObjectRef|File|Query} The input data to use in the map/reduce process
         * @since 2015.2
         */

        const getInputData = (inputContext) => {
            let extract = extractFileToJSON(41459);
            log.debug("extractFileToJSON", extract)
        }
   
        /**
         * Defines the function that is executed when the reduce entry point is triggered. This entry point is triggered
         * automatically when the associated map stage is complete. This function is applied to each group in the provided context.
         * @param {Object} reduceContext - Data collection containing the groups to process in the reduce stage. This parameter is
         *     provided automatically based on the results of the map stage.
         * @param {Iterator} reduceContext.errors - Serialized errors that were thrown during previous attempts to execute the
         *     reduce function on the current group
         * @param {number} reduceContext.executionNo - Number of times the reduce function has been executed on the current group
         * @param {boolean} reduceContext.isRestarted - Indicates whether the current invocation of this function is the first
         *     invocation (if true, the current invocation is not the first invocation and this function has been restarted)
         * @param {string} reduceContext.key - Key to be processed during the reduce stage
         * @param {List<String>} reduceContext.values - All values associated with a unique key that was passed to the reduce stage
         *     for processing
         * @since 2015.2
         */
        const reduce = (reduceContext) => {
          log.debug("SUCEESS");
        }

        return { getInputData, reduce}

    });
   

Attached the Non AMD Library files which are defined as a module in the header.

excel_extracting_path_specifier.json

{
  "baseUrl": "./",
  "paths": {
    "xlsx": "Excel Extracting/EXCEL"
  }
}

We need two more packages named “EXCEL.js” and “papaparse.min.js” which are attached in the corresponding task.