Dynamic Worksheets vs. Regular Spreadsheets

Dynamic Worksheets and regular spreadsheets each serve unique purposes, designed to meet varying data management needs. Below is a comparison:

Dynamic Worksheets

• Features:
  • Can have multiple sheets (like Excel workbooks).
  • Can import directly from Excel files.
  • Can define ranges of named cells.
  • Better suited for larger datasets.
  • Handles more complex data and calculations.

Regular Spreadsheets

• Features:
  • Limited to a single sheet.
  • Can only import JSON files.
  • Supports single named cells but not ranges.
  • Better for simpler, smaller worksheets.

Key Features Common to Both

• Support dollar sign references to pull in dynamic data.
• Conditional formatting for visual customization.
• Cell customization options (dropdowns, date pickers, etc.).
• Versioning capabilities for tracking changes.
• Support formulas and calculations.
• Export ranges to reports and customer portals.

Choosing the Right Tool

The primary benefit of Dynamic Worksheets lies in their ability to manage complex data and large datasets. In contrast, regular spreadsheets are more suitable for straightforward tasks. Users can start with a regular spreadsheet and upgrade it to a Dynamic Worksheet using the "Upgrade to Dynamic Spreadsheet" button if additional functionality is needed. This flexible system ensures users can adjust their approach as requirements evolve over time.

API Integration for Dynamic Worksheets

API V2 - Getting Worksheet Data

Test Worksheet Data

To retrieve worksheet data for a test, make a GET request to:

GET https://demo.qbench.net/qbench/api/v2/tests/{test_id}/worksheet/data

The response includes:

• _type: Entity type identifier
• _self: Self-referential URL
• data: Contains the worksheet information including:
  • id: Test identifier
  • worksheet_data: The defined Named Cell data
  • worksheet_processed: Processing status flag
  • worksheet_processed_error: Error status flag
  • worksheet_processed_error_msg: Error message if applicable

Query Parameters:

• raw_worksheet_data (boolean): Include raw worksheet data.
• worksheet_config (boolean): Include worksheet configuration.

Example Response:

{

   "_type": "test",

   "_self": "api/v2/tests/12/worksheet/data", 

   "data": {

       "id": 12,

       "worksheet_data": {

           "result_1": {"value": 123},

           "result_2": {"value": "abc"}

       },

       "worksheet_processed": true,

       "worksheet_processed_error": false,

       "worksheet_processed_error_msg": null

   }

}

Batch Worksheet Data

To retrieve worksheet data for a batch, make a GET request to:

GET https://demo.qbench.net/qbench/api/v2/batches/{batch_id}/worksheet/data

The response includes:

• _type: Entity type identifier
• _self: Self-referential URL
• data: Contains the worksheet information including:
  • id: Batch identifier
  • worksheet_data: The defined Named Cell data

Query Parameters:

• raw_worksheet_data (boolean): Include raw worksheet data.
• worksheet_config (boolean): Include worksheet configuration.

Example Response:

{

   "_type": "batch",

   "_self": "api/v2/batches/12/worksheet/data",

   "data": {

       "id": 12,

       "worksheet_data": {

           "result_1": {"value": 123},

           "result_2": {"value": "abc"}

       }

   }

}

For both endpoints, including raw_worksheet_data=true adds raw worksheet data to the response:

{

   "raw_worksheet_data": {

       "Sheet 1": {

           "A1": 123,

           "B4": "abc"

       }

   }

}

Dynamic Worksheets in QBench: Latest Updates and Features

Dynamic Worksheets continue to evolve, offering laboratories advanced tools to manage data dynamically. Below is a comprehensive summary of the most recent enhancements and features, ensuring your lab remains efficient and productive. These updates are presented chronologically, with the most recent enhancements listed first.

Latest Updates (Newest Features)

1. Export to Excel

• Released in v2.70
  Dynamic Worksheet data can now be exported directly to Excel. This feature simplifies sharing and external analysis, ensuring compatibility with widely used data processing tools.

2. Range-Based Cell Updates

• Released in v2.68
  Automations now allow updating or clearing a range of cells within Dynamic Worksheets. Users can also reference named ranges to target specific areas for updates, enhancing flexibility and reducing manual efforts.

3. Worksheet Reason Required

• Released in v2.69
  Users can now enable a “Reason Required” setting for Dynamic Worksheets. This option prompts users to provide a reason whenever updates are made, improving traceability and accountability.

