How to use Netsuite workbook using script?

Hi,

In this post we are going to learn accessing workbook in script. We already cover Data Set and Workbook in our previous post, click on this link to read.

To use workbook in script, you should use "N/query" module. We can query Workbook using Query APIs.

Lets go to step by step process of using Query Module

Step 1: Script should use "N/query" module and use query as parameter 

( You can use parameter name as you like) .

Step 2: Copy workbook internal id.

Step 3: Use "query.load" api and use internal id from step 2.

var mLoadedquery= query.load({
                        id: 'custworkbook_your_id_here'
                    }); 

Step 4: Run loaded query 

var resultSet = mLoadedquery.run();

Step 5: Access workbook column data in script. You can either iterator using iterator() or use for loop and get value one by one.

** Please note: We will use for loop to iterate each data line by line.

Step 6: Get results using  "resultSet.results"

var results = resultSet.results;

Step 7: Get columns using "resultSet.columns"

var theColumns = resultSet.columns;

Step 8: Use for loop and iterate till last of results

for (var i = 0; i < results.length; i++)

Step 8 (a) : Get values from each line using  "results[i].values"

                        var mValues = results[i].values;

 Step 8 (b) : Get each column of current line "mValues[0]"

                    var column1 =  mValues[0];

Please Note : Column count start from 0, so length should be less than results.length ( not Equal to results.length)

Step 8(c) : Get each columns from the workbook using same as step 8b.

Step 9: Close the loop. 

Important point to note here: If you want to set internal id on record ( example department or subsidiary etc),

You have to add additional column for each dropdown/select and use that column. We can not use getText in workbook as we do in saved searches.

We wrote Schedule script but you can try same in other scripts also, steps are same.

/**
 *@NApiVersion 2.x
 *@NScriptType ScheduledScript
 */
define(['N/search', 'N/record', 'N/email', 'N/runtime', 'N/render', 'N/query', 'N/format'],
    function(search, record, email, runtime, render, query, format) {
        function execute(context) {
            try{

                var myLoadedQuery = query.load({
                        id: 'custworkbook10'
                    });

                var resultSet = myLoadedQuery.run(); 

               var results = resultSet.results;
                var theColumns = resultSet.columns; 

                for (var i = 0; i < results.length; i++)
                    {
                        var mValues = results[i].values;
                        var column1 = mValues[0];

                }

            }
            catch(ex)
            {
                log.error('error', ex);
            }               
        }
        return {
            execute: execute
        };
    });

 Let us know if you like our effort, comment if you have any query. 

Hope this will help you in your day to day developments.

Thanks

Netsuite Guru

 

Download Netsuite Guru Android App
Follow Me on Linkedin
Our FB Page