Netsuite running a report with a SuiteScript using nlapiRunReport

This post will go over the insanity of fetching a report from Netsuite.

Currently, the only known way is to use a hidden and private function, nlapiRunReport.
This method is available for:

SuiteScript 1.0,
running as a RESTlet (not Client)

The process you will have to go through is:

Enable RESTlets on your account
Create a Script Record (Upload Script)
Create a Script Deployment
Test out the script
Connect to the RESTlet from the outside (later post)

#5 is a whole other circus that I'll probably go over in a future post.

The Script

Below is a script which I've set to fetch 2 cells from 1 specific custom report.

The parameters are:

periodFrom: This value is ideally something like "2023-01-01" but unfortunately the value is more like "491". You can get this value by opening the report on your browser, opening Inspector > Network, and looking at the payload of the request when you refresh a report
periodTo: Same thing
columnName: This script is set to fetch from one column. Enter the column name here - case sensitive. Netsuite can trick you into thinking the column name is ALLCAPS since that is what the report view shows, but beware that this is probably not the correct casing
Report ID: Fed into runStandardReport. The ID is in the browser URL of your desired report, probably as the parameter "cr"
Additional Settings: Set under reportSettings.addCriteria but do not try to set this first as this one can cause a lot of errors if you don't do it right. Leave it commented out for now

The base script is originally from this Chinese site, where they use the nlapiRunReport function.


/**
* nlapiRunReport
*/

function run(params) 
{
    var standardReports = new reports();
    return standardReports.doGet(params);
}

function reports() 
{
    var reportByPeriod;
    var columnName;

    this.doGet = function(params) {
        var params = {
            periodFrom: '491', 
            periodTo: '500', 
        };

        var params = validateParams(params);

        columnName = 'Balance'; 

        var result = {};

        result = runGeneralLedgerReport({
            'periodFrom': params.periodFrom,
            'periodTo': params.periodTo
        });

        nlapiLogExecution('AUDIT', 'doGet', 'result=' + JSON.stringify(result));
        return JSON.stringify(result);
    }

    function validateParams(params) {
        if (!params) {
            throw nlapiCreateError('RunStandardReportError', 'Run report params are undefined', true);
        }
        nlapiLogExecution('AUDIT', 'validateParams', 'params=' + JSON.stringify(params));

        if (!isObject(params)) {
            params = JSON.parse(params);
        }

        if (!params.periodFrom) {
            throw nlapiCreateError('RunStandardReportError', 'period from is mandatory', true);
        }

        if (!params.periodTo) {
            throw nlapiCreateError('RunStandardReportError', 'period to is mandatory', true);
        }

        return params;
    }

    function runGeneralLedgerReport(params) {
        nlapiLogExecution('AUDIT', 'runGeneralLedgerReport', 'params=' + JSON.stringify(params));
        return runStandardReport(293, composeGeneralLedgerReportSettings(params), columnName)
    }

    function runStandardReport(reportId, reportSettings, targetColumnName) {
        try {
            setupUserPref();
            var pivotTable = nlapiRunReport(reportId, reportSettings);
            if (!pivotTable) {
                return 0;
            }
            for(var thing in pivotTable) {
                nlapiLogExecution('DEBUG', 'runStandardReport', 'pivotTable: ' + thing.toString());
            }
            var rowHier = pivotTable.getRowHierarchy();
            for (var rowH in rowHier) {
                nlapiLogExecution('DEBUG', 'runStandardReport', 'getRowHierarchy: ' + rowH.toString());
            }
            var children = pivotTable.getRowHierarchy().getChildren();
            for (var child in children) {
                nlapiLogExecution('DEBUG', 'runStandardReport', 'children: ' + child.toString());
                for (var childchild in child) {
                    nlapiLogExecution('DEBUG', 'runStandardReport', 'children: ' + child.toString() + " " + childchild.toString() + childchild.getProperties() + child.getProperties());
                }
            }

            var colHier = pivotTable.getColumnHierarchy();
            var colChildren = colHier.getVisibleChildren();
            var targetCol = null;
            for ( var colIdx in colChildren) {
                nlapiLogExecution('DEBUG', 'runStandardReport', 'target col label: ' + colChildren[colIdx].getLabel());
                if (colChildren[colIdx].getLabel() === targetColumnName) {
                    targetCol = colChildren[colIdx];
                    break;
                }
            }

            if (targetCol === null) {
                throw nlapiCreateError('RunStandardReportError', 'Column ' + targetColumnName + ' does not exists', true);
            }

            recoverUserPref();

            nlapiLogExecution('DEBUG', 'runStandardReport', 'getRowHierarchyChild1: ' + pivotTable.getRowHierarchy().getChildren()[0].getLabel());
            nlapiLogExecution('DEBUG', 'runStandardReport', 'getRowHierarchyChild2: ' + pivotTable.getRowHierarchy().getChildren()[0].getDisplayValue());
            nlapiLogExecution('DEBUG', 'runStandardReport', 'getRowHierarchyChild3: ' + pivotTable.getRowHierarchy().getChildren()[0].getValue());
            nlapiLogExecution('DEBUG', 'runStandardReport', 'getRowHierarchyChild4: ' + pivotTable.getRowHierarchy().getChildren()[0].getAlias());
            nlapiLogExecution('DEBUG', 'runStandardReport', 'getRowHierarchyChild5: ' + pivotTable.getRowHierarchy().getChildren()[0].getSummaryLine());
            nlapiLogExecution('DEBUG', 'runStandardReport', 'getRowHierarchyChild5: ' + pivotTable.getRowHierarchy().getChildren()[0].getSummaryLine().getValue(targetCol));

            var testChild = pivotTable.getRowHierarchy().getChildren()[0].getChildren();
            for (var child in testChild) {
                nlapiLogExecution('DEBUG', 'runStandardReport', 'testChild: ' + child.toString());
            }

            nlapiLogExecution('DEBUG', 'runStandardReport', 'testChild: ' + pivotTable.getRowHierarchy().getChildren()[0].getChildren()[0].getSummaryLine().getValue(targetCol));
            nlapiLogExecution('DEBUG', 'runStandardReport', 'testChild: ' + pivotTable.getRowHierarchy().getChildren()[0].getChildren()[1].getSummaryLine().getValue(targetCol));
            nlapiLogExecution('DEBUG', 'runStandardReport', 'testChild: ' + pivotTable.getRowHierarchy().getChildren()[0].getChildren()[2].getSummaryLine().getValue(targetCol));
            nlapiLogExecution('DEBUG', 'runStandardReport', 'testChild: ' + pivotTable.getRowHierarchy().getChildren()[0].getChildren()[3].getSummaryLine().getValue(targetCol));
            nlapiLogExecution('DEBUG', 'runStandardReport', 'testChild: ' + pivotTable.getRowHierarchy().getChildren()[0].getChildren()[5].getSummaryLine().getValue(targetCol));

            var totalIncome = pivotTable.getRowHierarchy().getChildren()[0].getChildren()[2].getSummaryLine().getValue(targetCol);
            var totalExpenses = pivotTable.getRowHierarchy().getChildren()[0].getChildren()[3].getSummaryLine().getValue(targetCol);

            return {'total_income': totalIncome, 'total_expenses': totalExpenses};
        } catch (ex) {
            nlapiLogExecution('ERROR', 'runStandardReport Error', ex);
            recoverUserPref();
            throw ex;
        }
    }

    function composeGeneralLedgerReportSettings(params) {
        var reportSettings = composeReportSettings(params);
        // reportSettings.addCriteria('aatyme,account,saccttype,x,x', 'Bank');
        return reportSettings;
    }

    function composeReportSettings(params) {
        var settings = new nlobjReportSettings(params.periodFrom, params.periodTo);
        return settings;
    }

    function setupUserPref() {
        var userPref = nlapiLoadConfiguration('userpreferences');
        this.reportByPeriod = userPref.getFieldValue('reportbyperiod');
        nlapiLogExecution('AUDIT', 'setupUserPref', 'reportByPeriod = ' + this.reportByPeriod);
        if (this.reportByPeriod !== 'FINANCIALS') {
            userPref.setFieldValue('reportbyperiod', 'FINANCIALS');
            nlapiSubmitConfiguration(userPref);
        }
    }

    function recoverUserPref() {
        if (this.reportByPeriod !== 'FINANCIALS') {
            var userPref = nlapiLoadConfiguration('userpreferences');
            userPref.setFieldValue('reportbyperiod', this.reportByPeriod);
            nlapiSubmitConfiguration(userPref);
        }
    }

    function isObject(param) {
        return typeof param === 'object' && param.constructor === Object || Object.prototype.toString.apply(param) === '[object Object]';
    }
};

