The QUERY function in Google Sheets is a powerful function that helps to operate on a range of data, however, on a current project I needed the QUERY function to ignore rows where a certain column was empty. Here was how I was able to get the desired output. To ignore blank or empty cells using Google Sheet’s QUERY function add the condition IS NOT NULL in the WHERE clause for the column where the blank or empty cells are found. ...

If you have two or more columns of data and you want to merge these columns into one column then using the Google Sheets QUERY formula may be one way you can achieve this. Here’s an in depth explanation of how to achieve this: Step 1 – Prep your data If you data doesn’t contain any spaces then you’re good to go, if though your data does contain spaces then you will need to define a character at the end of each cell to get this to work. ...

How do you merge multiple columns and then expand them into a different arrangement using Google Sheets? Using a working example I will demonstrate how to migrate a specific data set containing columns, into a different data set using a different arrangement of columns. The final formula is quite the monster and I’ll dissect this piece by piece to help demonstrate the process: = A R R A Y F O R M U L A ( S P L I T ( A R R A Y F O R M U L A ( S U B S T I T U T E ( T R A N S P O S E ( S P L I T ( J O I N ( " " , Q U E R Y ( D a t a C o n c a t ! A : L , , R O W S ( D a t a C o n c a t ! A : A ) ) ) , " " ) ) , " ~ " , " " ) ) , " ; " , T R U E , F A L S E ) ) Original Data Structure I had exported the following salary data from our budgeting software which contained how much each staff person would be receiving each month throughout the financial year. ...

How do you apply the sum to a certain number of cells according to another input cell? The SUM function works amazingly well when the range sought for its total value is static, and one way of making a SUM function somewhat dynamic is to make the cells it references change, but what if you need the SUM function to total a range according to an input cell? Something that would look a little like this in words: ...

As mentioned in my previous post on what my preferred text editor is for SuiteScripting NetSuite does provide a deeper integration with JetBrains’ WebStorm product through a plugin. To enable this plugin in WebStorm you will need to access this help page in NetSuite’s documentation and follow the step by step guide. (The process is fairly straightforward, but unfortunately the plugin doesn’t play well with PyCharm.) Once you then have the plugin installed in WebStorm you then need to ensure you undertake (or already have done) the following steps: ...

If you’re looking for a good text editor to perform your SuiteScript coding there are good editors, such as Microsoft’s Visual Studio Code, but the one I personally use on a regular basis is PyCharm. Both platforms enable you to: Write and analyse your JavaScript code; Have integrated terminal and console windows; Git integration; Ability to code in other languages, such as Python (great for web-scraping and data analysis of your saved search csv files!); and Connection to your SuiteScript File Cabinet (although the plugin for VSCode is a little more cumbersome to set up), and once setup you can then upload directly to your project’s folder in NetSuite. Both are free PyCharm Community Edition, although I have an Ultimate licence that provides access to all of JetBrains products). PyCharm and the more commonly known other JetBrains IDE WebStorm have been around for quite some time, and I have been more familiar with their product because of this. ...

If you are testing the return value of a function in the immediate window in VBA and get the following error: Wrong number of arguments or invalid property assignment (Error 450) What you are doing is something like this: Function GetCollection() As Collection Dim coll As New Collection coll.Add "Hello" coll.Add "World" Set GetCollection = coll End Function Then in the immediate window typing: ? GetCollection() The code appears to work fine, but the error is a mystery. The reason for the error is that the immediate window call expects a collection, but isn’t receiving one. ...

When working with Advanced Templates, and the FreeMarker syntax in NetSuite there can be some oblique errors which make it difficult to diagnose. To help diagnose any such errors, I highly recommend writing your templates in a code editor. I personally prefer the editors from JetBrains as they package everything you need in one nice editor. To effectively write FreeMarker templates with syntax highlighting and auto-completion you will need the IntelliJ Editor. ...

I like NetSuite’s Advanced PDF/HTML templating system with the FreeMarker syntax. However, there are a couple of issues I’ve run into and one just recently: How can you force printing on two or more pages? One example I’ve been working on is the ability to print “how to pay” information on the back of our invoices that are issued to our customers. As our invoices can span a few lines we have been squeezing that information down in the footer of the page. This makes for small font and errors from the customer when entering payment details. I mean, you’ve got a lot more space on the back than you do in the footer! It would be so much more convenient if this information were to be printed on the back of the invoice, but to do this it would mean getting the template to do a page break. ...

Perhaps the easiest way to assign a default value to a variable is to append the || conditional after referencing the variable. For example, after looping through an array and mapping the values to properties within an object, I needed to test whether the property had been assigned. As I use Google Spreadsheets to iterate through data imagine the following data set: A B C 1 Name Age Choice 2 Ryan 50 A 3 Sara 27 4 Jude 29 C Data set containing rows of data, except for one row with a blank “Choice” value. ...