# * **Sheet Name:** The name of the sheet. # * **Type:** "Dimension", "Measure", "Title", "Subtitle", "Footnote", "Tooltip", "Text Object" (or similar categories). # * **Context:** "Dimension Name", "Measure Name", or a brief description of the element (e.g., "Chart Title"). # * **Variable:** A generated variable name to use in Qlik Sense expressions. # * **Text:** The original text to be translated. # * **Translation:** (Initially blank) A column for translators to enter the translated text. import websocket import json import ssl import uuid import pandas as pd import time import os from urllib.parse import urlparse, quote import re # For creating valid variable names # --- Configuration --- # Option 1: Automatically find the app by QVF name (more user-friendly) # Set QVF_NAME to the filename of your app (e.g., "MyAnalysisApp.qvf") # Leave APP_ID empty or None QVF_NAME = "3570412_Ctrack Prod_Analytics.qvf" # <--- CHANGE THIS TO YOUR QVF FILENAME APP_ID = None # Let the script find the App ID # Option 2: Manually specify the App ID (if you know it) # Find it in the URL when the app is open (e.g., http://localhost:4848/sense/app/YOUR_APP_ID/...) # Set APP_ID to that value (e.g., "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx") # Leave QVF_NAME empty or None # APP_ID = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx" # <--- OR USE THIS # QVF_NAME = None # --- Advanced Configuration --- QLIK_ENGINE_URL = "ws://localhost:4848/app/" OUTPUT_EXCEL_FILE = "qlik_app_translations.xlsx" MAX_WAIT_TIME = 10 # Max seconds to wait for a response DEBUG = False # Set to True for more detailed WebSocket communication logs # --- Global Variables --- ws = None request_id_counter = 1 pending_requests = {} # Store request details to match responses # --- Helper Functions --- def get_next_request_id(): """Generates a unique request ID.""" global request_id_counter req_id = request_id_counter request_id_counter += 1 return req_id def send_request(method, handle, params=[], req_id=None, delta=False): """Sends a JSON-RPC request to the Qlik Engine.""" global ws, pending_requests if not req_id: req_id = get_next_request_id() request_data = { "jsonrpc": "2.0", "id": req_id, "method": method, "handle": handle, "params": params } if delta: request_data["delta"] = delta # For specific methods like GetLayout if DEBUG: print(f"--> Sending Request (ID: {req_id}): {json.dumps(request_data)}") ws.send(json.dumps(request_data)) pending_requests[req_id] = {"method": method, "handle": handle, "params": params, "sent_time": time.time()} return req_id def receive_response(expected_req_id, timeout=MAX_WAIT_TIME): """Receives and parses a JSON-RPC response, waiting for a specific request ID.""" global ws, pending_requests start_time = time.time() while time.time() - start_time < timeout: try: message = ws.recv() if not message: continue # Skip empty messages response = json.loads(message) if DEBUG: print(f"<-- Received Response: {json.dumps(response)}") # Check for notifications or non-matching IDs if "method" in response and response["method"] == "OnAuthenticationInformation": print("Authentication notification received, ignoring.") continue if "method" in response and response["method"] == "OnConnected": print(f"Connection confirmation received: {response.get('params')}") continue if "id" not in response: print(f"Received message without ID, possibly a notification: {response}") continue resp_id = response.get("id") # If this response is for the request we're waiting for if resp_id == expected_req_id: if resp_id in pending_requests: del pending_requests[resp_id] # Remove from pending if "error" in response: print(f"!!! Engine API Error for Request ID {resp_id}: {response['error']}") return None # Indicate error return response # Return the successful response else: # If it's a response for another pending request, store it maybe? # For simplicity now, we just log it if it's unexpected. # Proper handling might involve a loop checking all pending_requests if resp_id in pending_requests: print(f"Received response for different request ID ({resp_id}), expected {expected_req_id}. Will keep waiting.") # Store this response if needed later, or handle async responses else: print(f"Received response with unexpected ID: {resp_id}. Full response: {response}") except websocket.WebSocketTimeoutException: print(f"WebSocket timeout waiting for response ID {expected_req_id}.") continue # Continue waiting until outer timeout except websocket.WebSocketConnectionClosedException: print("WebSocket connection closed unexpectedly.") raise # Re-raise the exception except json.JSONDecodeError: print(f"Could not decode JSON from message: {message}") continue except Exception as e: print(f"An error occurred during receive: {e}") raise # Re-raise other critical errors print(f"Timeout: Waited {timeout} seconds but did not receive expected response for request ID {expected_req_id}.") if expected_req_id in pending_requests: print(f"Details of timed-out request: {pending_requests[expected_req_id]}") del pending_requests[expected_req_id] return None # Indicate timeout def find_app_id_by_qvf_name(target_qvf_name): """Connects to the global interface and finds the App ID for a given QVF filename.""" global ws print(f"Attempting to find App ID for QVF: {target_qvf_name}") connect_url = QLIK_ENGINE_URL.replace("/app/", "/app/%3Ftransient%3D") # Connect globally try: # Establish temporary global connection global_ws = websocket.create_connection(connect_url, sslopt={"cert_reqs": ssl.CERT_NONE}) print("Connected to Qlik Engine (Global Interface).") # Send GetDocList request req_id = get_next_request_id() request_data = { "jsonrpc": "2.0", "id": req_id, "method": "GetDocList", "handle": -1, "params": [] } if DEBUG: print(f"--> Sending Request (ID: {req_id}): {json.dumps(request_data)}") global_ws.send(json.dumps(request_data)) # Receive response for GetDocList start_time = time.time() while time.time() - start_time < MAX_WAIT_TIME: message = global_ws.recv() if not message: continue response = json.loads(message) if DEBUG: print(f"<-- Received Response: {json.dumps(response)}") if response.get("id") == req_id: if "result" in response and "qDocList" in response["result"]: for doc in response["result"]["qDocList"]: # qDocName often contains the full path. Extract filename. doc_filename = os.path.basename(doc.get("qDocName", "")) if doc_filename.lower() == target_qvf_name.lower(): found_app_id = doc.get("qDocId") print(f"Found App ID: {found_app_id} for QVF: {target_qvf_name}") global_ws.close() return found_app_id print(f"Error: QVF '{target_qvf_name}' not found in the list of open/available apps.") print("Available apps:", [os.path.basename(d.get("qDocName", "")) for d in response["result"]["qDocList"]]) global_ws.close() return None else: print(f"Error: Could not get document list. Response: {response}") global_ws.close() return None elif "method" in response and response["method"] == "OnConnected": print(f"Connection confirmation received: {response.get('params')}") continue # Ignore connection confirmation else: print(f"Received unexpected message: {response}") print(f"Timeout waiting for GetDocList response.") global_ws.close() return None except Exception as e: print(f"Error finding App ID: {e}") if 'global_ws' in locals() and global_ws.connected: global_ws.close() return None finally: if 'global_ws' in locals() and global_ws.connected: global_ws.close() def generate_variable_name(text): """Generates a valid Qlik Sense variable name from text.""" # Remove special characters and spaces, replace with underscores cleaned_text = re.sub(r"[^a-zA-Z0-9_]+", "_", text) cleaned_text = cleaned_text.strip("_") # Remove leading/trailing underscores # Ensure the name starts with a letter or underscore if not cleaned_text or not (cleaned_text[0].isalpha() or cleaned_text[0] == "_"): cleaned_text = "_" + cleaned_text # Prefix with underscore if invalid # Limit length to 63 characters (Qlik Sense limit) return cleaned_text[:63] # --- Main Script Logic --- def main(): global ws, APP_ID translation_data = [] # List to store extracted data for Excel # --- Step 1: Determine App ID --- if not APP_ID and QVF_NAME: APP_ID = find_app_id_by_qvf_name(QVF_NAME) if not APP_ID: print(f"Could not find the App ID for {QVF_NAME}. Ensure the app is open in Qlik Sense Desktop.") return elif not APP_ID and not QVF_NAME: print("Error: You must specify either QVF_NAME or APP_ID.") return # Encode the App ID for the URL encoded_app_id = quote(APP_ID) app_specific_url = f"{QLIK_ENGINE_URL.rstrip('/')}/{encoded_app_id}" print(f"Connecting to App URL: {app_specific_url}") try: # --- Step 2: Connect to Qlik Engine for the specific App --- ws = websocket.create_connection(app_specific_url, sslopt={"cert_reqs": ssl.CERT_NONE}) print(f"Successfully connected to Qlik Engine for App ID: {APP_ID}") # Wait briefly for potential OnConnected message time.sleep(1) # Clear any initial messages like OnConnected before proceeding try: while ws.connected: msg = ws.recv() if msg: resp = json.loads(msg) if DEBUG: print(f"<-- Initial message: {resp}") if resp.get("method") == "OnConnected": print(f"Connection confirmed by engine: Session={resp['params']['qSessionState']}") break # Stop after getting confirmation else: break # Break if no message except websocket.WebSocketTimeoutException: pass # No initial message is fine except Exception as e: print(f"Warning: Error processing initial messages: {e}") # --- Step 3: Open the App (handle: -1 refers to global context) --- # Although we connected to the app URL, OpenDoc formalizes the session for the app print("Sending OpenDoc request...") open_doc_id = send_request("OpenDoc", -1, [APP_ID]) response = receive_response(open_doc_id) if not response or "result" not in response or "qReturn" not in response["result"]: print(f"Error: Failed to open app {APP_ID}. Response: {response}") ws.close() return app_handle = response["result"]["qReturn"]["qHandle"] print(f"App opened successfully. App Handle: {app_handle}") # --- Step 4: Get All Sheets (using CreateSessionObject for SheetList) --- print("Requesting sheet list...") sheet_list_def = { "qInfo": {"qType": "SheetList"}, "qAppObjectListDef": { "qType": "sheet", "qData": { "title": "/qMetaDef/title", "rank": "/qData/rank", # To filter out non-visible sheets (rank > 0 usually) "thumbnail": "/qMetaDef/thumbnail", "published": "/qMetaDef/published", # To check if public "description": "/qMetaDef/description" } } } create_sheetlist_id = send_request("CreateSessionObject", app_handle, [sheet_list_def]) response = receive_response(create_sheetlist_id) if not response or "result" not in response or "qReturn" not in response["result"]: print(f"Error: Failed to create SheetList object. Response: {response}") ws.close() return sheet_list_handle = response["result"]["qReturn"]["qHandle"] print(f"SheetList object created. Handle: {sheet_list_handle}") # Get layout of the sheet list get_sheetlist_layout_id = send_request("GetLayout", sheet_list_handle, []) response = receive_response(get_sheetlist_layout_id) if not response or "result" not in response or "qLayout" not in response["result"]: print(f"Error: Failed to get SheetList layout. Response: {response}") ws.close() return sheets = response["result"]["qLayout"]["qAppObjectList"]["qItems"] print(f"Found {len(sheets)} total sheets.") # --- Step 5: Iterate through Sheets --- # public_sheets = [s for s in sheets if s.get("qData", {}).get("published", False) and s.get("qData", {}).get("rank", 0) > 0] # Old all_sheets = sheets print(f"Processing {len(all_sheets)} sheets...") # --- OPTIONAL: Limit to 1 sheet for debugging --- # all_sheets = all_sheets[:1] # print(f"DEBUG: Limiting processing to the first sheet: {all_sheets[0].get('qMeta',{}).get('title', 'N/A')}") # --- for sheet_info in all_sheets: sheet_id = sheet_info["qInfo"]["qId"] sheet_title = sheet_info["qMeta"]["title"] print(f"\n--- Processing Sheet: '{sheet_title}' (ID: {sheet_id}) ---") # --- Step 6: Get Sheet Layout to find Objects --- # Get a handle to the sheet object first get_sheet_obj_id = send_request("GetObject", app_handle, [sheet_id]) response = receive_response(get_sheet_obj_id) if not response or "result" not in response or "qReturn" not in response["result"]: print(f"Error: Failed to get handle for sheet {sheet_id}. Response: {response}") continue sheet_handle = response["result"]["qReturn"]["qHandle"] # Get the layout of the sheet get_sheet_layout_id = send_request("GetLayout", sheet_handle, []) response = receive_response(get_sheet_layout_id) if not response or "result" not in response or "qLayout" not in response["result"]: print(f"Error: Failed to get layout for sheet {sheet_id}. Response: {response}") continue sheet_layout = response["result"]["qLayout"] sheet_objects = sheet_layout.get("qChildList", {}).get("qItems", []) # Older versions might use qItems directly under qLayout if not sheet_objects and "cells" in sheet_layout: # Newer grid layout sheet_objects = sheet_layout.get("cells", []) print(f"Found {len(sheet_objects)} potential objects/cells on sheet '{sheet_title}'.") # --- Step 7: Iterate through Objects on the Sheet --- for item in sheet_objects: # Structure differs: qChildList has qItems with qInfo.qId, cells have 'name' which is the object ID obj_id = item.get("qInfo", {}).get("qId") if "qInfo" in item else item.get("name") if not obj_id: print("Skipping item without object ID:", item) continue print(f" -- Analyzing Object ID: {obj_id} --") # --- Step 8: Get Object Handle and Layout --- get_obj_id = send_request("GetObject", app_handle, [obj_id]) response = receive_response(get_obj_id) if not response or "result" not in response or "qReturn" not in response["result"]: print(f" Error: Failed to get handle for object {obj_id}. Response: {response}") continue obj_handle = response["result"]["qReturn"]["qHandle"] obj_type = response["result"]["qReturn"]["qType"] # Get object type get_obj_layout_id = send_request("GetLayout", obj_handle, []) response = receive_response(get_obj_layout_id) if not response or "result" not in response or "qLayout" not in response["result"]: print(f" Error: Failed to get layout for object {obj_id}. Response: {response}") continue obj_layout = response["result"]["qLayout"] # --- Step 9: Extract Metadata and Populate Translation Data --- # Title title = obj_layout.get("qMeta", {}).get("title", "") # Check qMeta first (less common now) if not title: title = obj_layout.get("title", "") # Check layout.title (common) if not title and isinstance(obj_layout.get("qHyperCube"), dict): # Check HyperCube title property data_pages = obj_layout["qHyperCube"].get("qDataPages", []) # Get qDataPages, default to [] if data_pages: # Check if the list is not empty title = data_pages[0].get("qTitle", "") # Now it's safe to access [0] if not title and obj_type == 'text-image': # Specific handling for text object title = obj_layout.get("props", {}).get("text", "Text/Image Object (No Title Prop)")[:100] # Use text content as proxy if not title: title = f"Object Type '{obj_type}' (No Title Found)" subtitle = obj_layout.get("subtitle", "") footnote = obj_layout.get("footnote", "") # Tooltip extraction is complex and visualization-dependent. # We'll extract basic tooltip properties if available. tooltip_prop = obj_layout.get("tooltip", {}) tooltip_text = "" if isinstance(tooltip_prop, dict) and tooltip_prop.get("active", False): tooltip_text = f"Title: {tooltip_prop.get('title', 'N/A')}, Desc: {tooltip_prop.get('description', 'N/A')}" elif isinstance(tooltip_prop, str): # Sometimes it might just be a string tooltip_text = tooltip_prop dimensions = [] measures = [] # Check if the object has a HyperCube (typical for charts) if "qHyperCube" in obj_layout and isinstance(obj_layout["qHyperCube"], dict): hc = obj_layout["qHyperCube"] # Dimensions for dim_info in hc.get("qDimensionInfo", []): dim_label = dim_info.get("qFallbackTitle") if not dim_label: dim_label = dim_info.get("qApprMaxGlyphCount") # This is a strange place, but sometimes used? Check qFieldLabels? if not dim_label: # Check for calculated dimension label expression if isinstance(dim_info.get("qDef", {}).get("qLabelExpression"), str): dim_label = f"Expr: {dim_info['qDef']['qLabelExpression']}" else: # Fallback to field labels or definitions field_labels = dim_info.get("qFieldLabels", []) if field_labels and field_labels[0]: dim_label = field_labels[0] else: # Get the primary definition field/expression defs = dim_info.get("qLibraryId") or dim_info.get("qDef", {}).get("qFieldDefs", []) dim_label = str(defs[0]) if defs else "(Complex Dim Def)" dimensions.append((dim_label, "Dimension", "Dimension Name")) # Store the context # Measures for mea_info in hc.get("qMeasureInfo", []): mea_label = mea_info.get("qFallbackTitle") if not mea_label: # Check for calculated measure label expression if isinstance(mea_info.get("qDef", {}).get("qLabelExpression"), str): mea_label = f"Expr: {mea_info['qDef']['qLabelExpression']}" else: # Fallback to definition mea_label = mea_info.get("qLibraryId") or mea_info.get("qDef",{}).get("qDef") or "(Complex Measure Def)" measures.append((mea_label, "Measure", "Measure Name")) # Store the context else: # Handle objects without hypercubes (like text boxes, filter panes) dimensions.append(("(N/A - No HyperCube)", "Dimension", "Dimension Name")) # Store the context measures.append(("(N/A - No HyperCube)", "Measure", "Measure Name")) # Store the context if obj_type == "filterpane": # Extract dimensions from filter panes (different structure) dimensions = [] # Reset for item in obj_layout.get("qListObject", {}).get("qDataPages", [{}])[0].get("qMatrix", []): dim_text = item[0].get("qText", "(Filter Item)") dimensions.append((dim_text, "Dimension", "Dimension Name")) # Store the context if not dimensions: dimensions.append(("(No Items Found in Filter)", "Dimension", "Dimension Name")) # Store the context # Add Text to translation data - Titles and Subtitles if title: variable_name = generate_variable_name(f"{sheet_title}_{obj_id}_Title") translation_data.append({ "SheetName": sheet_title, "Type": "Title", "Context": "Chart Title", "Variable": variable_name, "Text": title, "Translation": "" }) if subtitle: variable_name = generate_variable_name(f"{sheet_title}_{obj_id}_Subtitle") translation_data.append({ "SheetName": sheet_title, "Type": "Subtitle", "Context": "Chart Subtitle", "Variable": variable_name, "Text": subtitle, "Translation": "" }) if footnote: variable_name = generate_variable_name(f"{sheet_title}_{obj_id}_Footnote") translation_data.append({ "SheetName": sheet_title, "Type": "Footnote", "Context": "Chart Footnote", "Variable": variable_name, "Text": footnote, "Translation": "" }) if tooltip_text: variable_name = generate_variable_name(f"{sheet_title}_{obj_id}_Tooltip") translation_data.append({ "SheetName": sheet_title, "Type": "Tooltip", "Context": "Chart Tooltip", "Variable": variable_name, "Text": tooltip_text, "Translation": "" }) # Add Dimensions and Measures for text, type_desc, context_desc in dimensions + measures: variable_name = generate_variable_name(f"{sheet_title}_{obj_id}_{text[:20]}_Label") # Use the first 20 chars of the dimension/measure as part of the name. Keep names shorter. translation_data.append({ "SheetName": sheet_title, "Type": type_desc, "Context": context_desc, # "Dimension Name" or "Measure Name" "Variable": variable_name, "Text": text, "Translation": "" }) # Add text object if obj_type == 'text-image' and obj_layout.get("props", {}).get("text"): text_object_text = obj_layout.get("props", {}).get("text", "") variable_name = generate_variable_name(f"{sheet_title}_{obj_id}_Text") translation_data.append({ "SheetName": sheet_title, "Type": "Text Object", "Context": "Text Object", "Variable": variable_name, "Text": text_object_text, "Translation": "" }) print(f" -> Extracted: Title='{title}', Subtitle='{subtitle}', Dims={len(dimensions)}, Measures={len(measures)}, Tooltip='{tooltip_text}'") # --- Step 11: Close WebSocket Connection --- print("\nClosing WebSocket connection...") ws.close() print("Connection closed.") # --- Step 12: Create Excel File --- if not translation_data: print("No translation data was extracted. Cannot create Excel file.") return print(f"Creating Excel file: {OUTPUT_EXCEL_FILE}") df = pd.DataFrame(translation_data) # Reorder columns for better readability (optional) df = df[["SheetName", "Type", "Context", "Variable", "Text", "Translation"]] df.to_excel(OUTPUT_EXCEL_FILE, index=False, engine='openpyxl') print(f"Successfully saved translation data to {OUTPUT_EXCEL_FILE}") except websocket.WebSocketException as e: print(f"WebSocket Error: {e}") except ConnectionRefusedError: print("Connection Refused: Ensure Qlik Sense Desktop is running.") except Exception as e: print(f"An unexpected error occurred: {e}") import traceback traceback.print_exc() # Print detailed traceback for debugging finally: # Ensure connection is closed even if errors occur if 'ws' in locals() and ws and ws.connected: ws.close() print("Ensured WebSocket connection is closed.") # --- Run the script --- if __name__ == "__main__": main()