Using make to filter data on google sheets

Hey everyone,

So I am trying to filter data based on specific criteria on google sheets.

However I am seem to be getting stuck at the very first step.

I currently have a google sheet with over 5k rows, and about 10 columns

Based on specific number values that can be found in 3 different columns I want those specific rows to be copied across onto a different sheet.

Now this is a tester filter that I setup

However even though I know those 2 values are available nothing gets transferred over.

What conditions do I need to specify in order to add more values and actually get the rows copied across to the second sheet?

Hey @KelvinPM

I think these two values are in separate rows; you should use two filters here.

1 Like

Hi @ManishMandot thank you for that

Okay that works so does this mean that I will need to add potentially 20 “OR” filter one after the other as you showed in your screenshot since they will all be on separate rows?

Is there no way to aggregate all the values into one condition?

Hopefully I’m making sense

Hello @KelvinPM,

For this, you may want to try the Google Sheets Search Rows (Advanced) module, which allows you to build a query (similar to SQL) which you can use to find your rows.
Unfortunately, the results don’t include row numbers, but in some cases that may be ok.

One query could get you all the data you need!

1 Like

You can use arrays in filters.

Screenshot_2024-04-29_220415

e.g.:

Screenshot_2024-04-29_220439

Hope this helps!

Links

Here are some useful links and guides you can use to learn more on how to use the Make platform, apps, and app modules. I found these useful when I was learning Make, and hope they might benefit you too —

General

Help Center Basics

Articles & Videos

samliewrequest private consultation

4 Likes

hey @samliew I like this however when I try it doesn’t give me any result, just to make sure I understand the logic, the add emptyarray condition contains all the criteria numbers I am looking for.

And the array operator is where I point make to look for those criteria numbers?

If this works would help me tremendously since I just need to list all the numbers once

Yes, that’s right. I even tested it myself to see if it works.

Module Export

You can copy and paste this module export into your scenario. This will paste the modules shown in my screenshots above.

  1. Copy the JSON code below by clicking the copy button when you mouseover the top-right of the code block
    Screenshot_2024-01-17_200117

  2. Enter your scenario editor. Press ESC to close any dialogs. Press CTRLV (paste keyboard shortcut for Windows) to paste directly in the canvas.

  3. Click on each imported module and save it for validation. You may be prompted to remap some variables and connections.

JSON

{
    "subflows": [
        {
            "flow": [
                {
                    "id": 102,
                    "module": "util:SetVariable2",
                    "version": 1,
                    "parameters": {},
                    "mapper": {
                        "name": "code",
                        "scope": "roundtrip",
                        "value": "18129"
                    },
                    "metadata": {
                        "designer": {
                            "x": 853,
                            "y": -984
                        },
                        "restore": {
                            "expect": {
                                "scope": {
                                    "label": "One cycle"
                                }
                            }
                        },
                        "expect": [
                            {
                                "name": "name",
                                "type": "text",
                                "label": "Variable name",
                                "required": true
                            },
                            {
                                "name": "scope",
                                "type": "select",
                                "label": "Variable lifetime",
                                "required": true,
                                "validate": {
                                    "enum": [
                                        "roundtrip",
                                        "execution"
                                    ]
                                }
                            },
                            {
                                "name": "value",
                                "type": "any",
                                "label": "Variable value"
                            }
                        ],
                        "interface": [
                            {
                                "name": "code",
                                "label": "code",
                                "type": "any"
                            }
                        ]
                    }
                },
                {
                    "id": 103,
                    "module": "util:SetVariable2",
                    "version": 1,
                    "parameters": {},
                    "filter": {
                        "name": "is valid",
                        "conditions": [
                            [
                                {
                                    "a": "{{add(emptyarray; 18129; 62020)}}",
                                    "o": "array:contain",
                                    "b": "{{102.code}}"
                                }
                            ]
                        ]
                    },
                    "mapper": {
                        "name": "valid",
                        "scope": "roundtrip",
                        "value": "{{true}}"
                    },
                    "metadata": {
                        "designer": {
                            "x": 1149,
                            "y": -985
                        },
                        "restore": {
                            "expect": {
                                "scope": {
                                    "label": "One cycle"
                                }
                            }
                        },
                        "expect": [
                            {
                                "name": "name",
                                "type": "text",
                                "label": "Variable name",
                                "required": true
                            },
                            {
                                "name": "scope",
                                "type": "select",
                                "label": "Variable lifetime",
                                "required": true,
                                "validate": {
                                    "enum": [
                                        "roundtrip",
                                        "execution"
                                    ]
                                }
                            },
                            {
                                "name": "value",
                                "type": "any",
                                "label": "Variable value"
                            }
                        ],
                        "interface": [
                            {
                                "name": "valid",
                                "label": "valid",
                                "type": "any"
                            }
                        ]
                    }
                }
            ]
        }
    ],
    "metadata": {
        "version": 1
    }
}
2 Likes

I was able to do it without copying the code, I realized that I literally typed the word “add” within the condition field instead of using the function, so Make was not registering it :sweat_smile:

But thank you so much!

Now time to get to work!

3 Likes