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:

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:

This is the same view in Raw mode:

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:

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:

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:

In the third step, we create an object for the current productgroup:

One property is called products, and its value is the list of products we created above:

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).

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).