Requirement
Set up a test account to establish a dynamic connectivity to the excel using a suitelet script. Excel should pick up data from a saved search.
Solution
Use a suitelet script External url with the saved search Id and a secret key to use in excel sheet.
/**
* @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_tgus_324')
let searchIdFromRecord = getRecordData[0].getValue('custrecord_jj_search_id_tgus_324')
if (checkForParameter(secretKeyFromRecord) && checkForParameter(searchIdFromRecord)) {
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 writes the response message
* @param responseMsg The message to be displayed as the response
* The function returns the response message
*/
function returnResponse(responseMsg,scriptContext){
try{
return scriptContext.response.writeLine((responseMsg))
}
catch (e) {
log.debug('Error@returnResponse',e)
}
}
/**
* Function defines the functionality to convert the data to a CSV format
* @param ObjArray {object} - the object data as the parameter to this function
* returns the data as the CSV file format
*/
function ConvertToCSV(objArray) {
let array = typeof objArray != 'object' ? JSON.parse(objArray) : objArray;
let str = '';
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 += ','
let addCol=(array[i][index].replaceAll(',',' '))
// line += (JSON.stringify(array[i][index]));
line += (JSON.stringify(addCol));
}
str += line + '\r\n';
}
//str = str.slice(0, -2) + '';
return str;
}
/**
* Function defines a custom record search to get the data
* @param {int} recId - Record Id of the custom record from the URL
* @param {Object} scriptContext
* This function returns the search result if result count is greater than 1
* Return response message showing empty search result
*/
function customRecordSearch(recId,scriptContext) {
let customrecord_jj_dynamic_connection_recordSearchObj = search.create({
type: "customrecord_dynamic_connection_tgus_324",
filters:
[
["internalid", "anyof", recId]
],
columns:
[
search.createColumn({name: "custrecord_jj_search_id_tgus_324", label: "Search ID"}),
search.createColumn({name: "custrecord_jj_secret_key_tgus_324", label: "Secret Key"})
]
});
let searchResultCount = customrecord_jj_dynamic_connection_recordSearchObj.runPaged().count;
let customRecordSearchResult = customrecord_jj_dynamic_connection_recordSearchObj.run().getRange({
start: 0,
end: 1
});
if (searchResultCount > 0) {
return customRecordSearchResult;
}
else{
let responseMsg='Search Result Empty !'
returnResponse(responseMsg,scriptContext)
}
}
/**
* Function defines the functionality to check matching the secret key from the custom record and the URL
* @param secretKey - Secret key from the URL
* @param secretKeyFromRecord - Secret key from the custom record
* @param searchIdFromRecord - Search Id from the custom record
* @param scriptContext - Search Id from the custom record
* This function returns the search result if secret key from the custom record and the URL matches.
* It also returns the column label from the search result
* If secret keys doesnt match, then will respond with corresponding response message.
*/
function checkSecretKey(secretKey, secretKeyFromRecord, searchIdFromRecord,scriptContext) {
try {
let columnLabel = []
let returnValue = []
if ((checkForParameter(secretKey) && checkForParameter(secretKeyFromRecord))) {
if (secretKey === secretKeyFromRecord) {
let loadSearch = search.load({
id: searchIdFromRecord
})
let searchResultCount = loadSearch.runPaged().count;
let loadSearchResult = loadSearch.run().getRange({
start: 0,
end: 1000
});
for (let column = 0; column < loadSearch.columns.length; column++) {
columnLabel.push(loadSearch.columns[column].label || ('Column ' + (column + 1)))
}
returnValue.push(columnLabel)
if (searchResultCount > 0) {
returnValue.push(loadSearchResult)
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 defines the functionality convert the data to an object format
* @param checkSecretKeyMatchHead - Column headings from the search result
* @param checkSecretKeyMatch - Search result if the secret keys match
* This function returns array of object
*/
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)
}
}
/**
* Function checks or validates the parameter value
* @param parameter - parameter passed to the function to chekc the value
* This function returns true if condition is satisfied
*/
const checkForParameter = function checkForParameter(parameter) {
if (parameter !== "" && parameter !== null && parameter !== undefined && parameter !== false && parameter !== "null" && parameter !== "undefined" && parameter !== " " && parameter !== 'false') {
return true;
}
}
return {onRequest}
});