Requirement
We need to create a Suitelet page to load the saved search and the search results should be loaded to the Excel sheet by connecting Excel to the Netsuite using the Suitelet URL. Suitelet should be respond with CSV file format.
Solution
We will be creating a suitelet script to load the saved search and then will convert the results to csv file format.
We will also create a custom record to store the Id of the saved search and a secret key to authenticate.
Data will be loaded by authenticating the secret key.
Following is the suitelet script for the functionality.
/**
* @NApiVersion 2.1
* @NScriptType Suitelet
*/
define(['N/search', 'N/url'],
/**
* @param{search} search
* @param{url} url
*/
(search, url) => {
/**
* Defines the Suitelet script trigger point.
* @param {Object} scriptContext
* @param {ServerRequest} scriptContext.request - Incoming request
* @param {ServerResponse} scriptContext.response - Suitelet response
* @since 2015.2
*/
const onRequest = (scriptContext) => {
try {
let custRecId = scriptContext.request.parameters.custscript_jj_custrec_id
let secretKey = scriptContext.request.parameters.custscript_jj_secreat_key
if (custRecId) {
let getRecordData = customRecordSearch(custRecId,scriptContext)
if (checkForParameter(getRecordData)) {
let secretKeyFromRecord = getRecordData[0].getValue('custrecord_jj_secret_key_realt9')
let searchIdFromRecord = getRecordData[0].getValue('custrecord_jj_search_id_realt9')
if (checkForParameter(secretKeyFromRecord) && checkForParameter(secretKeyFromRecord)) {
let checkSecretKeyMatch = checkSecretKey(secretKey, secretKeyFromRecord, searchIdFromRecord,scriptContext)
let objectArray = convertToObject(checkSecretKeyMatch[0],checkSecretKeyMatch[1])
let csvData = ConvertToCSV(objectArray)
let csvFileData = checkSecretKeyMatch[0].toString() + '\r\n' + csvData
return scriptContext.response.writeLine((csvFileData))
}
else
{
let responseMsg='Invalid Parameters !'
returnResponse(responseMsg,scriptContext)
}
}
else{
let responseMsg='Custom Record Id Does Not Exist !'
returnResponse(responseMsg,scriptContext)
}
}
else{
let responseMsg='Custom record Parameter value is empty !'
returnResponse(responseMsg,scriptContext)
}
} catch (e) {
log.debug('error@OnRequest', e)
}
}
function returnResponse(msg,scriptContext){
try{
return scriptContext.response.writeLine((msg))
}
catch (e) {
log.debug('Error@returnResponse',e)
}
}
function ConvertToCSV(objArray) {
let array = typeof objArray != 'object' ? JSON.parse(objArray) : objArray;
log.debug('array', array)
let str = '';
log.debug('array length',array.length)
for (var i = 0; i < array.length; i++) {
let line = '';
for (var index in array[i]) {
log.debug('array[i][index]',array[i][index])
if (line != '') line += ','
line += (JSON.stringify(array[i][index]));
}
str += line + '\r\n';
}
//str = str.slice(0, -2) + '';
return str;
}
function customRecordSearch(recId,scriptContext) {
let customrecord_jj_ap_balance_recordSearchObj = search.create({
type: "customrecord_jj_ap_balance_record_realt9",
filters:
[
["internalid", "anyof", recId]
],
columns:
[
search.createColumn({name: "custrecord_jj_search_id_realt9", label: "Search ID"}),
search.createColumn({name: "custrecord_jj_secret_key_realt9", label: "Secret Key"})
]
});
let searchResultCount = customrecord_jj_ap_balance_recordSearchObj.runPaged().count;
let customRecordSearchResult = customrecord_jj_ap_balance_recordSearchObj.run().getRange({
start: 0,
end: 1
});
if (searchResultCount > 0) {
return customRecordSearchResult;
}
else{
let responseMsg='Search Result Empty !'
returnResponse(responseMsg,scriptContext)
}
}
function checkSecretKey(secretKey, secretKeyFromRecord, searchIdFromRecord,scriptContext) {
try {
let columnLabel = []
let returnValue = []
log.debug('secretKey', secretKey)
log.debug('secretKeyFromRecord', secretKeyFromRecord)
if ((checkForParameter(secretKey) && checkForParameter(secretKeyFromRecord))) {
if (secretKey === secretKeyFromRecord) {
let apBalanceSearch = search.load({
id: searchIdFromRecord
})
let searchResultCount = apBalanceSearch.runPaged().count;
let apBalanceSearchResult = apBalanceSearch.run().getRange({
start: 0,
end: 1000
});
log.debug('apBalanceSearchResult', apBalanceSearchResult)
for (let column = 0; column < apBalanceSearch.columns.length; column++) {
columnLabel.push(apBalanceSearch.columns[column].label || ('Column ' + (column + 1)))
}
returnValue.push(columnLabel)
if (searchResultCount > 0) {
returnValue.push(apBalanceSearchResult)
return returnValue;
}
}
else{
let responseMsg='Invalid Secret Key !'
returnResponse(responseMsg,scriptContext)
}
}
else{
let responseMsg='Invalid Secret Key !'
returnResponse(responseMsg,scriptContext)
}
} catch (e) {
log.debug('Error@checkSecretKey', e)
}
}
function convertToObject(checkSecretKeyMatchHead,checkSecretKeyMatch) {
try {
let arrayObj = []
for (let i = 0; i < checkSecretKeyMatch.length; i++) {
let obj = {}
for(let columnHead=0;columnHead<checkSecretKeyMatchHead.length;columnHead++){
obj[checkSecretKeyMatchHead[columnHead]]=checkSecretKeyMatch[i].getValue(checkSecretKeyMatch[i].columns[columnHead])
}
arrayObj.push(obj)
}
return arrayObj
} catch (e) {
log.debug('error@convertToObject', e)
}
}
const checkForParameter = function checkForParameter(parameter) {
if (parameter !== "" && parameter !== null && parameter !== undefined && parameter !== false && parameter !== "null" && parameter !== "undefined" && parameter !== " " && parameter !== 'false') {
return true;
}
}
return {onRequest}
});