How to filter by sheet show condition in js?

[AKN]

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?

[alex_colombo]

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

[AKN]

Hey @alex_colombo , thanks.

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

[alex_colombo · Accepted solution]

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
                }
            }
        ]
    }
}

[Øystein_Kolsrud]

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

 

[alex_colombo]

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.

[Øystein_Kolsrud]

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.

[AKN]

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?

[alex_colombo]

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?

[AKN]

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.