topic Re: How to filter by sheet show condition in js? in Integration, Extension & APIs

How to filter by sheet show condition in js?

AKN — Mon, 08 Jan 2024 12:10:31 GMT

As you can see I'm hiding the sheet setting show condition to 0.

Using this I can get the sheets but this returns all of them.

'/qrs/app/object/full?xrfKey=' + xrfKey + '&filter=(objectType eq "sheet")'

How can I filter out the sheets that are hidden?

Re: How to filter by sheet show condition in js?

alex_colombo — Tue, 09 Jan 2024 13:45:08 GMT

Hi @AKN , this info seems not be exposed in QRS API. For getting this info you have to use Engine API.

Re: How to filter by sheet show condition in js?

AKN — Sun, 21 Jan 2024 13:37:58 GMT

Hey @alex_colombo , thanks.

How would I get this info using Engine API exactly? Couldn't seem to find out.

Re: How to filter by sheet show condition in js?

alex_colombo — Mon, 22 Jan 2024 08:27:52 GMT

Hey @AKN , this is how you can do this using Capability APIs (it is a wrapper on top of Engine APIs).

qlikApp.getAppObjectList("sheet", function (data) { data.qAppObjectList.qItems.forEach(async function (value) { //Evaluate sheet condition if present let sheetConditionValue = true if(value.qData.showCondition) { //Create generic object for evaluating the sheet condition const sheetCondition = await qlikApp.createGenericObject({condition: { qValueExpression: value.qData.showCondition} }); //Getting result of sheet condition const sheetConditionResult = await sheetCondition.getLayout(); sheetConditionValue = sheetConditionResult.condition } }) })

And this is how to do it with Engine APIs.

Create session object for having sheet list

{ "name": "SHEETLIST", "method": "CreateSessionObject", "handle": "${dochandle}", "params": [ { "qInfo": { "qType": "SheetList" }, "qAppObjectListDef": { "qType": "sheet", "qData": { "title": "/qMetaDef/title", "description": "/qMetaDef/description", "thumbnail": "/thumbnail", "cells": "/cells", "rank": "/rank", "columns": "/columns", "rows": "/rows" } } } ] }

From returing object, I'm calling GetLayout for reading sheet list

{ "method": "GetLayout", "handle": 2, "params": [], "outKey": -1, "id": 4 }

Then you have to loop over response and read sheets. I'm getting as example the first sheet using GetObject method

{ "handle": 1, "method": "GetObject", "params": { "qId": "67375059-2b6f-4f0f-bd5c-16e85b0b926f" } }

I'm performing a GetLayout method on sheet object for reading the show condition. Below a response example, you can find showCondition property at the end of this json object.

{ "jsonrpc": "2.0", "id": 6, "delta": true, "result": { "qLayout": { "qInfo": { "qId": "67375059-2b6f-4f0f-bd5c-16e85b0b926f", "qType": "sheet" }, "qMeta": { "title": "My new sheet", "description": "", "privileges": [ "read", "update", "delete", "exportdata", "approve" ] }, "qSelectionInfo": {}, "rank": -1, "thumbnail": { "qStaticContentUrl": {} }, "columns": 24, "rows": 12, "cells": [], "qChildList": { "qItems": [] }, "gridResolution": "small", "showCondition": "1=0" } } }

Then, I'll create a new session object for evaluating the show condition on Engine side.

{ "delta": true, "handle": 1, "method": "CreateSessionObject", "params": [ { "condition": { "qValueExpression": "1=0" }, "qInfo": { "qType": "mashup", } } ], "id": 9, "jsonrpc": "2.0" }

As last step, perform GetLayout for reading showCondition result. Below and example of response, you can find the resul in condition property (0 is false, -1 is true)

{ "jsonrpc": "2.0", "id": 10, "delta": true, "result": { "qLayout": [ { "op": "add", "path": "/", "value": { "qInfo": { "qId": "MUfDdDH", "qType": "mashup" }, "qMeta": { "privileges": [ "read", "update", "delete", "exportdata" ] }, "qSelectionInfo": {}, "condition": 0 } } ] } }

Re: How to filter by sheet show condition in js?

Øystein_Kolsrud — Mon, 22 Jan 2024 08:32:30 GMT

There's a "showCondition" property in the Properties of the the sheet. The Layout will contain the evaluated version:

Re: How to filter by sheet show condition in js?

alex_colombo — Mon, 22 Jan 2024 08:44:10 GMT

Hey @Øystein_Kolsrud , using Engine API Explorer, if I use GetObject on a sheet and then GetLayout I received the expression, and not the evaluation. I expected to see the result from GetLayout, but it doesn't, that's why I suggest to create a session object for evaluating the expression.

Re: How to filter by sheet show condition in js?

Øystein_Kolsrud — Mon, 22 Jan 2024 09:10:22 GMT

Hmm... That's odd. The layout should contain the evaluated form.

Anyway, if you create an AppObjectList like you do in your example, then you can also add the show condition directly in the "qData" section. Something like this:

"qData": { "title": "/qMetaDef/title", ... "rows": "/rows", "showCondition": "/showCondition" }

Then you'll find the evaluated version of the "showCondition" expression directly in the "qItems" list when you get the layout of you app object list. That way you don't have to open all the sheets to get the evaluated version.

Re: How to filter by sheet show condition in js?

AKN — Thu, 22 Feb 2024 22:33:59 GMT

For the example with Capability API it works, however there are 100+ apps that I need to check for their sheet conditions.

I'm looping all app ids and calling something like this:

apps.forEach((app) => { var _app = qliks.openApp(app.id, config); _app.getList("sheet", function (reply) { reply.qAppObjectList.qItems.forEach(function (sheet) { if (sheet.qData.showCondition == "0") return; }); }); });

It feels wrong and also I get websocket error insufficient resources

How to best handle this?

Re: How to filter by sheet show condition in js?

alex_colombo — Fri, 23 Feb 2024 08:50:05 GMT

Can you descrive your requirement? Why do you need to get sheet conditions from 100+ apps? Are you closing the websocket as soon as you have the sheet conditions?

Re: How to filter by sheet show condition in js?

AKN — Fri, 23 Feb 2024 09:29:55 GMT

I'm making a sidebar that contains all streams>apps>sheets as dropdown menu. If a sheet has show condition 0 then I don't want to show it there.

I can get all the sheets using qrs/app/object/full?xrfKey=xxx&filter=(objectType eq 'sheet')

but I can't filter show condition this way.

So what I'm doing is when I get the apps using qrs/app/full?xrfKey=xxx&filter=(stream ne null) I loop through all apps and open them. Then return if show condition is 0.

Can't think of any other way to do this so if you have an idea please tell me.

Re: How to filter by sheet show condition in js?

alex_colombo — Fri, 23 Feb 2024 13:23:35 GMT

As mentioned, are you closing the websocket as soon as you have the sheet condition?

An idea could be to just show the streams and the apps, then when a user select the apps, you will open the app and get the sheets list, instead of getting all the sheets from all the apps. Could this work?

Re: How to filter by sheet show condition in js?

AKN — Fri, 23 Feb 2024 13:29:32 GMT

No I'm not closing the websocket because I'm not sure how to do that...

And yeah that's what I'm doing right now! However, there is a searchbar and it searches through all streams apps and sheets. When user searches for a sheet, it doesn't show up since sheets are not loaded yet.

Re: How to filter by sheet show condition in js?

alex_colombo — Fri, 23 Feb 2024 13:34:42 GMT

Try closing the web socket in this way and let me know if this solve the issue.

Re: How to filter by sheet show condition in js?

AKN — Fri, 23 Feb 2024 13:45:59 GMT

Oh, this. Yes I've tried it before. It works for like half of the apps and then gives error, cannot connect to the server.