Google Sheets QUERY WHERE IN List: OR and MATCHES Examples
Google Sheets QUERY has no IN operator. Filter by a list using copy-ready OR, MATCHES, TEXTJOIN and NOT IN alternatives for text or numbers.
Read guideAll guides
244 practical notes, explanations and worked examples.
Google Sheets QUERY Errors: Troubleshooting Guide
Fix common Google Sheets QUERY errors including parse errors, NO_COLUMN, mismatched array row size, mixed data types, headers, Col notation and ARRAY_LITERAL.
Read guidePython List Comprehension With If/Else (Code Examples)
Use if else inside a Python list comprehension to transform elements, or a trailing if to filter them, with examples combining and nesting both.
Read guideXPath AND: Combining Multiple Conditions in One Expression
Combine multiple XPath conditions with and, stacked predicates, not() and grouped or expressions, with attribute, text and Selenium examples.
Read guideGoogle Sheets QUERY: Blank, Null and Empty-String Conditions
Filter blank and non-blank rows in Google Sheets QUERY with is null and is not null, including empty strings, whitespace and formula blanks.
Read guidePython *args and **kwargs Explained With Examples
Understand Python *args and **kwargs, how they collect function arguments, and how one or two asterisks unpack lists and dictionaries in calls.
Read guideSort a Python List of Lists by Multiple Columns
Sort a Python list of lists by multiple columns using tuple keys, itemgetter, mixed ascending and descending order, and missing-value handling.
Read guideRead A CSV File To List Of Dictionaries In Python
The most common form of data I have played with is generally found in CSV format. This makes the data easy to understand but difficult to modify or change, especially when the CSV file contains lots of columns. In this article I will demonstrate how I frequently use this file format to import it into a useable data structure in Python, namely, a list of Python dictionaries. Let’s start with…
Read guideHow To Ignore Blank And Empty Cells In Google Sheets
Ignore genuinely blank cells, empty strings and whitespace in Google Sheets using FILTER, COUNTIF, LEN, ISBLANK and QUERY.
Read guidePython List Not Appending? Troubleshooting - Solutions for Beginners
Why does a Python list not append an item? When you use the list method .append() to insert a list item to the end of its existing contents, you could be in for a surprise if this doesn’t happen in some instances. The main reasons for why this may occur are due to the improper usage of the list method or adding a variable that doesn’t actually contain the information…
Read guideJavascript Equivalent of Common Financial Formulas Found In Google Sheets
Continuing my work on RTU assets from yesterday, I found I needed to program some of the common formulas you see in Google Sheets into Javascript to be used in my Suitescript code. These common functions include: PV(rate, number periods, payment amount, future value, end or beginning) FV(rate, number periods, payment amount, present value, end or beginning) PMT(rate, number periods, present value, future value, end or beginning) Each of the…
Read guideHighlight Entire Row When Date Column Is In The Same Month And Year As Today
How do you highlight an entire row in Google Sheets on a table of data where a column in your table containing dates matches the same month and year as today? For this technique, you will need to use the Conditional Formatting section in Google Sheets and a Custom Formula. Here’s the Custom Formula I needed to enter (I’ll explain it underneath so that you can amend it for your…
Read guideHighlight Row When Two Date Cells In Row Have A Date Between Them: Conditional Formatting Example In Google Sheets
How can you highlight a column of dates based upon a condition where comparison is needed with the current column of dates to another column containing dates? I had a requirement where I needed to compare a column of dates to another column of dates and if a specific date was between those dates to highlight one of the columns. For example, I needed to highlight a cell when the…
Read guideRandomise Range In Google Sheets In 2 Simple Steps (No Plugins Or Script Needed)
How can you randomise a range in Google Sheets without needing a plugin or Google App Script? To randomise a range, simply use the RAND() formula in a column to set the random numbers for each element in your range, and then with the INDEX() and RANK() formulas combined, produce the new random range. Here’s an example of what this looks like in a Google Sheet spreadsheet starting with the…
Read guide2 Ways To Create A Python List With Identical Elements: One-Liners
How can you create a list with identical elements in it with Python? There are two ways to create a list with identical elements in Python. One method is fairly straightforward and easy to remember, whereas the other involves the itertools library. The one I apply the most in my Python coding is the method that uses the asterisk operator to a list containing the item I want to replicate.…
Read guideFix NameError: Python List Is Undefined
How do you fix the NameError: Python list is undefined type errors? When you get this error in your Python code there are a few ways to fix the problem and in essence, the easy fix is to check you’re working on a list variable that has been initiated as a list . The three common causes I have found in my own Python code for why this error pops…
Read guidePython List Not Sorting: Troubleshooting With Examples
Why does a list not sort properly in Python as expected? There are a couple of reasons why a list may not have sorted as expected in Python. Here are a couple of ways to diagnose the problem to help you check. Here’s a quick list (no pun intended!) you can use to quickly check: Are all elements within the list of the same data type? Look at setting them…
Read guidePython Lists: Order Alphabetically & Sorting Examples
How do you sort a Python list alphabetically? There are two easy ways to sort a list in Python: using the standard sorted() function or using the .sort() list method. Assuming you have a list of strings you can apply either method to sort the list alphabetically. If you have a list of numbers and strings you can use the parameter key to change all values within the list to…
Read guideFind NetSuite Field IDs: Tips and Tricks
How do you find the field ID on a Netsuite record? Whether you’re using SuiteScript or Saved Searches you will at times need to know the Field ID to use in your code or formulas. To ensure you can find the field ID on a record check your Preferences first. On the General tab make sure the Show Internal ID’s checkbox is ticked : Check your Preferences to see if…
Read guideCan't Find altemail In Saved Search Filter
Why can’t you search for altemail in a Saved Search filter? Whether you’re coding using SuiteScript or just using the Saved Search feature in Netsuite you might find it difficult to use the altemail field in Netsuite, as it doesn’t exist as a search feature in the Filter section of Netsuite’s documentation. So how do you search for this email address for your Individual Customer records where this field is…
Read guideNetSuite's IF Formula In Saved Search
Where is the IF function in Netsuite’s saved search area? It can seem quite odd at first trying to find the standard IF function in Netsuite’s saved search area, but you are not losing your eyesight, there is no IF function in Netsuite’s formulas. Instead of using an IF function you have two other types of functions available: CASE statement DECODE function Let’s explore these in a little more detail…
Read guideNetSuite Saved Search Formulas: Getting Started
How do you use a formula in a Netsuite saved search? There are several different types of formulas you can use in a Netsuite saved search. They vary according to the result you are seeking to produce from numbers, text, dates and even HTML. You would use a formula when trying to calculate something that needs an operation or filter applied to a field that cannot be obtained from other…
Read guideHow to Pronounce NetSuite: Say “Net Sweet”
NetSuite is pronounced “net sweet”: net, followed by suite as in a hotel suite. Learn how to say Oracle NetSuite correctly.
Read guideList Comprehension In Reverse Order With Python
How do you perform a list comprehension in reverse using Python? A list comprehension is generally a short one line code that allows you to create a new list by iterating through an iterator (such as another list). The syntax for a list comprehension is [expression for variable in iterable if condition] with the if condition part being optional. The easiest way to perform a reverse iteration is to use…
Read guideHow To Remove Leading Zeroes With Python Dates
How do you remove leading zeroes in Python formatted dates? I have been sending data through to a Suitescript API endpoint and have received the error that the date information I am sending through needs to be of the format D/M/YYYY , here’s the error specifically: Currently, the way the date is formatted and sent from my Python code is as follows: The f-string enables me to construct the date…
Read guideConvert JSON To CSV In Python: Complete Code With Example
How do you convert JSON to CSV using Python quickly and easily? If you’re looking to store data received from an API that responds with JSON data and want to convert this to a CSV file then Python can easily help. Simply import the standard json and csv libraries into your Python code then read the json data into a variable and construct it to a one-dimensional format that can…
Read guideJSON.dumps Not Able To Serialize Object
If you have tried to serialize an object in Python into JSON you may have come across an oblique error that mentions you’re unable to serialize it, but what does this mean? I recently had an example whereby I needed to transfer a dict object through to an external API endpoint and I thought by simply using the json.dumps() function that I would be able to create a json object…
Read guideNot Null In Google Sheets
Test and filter non-empty Google Sheets cells using ISBLANK, LEN, FILTER, COUNTIF and QUERY is not null, with empty-string caveats.
Read guideHow To Use Google Sheets QUERY SELECT & WHERE With Examples
Are you looking to learn how to use the Google Sheets QUERY function to select and filter data based on specific conditions? In this blog post, you’ll explore the ins and outs of the powerful QUERY function, particularly focusing on SELECT and WHERE clauses, to help you get the most out of your Google Sheets experience. In the following sections, I’ll start with an introduction to Google Sheets and its…
Read guideWhat Are Sheets in Excel? A Clear Explanation for Beginners
As someone who has been using Excel for several years, I know how important it is to understand the basics of the program. One of the most fundamental elements of Excel is the sheet, which is essentially a grid of cells that can be filled with data. Sheets in Excel are used to organise and manipulate data in a variety of ways. Each sheet can contain a vast amount of…
Read guideHow to Sort Data In Excel: Single Column & Multi-Column
Sorting data in Excel is a fundamental skill that can help you better analyse and understand your data. Whether you are working with a small or large dataset, sorting can help you quickly identify patterns, trends, and outliers. Excel provides several options for sorting data, depending on your specific needs. You can sort data by one or more columns, in ascending or descending order, and even customise the sort order…
Read guideWhat Are Cells in Excel: A Simple Explanation
What is a cell in Excel? Once you dive into learning Excel it doesn’t take long before you discover a whole new world with its own language! And cells are a frequent term used, so what is it? Simply put, cells are the individual unit of an Excel grid spreadsheet. Each cell can contain a piece of data, such as a number, text, or formula, and can be formatted in…
Read guideAbsolute Cells in Excel: Explained With Images
A visual Excel-specific guide to locking cells with $A$1, mixed references and the F4 shortcut when copying formulas.
Read guideDifference Between HLOOKUP And VLOOKUP In Excel
What’s the difference between the HLOOKUP and VLOOKUP formulas in Excel? While both functions are used to look up and retrieve data from a table, they operate in slightly different ways. HLOOKUP stands for “Horizontal Lookup,” while VLOOKUP stands for “Vertical Lookup.” The main difference between the two is the direction in which they search for data. HLOOKUP searches horizontally (from left to right) across the first row in the…
Read guideExcel HLOOKUP Examples With Tips And Tricks
How do you use the HLOOKUP function in Excel properly and effectively? As someone who frequently uses Excel, I understand how important it is to master various functions and features to enhance productivity and effectiveness. One such function that can be incredibly useful when working with large data sets is the HLOOKUP function. In this article, I’ll share a few examples to demonstrate how this function can improve your data…
Read guideDownload Multiple Files On Web Page Using Requests And BeautifulSoup
Ever found yourself scrolling through a web page filled with multiple files, feeling overwhelmed with the tedious process of clicking on each one individually to download them? Wouldn’t it be great to automate this task, saving both time and effort? I had a similar requirement where I needed to download a lot of PDFs on a single web page, and clicking on each, waiting for the PDF to download, and…
Read guideWhy Is My VLOOKUP Remaining as a Formula? Troubleshooting
Are you struggling with your VLOOKUP formula staying as a formula instead of returning the desired value? If so, you are not alone, and the solution is relatively simple. One common reason VLOOKUP stays as a formula is due to the formatting of the cells. If the cells are formatted as text , Excel will treat the VLOOKUP formula as text and not perform the lookup. To fix this issue,…
Read guideHow to VLOOKUP Across Multiple Sheets in Excel: Examples Included
Do you have multiple sheets in Excel and need to find and extract data quickly? VLOOKUP is a powerful function in Excel that allows you to search for and retrieve data from a specific column in a table. However, when you have multiple sheets, using VLOOKUP can be a bit tricky. In this article, we will show you how to use VLOOKUP across multiple sheets in Excel with examples. When…
Read guideExcel SUMIF: How to Sum Cells Starting with a Letter
If you are an Excel user, you are likely familiar with the SUMIF function. However, did you know that you can use SUMIF to add up values based on whether a cell starts with a specific letter? This can be a useful tool for quickly calculating totals for a specific category or group. The SUMIF function in Excel allows you to add values in a range of cells that meet…
Read guideSum Only Positive Numbers in Excel: SUMIF & SUMIFS Examples
If you’re working with a large dataset in Excel, you might need to sum only the positive numbers. This can be a tricky task, especially if you have a lot of data to work with. Fortunately, Excel has a built-in function that makes it easy to sum only positive numbers. The SUMIF function in Excel allows you to sum only the cells that meet certain criteria. In this case, you…
Read guideExcel SUM Function Not Adding Correctly: Troubleshooting Tips
What do you do when you use the SUM function and it doesn’t add up the values correctly? If you are a regular user of Microsoft Excel, you may have encountered an issue where the sum function does not add up correctly. This can be a frustrating issue, especially when you are dealing with large amounts of data and need to ensure accuracy. The sum function is one of the…
Read guideHow to Change an Excel Formula: A Simple Guide for Beginners
If you’re working with Excel, you’ll likely find yourself needing to change formulas from time to time. Whether you’re fixing an error or updating a calculation, it’s important to know how to make changes without disrupting your spreadsheet. Changing an Excel formula is a simple process that can be done in just a few steps. First, you’ll need to select the cell containing the formula you want to change. From…
Read guideCalculate Duration In Years And Months: Excel
One of the most useful features of Excel is its ability to calculate durations in years and months. This can be particularly helpful when you need to work with data that involves time, such as project timelines or financial data. Calculating durations in years and months is a relatively simple process in Excel. By using the DATEDIF function, you can easily calculate the number of years, months, and days between…
Read guideHow To Build A Formula in Excel
If you work with data, you’ll likely need to use Excel’s formulas. Formulas are equations that perform calculations on values in your worksheet. They can help you automatically calculate values, manipulate data, and make your work more efficient. In this article, we’ll explore how to build formulas in Excel. Excel has hundreds of built-in functions that you can use to create formulas. These functions are organised into categories such as…
Read guideIF Formulas in Excel: A Beginner's Guide
Microsoft Excel is a powerful tool that is widely used for organising, analysing, and presenting data. One of the most useful features of Excel is the ability to create formulas that automate calculations based on the data in your spreadsheet. One of the most commonly used formulas in Excel is the IF formula, which allows you to perform different calculations based on whether a certain condition is met or not.…
Read guideMost Used Excel Formulas: Boost Your Productivity With These Essential Functions
Microsoft Excel is a powerful tool that is used by millions of people worldwide. It is a spreadsheet program that allows users to organise, manipulate, and analyse data. One of the most important features of Excel is its ability to perform calculations using formulas. Excel formulas are a set of instructions that tell Excel what to do with the data in a particular cell or range of cells. There are…
Read guideHow to Find Data in Excel: A Step-by-Step Guide
If you work with large amounts of data in Excel, you know how important it is to be able to quickly and easily find specific information. Luckily, Excel has a built-in Find feature that makes it easy to do just that. Whether you need to find a specific word or number or you need to locate a certain cell or range of cells, the Find feature can help. To use…
Read guideMicrosoft Excel Quit Unexpectedly: Troubleshooting Tips and Solutions
What happens when your favourite software, Microsoft Excel, crashes? Sometimes Excel may quit unexpectedly, which can be frustrating for users who rely on the program for their work. This issue can occur for various reasons, including software conflicts, corrupted files, or outdated versions of Excel. If Excel quits unexpectedly, it can result in loss of unsaved data, disruption of work, and wasted time. This issue can occur at any time,…
Read guideHow to Improve at Excel: Tips and Tricks for Mastering Spreadsheets
Microsoft Excel is a powerful tool that can be used for a variety of tasks, from simple calculations to complex data analysis. However, many people struggle to use Excel effectively, often because they lack the necessary skills and knowledge. Fortunately, there are several ways to improve your Excel skills and become more proficient in using this valuable tool. One of the most important things you can do to improve your…
Read guideWhen To Use $ In Excel: A Quick Guide
When it comes to using Excel, there are many different functions and formulas that can be used to make calculations and organise data. One of the most commonly used symbols in Excel is the dollar sign ( $ ), which is used to indicate absolute references . Absolute references are a way of locking a cell reference in place so that it doesn’t change when you copy or fill a…
Read guideWhy Does Excel Use # Characters in Cells?
If you’ve ever worked with Microsoft Excel, you may have noticed the “ ” character appearing in a cell. This can be frustrating, especially if you’re not sure what it means or how to fix it. Fortunately, understanding why this happens can help you avoid this issue in the future and work more efficiently with your data. The “ ” character typically appears in a cell when the data in…
Read guideWhy Learn Excel?
Excel is a powerful spreadsheet software that has been around for decades. It has become an essential tool for businesses, students, and individuals alike. One of the reasons for its popularity is the vast range of features it offers. However, some users may wonder why Excel has certain features that they may not use or understand. One reason for the various features in Excel is to make it a versatile…
Read guideCan You Download Python for Free? Where and How?
If you are interested in learning Python programming language, you might be wondering whether you can download it for free. The answer is yes , you can download Python for free. Python is an open-source programming language, which means that its source code is freely available and can be modified and distributed by anyone. Python can be downloaded for free from the official Python website, which provides installers for Windows,…
Read guideDownload Files Using Python Requests Library
If you’re looking to download a file using Python, the requests library is a great option to consider. This library allows you to easily make HTTP requests and handle responses in a Pythonic way. First, you’ll need to install the requests library if you haven’t already. You can do this using pip , the package installer for Python. Once you have requests installed, you can use it to make a…
Read guideCopy List Using [:] In Python
The empty slice operator [:] in Python is a powerful and concise way of copying a list. It is shorthand syntax allowing you to create a new list that contains all the elements of the existing list where the operator is used. This operator is represented by one colon wrapped in square brackets [:] with no values or spaces inside. While the empty slice operator may seem like a simple…
Read guideWhy Does list1 = list2 Not Create A Copy In Python
When starting out in Python it can be easy to think that the expression list2 = list1 will make list2 contain a copy of list1 . But it doesn’t take long to realise that this doesn’t meet expectations of what actually happens in the wild. Take the following code as an example: Huh? Why did list2 contain the same contents as list1 even when the insertion of additional elements in…
Read guideid() Function in Python: What’s It Good For?
The id() function in Python is a built-in function that returns the unique identity of an object. This identity is an integer that is guaranteed to be unique and constant for the object during its lifetime . The id() function can be used to determine whether two variables refer to the same object in memory . This is helpful when determining the type of copy you have with two variables:…
Read guideUpdate Record With Button When Viewing In Browser: User Event & Client Scripts
How do you add an actionable button on the client-side view of a record in Netsuite that would allow the user, once the button is clicked, to insert something into the current record and have the changes saved to the record? I had a recent requirement where a button was needed on the client side when viewing a record. The intent was that if the user needed to update the…
Read guideSort a Python List of Lists by the Second Element
Sort a Python list of lists by its second or any other element using sorted(), list.sort(), lambda expressions and itemgetter.
Read guideGoogle Sheets Named Functions In 5 Minutes
What is a named function in Google Sheets and when is it best to use this new feature in your spreadsheet? Google Sheets new Named Functions feature enables you to refactor long formulas into what appears as a native function in your Google Sheets spreadsheet. Use this feature in Google Sheets if you find you are using a complex formula more than once within your Google Sheets. A recent need…
Read guideGoogle Sheets SWITCH Formula Example: Refactor IF Functions By 20%
How do you use the Google Sheets SWITCH() formula? The SWITCH() formula in Google Sheets enables you to compress a series of IF statements, even nested IF statements, into one succinct function. Take a recent example where I refactored the following formula which helped to add the appropriate years, months or days to an existing date. Here was the original formula which would calculate the next date according to an…
Read guideHow To Set A Default Value For VLOOKUP
How can you set a default value when using the VLOOKUP function? As VLOOKUP throws an N/A error when the searched item cannot be found in the first column range, wrap the VLOOKUP function in an IFERROR formula and set the value to the default sought. For example like this: Where default value is the placeholder where you would the data inserted into your spreadsheet if the item is not…
Read guideSuiteScript Change On Credit Hold Field On Customer Record
How do you change the Hold field found on the customer record using SuiteScript? The Hold field in a Customer record prevents users or the customer from having any new Sales Orders being placed and has three settings On , Off and Auto . The code name of the field is creditholdoverride . To set the value for this field using SuiteScript, enter the value in all capital letters according…
Read guideHow To Create A Radio Button In Suitelet Form
How do you create a radio button in a Suitelet form? To create a series of radio button elements in a Suitelet form you need to add each element of the radio series as distinct fields using the .addField function. The id property needs to be the same for each radio field and the source property needs to be unique for each element. Here’s an example demonstrating what this would…
Read guideHow To Remove From String In Python
How do you remove a newline character \n from a string in Python? The easiest way to remove a new line from a string in Python is by using the string method .replace() . Another way is to use the string method .strip() if the new line is at the start or end of the string. Here are some examples demonstrating how to remove the new line characters: As you…
Read guideClose Python In Terminal: Command & Shortcut
How do you quit out of Python when you’re in an interactive shell environment? The easiest way to close the Python shell in Terminal is to issue the quit() command. Here’s an example demonstrating how to exit from a terminal window using the a-Shell app on the iPhone: Use the exit() command As you can see from the example above I was able to access the Python interactive shell by…
Read guidePython: Move Files From One Directory To Another
How do you move files from one directory to another using Python? If you want to move specific files from one directory to another with Python, use the glob library to fetch the correct files, then use the os library’s rename method to change the current file to the new directory. Here is an example of how this might look using a Python script. With the current code I have…
Read guide3 Different Ways To Underline In Google Sheets: Shortcuts, Borders & Formulas
How many ways can you underline in Google Sheets? There are three broad approaches when seeking to emphasise text in Google Sheets by applying underlines. These three approaches are: underlying specific or whole text in the cell, underlying the whole cell itself with different styles, or using underscore characters. Here is each approach with examples below: Underline Specific Text The most common approach to underlying text in a cell in…
Read guideCreate Unique ID With Dates: Google Sheets
How can you create a unique ID with date values in Google Sheets? To create a unique ID of date values in Google Sheets use the TEXT() function to change the date into a string and then append any other useful identifier to that string to make the value unique, such as ROW() or a counter such as COUNTIFS() . Creating a unique ID for your data rows can help…
Read guide5 Easy Ways To Fix Google Sheets When It Is Slow
How do you fix Google Sheets when it is running slow? The five ways to speed up working in Google Sheets when it is running slow are: filter your data when working on specific rows, convert formulas to values where possible, sort your data if you are working with functions like VLOOKUP , create a unique column ID to help find data quicker, and look at alternative functions instead of…
Read guideEffortlessly Increment Your File Names with Python (Free Code)
How do you create a file name with an incrementing number in Python? To create an incrementing file name in Python, run a while loop that checks the existence of a file, and if it exists, use Regex to modify the file name by replacing the integer at the end of the file with the next number. Continue looping until the file name does not exist in the destination folder.…
Read guideHow To Format Time In 24-Hours Using Python (Examples)
How do you format time to be in 24-hours using Python? In Python you can format a datetime object to be displayed in 24-hour time using the format %H:%M . This is not to be confused with the 12-hour time which uses the format %I:%M and may have %p appended to the end to denote AM/PM . Here’s an example demonstrating the conversion of time from 12-hour format to a…
Read guideXPath OR Condition: Syntax and Practical Examples
Use the XPath or operator to match either of multiple conditions. Includes attribute, text, parentheses, not(), and Selenium examples.
Read guideHow To Format HH:MM AM/PM Time To 24 Hours In Python (Examples)
How do you format a string in the format of HH:MM AM/PM to 24-hour time in Python? To change a string in the format HH:MM AM/PM to 24-hour time in Python use the datetime.datetime.strptime(original string, format string) function located in the datetime library. As the datetime class needs to be imported from the datetime library you need to make sure you include the reference twice, otherwise you may get strptime…
Read guideConvert DD/MM/YY To Date In Python (Examples)
How do you convert a string in the format of DD/MM/YY to a date in Python? To convert a string in the format of DD/MM/YY to a date in Python import the datetime library and use the datetime.datetime.strptime(date string, format string) function (yes, that is not a typo the datetime library has a datetime class and it contains the strptime function). To make it easier to access the strptime() function…
Read guide3 Ways To Wrap Text In Google Sheets
How can you wrap text in a cell in Google Sheets? There are 3 ways you can wrap text in a cell in Google Sheets. The most popular method is to click on the Google Sheets wrap icon, whereas the two other two methods are more manual and require you to enter the line break for the cell – of the two manual methods, one is a keyboard shortcut, and…
Read guide5 Ways To Alphabetize In Google Sheets (Screenshots)
How can you alphabetize or sort your data in Google Sheets? There are 5 ways to alphabetize data in Google Sheets: two approaches involve using formulas; namely, the SORT() and QUERY() functions, and the other three approaches involve using the menu items located in the menu bar. All approaches require knowing if the sorting will be done in ascending order, where your data starts from those cells closest to A…
Read guideHow To Find The Difference Between Two Or More Sets In Python
How can you find the difference between two sets in Python? In Python, you can find the difference between two sets using the .difference() method. The set and frozenset objects contain a built-in method labelled difference() which helps to find the non-matching elements in the source object not found in the other sets passed in to the .difference( others) parameter. Here is an example demonstrating how to find the difference…
Read guide3 Ways To Use Multiple Criteria In Google Sheets QUERY Function (Examples)
Can you use multiple criteria to filter data in Google Sheets using the QUERY function? Within the query parameter of the QUERY function the WHERE clause enables users to filter data based on multiple criteria. The three types of logical operators permitted when combining multiple criteria are AND , OR and NOT . Here are some examples demonstrating each of the logical operators, and to assist in demonstrating how these…
Read guideGoogle Sheets: 3 Ways To Reference Data In Another Sheet (Examples)
How do you reference data in another sheet using Google Sheets? If you need to reference data in the same Google Sheet, there are two means: the sheet reference syntax using the name of the sheet followed by an exclamation mark and the range (i.e. "Sheet2!A1" ), or the function called INDIRECT . If you need to reference data in an external Google Sheet, there is a function called IMPORTRANGE…
Read guideGoogle Sheets HLOOKUP Function With Examples
How do you use the HLOOKUP function in Google Sheets, and what are some best use cases? The HLOOKUP function searches for data in the first row of a range and returns a specific nth cell in the column found. HLOOKUP is an excellent function to use in data sets where the primary search needs to be performed on data contained in the first row. The HLOOKUP function contains three…
Read guideHow To Change Aggregate Name In Google Sheet QUERY Function (Examples)
How do you change the default aggregate name created in Google Sheets when using the QUERY() function? To change the header label of an aggregate column from a QUERY() function append LABEL aggregate column 'YOUR LABEL' to your SELECT statement. For example, if you had the following QUERY formula in your Google Sheet and you wanted to change the default label of sum(Sales Qty) to Total Sold then this is…
Read guideHow To Aggregate Data From Multiple Sheets With QUERY In Google Sheets
How can you aggregate data using multiple sheets with the QUERY function in Google Sheets? To aggregate data sourced from multiple sheets, create a data set using the set notation {} by referencing each sheet then within the query statement of the QUERY function reference columns using ColX (with X being the index number of the column, starting at 1). As the QUERY function contains three parameters, the first parameter…
Read guideConcatenate Multiple Ranges Into One And Filter Using QUERY: Google Sheets (Example)
How do you concatenate two ranges into one contiguous range for use in the QUERY function for the data parameter in Google Sheets? To concatenate two ranges into one for use as the first parameter in the QUERY function in Google Sheets, simply combine your data sets together using the set notation {} and the semi-colon character to separate each range ; e.g. {{Data!A:A, Data!B:B};{Data!A:A, Data!C:C}} . For example, suppose…
Read guideGoogle Sheets QUERY Function WHERE Reference To Cell Value: Examples
How do you reference a cell in the WHERE clause of a Google Sheets QUERY function? To reference a cell in the Google Sheets’ QUERY function WHERE clause, simply break the query string by closing with a double-quoted string " append the concatenation symbol & then reference the cell append the & to open up the query string again " so you can continue writing the rest of your query…
Read guidePython Ceiling Function: What Is It And How Does It Work?
What is the ceiling function in Python, and how does it work? The ceiling function in Python is found in the math library and it’s method name is ceil() which takes a sole numeric parameter. To use the ceiling function in Python you will need to import the math library, like so: As you can see from the above Python REPL code the output produces the greatest integer. If the…
Read guideConditional Formatting With Checkboxes In Google Sheets
How can you apply conditional formatting on checkbox cells in Google Sheets? Conditional formatting in Google Sheets can be applied to a range of checkboxes by applying the conditional formatting condition of Is equal to to TRUE . Here’s an example demonstrating how to set a conditional format on checkboxes. Create Checkbox Range To create a range of checkboxes, select your range and then click on the Data Validation menu…
Read guideHow To Highlight Duplicates In A Range: Google Sheets Conditional Formatting
How do you highlight duplicates in a defined range using conditional formatting in Google Sheets? To highlight cells that are the same value in a range, select the range and use a custom formula in the conditional formatting area that uses relative referencing. The custom formula you will want to insert into the conditional formatting area is: Where range is the same highlighted range of the conditional formatting range. Here’s…
Read guideDownload Images Using Selenium Python With Full Code Examples
How do you download images using Selenium Python? Selenium provides a way to create a screenshot of your browser’s view using the .save screenshot(file name) method, but this will take a photo of the viewport – what if you just want to download the image as it is ? Unfortunately, Selenium doesn’t have the capability of selecting menu items in your browser window, therefore you will need to install a…
Read guideREGEXEXTRACT Date From String In Google Sheets
How can you extract a date from a string in a cell in Google Sheets using the powerful REGEXEXTRACT() function? The REGEXEXTRACT(text, regular expression) function has two parameters with the first labelled as text being the string operated on and the second labelled as regular expression being the regular expression (using RE2 syntax) to extract data from. Here are a few popular examples of how you can use this powerful…
Read guideSelenium: Download PDF From URL To Specific Folder - No Printing Or Special Keys (Python)
How do you download a PDF file when the URL opens up a PDF in your Chrome browser in Python without needing to print the page or use special key presses? And how can you set the location of the PDF? The trick to be able to download a PDF file using Selenium without the Chrome browser opening the PDF file within the browser window is to set the preferences…
Read guideApply Conditional Formatting To Entire Row: Google Sheets
How can you highlight an entire row based on a single condition in another column? To highlight an entire row based on a value in a column using conditional formatting requires using the INDIRECT() formula. A spreadsheet contains the following simple data where the first column contains a list of dates and the other columns contain corresponding data for that date. Here’s a snapshot of the spreadsheet which contains Date…
Read guidePython: Sort List Of Tuples By First Element In 10 Seconds
How do you sort a list of tuples by the first element in each tuple in Python? To sort a list of tuples in Python use the .sort() list method if you want to modify the list to sort or the sorted() function if you want to generate a new list. Use the parameter key with either function and set the value of that parameter to a lambda expression that…
Read guideWhat Does AttributeError: Module datetime Has No Attribute strptime Mean?
What does AttributeError: module 'datetime' has no attribute 'strptime' mean and how can you easily fix it? When parsing a string and transforming that to a date you can make use of the function strptime in the datetime module. To use this function simply import datetime as you normally would with any module, and use the function as follows: Notice in the above code that the function strptime is found…
Read guideWhat Does $A$1 Mean in Excel or Google Sheets?
In Excel and Google Sheets, $A$1 is an absolute cell reference: both column A and row 1 remain fixed when a formula is copied.
Read guideWhat Does $A$1 Mean? Absolute References in Excel and Google Sheets
Understand what $A$1, $A1 and A$1 mean and how absolute and mixed references behave when formulas are copied in Excel and Google Sheets.
Read guideHow To Delete App Script File And Project From Google Sheets
How do delete an attached Google App Script file and project from a Google Sheet? I recently wanted to copy a Google Sheet and as I did I noticed it had an attached Google App Script file. Here’s what that dialog window displayed: Copying a Google Sheet will bring with it it’s App Scripts However, I didn’t want the adjoining scripts with the copy, in fact I wanted to delete…
Read guideConditional Formatting With Custom Formula Using Relative References
How can you apply conditional formatting using a custom formula that contains a relative reference to an adjacent row or column in Google Sheets? If you want to highlight a cell in Google Sheets using conditional formatting based on the condition of a nearby cell you can easily do so by using the Custom Formula feature along with the INDIRECT() formula that contains a relative reference. The INDIRECT(cell reference, is…
Read guideAdd Button To Record Using Client Script And User Event Script: SuiteScript
How can you add a button to a record in NetSuite that can be used to trigger a process regardless of whether the record is being viewed or edited ? Creating a button on a client script does have the problem where it can only be accessed when the record is in EDIT mode which doesn’t help when you want to trigger a process when the record is in VIEW…
Read guideGoogle Sheets Multi Select Dropdown List
Can a Google Sheets drop-down list in a cell allow you to do multiple select? Google Sheets doesn’t natively support the ability to select more than one item on a drop-down list in a cell, but there is a way where you can click an item in the drop-down list and have the clicked item populate the active cell. Here’s an example of what this would look like: Create Your…
Read guideNetSuite RESTlet Example (OAuth1): Send File Using Python
How do you send a file through to your NetSuite instance via a RESTlet using a Python Script? If you know how to send a POST request through to a RESTlet using Python then only minor modifications need to be made to enable the sending of a file. One reason why you may prefer sending a file for NetSuite process is where you have a lot of data to consume…
Read guideNetSuite RESTlet Example (OAuth1): Python Script To RESTlet
How do you send data to your NetSuite RESTlet using Python? To send data to your NetSuite RESTlet using Python code utilise the handy requests oauthlib library. If you need to upload a series of CSV files and want to perform some data cleaning before being uploaded and want to avoid the Import CSV process then you can create a simple RESTlet and insert your clean data directly. Create Your…
Read guideHow To Remove Trailing Slash In Python
How do you remove a trailing slash in a string using Python? There are two popular ways to remove a trailing slash from a string in Python. The most common approach is to use the .rstrip() string method which will remove all consecutive trailing slashes at the end of a string. If you only need to remove one trailing slash the string slice operator can be another alternative. Here’s how…
Read guideHow To Make An Email Address Clickable In Excel
How do you make an email address in an Excel spreadsheet clickable? To make an email address clickable in a spreadsheet check the format of the cell is not set to text, if not when entering an email address Excel should automatically detect that it is an email address and add the mailto: hyperlink automatically. If after checking the cell is not set to Text and even after entering a…
Read guidePython Int Function: How It Works And Why You May Need To Create Your Own
How does the int() function work I. Python and could you write your own function? The int(x, base=10) function in Python takes two parameters: the first parameter x being either a number or string and the second representing the base number to return ( 10 being default which represents the decimal number system) and converts x into a whole integer number. A simple example of converting a string is demonstrated…
Read guideHow To Read File Into List With 1 Line Of Code In Python
How do read the contents of a file in Python and insert these lines into a list? Using the built-in function open() and the asterisk operator a file’s contents can easily be translated into a list with the following one-liner: [ open('my file.txt')] . What Does open() Function Do? The built-in open() function has one required parameter and several optional parameters. The required parameter is the location of the file.…
Read guideHow To Prepend List With A New Value Or Contents Of Another List In Python
How do you prepend a value or item to a list? Or how do you prepend the contents of a list to another list using Python? There are two ways to prepend items to an existing list in Python: for single items use the list method .insert(idx, value) – this method will mutate the original list, or use the list concatenation operator new list + old list which can be…
Read guideChange List Elements To Int In Python: 1 Line Of Code
How do you convert a list of strings to a list of integers in Python? And can you do it with one line of code? To convert a list of strings to a list of integers use the built-in map() function if you know the contents of the original list will all convert to integers, otherwise use a lambda function in your map() , or use a list comprehension with…
Read guideRange And Len Functions Together In Python
How do you find the length of a range object in Python? In Python the built-in function len() can provide the length of a string or list, can the same function be used to count the length of a range object too? What Is A Range Object? A range object is one of the three basic sequence types in Python alongside tuples and lists. The built-in function range(start, stop[, step])…
Read guideConvert Paragraph To List Item In Google Docs Using Google App Script
How do you change a paragraph into a list item in Google Docs using Google App Script? To convert a paragraph into a list item using Google Docs identify first the paragraph you want to modify, then obtain the childIndex of where the paragraph is and finally insert the list item using insertListItem() function. Here’s how this process works going through step-by-step: Locate Paragraph To Change In your Google App…
Read guide5 String Operators In Python Everyone Needs To Know
What are the most common string operators used in Python and why they’re essential to know? It doesn’t take long when you begin programming in Python to work with strings and to start modifying these strings by using common operators. Here I’ll look at 5 of the most common string operators I use in my own Python code and how you can use them in your own code. String Concatenation…
Read guideHow To Remove File Extension From Path String In Python: One-Liner
How do you remove the file extension from a path in Python? And can you do it using just one line of code? The file extension is generally the last set of characters after the final period in a path string. Removing the file extension helps with trying to either rename the file name or with renaming the file extension. For example, if my full path string to a particular…
Read guideWhat Does [:-1] In Python Mean And Why Use It?
What does [:-1] in Python actually do and why would you want to use it? [:-1] in Python is a slice operation used on strings or lists and captures all contents of the string or list except for the last character or element . Here are some examples demonstrating the operation of this code with strings: As you can see from the simple code example above a variable my string…
Read guideSet Conditional Format Based On Another Cell Value In Google Sheets
How can you create a conditional format in Google Sheets based on the value from another cell or column regardless of whether that cell is on the same sheet or another? To reference any cell or column in the Custom Formula field in Google Sheets’ conditional formatting section use the INDIRECT() function referencing that cell or adjacent column. Recently I had a simple requirement where I wanted to highlight a…
Read guide** In Python In 2 Minutes With Code Examples
What operator is used to raise a number to a power in Python? In Python the double asterisk operator is used to help calculate the exponent of a number raised to a power. This is done without a need to import the Python math library. For example, 2 to the power of 3 can be expressed using the double asterisk operator 2 3 , here the resulting output with the…
Read guideWhat Does Asterisk Before Variable Mean In Python?
Learn what a single asterisk before a Python variable means in unpacking, function calls, starred assignment and *args parameters.
Read guideInsert Line Breaks In HTML Code
How do you insert a line break into your HTML code? To insert a line break in HTML use the line break tag . The line break tag is self-closing and doesn’t have a corresponding closing tag, like the paragraph tag does: . For example, to include a line break tag in your HTML code insert it where needed, like so: As you can see line breaks are popular tags…
Read guideWhat Does Colon Equals Mean In Python? The New Walrus Operator :=
In Python 3.8 a new assignment expression hit allowing for easier access of variables that are used when being assigned. Here are some tips on how this can be used in your everyday coding. Wait, what’s an assignment expression ? You may have heard of the term assignment operator and seen its use in the wild when doing things like incrementing a variable by 1 in Python. This is where…
Read guideWe're sorry, a server error occurred while reading from storage. Error code NOT_FOUND
What do you do when you’re running a Google App Script and you encounter the following error in the Execution Log area? Check the libraries you have attached to your project on the left-hand side and see if they are correctly referencing the versions you need from them to operate your code properly. Otherwise, if everything is working well there then go to your IDE and check the appscript.json file.…
Read guideHow To Zip Two Or Multiple Lists Together In Python
How do you zip two lists together in Python? What if you want to zip multiple lists together, is it the same process or something different? The built-in function zip() allows users to combine iterables, such as a list, into tuples by taking each corresponding item from the lists passed into its parameters merging the lists into one. It does not matter whether you have two or multiple lists the…
Read guidePython Initialize Dictionary: Use Dict Constructor Or Dict Literal?
How do you initialize an empty dictionary in Python? Is it best to use dict() or {} ? There are two common ways of initializing a dictionary in Python. One involves using the built-in function dict() and is known as the dict constructor , whereas the other method is known as dict literal and has the syntax {} . Here’s an example demonstrating how to initialize using both approaches in…
Read guideGet the First or Last N Characters of a Python String
Get the first or last N characters of a Python string with text[:n] and text[-n:], including zero, oversized and combined slices.
Read guideHow To Clean And Format Phone Numbers In Google Sheets
If you’re working with data in Google Sheets you’ll soon come across a time when you will need to clean and format phone number data entries. To clean phone numbers in Google Sheets using the REGEXEXTRACT() function extract all the different fields according to the phone number entries and then combine them all into the desired format. Here’s an example walking step by step through the process of cleaning the…
Read guideHow To Increment By 2 Or More In For Loop In Python [Examples]
How do you increment through a for loop by 2 or more using Python? To iterate by 2 or more when processing a for loop expression use the third parameter of the range(start, stop, step) built-in function, or if using the slice operator use the third parameter. Here is an example demonstrating how to iterate through a for-loop in Python using the range() function. As you can see from the…
Read guideCheck Phone Numbers Using Regex In Python [Examples]
How can you use the Python Regex library to check if a string represents a phone number? To check if a string matches a specific pattern use the Regex library’s match or exec methods. Before writing your Regex pattern inspect the variants for the phone number field to see whether you’re Regex pattern will match. For example, if upon your inspection you find the following variants: By noting all the…
Read guideHow To Stop A Map/Reduce SuiteScript When It Is Processing
How do you stop a long running map/reduce script in SuiteScript? There are two ways to stop the running of a map/reduce script: cancel the script in the Map/Reduce Script Status area, or make the running Map/Reduce script inactive . Here’s how each method works. Cancel Map/Reduce Script You might be able to stop a map/reduce script running in SuiteScript if there are other scripts running currently and your newly…
Read guide5 Alternatives To VLOOKUP Function: How To Get Values To The Left Of VLOOKUP Range
How do you get the value from a column that is to the left of a VLOOKUP range? The VLOOKUP function is a powerful function that enables you to capture the value from a range, provided the value is to the right of the lookup range. For example, suppose you have the following data in your spreadsheet that contains the employee ID in column A, the name of the employee…
Read guideConvert Percentage String To Decimal Like 30% To 0.3 In Python
How do you convert a string representing a percentage number into an actual number that can be used to perform calculations? To convert a string represented as a percentage number into an actual decimal number in Python use the built-in float() function after removing the percent symbol from the string and any other characters that prevent numeric conversion. Without purging from the original string the percent symbol you will get…
Read guideHow to Check If a Python String Can Convert to an Integer
Check whether a Python string is a valid integer using int(), try and except, or string methods, including signs and surrounding whitespace.
Read guideHow To Format Text Like 1st Jan 22 To A Date In Google Sheets
How do you format a cell that contains text like 1st Jan 2022 into a cell that Google Sheets can recognize as a date cell? There are certain types of date formats that can prevent the automatic import of data into Google Sheets into dates. One recent data type I had to deal with was where the day field was an abbreviated ordinal number (i.e. 1st, 2nd, 3rd, 4th… etc)…
Read guideHow To Capitalize First Letter Of Every Word In Python (One-Liner)
How do you capitalize the first letter of each word in a string using Python? To capitalize the first letter of every word in Python using one line of code enter the following: " ".join([x.capitalize() for x in my string.split()]) . Here is an example demonstrating how the code works in the Python REPL: As you can see from the above example the output produces a string with each character…
Read guideHow To Find The Length Of A String In Python Using Len
How do you find the length of the string in Python without importing any libraries? To find the length of a string in Python use the built-in len() function, which takes one parameter that can be of data type string, bytes, tuple, list, range, or a collection (such as a dictionary, set, or frozen set). The len() function, when used on a string, will return the number of characters in…
Read guideDoes Python Have Ternary Operator?
Does Python have a ternary operator? If so, how does it work? A ternary operator, otherwise known as a conditional expression, is a simple if-else statement that is performed on one line of code to evaluate a condition. The value entered before the if clause is the True value returned, whereas the value after the else clause is the False value returned. The expression between the if and the else…
Read guideWhat Is The Difference Between AND And OR In Python?
What is the difference between using the logical operators and and or in Python? When using the and logical operator in Python all conditions in the statement need to be True for the entire expression to evaluate to True . If one condition evaluates to False then the entire expression evaluates to False . When using the or logical operator only one condition in the entire expression needs to evaluate…
Read guideWhat Does := Do in Python? The Walrus Operator
The Python walrus operator := assigns a value and returns it in one expression. Learn where it works, where it is illegal, and common patterns.
Read guideHow To Split A String In Half In Python: 1 Line Of Code
How do you split a string into two equal halves using Python? To tackle this exercise you need to know how you’re going to operate on odd numbered string lengths. For example, how would you like your code to operate on the string halve . As it has 5 characters you’re going to have 5 possible solutions: hal and ve OR ha and lve ha, l and ve halve OR…
Read guideHow To Sort By Date In Google Sheets And What To Do When It Doesn't Work
How do you sort a range containing dates in Google Sheets? Sorting a data set in columns or rows containing date data in Google Sheets can easily be done by highlighting the range and then clicking on Data > Sort Range > Advanced range sorting options , as shown below: To sort a range highlight first, then click Data, then Sort Range, then Advanced range sorting options If the Sort…
Read guideWhat Does Double Slash Operation // Do In Python?
What is the double slash operator and what does the double slash operator // do in Python? The double slash operator in Python returns the quotient value from a division operation. The operator requires two numbers: a dividend and a divisor, which are the same numbers used with standard division operations in mathematics. For example, the mathematical expression 75 ÷ 10 has the 75 as the dividend, 10 as the…
Read guideSquare A Number In Python Without Importing Math Library
How do you square a number in Python, and how do you check if a number is a square using Python? To square a number in Python use the power operator followed by the number 2. For example, to square the number 9 simply write 9 2 . Here’s an example demonstrating the use of this operator when squaring a number: Wait! I thought squaring a negative number produces a…
Read guideSquare Root In Python Without Importing Math Library
How do you find the square root of a number in Python? Can you find the square root without needing to import the math library? The most common approach in calculating the square root of a number in Python is to import the math library and use the method math.sqrt(n) where n is the number you want to square root. But there is an even easier way of being able…
Read guideFind Duplicates In List: Python One Liner
How do you find all the duplicates from a list in Python? To find all the duplicate elements from a list using Python, use the following list comprehension: The result from the above code will be a list of unique elements representing all the duplicate elements from the original list. Here’s an example of how this looks in your Python console: Get List Of Duplicates What if you want a…
Read guidePython Code To Remove Duplicates From List - 1 Liner
How do you remove duplicates from a list using Python? To remove duplicate elements from a list in Python use a list comprehension to create a new list with no duplicates with this code: [x for idx, x in enumerate(original list) if x not in original list[idx+1:]] Here is an example demonstrating how this code works: As you can see from the example above the result from the list comprehension…
Read guideHow to Add a Blank Line or Empty Row in HTML
Add vertical space in HTML with CSS margin or padding. Use br only for meaningful line breaks, and spacer rows with colspan for tables.
Read guidePython ++ How To Increment A Variable (Code Examples)
How do you increment an integer variable in Python? Many other languages use the double plus sign operator on the variable to increment by 1, but what is Python’s plus plus operator equivalent? Python does not yet (as of version 3.9) have the ++ operator. Instead to increment an integer variable in Python by 1 use the operator assignment syntax i += 1. Is There A ++ In Python? As…
Read guideHow To Check If A Number In Python Is Even Or Odd
How can you tell if a number is odd or even in Python? The easiest way to determine if a number is even or odd in Python is to use the modulus operator . This operator, denoted as the percentage sign % displays the remainder from a division operation. To determine if a number is even simply apply my number % 2 == 0 where my number is your number…
Read guideHow To Find Most Frequent Element In List In Python (Code Examples, No Import Statements)
How do you find the most common element in a list using Python without importing any special libraries? To find the most frequent elements in a list iterate through the list and store the frequency of each element into a dictionary with the key representing the element and the value representing the frequency. Finally, sort the dictionary by value in descending order to see the results with the highest frequency…
Read guideHow To Use The SWITCH Function In Google Sheets?
The SWITCH() function in Google Sheets is handy when dealing with multiple criteria based on a single result. The SWITCH() function takes at least 3 parameters, with the first parameter being the value to evaluate, the second and third parameter representing a pair of combined cases and values. There’s also an optional final parameter that acts as the default result if no cases are satisfied. Here is a simple demonstration…
Read guideHow To Quickly Check Python Version In PyCharm
How can you check the version of Python you are using in PyCharm? There are three ways to check the version of your Python interpreter being used in PyCharm: 1. check in the Settings section; 2. open a terminal prompt in your PyCharm project; 3. open the Python Console window in your Python project. Let’s look at each of these in a little more detail: How To Check Python Version…
Read guideWhat Does [:] Mean In Python? Code Examples
The slice operator enables you to capture a subset of data from an original list or string using the format [start:stop:step] . Some popular use cases where I have used the slice operator include: Extracting year and/or month and/or day of the month from a string Extracting the zip code at the tail end of an address string Masking bank account or credit card numbers Extracting area code from phone…
Read guideHow To Print A String And A Variable In Python (3 Techniques)
There are times when you want to be able to print the contents of a variable along with a string to help provide some context around what has been output to the console. So how can you print both a string and a variable in Python? The three methods of being able to print a string and a variable together in Python is by using: string concatenation (using + ),…
Read guidePython Lower: Examples
How do you get a string to be all lowercase in Python? Thankfully there’s a built-in method that doesn’t require importing any external libraries to get a string to lower case, here’s how you do it. To get a string to all lowercase, use the built-in string method .lower() , which turns all characters in the string variable to lower case. Here is an example demonstrating the transformation of a…
Read guidePython Inline For Loop With If (List Comprehension Examples)
Python inline for loop with if: write a list comprehension, put if after for to filter, or if/else before for to transform elements.
Read guideHow To Get First Character From String (Shortest Code And Examples)
Is there a quick and easy way to get the first character from a string in Python? Yes, there is using the slice operator. To get the first character in a string simply use the code my string[0] where my\ string is a variable containing the string and the slice operator [0] captures the first index of that string, being the first character. Index numbers start at 0 from the…
Read guideHow To Get Last Character From A String (Shortest Code Examples)
How do you get the last character from a string using Python? Thankfully Python has an easy way to fetch the last character from a string using the slice operator . To get the last character of the string in Python use the syntax my string[-1] . The square brackets are used to indicate a specific character by index number, and as string data types can be iterated, you can…
Read guideCreate a List of Zeros in Python
Create a list of zeros in Python with [0] * n, and avoid the shared-reference trap when multiplying lists of mutable objects.
Read guideHow To Remove First & Last Characters From String In Python (Code Examples)
How can you remove the first and last characters from a string easily in Python? Can it be done using just one line of code? To remove the first and last characters from a string in Python use the slice operator on the string to return the needed characters, for example, my string[1:-1] . Here is an example demonstrating the technique: Slice Notation: How Does It Work? The powerful slice…
Read guideHow To Check If Element In List Is Empty In Python
Check one item or an entire Python list for None, empty strings and other empty values using is, any() and clear generator expressions.
Read guideHow To Add An Empty Element To A List In Python
To insert or append an empty element into a Python list you need to first define what is meant by the word empty . By empty do you mean an empty string, None or something else? While I may use None in this post you can easily substitute it for something else according to your requirements. To add an empty element into a list either replace the existing element with…
Read guideHow To Check If List Is Empty In Python
Similar to the previous post on how to check if a string is empty the same principles and methods apply when checking if a list is empty in Python. To check if a list is empty either use the direct approach by using an expression where a list is compared to an empty list or use the boolean expression with a not operator (i.e. if not my empty list: )…
Read guideCheck Empty String Python - 3 Unique Approaches
How can you check if a string is empty in Python? To determine if a string is empty in Python use of the following three means available: see if the string has any length by using the built-in len() function, do a direct comparison using the expression == "" or convert the string to a boolean data type and if the result is false it is an empty string. Let’s…
Read guideHow To Change A String To Lower Case In Python
A way to easily sort a list of strings equally is to change all the strings to lower case, but how do you change strings to lower case in Python? To change a string to lower case in Python use the string method .lower() like this: my string.lower() . Similarly, to change a string to upper case in Python use the string method .upper() like this: my string.upper() . An…
Read guideSort Dict By Value Or Sort Dict By Key In Python: 1 Liners
It can be easy to sort a list, whether a list of strings or even a list of dictionaries, but can you sort a dictionary? One way to sort a dictionary by the values of each key : value pair in Python is to use a dictionary comprehension . Dictionary comprehensions are very similar to list comprehensions and have the following schema: The first expression within the dictionary comprehension, the…
Read guidePython ord() Function (Code Examples)
What does the ord() function do? The built-in ord() function in Python converts any Unicode character into a number. It only takes one parameter which must be a single Unicode character, any more than one character produces an error. An example of the type of error you will get when sending more than 1 Unicode character is demonstrated here: Notice how Python informs us of what is expected – a…
Read guideSplit A String Into List Of Characters In Python [Code Examples, One-Liner, No Imports]
A common requirement in Python is to split a string into the characters which make up the string. I’ve previously shown how you can do this by breaking up a word into separate cells using a spreadsheet, but how do you do this in Python? As a string is a data type that can be iterated it means each unit element within the string, being a character, can be referencing…
Read guideList Length In Python: Using len() Function
To find the length of a list use the built-in len() function which takes a single parameter that is iterable. For example this could be a string, list, tuple or dictionary and here is how it would look: This may be the easiest and quickest way of being able to find the length of a list and other data types, but what if you want to exclude certain items from…
Read guideHow To Use NOT Operator In IF Statement In Python [Code Examples]
One way to determine if one value does not equal another is to use the != comparator, but what if you’re not comparing two values – how can you determine if something is not true? Python has an inbuilt operator aptly called not which permits a user to check a variable, or a function’s result to test if the variable or returned value is not valid. Some classic use cases…
Read guideEnumerate Dictionary In Python: Video Demonstration
Previously I looked at how to use the enumerate() built-in function with a for loop to provide two variables when iterating through a list. In that article the enumerate() function provides both an index number and the element in each list by wrapping these together into a tuple. But what happens when the enumerate() function is applied to a dictionary? As done previously let’s inspect the output of the enumerated…
Read guideHow To Use 2 Variables In For Loop In Python (Code Examples)
The for loop in Python allows the user to iterate through a variable that is iterable , such as a list. In Python the syntax for the loop is simply: for x in my list: and you’re off looping through each element in my list . But how do you access the index number of the element? In other programming languages, such as JavaScript, looping involves just the index number:…
Read guideHow to Print a Tab Character in Python
Print a tab character in Python with the \t escape sequence, align multiple values, show a literal \t, and use tabs safely in f-strings.
Read guideHow To Print A List In Python (And With Custom Formatting)
When you want to print the contents of a list you have a couple of different options depending upon the context. If you’re in the REPL there’s the easy method of entering the name of the variable storing the list, to the option of using the standard print function. If you’re within a script file the best option would be to use the print function, or if you want to…
Read guideAdd Formula In Excel: Examples
What is the formula to add in Excel? There are two popular ways to add numbers together in Excel: using the plus sign + or using the SUM formula. Use the + sign where values being added are manually entered, and use SUM when referencing specific cell values. Here are some examples demonstrating the use of each type: When To Use + (Plus) Sign In a spreadsheet the plus sign…
Read guideHow To Separate Characters Into Cells In Excel
If a cell contains words it can be easy to split these into individual cells using the Text to columns feature in Excel. Simply select the cells you want to split into multiple columns, navigate to the Data menu then click on the Text to Columns button. From this Text Wizard window select Delimited width (click Next ), then set the delimiter type to Space (click Next ) then click…
Read guideHow Do You Type A + (Plus) In Excel?
When you start entering text into a cell and the first character of that cell is a plus symbol ( + ) you will get an error NAME? and you would have noticed the cell changed to =+A . So how can you just display a cell with a plus sign? A --- --- 1 \ NAME? =+A {.spreadsheet} Result after starting cell with + To display a cell that…
Read guideHow To Get Rid Of E+(Number) In Excel
When using large numbers in Excel or any other spreadsheet application, such as Google Sheets, some cells may display a number in scientific notation like 9.991E+35 . How do you get rid of that E+n bit, where n is some number, in the cell? A --- --- 1 9.997E+11 =9999^3 {.spreadsheet} Large number in cell The easiest way to change a number being displayed as E+n (where n is a…
Read guidePython Increment By 1
How do you increase a variable by one in Python? In other popular programming languages to increment a variable by one you can use the simple increment syntax of ++ , for example i++ will make the value of i increase by 1 , but how do you do this in Python? To increment a variable in Python use the syntax += 1 , for example to increment the variable…
Read guideTranspose A List Of Lists: Python Examples, No Imports
When using a spreadsheet it can be as easy as using the TRANSPOSE function on a range to transform the rows and columns into columns and rows, but how do you do that in Python? To transpose a list of lists in Python first flatten the two-dimensional list into a one-dimensional list then use the following: [flat list[e::len(list 2d[0])] for e in range(len(list 2d[0]))] where flat list represents the flattened…
Read guideHow To Create A List With Values: Python Code (Examples, One-Liners, No Imports)
You can construct a list in a certain number of ways as detailed in the Python documentation. The most popular I use the most is by defining my list variables using the [] list square bracket notation, for example as my list = [] . This works well when you want to start a list empty, but what if you want to be able to start with a list so…
Read guideRemove Multiple Items From List By Index: Python (Examples, No Imports, One-Liners)
To remove an individual item from a list in Python you can use either a list method or the del statement, both approaches mutate the original list – meaning the list being operated on changes according to the instruction. To remove multiple items from a list you can use the del statement with the slice operator, or you can use a list comprehension with an if condition to filter the…
Read guideWhy Does VLOOKUP Return #N/A When Value Exists? (Examples)
The VLOOKUP formula is a popular function for getting the value from a tabular data set and has 3 required parameters and an optional fourth, and looks something like this: The first parameter reference value is the value you are searching for in the first column of your data set (the second parameter). The third parameter returning column value is the column from your data set that you wish to…
Read guideSort List By String Length: Python Code Examples (No Imports)
To sort a list of strings according to their length, use a lambda function on the key parameter with the lambda function using the len function on each element. This is represented as follows using the sorted function: sorted(my list, key=lambda el: len(el)) . Here is a simple example demonstrating the use of this code: Or, if using the .sort() list method would be represented as follows: What if after…
Read guideHow To Use Noncontiguous Ranges In QUERY Function: Google Sheets
Can you use noncontiguous ranges in the data parameter in a QUERY() function? Yes, you can. Google Sheet’s QUERY() function permits the ability to use noncontiguous ranges, but they must be wrapped in set notation with curly braces {} . When applying a filter in the query parameter, you will need to use the ColX notation to reference the specific range according to its order in the set. Recall that…
Read guideHow To Unmerge Multiple Cells With One Click: Google Sheets
Merged cells are a great way to span content over multiple cells, and you can easily remove a single merged cell with one click by clicking on the merge cell button, but unfortunately, you cannot apply the same process when trying to un merge a whole array of rows or columns that contain cells with multiple merges. For example, have a look at the following spreadsheet which contains a multiplicity…
Read guideHow Do You Put Multiple Conditions In One Cell In Google Sheets?
There will come a time when using Google Sheets where you will be checking the value of a cell against multiple criteria, which one is the best to use? There are three handy functions you can use within a single cell to handle multiple conditions: IF , IFS and SWITCH . Let’s examine each formula individually and how they might fit your needed case. IF Formula The IF formula is…
Read guideReverse Last Name, First Name With Comma Using One Formula: Google Sheets
Recently I had a column of names in a spreadsheet with the following structure: Last Name, First Name and they needed to change to the structure: First Name Last Name . For example, the original structure of someone’s name would be Smith, John and this needed to change to John Smith . So a couple of medications were needed. First, fetch the respective names and identify them positionally according to…
Read guideOmniGraffle API: Get Text Data From Shapes
I recently had an exercise where I needed to extract all the text from an OmniGraffle document. The task at hand was to check a beautifully designed organisation chart against a spreadsheet list of staff. Doing this task manually was going to take far too long. There had to be a better way of extracting the information I needed from the document: and thankfully there was! OmniGraffle have a new…
Read guidePython: Empty String To INT
To convert a string to an integer you use the handy int() function. For example, if you have a string such as "79" and you insert this like so into the int() function: int("79") you get the result 79 . But what happens when you don’t have a string that easily converts into a number? Here are some examples where converting a string into an integer type simply will not…
Read guideGoogle Sheets: IF Statement Multiple Conditions
One of the first formulas I started with when exploring more about the functionality of spreadsheets is the IF formula. The IF formula is easy to understand and contains only three parameters which are all required. The first parameter is the condition to check, the second is the returned value if the condition is true, and the third parameter is the returned value if the condition evaluates to false. Let’s…
Read guideHow To Check If Cell Is Empty (Or Not): Google Sheets
How do you check if a cell is empty or blank in Google Sheets? There is a handy function called ISBLANK which enables you to check if a cell is empty. What Does Empty Really Mean? In Google Sheets there are two ways of having an empty cell, one way is by defining an empty string "" and another way is by having nothing in that cell. To check that…
Read guideWhat Is The Formula To Calculate Age?
Recently, I had an issue where I needed to calculate the age of a person at specific dates throughout the year. Using a Google Sheet, I thought I could simply subtract one date from the other and divide by 365, but this ended up not being as accurate as I wanted. To calculate somebody’s age at a specific point in time you need their birth date and a comparison date…
Read guideHow To Flatten A List Of Lists In Python (Examples & No Imports)
Recently, I had an exercise where I needed to flatten a two-dimensional list down to just one dimension, something where I needed the result to be like this: There were a couple of ways I completed this task, one involved using the common for-loop process but as a one-liner, another involved using a standard Python function, and the third way introduced me to the concept of recursion in Python .…
Read guideIs A List A Data Type?
In Python there is a handy function to determine the data type of any variable, and it is aptly called type() . This function can help to assess whether a variable is of a certain data type to help you perform any type of computation on it. The different data types available in Python are: int , float , str , dict , list , and tuple . Therefore, list…
Read guideGoogle Sheets Count If Not Blank: Best Approach
How do you count a range of cells and exclude counting empty or blank ones in Google Sheets? The easiest approach to count cells that are not blank is to use the COUNTA() function. The COUNTA() function has the following parameters: The COUNTA() Google Sheets function takes one or more values and counts those values that do NOT have any content. However, just because a cell may look like it…
Read guidePython For Loop One Liner With IF Conditions [Code Examples]
There have been times when I wanted to perform a simple for-loop filter operation on a list, and I’ve often wondered if there’s a quick and simple way to do this without having to import any libraries. What I discovered is that there was an easy way, and what’s awesome about it is that it can be done in one simple line! If you’ve been operating with dictionaries or lists,…
Read guidePython: Get Average Of Two Dimensional List - No Imports [Code Examples]
In my previous article where I found the average of a list in Python without using imports, in this article I want to expand how you could apply the same concept, but to two-dimensional lists. Thankfully, if you know how to find the average of a one dimensional list in Python, you can simply expand this knowledge to finding the average of a two-dimensional list. The big question to ask,…
Read guideHow To Sort 2D Array In Python: 1 Line Of Code Without Numpy
How do you sort a two-dimensional array in Python easily without importing libraries? Thankfully, some native functions in Python make sorting arrays a breeze. I recently had a project where I had the following data structures, representing each unpaid invoice by the customer, the days they were overdue and how much was outstanding. Here’s a simplified sample of the data I was working with (the first row contains the name…
Read guidePython: Average Of List Of Numbers - Without Imports [One-Liner]
How do you calculate the simple average of a list of numbers in Python, without needing to import any libraries? Unfortunately, there’s no native average or avg function in Python to easily calculate the average of a list containing just numbers: To calculate the average of a list of numbers in Python, use the sum function and the len function and divide the total sum by the total length of…
Read guideCheck For Word In String: Python 1 Liner
How can you check if a word is found in a string using Python? Recently, I had an issue where I wanted to exclude strings from a list if they contained a certain word. I thought I could use the following common code familiar to most using Python: But the problem ended up being a little more difficult than that. For example, what if you want to exclude the term…
Read guideSort List Of Dictionaries By Key: Python 1 Liner
How can you sort a list of dictionaries by a key contained in each dictionary using Python? Can you do it in 1 line to show off your friends? Of course! To sort a list of dictionaries use the sorted() function with the key parameter set to a lambda function that uses the common key found in each dictionary that you want to sort the list by. Previously I posted…
Read guidePython: Sort List From Second (or Nth Index) On
How do you sort a list by the second (or nth) element on in Python? How can you leave the first or nth elements in a list as they are but then have the remaining elements in the list sorted? When applying the .sort() list method on a list Python will sort all elements in that list, but what if you only wanted to sort after a certain index number?…
Read guideReverse Order Of List In Python Without For Loops (Examples, One-Liners, No Imports)
There have been times when after creating a list in Python I’ve wanted to sort a list and then have the list reversed from ascending to descending. To reverse the order of a list in Python use either the .reverse() list method which mutates the original list, or the slice operator technique [::-1] which returns a new list. What are the quickest ways to get this task done? Here are…
Read guideHow To Use FORMULA In Filter Expression - SuiteScript 2.0
I had recently had to use a FORMULA type in a SuiteScript 2.0 search and found it as a very useful filter for list values. To use FORMULA types in a filter expression, such as, FORMULATEXT , FORMULAPERCENT , FORMULANUMERIC , FORMULACURRENCY or FORMULADATETIME just prefix the type of FORMULA at the beginning of your filter expression and then write your formula. Use the operator and value as you normally…
Read guideHow Many Spaces Is a Tab? 2, 4 or 8 Explained
How many spaces is a tab? Usually 2 or 4 in code editors and 8 columns in terminals. Learn why tab width changes and how Word tab stops work.
Read guideHow To Lock A Column In Google Sheets
Very similar to our previous article on how to make a header row in Google Sheets, in this article we’ll show how you can lock a column in Google Sheets. To lock a column in Google Sheets easily just navigate and click on the View menu, then on the sub-menu Freeze , then click on either of the options presented: “No column” (to remove any locked columns), “1 column” (to…
Read guideHow To Make A Header Row In Google Sheets
I was recently working with a lot data on my Google Sheets spreadsheet and as I scrolled down the page the information from the top rows moved off and I could no longer see (and could no longer remember) what each column’s label in the first row was. Thankfully there’s a nifty little feature in Google Sheets where you can freeze a set number of rows to lock the screen…
Read guideWord Count WordPress - PHP Code
If you want to know how many words your WordPress articles have you can open up each post individually and by clicking on the top left-hand corner’s information icon it will display the number of words. For example, when I completed this post I was curious as to the quantity of words I wrote and upon clicking the information icon within that post was informed of the following data: Wordpress…
Read guideIgnore Blank Cells In IF Functions Google Sheets
In a previous post, I explored how to ignore blank cells when using the QUERY function in Google Sheets, which had its own distinct way of removing blank cells from the data capture. But how do you ignore blank cells in your spreadsheet when you’re not in a QUERY function? What if you’re in one of the many different IF functions such as IF , IFS , SUMIF , SUMIFS…
Read guideSuiteScript UNEXPECTED_ERROR With RESTlet
A recent project hit a roadblock when I changed some code depending on the N/search module and refactored it to use N/query module. While I had some initial hassles with accepting the Beta nature of the N/query script I soon ran into another error once I had got the code to a level I knew would work: Great. Another helpful SuiteScript error. This one took some time to diagnose, but…
Read guideHow To Format Numbers In Thousands (K) In Google Sheets
Show Google Sheets numbers in thousands (k) or millions (m) with a custom number format code, including rounding, decimals and negative values.
Read guideHow To Remove Characters From A String In Python (Examples, No Imports)
There are three main ways within Python on how to remove specific characters from a string in Python, and I’ve clustered these approaches based on the following methods: Built-in string methods By pattern By position Each approach has its own unique way of being able to perform the task required, so we will explore each with the use of examples to illustrate what might suit your use case best. Remove…
Read guideHow To Install Multiple WordPress Sites On One Bitnami Stack (AWS Lightsail + Cloudflare)
WARNING – The content contained in this article no longer works per Bitnami’s deprecation of modules – their announcement is here if you want to find out more. I found it tedious trying to change all of my websites (6 in total) to Amazon’s Lightsail Bitnami WordPress box. There are many instructions on how to do this using the WordPress Multisite feature, but not if you actually need individual WordPress…
Read guideCalculate Days Between Two Dates In Google Sheets: DATEDIF Formula
The DATEDIF function calculates the number of periods between two dates. The best way to remember this function is it calculates the DATE DIF ference between two dates. What Is DATEDIF ? The DATEDIF formula calculates the difference between two dates according to a third parameter determining the type of difference needed, for example, days, months, years (etc). The DATEDIF function is a popular formula that has 3 parameters that…
Read guideCopy Files From Windows Server To S3 Bucket
I was once connecting my Dropbox account to a Windows VPS to try and sync files from the server to my computer. While I ran into a few problems initially with probably the biggest problem being NOT selecting which folders TO sync and as a result I fried a few Windows boxes. And the software doing the pushing needs to be lightweight. Store your images on Amazon’s S3 It is…
Read guideAmazon SES: Send Email From Your Domain Using Gmail
If you have a custom domain and want to be able to send email through your personal Gmail account without having to register for a Business Gmail account then here’s how you can do it using Amazon’s SES. Prerequisite If you haven’t done so already you will need to set up your custom domain in Amazon’s SES. Here is a detailed step-by-step guide on how to create your email accounts…
Read guideAmazon SES: How To Receive Email
If you’ve created your own custom domain and want to be able to create custom email accounts, but don’t want to register for a Gmail Business account due to cost then there’s an easy way to manage an email server within Amazon called Simple Email Service (SES). In this article you will learn how to register a domain within SES ready for receiving email, then you will see how we…
Read guideHugo: Redirect 301 Layout
If you’ve created a static site using the wonderful Hugo package and need to create some simple redirect HTML web pages then you’ll need to do the following: Create your HTML template and place it in your main layout folder. Create an archetype template file. Create a file using the archetype and fill in all the meta header information. Publish your page. Creating a simple redirect layout in Hugo is…
Read guideHow to Create a Google App Script Library
In our previous post on clasp and Google App Script code we discovered how we can pull code down locally, do our edits and then push it back to Google Apps. This is great when we’re working on code on the back-end of Google Sheets, or Google Forms, or any of the other great Google Apps. However, what do you do when you want to write code, but you don’t…
Read guideFlip ARRAY_CONSTRAIN Formula in Google Sheets (Example)
Previously I posted how you can use the INDEX() function to obtain the fields needed for a simple SUM() function. Then I came across another handy Google Sheet function ARRAY CONSTRAIN() . What ARRAY CONSTRAIN Does There are three parameters with this formula: range – insert the range for the formula to operate on. num rows – set the number of rows to compress. num cols – set the number…
Read guideIgnore Blank Cells in QUERY Function Google Sheets
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…
Read guideMerge Two Columns Using QUERY: Google Sheets (Step By Step Example)
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…
Read guideGoogle Sheets QUERY: Merge Multiple Columns - Expand Into New Structure (Example)
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: Original Data Structure I had exported the…
Read guideHow To Sum A Dynamic Range: Google Sheets (Example)
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…
Read guideBest Set Up For SuiteScript Coding in WebStorm
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…
Read guideBest Editor For Coding SuiteScript Seamlessly
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…
Read guideHow To Fix Invalid Property Assignment (Error 450)
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: Then in the immediate window typing: 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…
Read guideHow To Fix Oblique Advanced PDF/HTML Template Errors In Netsuite
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…
Read guideHow To Print More Than 1 Page In Advanced PDF/HTML Templates
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…
Read guideGoogle Apps Script: Set Value Of Blank Cell
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…
Read guideNetsuite Aggregate CASE Values
If you want to show in your Saved Search results an aggregation of your results from a CASE statement then you will want to apply the Sum aggregation type in the Group section of your saved search field. Let’s illustrate this through the use of an example. For example, let us assume I want to find out the BALANCE of an Account on what has been charged for our customers…
Read guideWhere Is The IF Statement In A Saved Search Formula?
Saved searches are a powerful tool to fetch and display data in NetSuite. However, there may be times with the display of our saved search data where we want to transform the data into something else. To display data in a format based on a condition we need to use the Formula fields in the Result tab. Within the Formula field for us to apply a condition we can use…
Read guideNetsuite Advanced PDF: Loop Through List
When working with NetSuite’s Advanced PDF/HTML Templating system it’s difficult in being able to find documentation on the syntax structure for working with logic in the templates. One of the requirements I had was being able to determine whether the quantity ordered on an item has the same quantity ordered as the previous item. This required the ability to determine that the item being iterated through with the list function…
Read guideCreate Weekly Cash Flow Model With Cube Sheets
An important aspect within any business to effectively plan is the ability to monitor cash flow. Everybody in business knows “cash is king” and that “cash flow is the life blood of any business”. Without having cash you can’t do anything. Being able to monitor a weekly cash flow statement in Adaptive Planning is a little cumbersome, due to its standard monthly time-frame structure, however, creating a cash flow statement…
Read guideHow To Add Attributes To Images In Markdown
Markdown is a popular way of writing content and converting it to HTML. Many popular web development sites use it including StackOverflow and GitHub. In fact these very pages that you see here on this sight have been created from source documents written in Markdown. However, one little problem I’ve recently bumped into is appending width and height parameters to the image tags so images can be sized responsively, which…
Read guideSolved! Unknown Binary Operator in Adaptive Planning
When working with formulas in your model sheets in Adaptive Planning you may come across an oblique error which doesn’t initially appear to make sense, but once you understand what it is, it becomes fairly obvious what it means (like most errors!). Problem So what is the error and how do you fix it? Have you been writing an if statement or two in your formula? The reason for the…
Read guideWorking With Date Functions In Adaptive Planning
When working with Adaptive Planning, especially for the first time as I have recently done, it is a little difficult understanding the time functions and what they return when they are used within time-series sheets (such as the Standard, Cube or Calculated Accounts in Modelled Sheets). If you’re looking for a brief understanding on what each of these functions return here’s a little sample on what they return. Assumptions about…
Read guideHandlebars Must pass iterator to each (Error)
If you’re starting out with Handlebars and using it to create your HTML web pages, you may come across an oblique error message which may not make too much sense. One such error you may encounter returns the following response: throw new Exception(‘Must pass iterator to each’) To solve this problem you need to locate within your Handlebars template code an {{ each}} call. For example, you may have something…
Read guideHow To Merge Two Columns Into One (Google Sheets)
Recently, I had to merge two columns into only one column on a spreadsheet. The way I found to do this was by using the following common spreadsheet functions: JOIN , TRANSPOSE and SPLIT , and if needed UNIQUE . I was able to find a solution, and I’ll illustrate how it worked by using an example. Let’s assume the following columns of data: Appending two columns of data, our…
Read guidecboxElement Missing Settings Object Solved!
I’ve fallen in love with the new Lightbox / Fancybox alternative Colorbox. Unfortunately though I spent half a day in frustration trying to get a simple YouTube clip displayed in an iframe popup. The errors I received in the console were: Which produced Access-Control-Allow-Origin errors or http protocol errors and a whole myriad of other warnings – just from a simple YouTube iframe setup. My simple code on the Genesis…
Read guideHow To Insert Equation When Equation Editor Greyed Out [Solved]
If you open up certain types of Microsoft Word documents you will find you’re unable to insert an equation as the icon used to insert equations into your document cannot be clicked and looks greyed out. Something that looks a little like this: Equation Editor icon in Microsoft Word 2011 for Mac greyed out Thankfully the solution is quite simple. To get around this problem simply save the Word document…
Read guideHow To Create A Simple jQuery Background Image Slider
In one recent project I had to incorporate a traditional slider on the front page of a WordPress installation to showcase the range of products this site was selling. I was a little lazy and bought a popular package to accommodate the needs of my clients’ request. Unfortunately though, after tinkering around with it, I found it more of a nuisance than a help. Even after setting the package to…
Read guideHow To Write Fractions Using Only HTML & CSS
Usually when I write fractions I use LaTeX’s command: \frac{x^2 + 3x + 5}{2x} which is easy, neat and when rendered does an awesome job: $$ \frac{x^2 + 3x + 5}{2x} $$ Currently, though I’m designing a simple website for my math students where I’d prefer not use a math renderer (if possible). The reason being that most of the content which is published does not require extensive math notation…
Read guideCreate A Dictionary In Python: Quick 5 Minute Beginners Guide
How do you create a dictionary in Python? Dictionaries in Python are mutable data types that contain key: value pairs. A dictionary can be a great way to organise information, especially if checks need to be performed prior for uniqueness and/or the value of that key needs to be changed, skipped or inserted. Creating A Dictionary Dictionaries can be created using either the dict() constructor method, using the dict literal…
Read guideList Changes Unexpectedly In Python: How Can You Stop It?
Why is it when you copy a list in Python doing b list = a list that, any changes made to a list or to b list modify the other list? If you’ve played with lists in Python you will reach a point where you want to copy a list to make modifications to it without changing the original list. Initially you would think the following would work: As you…
Read guideSort Nested Dictionary By Key Or By Value In Python: One Liner
How do you sort a nested dictionary in Python by key or by value? Throughout this article, I will use the following example, which displays a nested dictionary that contains three main properties labelled dict1 , dict2 , dict3 and from each key they reference another dictionary which contains a simple dictionary of three keys labelled A , B and C with each key matched to the same value 1…
Read guide3 Ways To Make VLOOKUP Dynamic In Google Sheets (Examples)
How do you make the range and column index number in a VLOOKUP function in Google Sheets dynamic? The VLOOKUP function is a popular formula used in spreadsheets to source data using the first column of a range as the primary key to search the search key and then to return the intersection of the cell in that row with column index number . Here is the syntax of the…
Read guide