Grouping items in a list
This article describes how you can implement a group by
in an automation, for items in a list.
We will use following simple Google Sheet as example:
A sample Google Sheet.
Group items: count items per group
First we will group the items by Productgroup. We do this by using a variable of type Object, that we use as a dictionary. This means that the keys will be the field on which we want to group, and the values will be the count of the items in said group:
Grouping items by Productgroup.
This is the same view in Raw mode
:
Grouping items by Productgroup, in raw code.
As you can see, we use the Productgroup as the key in the object:
{$.getDataFromSheetWithHeaders.item.Productgroup}
And for the value, we add 1 to the previous value of the same key in the same object:
{ add: { $.myObj.{$.getDataFromSheetWithHeaders.item.Productgroup} }, 1 }
As you can see, the add
formula has two inputs. The first input is the previous value of the key in myObj:
$.myObj.{$.getDataFromSheetWithHeaders.item.Productgroup}
We reference the object with $.myObj
and then we append the key which is {$.getDataFromSheetWithHeaders.item.Productgroup}
.
Note that you can add square brackets around the key, in case it has special characters:
$.myObj.[{$.getDataFromSheetWithHeaders.item.Productgroup}]
The result of myObject is the following object where the keys are the product groups, and the values are the number of items in each group:
{
"Fruit": 2,
"Vegetable": 1
}
Group items: make sum per group
Next, we will do the same grouping, but this time we will make the sum of the quantities of each item:
Grouping quantities.
This automation is exactly the same, only this time we add the quantity to the value:
{ add: { $.myObj.{$.getDataFromSheetWithHeaders2.item.Productgroup} },
{ $.getDataFromSheetWithHeaders2.item.Quantity } }
As you can see, the add
formula now has following 2 inputs:
{ $.myObj.{$.getDataFromSheetWithHeaders2.item.Productgroup} }
and
{ $.getDataFromSheetWithHeaders2.item.Quantity }
The first input is the previous value of the property in myObj (with the same key = same product group) and the second input is the quantity to add of the current item in the loop.
The result in myObj is:
{
"Fruit": 7,
"Vegetable": 2
}
See the article Converting an object with key/values to a list to learn how to convert the above object to a list (array).
Group items: make list of items per group
In the next example, we will create a list of products, per productgroup. We use four variables to accomplish this:
- product: object with key/values of one
sub item
- products: a list of products from one product group
- productgroup: object with key/values of one product group, one key is
products
and its value is a list of all the products in that group - productgroups: object which is used as a
dictionary
, each key is a unique id for a group. In this example we use the productgroup name (Fruit, Vegetable) as the unique key.
We loop over all rows of our Google Sheet. In the first step, we create a product object for the current row:
Creating a product object.
In the second step we add the product to a list called products
. We make the list empty, then we set it equal to the previous list of the current productgroup and then we add the new item:
Setting a new variable.
Adding an item to the list.
In the third step, we create an object for the current productgroup:
Creating a new object.
One property is called products
, and its value is the list of products we created above:
Setting the property value.
In the fourth step, we add the productgroup to a dictionary
. This is an object where each key is a unique value for one productgroup. Note that we will overwrite key/values while we loop, and that's OK since we always merge the previous list of products (sub items) into the new list (see above).
Adding the productgroup to a dictionary.
The final result is an object where each key is a product group (Fruit, Vegetable). For each productgroup there are properties (e.g. groupname) and there is a property products
that contains a list of products:
{
"Fruit": {
"groupname": "Fruit",
"products": [
{
"name": "Apple",
"quantity": "4"
},
{
"name": "Banana",
"quantity": "3"
}
]
},
"Vegetable": {
"groupname": "Vegetable",
"products": [
{
"name": "Tomato",
"quantity": "2"
}
]
}
}
See the article Converting an object with key/values to a list to learn how to convert the above object to a list (array).
Did this page help you?
If you find any issues with this page or its content – a typo, a missing step, or a technical error – let us know how we can improve!