Introducing the Matrix data type
Hello spreadsheets enthusiasts! 👋
We’re burning to tell you all about our new data type - the Matrix.
The Matrix type is behind some of the coolest tasks Rows users combine to power up their spreadsheets. This article is for the technically curious minds. The ones who thrive on knowing the details of what goes on behind the scenes in our spreadsheet. And while knowing this is definitely not a requirement to use Rows, we know many of you would love to nerd this one out with us.
Why we created it
Rows is the only true spreadsheet with built-in integrations and a beautiful sharing experience. Most of those powerful integrations allow users to interact with external data sources such as CRMs, social networks, email, SMS services and others. However, processing data from third-parties poses a big challenge: spreadsheets show data in a tabular format but most of the third-party applications provide data through REST APIs.
As you know spreadsheets work with various data types, mainly: dates, text, currencies, and numbers. Defining a data type helps a spreadsheet to attribute any data to a certain type and run calculations correctly. But with the growing complexity of data and variety of tasks performed the existing types weren’t enough to work with data. This is especially important in products like Rows which exports high volume and variety of data through APIs. We needed something new to tackle this complexity.
So, where exactly is the challenge?
JSON is the most common data format used by APIs, but...
Spreadsheets don’t handle this format. Spreadsheet users work with numbers, booleans (TRUE or FALSE), currencies, text, and dates.
When you copy and paste a data structure such as JSON in a cell, traditionally spreadsheets (Excel, Google Sheets) assume that it is dealing with text. At Rows we decided to upgrade spreadsheets to better handle JSON and ranges.
This is how we deal with JSON objects in our spreadsheets.
Matrix type handling JSON and ranges 👌
JSONs are a tree-like structure that includes arrays [] and objects {}. At Rows, JSON is a first-class citizen, which means that we let you deal with it as an input and output.
For example, you can search and parse data through both our visual Data Explorer interface and through functions like PARSE. However, often users need to do more than cherry-picking a piece of data or two. You want to be able to use all the typical spreadsheet functions on data, and then add some more magic. That’s where the Matrix type comes into play.
First, let’s take a look at the similarities between JSON objects and ranges. Consider the following two data structures. They look pretty similar, right?
What about these two structures? Still similar.
As you can see, all of the data presented is equivalent. That was what we noticed too. So, we decided to introduce a new way to consume data, whether it comes in the form of a JSON object, or cell ranges.
And this is how we got the idea of creating the Matrix type. As a result, the functions supporting the Matrix type work with JSON objects and ranges in the same way. It doesn’t matter which one you decide to provide to the function. Internally, we convert your data into a Matrix type.
Adding data with Matrix type
The first functions to support this type were the INSERT, UPDATE, and OVERWRITE. These are some of the most important spreadsheet functions available at Rows. They help you to add or update data into a destination table.
Let’s look at one of these functions - INSERT. The goal of the INSERT function is to add data into a destination iteratively. In INSERT(data, [destination]), the first parameter - data - is always coerced to the Matrix data type. This offers users the ability to insert data from a JSON or a range data into a destination table.
Let’s see how the INSERT function works now with the Matrix type support.
Adding JSON data into a range
The first example shows how we can add JSON data (from cell A1) into a range (A3:C3). The function translates {data} into rows and columns. Then it adds each value in the range defined. This data transformation is handled by the Matrix type.
Adding data from a range into a destination table
This example demonstrates how you can add data from a range (A1:C4) into a destination table (A6:B6). The function is also able to interpret ranges of values without any additional parameter or function. You don’t need to worry about this data management. The function gets you covered!
Expanding the Matrix type to common functions
Now that you know more about the Matrix type, let’s check one of the most common spreadsheet functions - SUM. The next two images show how you can leverage this new data type with this function.
Manual calculation
The first example demonstrates a common use case for the SUM function. The user is manually calculating total deals in the sales pipeline.
Calculation with JSON data 🙌
The second example demonstrates the exact same use case but with JSON data. Let's assume that the user got this information from a CRM. As you can see, there’s no need to parse data and select a range to calculate the total spending. You can do it out of the box.
Data conversion automated
Matrix type allows you to obtain data from external sources such as CRMs or social networking services and convert it into a table automatically, applying the regular spreadsheet operations you’re used to.
This new data type will help you to build great spreadsheet applications in Rows. You can already use the Matrix type in the following functions:
SUM
VLOOKUP
INSERT
UPDATE
OVERWRITE
INDEX
MATCH
SUMPRODUCT
PRODUCT
MAX
MIN
AVERAGE
MEDIAN
MODE
PARSE
COUNT
COUNTA
COUNTIF
COUNTIFS
SUMIF
SUMIFS
I hope now you can leverage the Matrix type to level up your spreadsheet game. Make sure to check it out at Rows.
— João Azevedo, Product Manager
[object Object] is the first true spreadsheet with built-in integrations and a slick sharing experience. Automate your processes with powerful tools that integrate with your stack. Currently in public beta.
