How To: Avoid Data Downloads with Excel Formula Based NetSuite Saved Search Queries

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.


Be Sociable, Share!

Marty Zigman

Holding all three official certifications, Marty is Southern California's NetSuite expert and leads a team of senior professionals at Prolecto Resources, Inc. He is a former Deloitte & Touche CPA and has held CTO roles. For over 25 years, Marty has produced leadership in ERP, CRM and eCommerce business systems. Contact Marty to set up a conversation.

More Posts - Website - Twitter - Facebook - LinkedIn - Google Plus - YouTube

| Tags: , , | Category: Reporting, Technical | 7 Comments

6 Comments

  1. Brian
    Posted March 23, 2018 at 7:56 am | Permalink

    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.

  2. Posted March 24, 2018 at 3:29 pm | Permalink

    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

  3. Brian
    Posted March 26, 2018 at 5:15 am | Permalink

    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.

  4. Posted March 27, 2018 at 4:30 am | Permalink

    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:

    http://blog.prolecto.com/2017/07/15/update-netsuite-directly-via-google-sheets-without-csv-files/

    Marty

  5. Louis
    Posted August 29, 2018 at 9:52 am | Permalink

    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?

  6. Posted September 2, 2018 at 5:50 am | Permalink

    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:
    http://blog.prolecto.com/2017/10/14/download-netsuite-oauth-token-based-authentication-sample-node-js-program/

    Marty

One Trackback

  1. […] on the design pattern offered in this article I previously crafted, How To: Avoid Data Downloads with Excel Formula Based NetSuite Saved Search Queries, we solved the challenge. ¬†The article demonstrates the way to create Excel Macros that uses […]

Post a Comment

Your email is never published nor shared. Required fields are marked *

*
*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>