1. Enable RESTlets on your account

Go do that

2. Create a Script Record (Upload Script)

Go to Customization > Scripting > Script > New.

Click on the invisible "+" icon that appears when you hover over the select input to open a window to upload your script file.
Once uploaded, the little window will close, and the select input will show your script name. Click "Create Script Record".
Set the script as a RESTlet (it won't work as Client), and set the GET function to just the word "run" as that is the method we want to run when we make a GET request to this RESTlet.

3. Create a Script Deployment

Now you need to "publish" your script.

Go to Customization > Scripting > Script, and find your script. Click "View" on it.
Click "Deploy Script", and set it as "Released" so you don't forget about it later (no one else can view this URL anyway).
You will see a clickable "URL" value. This is a URL that only a staff member can view on the browser, while logged in.
It will probably be outputting an error right now, but this is where you would want to print your JSON e.g. "{balance: 200}"
This is not the final URL you will use, but it will be the URL you use in finding your desired values in the next step.

4. Test out the Script

This is the part you will take hours on, as you try to debug and dig out your desired values.

The page you will be refreshing 2000 times is the URL you got from step 3, and the debugging page.

Since you can't just "console.log" your way through this, you need to use Netsuite's special slow debugging tool at Customization > Scripting > Script Execution Logs.
Filter the log to just your script name. About 1-2 minutes after you fire your script, the logs here will appear.
As the script already has many logs put in, the log should already have a fair amount of lines.

So now what you will do is to keep editing the script (Click "Edit" on the script name when viewing the Script Record) to try and dig out the right column/row indexes to get your desired value, using debug statements and Your Best Guess. The bulk of your time will probably be inside the runStandardReport function.

One of the fun things about this is that when you debug things, you'll get a mix of Javascript errors, and Java errors. Sometimes you might even get Java pointers like com.netledger.app.common.scripting.version1.nlobjPivotRowImplV1. "What's nlobjPivotRowImplV1", you may ask. Nobody knows because it's all private Java code.

God save your soul

5. Connect to the RESTlet

This is another long process for another post yarr

Back to Reads