Netsuite Tips | Ursus Code

Boost Efficiency: Convert Large Netsuite CSVs to Excel XLSX

May 19, 2023 Adolfo Garza Comments 0 Comment

I faced a challenging project that involved converting large CSV files stored in Netsuite’s file cabinet into XLSX format, specifically in Excel files. These files varied in size, with some exceeding 30 MB and even 100 MB. Initially, I attempted to use SheetJS, a plugin designed for this purpose (https://docs.sheetjs.com/docs/demos/cloud/netsuite/). However, I encountered difficulties as the plugin loaded the entire CSV file into memory, causing errors and performance issues.

To overcome this limitation, I devised an alternative solution for converting the CSV files to Excel XLSX format. My approach involved iterating through the CSV file line by line and constructing the XLSX file from scratch. Leveraging the fact that XLSX files are essentially zip files containing multiple XML files, I successfully accomplished the conversion. As a result, not only were the CSV files successfully converted, but the resulting XLSX files also demonstrated significant reductions in file size. In some cases, file sizes were reduced by up to 50%.

However, it is important to be aware of certain caveats associated with this conversion method. There are instances where the size of the resulting ZIP file becomes too large to compress, leading to a failure in the conversion process and triggering a timeout error. The exact threshold for this issue is not specified, as I have encountered failures with files around 50 MB, while others as large as 200 MB were processed without any problems. Consequently, it is crucial to consider the possibility of encountering heavy files that may not be compatible with this code. For optimal processing power, it is advisable to execute the code as a Schedule/Mass Reduce script.

To utilize the function effectively, ensure you are running it on Suitescript 2.1 and that the following modules are loaded: “N/xml,” “N/file,” and “N/compress.”

Simply provide the function with the File Cabinet path of the CSV file, and it will seamlessly transform it into an Excel file, preserving both the original file name and folder location.
Eg:

1	createExcelFileFromCSV('SuiteScripts/test.csv'); // Will produce a file at 'SuiteScripts/test.xslx'

/**
 * This function converts a CSV file to an Excel file. 
 * It takes the path to the CSV file as a parameter and 
 * returns the ID of the Excel file. The function works 
 * by first creating a new Excel file. Then, it iterates 
 * over the rows of the CSV file and adds each row to 
 * the Excel file. Finally, it saves the Excel file and
 * it returns the ID of the Excel file.
 *
 * @param {string} csvFilePath The path to the CSV file.
 * @returns {string} The ID of the Excel file.
 */
function createExcelFileFromCSV(csvFilePath){  
    purgeExcelFiles(csvFilePath);
	
    let csvData = file.load({ id: csvFilePath });
    let fileName = csvFilePath.split('.').shift().split('/').pop();
    let folderId = csvData.folder;

    let xlsHeader = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?><worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:xdr="http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing" xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"><sheetPr filterMode="false"><pageSetUpPr fitToPage="false"/></sheetPr><dimension ref="A1:B2"/><sheetViews><sheetView showFormulas="false" showGridLines="true" showRowColHeaders="true" showZeros="true" rightToLeft="false" tabSelected="true" showOutlineSymbols="true" defaultGridColor="true" view="normal" topLeftCell="A1" colorId="64" zoomScale="100" zoomScaleNormal="100" zoomScalePageLayoutView="100" workbookViewId="0"><selection pane="topLeft" activeCell="B2" activeCellId="0" sqref="B2"/></sheetView></sheetViews><sheetFormatPr defaultColWidth="11.60546875" defaultRowHeight="12.8" zeroHeight="false" outlineLevelRow="0" outlineLevelCol="0"/><sheetData>`;
    let xlsFooter = `</sheetData><printOptions headings="false" gridLines="false" gridLinesSet="true" horizontalCentered="false" verticalCentered="false"/><pageMargins left="0.7875" right="0.7875" top="1.05277777777778" bottom="1.05277777777778" header="0.7875" footer="0.7875"/><pageSetup paperSize="1" scale="100" fitToWidth="1" fitToHeight="1" pageOrder="downThenOver" orientation="portrait" blackAndWhite="false" draft="false" cellComments="none" firstPageNumber="1" useFirstPageNumber="true" horizontalDpi="300" verticalDpi="300" copies="1"/><headerFooter differentFirst="false" differentOddEven="false"><oddHeader>&amp;C&amp;&quot;Times New Roman,Regular&quot;&amp;12&amp;A</oddHeader><oddFooter>&amp;C&amp;&quot;Times New Roman,Regular&quot;&amp;12Page &amp;P</oddFooter></headerFooter></worksheet>`
    
    let tempFileObj = file.create({
        name: 'sheet1.xml',
        fileType: 'PLAINTEXT',
        contents: '',
        encoding: file.Encoding.UTF_8
    });
    
    tempFileObj.appendLine({
        value: xlsHeader
    });

    let iterator = csvData.lines.iterator();
    let rowNumber = 1;
    //Headers
    iterator.each(function (line) {
        let parsedLine = CSVParser().parse(line.value, {header:false});
        let obj = parsedLine.data[0];
        let XMLCells = '';
        
        for(let i=0; i < obj.length; i++){
            let XMLCell = createXMLCell(rowNumber,i,obj[i]);
            XMLCells += XMLCell;
        }
        
        let rowXML = `<row r="${rowNumber}" spans="1:1">${XMLCells}</row>`;
        tempFileObj.appendLine({ value: rowXML });
        rowNumber++;
        return true;
    });
    
    tempFileObj.appendLine({ value: xlsFooter });
    
    let archiver = createArchiverObject();
    archiver.add({
        file: tempFileObj,
        directory: 'xl/worksheets/'
    });
    
    let zipFile = archiver.archive({
        name: fileName + '.zip'
    });

    //Save the archive to the same folder
    zipFile.folder = folderId;
    zipFile.conflictResolution = file.NameConflictResolution.OVERWRITE;
    let zipFileId = zipFile.save();
    let zipFileObj = file.load({
        id: zipFileId
    });
    zipFileObj.name = fileName + '.xlsx';
    zipFileObj.conflictResolution = file.NameConflictResolution.OVERWRITE;
    let excelFileId = zipFileObj.save();
    
    if(excelFileId != zipFileId){
        file.delete({id: zipFileId });
    }
    return fileId;
	
	
	//########################
	//### HELPER FUNCTIONS ###
	//########################
	
	/**
	 * This function creates a boilerplate archiver object. 
	 * The archiver object is used to create a ZIP file 
	 * which will be saved as an Excel file.
	 *
	 * @returns {Object} The archiver object.
	 */
	function createArchiverObject(){
		let archiver = compress.createArchiver();
		
		let rels = `<?xml version="1.0" encoding="UTF-8"?>
	<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/><Relationship Id="rId2" Type="http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties" Target="docProps/core.xml"/><Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties" Target="docProps/app.xml"/>
	</Relationships>`;
		let appxml = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
	<Properties xmlns="http://schemas.openxmlformats.org/officeDocument/2006/extended-properties" xmlns:vt="http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes"><Template></Template><TotalTime>0</TotalTime><Application>LibreOffice/7.2.2.2$Windows_X86_64 LibreOffice_project/02b2acce88a210515b4a5bb2e46cbfb63fe97d56</Application><AppVersion>15.0000</AppVersion></Properties>`;
		let corexml = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
	<cp:coreProperties xmlns:cp="http://schemas.openxmlformats.org/package/2006/metadata/core-properties" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:dcterms="http://purl.org/dc/terms/" xmlns:dcmitype="http://purl.org/dc/dcmitype/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><dcterms:created xsi:type="dcterms:W3CDTF">2022-11-21T12:59:32Z</dcterms:created><dc:creator></dc:creator><dc:description></dc:description><dc:language>en-US</dc:language><cp:lastModifiedBy></cp:lastModifiedBy><cp:revision>0</cp:revision><dc:subject></dc:subject><dc:title></dc:title></cp:coreProperties>`;
		let contentTypesxml = `<?xml version="1.0" encoding="UTF-8"?>
	<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types"><Default Extension="xml" ContentType="application/xml"/><Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/><Default Extension="png" ContentType="image/png"/><Default Extension="jpeg" ContentType="image/jpeg"/><Override PartName="/_rels/.rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/><Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/><Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/><Override PartName="/xl/worksheets/sheet1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/><Override PartName="/xl/_rels/workbook.xml.rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/><Override PartName="/docProps/core.xml" ContentType="application/vnd.openxmlformats-package.core-properties+xml"/><Override PartName="/docProps/app.xml" ContentType="application/vnd.openxmlformats-officedocument.extended-properties+xml"/>
	</Types>`;
		let workbookrels = `<?xml version="1.0" encoding="UTF-8"?>
	<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml"/><Relationship Id="rId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet1.xml"/>
	</Relationships>`;
		let stylesxml = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
	<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><numFmts count="1"><numFmt numFmtId="164" formatCode="General"/></numFmts><fonts count="4"><font><sz val="10"/><name val="Arial"/><family val="2"/></font><font><sz val="10"/><name val="Arial"/><family val="0"/></font><font><sz val="10"/><name val="Arial"/><family val="0"/></font><font><sz val="10"/><name val="Arial"/><family val="0"/></font></fonts><fills count="2"><fill><patternFill patternType="none"/></fill><fill><patternFill patternType="gray125"/></fill></fills><borders count="1"><border diagonalUp="false" diagonalDown="false"><left/><right/><top/><bottom/><diagonal/></border></borders><cellStyleXfs count="20"><xf numFmtId="164" fontId="0" fillId="0" borderId="0" applyFont="true" applyBorder="true" applyAlignment="true" applyProtection="true"><alignment horizontal="general" vertical="bottom" textRotation="0" wrapText="false" indent="0" shrinkToFit="false"/><protection locked="true" hidden="false"/></xf><xf numFmtId="0" fontId="1" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="0" fontId="1" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="0" fontId="2" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="0" fontId="2" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="0" fontId="0" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="0" fontId="0" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="0" fontId="0" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="0" fontId="0" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="0" fontId="0" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="0" fontId="0" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="0" fontId="0" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="0" fontId="0" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="0" fontId="0" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="0" fontId="0" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="43" fontId="1" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="41" fontId="1" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="44" fontId="1" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="42" fontId="1" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="9" fontId="1" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf></cellStyleXfs><cellXfs count="1"><xf numFmtId="164" fontId="0" fillId="0" borderId="0" xfId="0" applyFont="false" applyBorder="false" applyAlignment="false" applyProtection="false"><alignment horizontal="general" vertical="bottom" textRotation="0" wrapText="false" indent="0" shrinkToFit="false"/><protection locked="true" hidden="false"/></xf></cellXfs><cellStyles count="6"><cellStyle name="Normal" xfId="0" builtinId="0"/><cellStyle name="Comma" xfId="15" builtinId="3"/><cellStyle name="Comma [0]" xfId="16" builtinId="6"/><cellStyle name="Currency" xfId="17" builtinId="4"/><cellStyle name="Currency [0]" xfId="18" builtinId="7"/><cellStyle name="Percent" xfId="19" builtinId="5"/></cellStyles></styleSheet>`;
		let workbookxml = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
	<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"><fileVersion appName="Calc"/><workbookPr backupFile="false" showObjects="all" date1904="false"/><workbookProtection/><bookViews><workbookView showHorizontalScroll="true" showVerticalScroll="true" showSheetTabs="true" xWindow="0" yWindow="0" windowWidth="16384" windowHeight="8192" tabRatio="500" firstSheet="0" activeTab="0"/></bookViews><sheets><sheet name="Sheet1" sheetId="1" state="visible" r:id="rId2"/></sheets><calcPr iterateCount="100" refMode="A1" iterate="false" iterateDelta="0.001"/><extLst><ext xmlns:loext="http://schemas.libreoffice.org/" uri="{7626C862-2A13-11E5-B345-FEFF819CDC9F}"><loext:extCalcPr stringRefSyntax="CalcA1"/></ext></extLst></workbook>`;
		
		archiver.add({
			file: file.create({
				name: '.rels',
				fileType: 'PLAINTEXT',
				contents: rels,
				encoding: file.Encoding.UTF_8
			}),
			directory: '_rels/'
		});
		archiver.add({
			file: file.create({
				name: 'app.xml',
				fileType: 'PLAINTEXT',
				contents: appxml,
				encoding: file.Encoding.UTF_8
			}),
			directory: 'docProps/'
		});
		archiver.add({
			file: file.create({
				name: 'core.xml',
				fileType: 'PLAINTEXT',
				contents: corexml,
				encoding: file.Encoding.UTF_8
			}),
			directory: 'docProps/'
		});
		archiver.add({
			file: file.create({
				name: `[Content_Types].xml`,
				fileType: 'PLAINTEXT',
				contents: contentTypesxml,
				encoding: file.Encoding.UTF_8
			}),
			directory: ''
		});
		archiver.add({
			file: file.create({
				name: 'workbook.xml.rels',
				fileType: 'PLAINTEXT',
				contents: workbookrels,
				encoding: file.Encoding.UTF_8
			}),
			directory: 'xl/_rels/'
		});
		archiver.add({
			file: file.create({
				name: 'styles.xml',
				fileType: 'PLAINTEXT',
				contents: stylesxml,
				encoding: file.Encoding.UTF_8
			}),
			directory: 'xl/'
		});
		archiver.add({
			file: file.create({
				name: 'workbook.xml',
				fileType: 'PLAINTEXT',
				contents: workbookxml,
				encoding: file.Encoding.UTF_8
			}),
			directory: 'xl/'
		});
		
		return archiver;
	}

	/**
	 * Converts a CSV line to an XML cell.
	 *
	 * @param {number} rowNumber The row number of the CSV line.
	 * @param {string} cellValue The value of the CSV cell.
	 * @returns {string} The XML cell.
	 */
	function createXMLCell(rowNumber,columnNumber,textValue){
		let cellTemplate = `<c r="${columnToAlphabet(columnNumber)}${rowNumber}" t="inlineStr"><is><t>${xml.escape({ xmlText : textValue})}</t></is></c>`;
		
		return cellTemplate;
		
		function columnToAlphabet(n){
			let ordA = 'a'.charCodeAt(0);
			let ordZ = 'z'.charCodeAt(0);
			let len = ordZ - ordA + 1;
		  
			let s = "";
			while(n >= 0) {
				s = String.fromCharCode(n % len + ordA) + s;
				n = Math.floor(n / len) - 1;
			}
			return s.toUpperCase();
		}
	}

	/**
	 * Delete any Excel files that were created from CSV files, if they exist.
	 *
	 * @param {string} filePath The path to the CSV file.
	 */
	function purgeExcelFiles(csvFilePath){
		try{
			let fileName = csvFilePath.split('.').shift().split('/').pop();
			let excelFilePath = csvFilePath.substring(0,csvFilePath.length-fileName.length-4) + ".xlsx";
			let excelFile = file.load({ id: excelFilePath });
			file.delete({id: excelFile.id });
        } catch(e){
			
		}
		try{
			let zipFilePath = csvFilePath.substring(0,csvFilePath.length-fileName.length-4) + ".zip";
			let zipFile = file.load({ id: zipFilePath });
			file.delete({id: zipFile.id });
		} catch(e){
			
		}
	}
	
	/**
	 * CSV Parser Library (Baby Parse) which is based on Papa Parse.
	 * Examples of use:
	 * CSVParser().parse((csvString[, config]);
	 * CSVParser().unparse(data[, config])
	 *
	 * @returns {object} The CSVParser object.
	 */
	function CSVParser(){function e(r,n){if(Array.isArray(r)){var i=[];return r.forEach(function(t){"object"==typeof t?i.push(e(t.file,t.config)):i.push(e(t,n))}),i}var i={data:[],errors:[]};if(!/(\.csv|\.txt)$/.test(r))return i.errors.push({type:"",code:"",message:"Unsupported file type.",row:""}),i;try{var a=fs.readFileSync(r).toString();return t(a,n)}catch(s){return i.errors.push(s),i}}function t(e,t){var r=a(t),i=new n(r),s=i.parse(e);return f(r.complete)&&r.complete(s),s}function r(e,t){function r(){"object"==typeof t&&("string"==typeof t.delimiter&&1==t.delimiter.length&&-1==l.BAD_DELIMITERS.indexOf(t.delimiter)&&(o=t.delimiter),("boolean"==typeof t.quotes||t.quotes instanceof Array)&&(f=t.quotes),"string"==typeof t.newline&&(d=t.newline))}function n(e){if("object"!=typeof e)return[];var t=[];for(var r in e)t.push(r);return t}function i(e,t){var r="";"string"==typeof e&&(e=JSON.parse(e)),"string"==typeof t&&(t=JSON.parse(t));var n=e instanceof Array&&e.length>0,i=!(t[0]instanceof Array);if(n){for(var s=0;s<e.length;s++)s>0&&(r+=o),r+=a(e[s],s);t.length>0&&(r+=d)}for(var f=0;f<t.length;f++){for(var l=n?e.length:t[f].length,u=0;l>u;u++){u>0&&(r+=o);var p=n&&i?e[u]:u;r+=a(t[f][p],u)}f<t.length-1&&(r+=d)}return r}function a(e,t){if("undefined"==typeof e||null===e)return"";e=e.toString().replace(/"/g,'""');var r="boolean"==typeof f&&f||f instanceof Array&&f[t]||s(e,l.BAD_DELIMITERS)||e.indexOf(o)>-1||" "==e.charAt(0)||" "==e.charAt(e.length-1);return r?'"'+e+'"':e}function s(e,t){for(var r=0;r<t.length;r++)if(e.indexOf(t[r])>-1)return!0;return!1}var f=!1,o=",",d="\r\n";if(r(),"string"==typeof e&&(e=JSON.parse(e)),e instanceof Array){if(!e.length||e[0]instanceof Array)return i(null,e);if("object"==typeof e[0])return i(n(e[0]),e)}else if("object"==typeof e)return"string"==typeof e.data&&(e.data=JSON.parse(e.data)),e.data instanceof Array&&(e.fields||(e.fields=e.data[0]instanceof Array?e.fields:n(e.data[0])),e.data[0]instanceof Array||"object"==typeof e.data[0]||(e.data=[e.data])),i(e.fields||[],e.data||[]);throw"exception: Unable to serialize unrecognized input"}function n(e){function t(){if(E&&m&&(p("Delimiter","UndetectableDelimiter","Unable to auto-detect delimiting character; defaulted to '"+l.DefaultDelimiter+"'"),m=!1),e.skipEmptyLines)for(var t=0;t<E.data.length;t++)1==E.data[t].length&&""==E.data[t][0]&&E.data.splice(t--,1);return r()&&n(),a()}function r(){return e.header&&0==w.length}function n(){if(E){for(var e=0;r()&&e<E.data.length;e++)for(var t=0;t<E.data[e].length;t++)w.push(E.data[e][t]);E.data.splice(0,1)}}function a(){if(!E||!e.header&&!e.dynamicTyping)return E;for(var t=0;t<E.data.length;t++){for(var r={},n=0;n<E.data[t].length;n++){if(e.dynamicTyping){var i=E.data[t][n];"true"==i||"TRUE"===i?E.data[t][n]=!0:"false"==i||"FALSE"===i?E.data[t][n]=!1:E.data[t][n]=u(i)}e.header&&(n>=w.length?(r.__parsed_extra||(r.__parsed_extra=[]),r.__parsed_extra.push(E.data[t][n])):r[w[n]]=E.data[t][n])}e.header&&(E.data[t]=r,n>w.length?p("FieldMismatch","TooManyFields","Too many fields: expected "+w.length+" fields but parsed "+n,t):n<w.length&&p("FieldMismatch","TooFewFields","Too few fields: expected "+w.length+" fields but parsed "+n,t))}return e.header&&E.meta&&(E.meta.fields=w),E}function o(t){for(var r,n,a,s=[",","    ","|",";",l.RECORD_SEP,l.UNIT_SEP],f=0;f<s.length;f++){var o=s[f],d=0,u=0;a=void 0;for(var p=new i({delimiter:o,preview:10}).parse(t),c=0;c<p.data.length;c++){var h=p.data[c].length;u+=h,"undefined"!=typeof a?h>1&&(d+=Math.abs(h-a),a=h):a=h}u/=p.data.length,("undefined"==typeof n||n>d)&&u>1.99&&(n=d,r=o)}return e.delimiter=r,{successful:!!r,bestDelimiter:r}}function d(e){e=e.substr(0,1048576);var t=e.split("\r");if(1==t.length)return"\n";for(var r=0,n=0;n<t.length;n++)"\n"==t[n][0]&&r++;return r>=t.length/2?"\r\n":"\r"}function u(e){var t=g.test(e);return t?parseFloat(e):e}function p(e,t,r,n){E.errors.push({type:e,code:t,message:r,row:n})}var c,h,m,g=/^\s*-?(\d*\.?\d+|\d+\.?\d*)(e[-+]?\d+)?\s*$/i,y=this,v=0,b=!1,w=[],E={data:[],errors:[],meta:{}};if(f(e.step)){var x=e.step;e.step=function(n){if(E=n,r())t();else{if(t(),0==E.data.length)return;v+=n.data.length,e.preview&&v>e.preview?h.abort():x(E,y)}}}this.parse=function(r){if(e.newline||(e.newline=d(r)),m=!1,!e.delimiter){var n=o(r);n.successful?e.delimiter=n.bestDelimiter:(m=!0,e.delimiter=l.DefaultDelimiter),E.meta.delimiter=e.delimiter}var a=s(e);return e.preview&&e.header&&a.preview++,c=r,h=new i(a),E=h.parse(c),t(),!f(e.complete)||b||y.streamer&&!y.streamer.finished()||e.complete(E),b?{meta:{paused:!0}}:E||{meta:{paused:!1}}},this.pause=function(){b=!0,h.abort(),c=c.substr(h.getCharIndex())},this.resume=function(){b=!1,h=new i(e),h.parse(c),b||(y.streamer&&!y.streamer.finished()?y.streamer.resume():f(e.complete)&&e.complete(E))},this.abort=function(){h.abort(),f(e.complete)&&e.complete(E),c=""}}function i(e){e=e||{};var t=e.delimiter,r=e.newline,n=e.comments,i=e.step,a=e.preview,s=e.fastMode;if(("string"!=typeof t||1!=t.length||l.BAD_DELIMITERS.indexOf(t)>-1)&&(t=","),n===t)throw"Comment character same as delimiter";n===!0?n="#":("string"!=typeof n||l.BAD_DELIMITERS.indexOf(n)>-1)&&(n=!1),"\n"!=r&&"\r"!=r&&"\r\n"!=r&&(r="\n");var f=0,o=!1;this.parse=function(e){function l(){return w.push(e.substr(f)),v.push(w),f=c,y&&p(),u()}function d(t){v.push(w),w=[],f=t,O=e.indexOf(r,f)}function u(e){return{data:v,errors:b,meta:{delimiter:t,linebreak:r,aborted:o,truncated:!!e}}}function p(){i(u()),v=[],b=[]}if("string"!=typeof e)throw"Input must be a string";var c=e.length,h=t.length,m=r.length,g=n.length,y="function"==typeof i;f=0;var v=[],b=[],w=[];if(!e)return u();if(s){for(var E=e.split(r),x=0;x<E.length;x++)if(!n||E[x].substr(0,g)!=n){if(y){if(v=[E[x].split(t)],p(),o)return u()}else v.push(E[x].split(t));if(a&&x>=a)return v=v.slice(0,a),u(!0)}return u()}for(var D=e.indexOf(t,f),O=e.indexOf(r,f);;)if('"'!=e[f])if(n&&0===w.length&&e.substr(f,g)===n){if(-1==O)return u();f=O+m,O=e.indexOf(r,f),D=e.indexOf(t,f)}else if(-1!==D&&(O>D||-1===O))w.push(e.substring(f,D)),f=D+h,D=e.indexOf(t,f);else{if(-1===O)break;if(w.push(e.substring(f,O)),d(O+m),y&&(p(),o))return u();if(a&&v.length>=a)return u(!0)}else{var A=f;for(f++;;){var A=e.indexOf('"',A+1);if(-1===A)return b.push({type:"Quotes",code:"MissingQuotes",message:"Quoted field unterminated",row:v.length,index:f}),l();if(A===c-1)return w.push(e.substring(f,A).replace(/""/g,'"')),v.push(w),y&&p(),u();if('"'!=e[A+1]){if(e[A+1]==t){w.push(e.substring(f,A).replace(/""/g,'"')),f=A+1+h,D=e.indexOf(t,f),O=e.indexOf(r,f);break}if(e.substr(A+1,m)===r){if(w.push(e.substring(f,A).replace(/""/g,'"')),d(A+1+m),D=e.indexOf(t,f),y&&(p(),o))return u();if(a&&v.length>=a)return u(!0);break}}else A++}}return l()},this.abort=function(){o=!0},this.getCharIndex=function(){return f}}function a(e){"object"!=typeof e&&(e={});var t=s(e);return("string"!=typeof t.delimiter||1!=t.delimiter.length||l.BAD_DELIMITERS.indexOf(t.delimiter)>-1)&&(t.delimiter=o.delimiter),"\n"!=t.newline&&"\r"!=t.newline&&"\r\n"!=t.newline&&(t.newline=o.newline),"boolean"!=typeof t.header&&(t.header=o.header),"boolean"!=typeof t.dynamicTyping&&(t.dynamicTyping=o.dynamicTyping),"number"!=typeof t.preview&&(t.preview=o.preview),"function"!=typeof t.step&&(t.step=o.step),"function"!=typeof t.complete&&(t.complete=o.complete),"boolean"!=typeof t.skipEmptyLines&&(t.skipEmptyLines=o.skipEmptyLines),"boolean"!=typeof t.fastMode&&(t.fastMode=o.fastMode),t}function s(e){if("object"!=typeof e)return e;var t=e instanceof Array?[]:{};for(var r in e)t[r]=s(e[r]);return t}function f(e){return"function"==typeof e}var o={delimiter:"",newline:"",header:!1,dynamicTyping:!1,preview:0,step:void 0,comments:!1,complete:void 0,skipEmptyLines:!1,fastMode:!1},l={};return l.parse=t,l.parseFiles=e,l.unparse=r,l.RECORD_SEP=String.fromCharCode(30),l.UNIT_SEP=String.fromCharCode(31),l.BYTE_ORDER_MARK="\ufeff",l.BAD_DELIMITERS=["\r","\n",'"',l.BYTE_ORDER_MARK],l.DefaultDelimiter=",",l.Parser=i,l.ParserHandle=n,l}
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

/**

* This function converts a CSV file to an Excel file.

* It takes the path to the CSV file as a parameter and

* returns the ID of the Excel file. The function works

* by first creating a new Excel file. Then, it iterates

* over the rows of the CSV file and adds each row to

* the Excel file. Finally, it saves the Excel file and

* it returns the ID of the Excel file.

* @param {string} csvFilePath The path to the CSV file.

* @returns {string} The ID of the Excel file.

function createExcelFileFromCSV(csvFilePath){

purgeExcelFiles(csvFilePath);

let csvData = file.load({ id: csvFilePath });

let fileName = csvFilePath.split('.').shift().split('/').pop();

let folderId = csvData.folder;

let xlsHeader = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?><worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:xdr="http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing" xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"><sheetPr filterMode="false"><pageSetUpPr fitToPage="false"/></sheetPr><dimension ref="A1:B2"/><sheetViews><sheetView showFormulas="false" showGridLines="true" showRowColHeaders="true" showZeros="true" rightToLeft="false" tabSelected="true" showOutlineSymbols="true" defaultGridColor="true" view="normal" topLeftCell="A1" colorId="64" zoomScale="100" zoomScaleNormal="100" zoomScalePageLayoutView="100" workbookViewId="0"><selection pane="topLeft" activeCell="B2" activeCellId="0" sqref="B2"/></sheetView></sheetViews><sheetFormatPr defaultColWidth="11.60546875" defaultRowHeight="12.8" zeroHeight="false" outlineLevelRow="0" outlineLevelCol="0"/><sheetData>`;

let xlsFooter = `</sheetData><printOptions headings="false" gridLines="false" gridLinesSet="true" horizontalCentered="false" verticalCentered="false"/><pageMargins left="0.7875" right="0.7875" top="1.05277777777778" bottom="1.05277777777778" header="0.7875" footer="0.7875"/><pageSetup paperSize="1" scale="100" fitToWidth="1" fitToHeight="1" pageOrder="downThenOver" orientation="portrait" blackAndWhite="false" draft="false" cellComments="none" firstPageNumber="1" useFirstPageNumber="true" horizontalDpi="300" verticalDpi="300" copies="1"/><headerFooter differentFirst="false" differentOddEven="false"><oddHeader>&C&"Times New Roman,Regular"&12&A</oddHeader><oddFooter>&C&"Times New Roman,Regular"&12Page &P</oddFooter></headerFooter></worksheet>`

let tempFileObj = file.create({

fileType: 'PLAINTEXT',

contents: '',

encoding: file.Encoding.UTF_8

});

tempFileObj.appendLine({

value: xlsHeader

});

let iterator = csvData.lines.iterator();

let rowNumber = 1;

//Headers

iterator.each(function (line) {

let parsedLine = CSVParser().parse(line.value, {header:false});

let obj = parsedLine.data[0];

let XMLCells = '';

for(let i=0; i < obj.length; i++){

let XMLCell = createXMLCell(rowNumber,i,obj[i]);

XMLCells += XMLCell;

}

let rowXML = `<row r="${rowNumber}" spans="1:1">${XMLCells}</row>`;

tempFileObj.appendLine({ value: rowXML });

rowNumber++;

return true;

});

tempFileObj.appendLine({ value: xlsFooter });

let archiver = createArchiverObject();

archiver.add({

file: tempFileObj,

directory: 'xl/worksheets/'

});

let zipFile = archiver.archive({

});

//Save the archive to the same folder

zipFile.folder = folderId;

zipFile.conflictResolution = file.NameConflictResolution.OVERWRITE;

let zipFileId = zipFile.save();

let zipFileObj = file.load({

id: zipFileId

});

zipFileObj.name = fileName + '.xlsx';

zipFileObj.conflictResolution = file.NameConflictResolution.OVERWRITE;

let excelFileId = zipFileObj.save();

if(excelFileId != zipFileId){

file.delete({id: zipFileId });

}

return fileId;

//########################

//### HELPER FUNCTIONS ###

//########################

/**

* This function creates a boilerplate archiver object.

* The archiver object is used to create a ZIP file

* which will be saved as an Excel file.

* @returns {Object} The archiver object.

function createArchiverObject(){

let archiver = compress.createArchiver();

let rels = `<?xml version="1.0" encoding="UTF-8"?>

</Relationships>`;

let appxml = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

<Properties xmlns="http://schemas.openxmlformats.org/officeDocument/2006/extended-properties" xmlns:vt="http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes"><Template></Template><TotalTime>0</TotalTime><Application>LibreOffice/7.2.2.2$Windows_X86_64 LibreOffice_project/02b2acce88a210515b4a5bb2e46cbfb63fe97d56</Application><AppVersion>15.0000</AppVersion></Properties>`;

let corexml = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

<cp:coreProperties xmlns:cp="http://schemas.openxmlformats.org/package/2006/metadata/core-properties" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:dcterms="http://purl.org/dc/terms/" xmlns:dcmitype="http://purl.org/dc/dcmitype/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><dcterms:created xsi:type="dcterms:W3CDTF">2022-11-21T12:59:32Z</dcterms:created><dc:creator></dc:creator><dc:description></dc:description><dc:language>en-US</dc:language><cp:lastModifiedBy></cp:lastModifiedBy><cp:revision>0</cp:revision><dc:subject></dc:subject><dc:title></dc:title></cp:coreProperties>`;

let contentTypesxml = `<?xml version="1.0" encoding="UTF-8"?>

</Types>`;

let workbookrels = `<?xml version="1.0" encoding="UTF-8"?>

</Relationships>`;

let stylesxml = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><numFmts count="1"><numFmt numFmtId="164" formatCode="General"/></numFmts><fonts count="4"><sz val="10"/><name val="Arial"/><family val="2"/><sz val="10"/><name val="Arial"/><family val="0"/><sz val="10"/><name val="Arial"/><family val="0"/><sz val="10"/><name val="Arial"/><family val="0"/></fonts><fills count="2"><fill><patternFill patternType="none"/></fill><fill><patternFill patternType="gray125"/></fill></fills><borders count="1"><border diagonalUp="false" diagonalDown="false"><left/><right/><top/><bottom/><diagonal/></border></borders><cellStyleXfs count="20"><xf numFmtId="164" fontId="0" fillId="0" borderId="0" applyFont="true" applyBorder="true" applyAlignment="true" applyProtection="true"><alignment horizontal="general" vertical="bottom" textRotation="0" wrapText="false" indent="0" shrinkToFit="false"/><protection locked="true" hidden="false"/></xf><xf numFmtId="0" fontId="1" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="0" fontId="1" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="0" fontId="2" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="0" fontId="2" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="0" fontId="0" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="0" fontId="0" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="0" fontId="0" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="0" fontId="0" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="0" fontId="0" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="0" fontId="0" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="0" fontId="0" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="0" fontId="0" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="0" fontId="0" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="0" fontId="0" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="43" fontId="1" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="41" fontId="1" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="44" fontId="1" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="42" fontId="1" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf><xf numFmtId="9" fontId="1" fillId="0" borderId="0" applyFont="true" applyBorder="false" applyAlignment="false" applyProtection="false"></xf></cellStyleXfs><cellXfs count="1"><xf numFmtId="164" fontId="0" fillId="0" borderId="0" xfId="0" applyFont="false" applyBorder="false" applyAlignment="false" applyProtection="false"><alignment horizontal="general" vertical="bottom" textRotation="0" wrapText="false" indent="0" shrinkToFit="false"/><protection locked="true" hidden="false"/></xf></cellXfs><cellStyles count="6"><cellStyle name="Normal" xfId="0" builtinId="0"/><cellStyle name="Comma" xfId="15" builtinId="3"/><cellStyle name="Comma [0]" xfId="16" builtinId="6"/><cellStyle name="Currency" xfId="17" builtinId="4"/><cellStyle name="Currency [0]" xfId="18" builtinId="7"/><cellStyle name="Percent" xfId="19" builtinId="5"/></cellStyles></styleSheet>`;

let workbookxml = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"><fileVersion appName="Calc"/><workbookPr backupFile="false" showObjects="all" date1904="false"/><workbookProtection/><bookViews><workbookView showHorizontalScroll="true" showVerticalScroll="true" showSheetTabs="true" xWindow="0" yWindow="0" windowWidth="16384" windowHeight="8192" tabRatio="500" firstSheet="0" activeTab="0"/></bookViews><sheets><sheet name="Sheet1" sheetId="1" state="visible" r:id="rId2"/></sheets><calcPr iterateCount="100" refMode="A1" iterate="false" iterateDelta="0.001"/><extLst><ext xmlns:loext="http://schemas.libreoffice.org/" uri="{7626C862-2A13-11E5-B345-FEFF819CDC9F}"><loext:extCalcPr stringRefSyntax="CalcA1"/></ext></extLst></workbook>`;

archiver.add({

file: file.create({

fileType: 'PLAINTEXT',

contents: rels,

encoding: file.Encoding.UTF_8

}),

directory: '_rels/'

});

archiver.add({

file: file.create({

fileType: 'PLAINTEXT',

contents: appxml,

encoding: file.Encoding.UTF_8

}),

directory: 'docProps/'

});

archiver.add({

file: file.create({

fileType: 'PLAINTEXT',

contents: corexml,

encoding: file.Encoding.UTF_8

}),

directory: 'docProps/'

});

archiver.add({

file: file.create({

fileType: 'PLAINTEXT',

contents: contentTypesxml,

encoding: file.Encoding.UTF_8

}),

directory: ''

});

archiver.add({

file: file.create({

fileType: 'PLAINTEXT',

contents: workbookrels,

encoding: file.Encoding.UTF_8

}),

directory: 'xl/_rels/'

});

archiver.add({

file: file.create({

fileType: 'PLAINTEXT',

contents: stylesxml,

encoding: file.Encoding.UTF_8

}),

directory: 'xl/'

});

archiver.add({

file: file.create({

fileType: 'PLAINTEXT',

contents: workbookxml,

encoding: file.Encoding.UTF_8

}),

directory: 'xl/'

});

return archiver;

}

/**

* Converts a CSV line to an XML cell.

* @param {number} rowNumber The row number of the CSV line.

* @param {string} cellValue The value of the CSV cell.

* @returns {string} The XML cell.

function createXMLCell(rowNumber,columnNumber,textValue){

let cellTemplate = `<c r="${columnToAlphabet(columnNumber)}${rowNumber}" t="inlineStr"><is><t>${xml.escape({ xmlText : textValue})}</t></is></c>`;

return cellTemplate;

function columnToAlphabet(n){

let ordA = 'a'.charCodeAt(0);

let ordZ = 'z'.charCodeAt(0);

let len = ordZ - ordA + 1;

let s = "";

while(n >= 0) {

s = String.fromCharCode(n % len + ordA) + s;

n = Math.floor(n / len) - 1;

}

return s.toUpperCase();

}

/**

* Delete any Excel files that were created from CSV files, if they exist.

* @param {string} filePath The path to the CSV file.

function purgeExcelFiles(csvFilePath){

try{

let fileName = csvFilePath.split('.').shift().split('/').pop();

let excelFilePath = csvFilePath.substring(0,csvFilePath.length-fileName.length-4) + ".xlsx";

let excelFile = file.load({ id: excelFilePath });

file.delete({id: excelFile.id });

} catch(e){

}

try{

let zipFilePath = csvFilePath.substring(0,csvFilePath.length-fileName.length-4) + ".zip";

let zipFile = file.load({ id: zipFilePath });

file.delete({id: zipFile.id });

} catch(e){

}

/**

* CSV Parser Library (Baby Parse) which is based on Papa Parse.

* Examples of use:

* CSVParser().parse((csvString[, config]);

* CSVParser().unparse(data[, config])

* @returns {object} The CSVParser object.

}

Suitescript 2.0 SFTP Tool

August 24, 2022 Adolfo Garza Comments 3 comments

One of the new features of SuiteScript API 2016.2 is the ability to connect to SFTP Servers directly from Netsuite.

Here are some downsides I found:

~~Not way to list the contents of a remote directory~~ <– This has been added
~~No way of connecting using a private key~~. <– This has been added
Not all SFTP servers are supported as Netsuite requires the server to support some encryption algorithms.
No way of obtaining the server’s hostkey natively.

Unfortunately the process is a bit confusing if you are new to the SFTP world so I created a tool that will help you jump-start your script. I found this tool very useful as I was able to test an SFTP connection quickly for a client and turned out the SFTP server that they were trying to use didn’t actually support Netsuite’s approved algorithms (aes256-ctr, es192-ctr, es128-ctr), so it saved me a lot of time as I was able to quickly discard it and move on.

I also included an endpoint to get the HostKey of the URL you are trying to connect to. I think this will be very useful for people who don’t have a Linux server lying around where they can run ssh keyscan.

Note: The HostKey endpoint should be used to grab the hostkey and store it elsewhere on your end. Do not use the endpoint to request the hostkey every time you make a request. If your server HostKey changes very frequently then I suggest to build your own tool.

Here’s a video tutorial:

https://www.youtube.com/watch?v=azxo7GS3xRQ

And here’s the code ready to be uploaded as a Suitelet, have fun!:

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

/* = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =\

|| This script is a tool to help develop SFTP connections ||

|| in Suitescript 2.0 ||

|| ||

|| Version Date Author Remarks ||

|| 1.0 Oct 03 2016 Adolfo Garza Initial commit ||

|| 1.1 Oct 11 2016 Adolfo Garza Casting Port and Timeout ||

|| to Number ||

|| 1.2 Dec 23 2016 Adolfo Garza Added support for HostKey ||

|| Port and Type ||

\= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = */

var HTTPSMODULE, SFTPMODULE, SERVERWIDGETMODULE;

var HOST_KEY_TOOL_URL = 'https://ursuscode.com/tools/sshkeyscan.php?url=';

/**

*@NApiVersion 2.x

*@NScriptType Suitelet

*@NModuleScope Public

define(["N/https", "N/sftp", "N/ui/serverWidget"], runSuitelet);

//********************** MAIN FUNCTION **********************

function runSuitelet(https, sftp, serverwidget){

HTTPSMODULE= https;

SERVERWIDGETMODULE= serverwidget;

SFTPMODULE= sftp;

var returnObj = {};

returnObj.onRequest = execute;

return returnObj;

}

function execute(context){

var method = context.request.method;

var form = getFormTemplate(method);

if (method == 'GET') {

form = addSelectorFields(form);

}

if (method == 'POST') {

var selectaction = context.request.parameters.selectaction;

if(selectaction == 'getpasswordguid'){

form = addPasswordGUID1Fields(form);

}

else if(selectaction == 'gethostkey'){

form = addHostKeyFields(form);

}

else if(selectaction == 'downloadfile'){

form = addDownloadFileFields(form);

} else {

var password = context.request.parameters.password;

var username = context.request.parameters.username;

var passwordGuid = context.request.parameters.passwordguid;

var url = context.request.parameters.url;

var hostKey = context.request.parameters.hostkey;

var hostKeyType = context.request.parameters.hostkeytype;

var port = context.request.parameters.port;

var directory = context.request.parameters.directory;

var timeout = context.request.parameters.timeout;

var filename = context.request.parameters.filename;

var restricttoscriptids = context.request.parameters.restricttoscriptids;

var restricttodomains = context.request.parameters.restricttodomains;

if(restricttoscriptids && restricttodomains){

form = addPasswordGUID2Fields(form, restricttoscriptids, restricttodomains);

}

if(password){

form.addField({

id : 'passwordguidresponse',

type : SERVERWIDGETMODULE.FieldType.LONGTEXT,

label : 'PasswordGUID Response',

displayType: SERVERWIDGETMODULE.FieldDisplayType.INLINE

}).defaultValue = password;

}

if(url && passwordGuid && hostKey && filename){

var sftpConnection = getSFTPConnection(username, passwordGuid, url, hostKey, hostKeyType, port, directory, timeout);

var downloadedFile = sftpConnection.download({

filename: filename

}).getContents();

form.addField({

id : 'filecontents',

type : SERVERWIDGETMODULE.FieldType.LONGTEXT,

label : 'File Contents',

displayType: SERVERWIDGETMODULE.FieldDisplayType.INLINE

}).defaultValue = downloadedFile;

} else if (url) {

var myUrl = HOST_KEY_TOOL_URL + url + "&port=" + port + "&type=" + hostKeyType;

var theResponse = HTTPSMODULE.get({url: myUrl}).body;

form.addField({

id : 'hostkeyresponse',

type : SERVERWIDGETMODULE.FieldType.LONGTEXT,

label : 'Host Key Response',

displayType: SERVERWIDGETMODULE.FieldDisplayType.INLINE

}).defaultValue = theResponse;

}

context.response.writePage(form);

return;

}

function addSelectorFields(form){

var select = form.addField({

id: 'selectaction',

type: SERVERWIDGETMODULE.FieldType.SELECT,

label: 'Select Action'

});

select.addSelectOption({

value: 'getpasswordguid',

text: 'Get Password GUID',

});

select.addSelectOption({

value: 'gethostkey',

text: 'Get Host Key'

});

select.addSelectOption({

value: 'downloadfile',

text: 'Download File'

});

return form;

}

function addPasswordGUID1Fields(form){

form.addField({

id : 'restricttoscriptids',

type : SERVERWIDGETMODULE.FieldType.TEXT,

label : 'Restrict To Script Ids',

}).isMandatory = true;

form.addField({

id : 'restricttodomains',

type : SERVERWIDGETMODULE.FieldType.TEXT,

label : 'Restrict To Domains',

}).isMandatory = true;

return form;

}

function addPasswordGUID2Fields(form, restrictToScriptIds, restrictToDomains){

form.addCredentialField({

id : 'password',

label : 'Password',

restrictToScriptIds: restrictToScriptIds.replace(' ', '').split(','),

restrictToDomains: restrictToDomains.replace(' ', '').split(','),

});

return form;

}

function addHostKeyFields(form){

form.addField({

id : 'url',

type : SERVERWIDGETMODULE.FieldType.TEXT,

label : 'URL (Required)',

});

form.addField({

id : 'port',

type : SERVERWIDGETMODULE.FieldType.INTEGER,

label : 'Port (Optional)',

});

form.addField({

id : 'hostkeytype',

type : SERVERWIDGETMODULE.FieldType.TEXT,

label : 'Type (Optional)',

});

return form;

}

function addDownloadFileFields(form){

form.addField({

id : 'url',

type : SERVERWIDGETMODULE.FieldType.TEXT,

label : 'URL (Required)',

});

form.addField({

id : 'username',

type : SERVERWIDGETMODULE.FieldType.TEXT,

label : 'Username',

});

form.addField({

id : 'passwordguid',

type : SERVERWIDGETMODULE.FieldType.LONGTEXT,

label : 'PasswordGuid (Required)',

});

form.addField({

id : 'hostkey',

type : SERVERWIDGETMODULE.FieldType.LONGTEXT,

label : 'Host Key (Required)',

});

form.addField({

id : 'hostkeytype',

type : SERVERWIDGETMODULE.FieldType.TEXT,

label : 'Host Key Type',

});

form.addField({

id : 'filename',

type : SERVERWIDGETMODULE.FieldType.TEXT,

label : 'File Name',

});

form.addField({

id : 'port',

type : SERVERWIDGETMODULE.FieldType.INTEGER,

label : 'Port',

});

form.addField({

id : 'directory',

type : SERVERWIDGETMODULE.FieldType.TEXT,

label : 'Directory',

});

form.addField({

id : 'timeout',

type : SERVERWIDGETMODULE.FieldType.INTEGER,

label : 'Timeout',

});

return form;

}

function getFormTemplate(){

var form = SERVERWIDGETMODULE.createForm({

title : 'SFTP Helper Tool'

});

form.addSubmitButton({

label : 'Submit'

});

return form;

}

function getSFTPConnection(username, passwordGuid, url, hostKey, hostKeyType, port, directory, timeout){

var preConnectionObj = {};

preConnectionObj.passwordGuid = passwordGuid;

preConnectionObj.url = url;

preConnectionObj.hostKey = hostKey;

if(username){ preConnectionObj.username = username; }

if(hostKeyType){ preConnectionObj.hostKeyType = hostKeyType; }

if(port){ preConnectionObj.port = Number(port); }

if(directory){ preConnectionObj.directory = directory; }

if(timeout){ preConnectionObj.timeout = Number(timeout); }

var connectionObj = SFTPMODULE.createConnection(preConnectionObj);

return connectionObj;

}

Suitescript 2.0 Quickstart Examples

August 24, 2022 Adolfo Garza Comments 3 comments

Here I will be posting the most used scripts from Suitescript 2.0. These code examples are meant for beginners to be a quick start so they can quickly get a script started and add their code to them. I tried to keep them short. If you are a beginner I recommend you to check Netsuite’s Help section and navigate to Suitescript 2.0 API so you can learn more about each.

Client Script

Note: Check out how the helper functions in clientscript have to be nested inside the main runClientscript function. This is something particular to client scripts.

100

101

102

var SEARCHMODULE;

/**

*@NApiVersion 2.x

*@NModuleScope Public

*@NScriptType ClientScript

define(['N/search'], runClientscript);

function runClientscript(search){

SEARCHMODULE = search;

//*********** HELPER FUNCTIONS ***********

function lineInit(context) {

//SEARCHMODULE.load(123);

context.currentRecord;

context.sublistId;

alert("lineInit Triggered!");

return;

}

function pageInit(context) {

context.currentRecord;

context.mode;

alert("pageInit Triggered!");

return;

}

function postSourcing(context) {

context.currentRecord;

context.sublistId;

context.fieldId;

alert("postSourcing Triggered!");

return;

}

function saveRecord(context) {

context.currentRecord;

alert("saveRecord Triggered!");

return true; //Return true if you want to continue saving the record.

}

function sublistChanged(context) {

context.currentRecord;

context.sublistId;

alert("sublistChanged Triggered!");

}

function validateDelete(context) {

context.currentRecord;

context.sublistId;

alert("validateDelete Triggered!");

return true; //Return true if the line deletion is valid.

}

function validateField(context) {

context.currentRecord;

context.sublistId;

context.fieldId;

context.line;

context.column;

alert("validateField Triggered!");

return true; //Return true to continue with the change.

}

function validateInsert(context) {

context.currentRecord;

context.sublistId;

alert("validateInsert Triggered!");

return true; //Return true if the line insertion is valid.

}

function validateLine(context) {

context.currentRecord;

context.sublistId;

alert("validateLine Triggered!");

return true; //Return true if the line is valid.

}

function fieldChanged(context) {

context.currentRecord;

context.sublistId;

context.fieldId;

context.line;

context.column;

alert("fieldChanged Triggered!");

return;

}

var returnObj = {};

returnObj.lineInit = lineInit;

returnObj.pageInit = pageInit;

returnObj.postSourcing = postSourcing;

returnObj.saveRecord = saveRecord;

returnObj.sublistChanged = sublistChanged;

returnObj.validateDelete = validateDelete;

returnObj.validateField = validateField;

returnObj.validateInsert = validateInsert;

returnObj.validateLine = validateLine;

returnObj.fieldChanged = fieldChanged;

return returnObj;

}

User Event Script

var SEARCHMODULE;

/**

*@NApiVersion 2.x

*@NModuleScope Public

*@NScriptType UserEventScript

define(['N/search'], runUserEvent);

function runUserEvent(search) {

SEARCHMODULE = search;

var returnObj = {};

returnObj.beforeLoad = beforeLoad;

returnObj.afterSubmit = afterSubmit;

returnObj.beforeSubmit = beforeSubmit;

return returnObj;

}

function beforeLoad(context) {

//SEARCHMODULE.load(123);

log.debug('beforeLoad Triggered');

context.newRecord;

context.type;

context.form;

return;

}

function afterSubmit(context) {

//SEARCHMODULE.load(123);

log.debug('afterSubmit Triggered');

context.newRecord;

context.oldRecord;

context.type;

return;

}

function beforeSubmit(context) {

//SEARCHMODULE.load(123);

log.debug('beforeSubmit Triggered');

context.newRecord;

context.oldRecord;

context.type;

return;

}

Scheduled Script

var SEARCHMODULE;

/**

*@NApiVersion 2.x

*@NScriptType ScheduledScript

*@NModuleScope Public

define(["N/search"], runScheduledScript);

//********************** MAIN FUNCTION **********************

function runScheduledScript(search){

SEARCHMODULE = search;

var returnObj = {};

returnObj.execute = execute;

return returnObj;

}

function execute(context){

//SEARCHMODULE.load(123);

context.type;

log.debug("ScheduledScript Triggered");

}

Suitelet

var SEARCHMODULE, UIMODULE;

/**

*@NApiVersion 2.x

*@NScriptType Suitelet

*@NModuleScope Public

define(['N/search', 'N/ui/serverWidget'], runSuitelet);

//********************** MAIN FUNCTION **********************

function runSuitelet(search, ui){

SEARCHMODULE = search;

UIMODULE = ui;

var returnObj = {};

returnObj.onRequest = execute;

return returnObj;

}

function execute(context){

//SEARCHMODULE.load(123);

if (context.request.method == 'GET') {

context.response.write('Hello World'); //Example writing HTML string

return;

} else {

var form = UIMODULE.createForm({ title: 'Demo Suitelet Form' }); //Example writing form object

context.response.writePage(form);

}

RESTlet

var SEARCHMODULE;

/**

*@NApiVersion 2.x

*@NScriptType Restlet

*@NModuleScope Public

define(['N/search'], runRestlet);

//********************** MAIN FUNCTION **********************

function runRestlet(search){

SEARCHMODULE = search;

var returnObj = {};

returnObj.get = doGet;

returnObj.post = doPost;

returnObj.put = doPut;

returnObj.delete = doDelete;

return returnObj;

}

function doGet(restletBody){

log.debug('Called from GET', restletBody);

return "Hello from GET. Data received: " + JSON.stringify(restletBody);

}

function doPost(restletBody){

log.debug('Called from POST', restletBody);

return "Hello from POST.\nData received:\n" + JSON.stringify(restletBody);

}

function doPut(restletBody){

log.debug('Called from PUT', restletBody);

return "Hello from PUT.\nData received:\n" + JSON.stringify(restletBody);

}

function doDelete(restletBody){

log.debug('Called from DELETE', restletBody);

return "Hello from DELETE.\nData received:\n" + JSON.stringify(restletBody);

}

Leave a comment if you want to see other examples.