4. Protocol-Level Support

• Released in v2.69
  Dynamic Worksheets are now compatible with Protocol worksheets at both the batch and test levels. This enhancement supports seamless migration from legacy Spreadsheet Worksheets, improving workflow integration.

5. Images in Dynamic Worksheets

• Released in v2.75
  Users can now embed images directly into Dynamic Worksheets. This feature allows for richer data representation in tests and reports, enhancing the utility of worksheets.

6. Freeze Rows/Columns

• Released in v2.74
  Users can freeze specific rows or columns, improving navigation within large datasets. This enhancement keeps headers or key data visible while scrolling.

7. Improved Load Times for Large Worksheets

• Released in v2.74
  Optimized load times for Dynamic Worksheets handling large datasets. This ensures faster access and smoother user experiences.

8. Ignore Empty Rows in Report Rendering

• Released in v2.78
  Rendered worksheets now offer the option to ignore rows without values, resulting in cleaner and more concise reports.

9. Unsaved Changes Warning

• Released in v2.70
  Dynamic Worksheets now display an alert when there are unsaved changes, helping prevent accidental data loss.

10. Support for Multiple Export Ranges

• Released in v2.73
  Dynamic Worksheets now allow defining multiple export ranges, providing greater flexibility in generating reports and sharing specific data subsets.

11. Named Cells Enhancements

• Released in v2.73
  Named cells in Dynamic Worksheets now support expanded configuration options, allowing users to mark fields as exportable for easier integration with reports and automations.

Earlier Updates

12. Freeze Rows/Columns in Spreadsheet Worksheets

• Released in v2.59
  The ability to freeze rows and columns was introduced, making it easier to navigate larger datasets and keep key headers visible. This feature was initially added for regular Spreadsheet Worksheets but laid the groundwork for enhanced navigation in Dynamic Worksheets.

13. Preview Dynamic Worksheets on the Worksheet Config Page

• Released in v2.59
  Dynamic Worksheets can now be previewed directly on the Worksheet Configuration page, making it easier to verify setup and configurations.

14. Render Dynamic Worksheets in Reports

• Released in v2.59
  Dynamic Worksheets are now compatible with report templates, allowing their data to be rendered seamlessly within reports\.

15. Batch-Level Dynamic Worksheets

• Released in v2.65
  Dynamic Worksheets now support batch-level operations, extending their usability beyond test-level workflows. This feature enhances data organization and processing for batch-based tasks.

16. In-App Automations Support

• Released in v2.66
  Dynamic Worksheets now fully integrate with in-app Automations, allowing users to update worksheet data automatically based on defined triggers. Data can seamlessly flow between Batch and Test worksheets without requiring programming skills.

17. File Parser Integration

• Released in v2.66
  No-Code File Parsers can now send data directly into specific tabs within Dynamic Worksheets. This improvement supports better data organization, such as separating raw data from calculated results or reports.

18. KV Store Integration

• Released in v2.66
  Dynamic Worksheets can now fetch values from the Key/Value (KV) Store. This feature allows users to centralize and dynamically retrieve critical values like Limits of Detection (LoD) or Limits of Quantitation (LoQ) for consistent data analysis across worksheets.

19. Configurable Reporting Ranges

• Released in v2.66
  Users can now specify reportable export ranges within Dynamic Worksheets. These ranges are rendered in reports, improving data precision and customization capabilities

20. Dynamic Controls Integration

• Released in v2.66
  Controls can now be mapped to named cells or ranges within Dynamic Worksheets. This integration supports quality control workflows, enabling the generation of QC result charts directly from worksheet data.

Bug Fixes for Dynamic Worksheets

Addressed Bugs

1. Preventing Loss of Dollar References
   
   
   • Issue where dollar references were lost after updating worksheets resolved in v2.66.
2. Improved Performance for Large Datasets
   
   
   • Optimization issues leading to sluggish performance resolved in v2.74.
3. Conditional Formatting Issues
   
   
   • Fixed inconsistency where conditional formatting was not applied across all tabs in v2.66.
4. API Compatibility
   
   
   • Fixed API errors where certain worksheet data configurations failed to return correct results in v2.69.
5. Named Cell Errors
   
   
   • Addressed issues where named cells with specific configurations were not accessible in reports in v2.73.