Hello
can someone please help me with the expression for extracting data from google sheets for specific condition
for example i have 20 employees andi want the data of employees that are only in the sales department
i have tired this expression
i tried this one
= output2[item.department=“sales”] (output2 being the data that was read from the sheet)
but the value was giving me empty brackets
I tried this aswell
output2[item in item[5] = “sales”][2]
this is giving a data of
[“########”,“Eva”,“5870000000”,“53”,“sales”] but not all data for the members in the department sales
can you please tell me where im going wrong here
@Wish - the [department="Sales"] isn’t a valid FEEL expression (department="Sales" evaluates to either True or False, so that would then evaluate further to employeeData[True] or employeeData[False], which are both empty sets).
However, we can’t help further unless you share a sample of the data that you are working with and what you expect the output to be.
This is the sample data im working with
i want the ouput to be the names of all the employees whose department is listed as “sales”
@Wish - looking at the Google Sheets API documentation, I think the data from the Sheets Connector looks like this (you can confirm this inside Operate):
{
"action": "...",
"status": "...",
"response": [
[ "Timestamp", "name", "contact", "id", "department" ],
[ "###", "unkown", "1230000000", "2", "development" ],
[ "###", "idk", "1230000000", "5", "sales" ]
...
]
}
Therefore, using the filter FEEL expession, you want something like this:
=output2.response[employee[5] = "sales"]
@Wish - you will need to share what the contents of output2 (or response) look like to help further. I was making an educated guess based on the screenshot of Google Sheets you shared, but perhaps the data is formatted differently than what I guessed. (It’s also possible the FEEL expression I gave is incorrect, but having sample data to test against would help either way!)
{"action":"Get worksheet data","status":"OK","response":[["Timestamp","name","contact","id","department"],["########","unkown","1230000000","2","development"],["","idk","1230000000","5","sales"],["########","alishba","1234567","12","hr"],["########","Diana","9070000000","10","support"],["########","Alice","2910000000","86","hr"],["########","Alice","6480000000","95","sales"],["########","Sam","9570000000","38","finance"],["########","Diana","9880000000","41","sales"],["########","Jane","2050000000","30","finance"],["########","Sam","4940000000","69","finance"],["########","Diana","4410000000","36","finance"],["########","Jane","3880000000","38","marketing"],["########","Jane","3580000000","36","sales"],["########","Alice","3030000000","73","development"],["########","Eva","5870000000","53","sales"],["########","Lucy","6430000000","74","finance"],["########","Bob","4950000000","23","hr"],["########","Diana","4290000000","74","finance"],["########","Tom","1850000000","58","development"],["########","Mark","4930000000","2","hr"],["########","Jane","5920000000","73","support"],["########","Lucy","6190000000","85","support"],["########","John","7160000000","40","sales"],["########","Alice","4200000000","27","hr"],["########","Sam","6960000000","19","development"],["########","Sam","6240000000","85","sales"],["########","Lucy","1780000000","87","finance"],["########","Lucy","5230000000","95","support"],["########","Mark","3810000000","30","support"],["########","John","9860000000","31","support"],["########","Eva","8200000000","72","sales"],["########","Sam","8580000000","28","sales"],["########","Tom","2330000000","29","support"],["########","Alice","7050000000","4","finance"],["########","Bob","8000000000","99","sales"],["########","Tom","2550000000","48","support"],["########","Eva","1040000000","10","support"],["########","Mark","7380000000","68","marketing"],["########","Eva","6320000000","28","marketing"],["########","Alice","1340000000","41","support"],["########","Lucy","6960000000","57","marketing"],["########","Bob","8570000000","48","support"],["########","John","3140000000","78","sales"],["########","Lucy","6570000000","88","development"],["########","Jane","2960000000","47","finance"],["########","Mark","1540000000","69","development"],["########","Tom","2620000000","99","marketing"],["########","Sam","4890000000","28","finance"],["########","Alice","6680000000","34","hr"],["########","Diana","1270000000","15","hr"],["########","John","6480000000","37","marketing"],["########","Diana","8280000000","10","marketing"],["########","Bob","5220000000","14","marketing"],["########","Tom","1950000000","96","finance"],["########","Eva","3040000000","8","finance"],["########","John","4440000000","76","support"],["########","Mark","1610000000","41","development"],["########","Bob","1470000000","40","development"],["########","Tom","9270000000","94","support"],["########","Sam","8810000000","73","support"],["########","Eva","8970000000","38","finance"],["########","Sam","4310000000","92","finance"],["########","John","4840000000","77","support"],["########","Alice","8490000000","1","support"],["########","Sam","9300000000","57","finance"],["########","Sam","9190000000","23","sales"],["########","Sam","7600000000","20","support"],["########","Mark","9570000000","49","support"],["########","Sam","9360000000","36","support"],["########","Alice","3940000000","23","finance"],["########","Bob","5090000000","41","hr"],["########","Lucy","3810000000","100","sales"],["########","Alice","8770000000","79","marketing"],["########","Diana","2070000000","6","sales"],["########","Sam","6410000000","48","development"],["########","Eva","1020000000","65","finance"],["########","Alice","3690000000","59","sales"],["########","Sam","4610000000","11","finance"],["########","Alice","7230000000","3","sales"],["########","Bob","2010000000","90","marketing"],["########","Eva","1210000000","84","marketing"],["########","Tom","8810000000","21","finance"],["########","Jane","3770000000","60","hr"],["########","Sam","5360000000","52","hr"],["########","Jane","2280000000","16","support"],["########","Alice","9220000000","76","finance"],["########","Sam","1550000000","53","support"],["########","Lucy","6910000000","32","hr"],["########","Diana","5240000000","70","sales"],["########","Tom","6330000000","74","development"],["########","Sam","6670000000","32","development"],["########","Eva","8840000000","99","hr"],["########","John","4460000000","6","hr"],["########","Alice","8130000000","4","development"],["########","Eva","5470000000","72","support"],["########","Bob","6010000000","1","marketing"],["########","Alice","8250000000","4","sales"],["########","Diana","4990000000","57","marketing"],["########","Mark","6390000000","14","finance"],["########","Tom","3290000000","23","hr"],["########","Sam","7770000000","96","sales"],["########","Tom","1240000000","86","support"],["########","Bob","1670000000","4","marketing"]]}
this is the content of output2
@Wish - the format is what I expected, but I made a small typo in my expression! Inside the brackets, the FEEL expression uses the variable name item not employee. It should be:
output2.response[item[5] = "sales"]
You can also test it yourself in a FEEL playground online, like this one! That link should include the data and the expression and you can see the result.
1 Like
hi
Thankyou so much for the help i tried out the expression and it worked
1 Like
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.