This article is relevant if you are looking to build Microsoft Excel worksheets using formulas which will retrieve NetSuite data leveraging Saved Search technology.
Background
While helping a client build an Excel based financial reporting environment (see Finally, NetSuite Financial Statements Produced in Excel), they needed to get at information that was not readily available from the new tools they were using. Because they loved that they could use easy Excel formulas to retrieve income statement and balance sheet balances, they wanted the same capacity to get at specific operational information.
In relatively short fashion, we invented a provocative way to retrieve data from a NetSuite Saved Search that does NOT require ODBC or Web Services. Instead, we leverage NetSuite Suitelet technology (Restlets can work too) to expose selective business information to your Excel Workbook.
Software Architecture
The diagram (click to enlarge) illustrates the software model to connect up Excel to your NetSuite Saved Search. While a full framework can be developed that generalizes the capacity, I will offer short code snippets to help you understand better what is going on. Naturally, all of the software must come together to be a full solution and often it is faster to solve a specific use case. Remember, the code examples below are NOT secure. Please consider this in your ultimate solution.
Microsoft Excel VBA Code Snippet
Using Microsoft Excel VBA, create a user defined function that will effectively call out the saved search. In real use, parameterize the query so that it is flexible.
'/ Do not distribute this link; in production use, properly manage the security credential; this is NOT secure
Const NS_URL As String = "https://forms.netsuite.com/app/site/hosting/scriptlet.nl?script=398&deploy=1&compid=852534&h=231a34ed464989202228"
'/user defined function used as a formula in Excel
Public Function getHours(Day, Month, Year)
If Not IsNumeric(Day) Then
Exit Function
End If
If Not IsNumeric(Month) Then
Exit Function
End If
If Not IsNumeric(Year) Then
Exit Function
End If
'/ craft the URL and add a parameter for the date
Dim url As String
url = NS_URL & "&date=" & Month & "/" & Day & "/" & Year
'ITSLocation = url
'/ call the API
getHours = NSCaller(url)
Exit Function
End Function
'/function to retrieve value from Suitelet
Private Function NSCaller(url)
Set xmlhttp = CreateObject("Microsoft.XMLHTTP")
With xmlhttp
.Open "GET", url, False
.setRequestHeader "Content-Type", "text/html"
.setRequestHeader "Accept", "text/html"
.send
NSCaller = .responseText
End With
Exit Function
End Function
NetSuite Suitelet Code Snippet
This is a simple Suitelet that returns a single value. In real life, you would do work to validate input, produce error handling, and consider security.
var CONST_SS = ‘customsearch_gethours’;
function GetHoursSuitelet(request, response) {
var sMethod = request.getMethod();
if ( sMethod == 'GET' ){
var sDate = request.getParameter('date'), get the date
var filter = new Array();
filter[0] = new nlobjSearchFilter('custrecord_hours_date', null, 'is', sDate);
var search_results = nlapiSearchRecord('customrecord_mycohours', CONST_SS, filter, null);
if (!search_results)return;
if (search_results.length==0) return;
// should only return one result
var hours = search_results[0];
val = hours.getValue('total');
if (val){
response.write(val);
};
return;
};
};
Get at your NetSuite Data
The example above illustrates the power of the NetSuite platform and it is one of the many reasons we love working in the environment. We can invent to take care of our customers’ challenges.
Never feel hostage to your information in NetSuite. If you would like help to address your specific concern, let’s talk.
I’m really intrested in implementing the pull of a saved search into excel. Today we have numerous executive reports that I have to pull several saved searches out via export to csv .. paste those into tabs in excel and then run a macro that generates all the carts/graphs etc for the report. What i don’t see here is a step by step implementaiton. I get adding the function to Excel. That’s eazy. I take it i can then just use a call from a cell for the function. On the Suitelet side. The search appears to be contained in the Suitelet or do i need a saved search created and saved in NS? Could this be modified to do something like below where dataIn would be the saved search ID?
function executeSavedSearches(dataIn) {
try {
if ( dataIn != undefined ) {
nlapiLogExecution(‘DEBUG’, ‘dataIn’, JSON.stringify(dataIn));
} else {
nlapiLogExecution(‘ERROR’, ‘ERROR’, ‘dataIn undefined!’);
return ‘{“error”:”dataIn undefined!”}’;
}
if (dataIn.type === undefined || dataIn.id === undefined) {
nlapiLogExecution(‘ERROR’, ‘ERROR’, ‘Incomplete parameters’);
return ‘{“error”:”Incomplete parameters!”}’;
}
var searchresults = nlapiLoadSearch(dataIn.type, dataIn.id);
if (searchresults !== undefined) {
nlapiLogExecution(‘DEBUG’, ‘Step’, “Run Search…”);
var resultset = searchresults.runSearch();
var results = [];
var index = 0;
do {
var subset = resultset.getResults(index, index+1000);
if ( !subset ) break;
subset.forEach( function (row) {
results.push(row);
index++;
});
} while (subset.length > 0);
nlapiLogExecution(‘DEBUG’, ‘results’, results);
nlapiLogExecution(‘DEBUG’, ‘Number of sites’, index);
return results;
}
return null;
} catch(e) {
var message = “ERROR”;
if (e instanceof nlobjError) {
message = e.getCode() + ‘: ‘ + e.getDetails();
nlapiLogExecution(‘ERROR’, ‘system error – ‘ + e.getCode() + ‘\n’ + e.getDetails() + ‘\n’ + JSON.stringify(e.getStackTrace()));
} else {
message = e.toString();
nlapiLogExecution(‘ERROR’, ‘unexpected error – ‘ + e.toString());
}
nlapiSendEmail(960, 960, ‘Error’, message);
return null;
}
}
Thought here – is that my current macro’s run off data pasted into given tabs. Each tab represents the CSV of a saved search. Could this all be plumbed together to get the results pulled into the Excel into their existing TAB placeholders.
In theory, you can do this. You may run into paging challenges and many argue that it’s not good to have insecure links pull data out of NetSuite. But did you see this article about getting data to Excel from NetSuite?
Marty
I caught the link on the Excel & Reports. Issue i have with Reports is the data i’m pulling isn’t always available in the reports. I’ve tried modifying reports but they seem very structured and canned and not as flexabile as a saved search.
Hi Brian,
We could leverage this pattern to effectively publish a RESTlet Endpoint which will pull the data out of NetSuite and drop it into Excel:
https://blog.prolecto.com/2017/07/15/update-netsuite-directly-via-google-sheets-without-csv-files/
Marty
Marty,
Thank you for this tip. I understand how you did this with an anonymous login. Is there a way to do it while maintaining a secure login, or would that require a restlet instead of a suitelet?
Louis,
You can program the system with a secure login. You just need to go through the mechanics of token based authentication. This article can help:
https://blog.prolecto.com/2017/10/14/download-netsuite-oauth-token-based-authentication-sample-node-js-program/
Marty