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"

       }

   }

}

Border Logic in Dynamic Worksheets

Overview

Borders are a simple yet powerful design tool that enhance the visual structure of worksheets. Understanding the underlying logic of how borders behave not only helps in creating visually appealing worksheets and reports but also reduces time spent debugging unexpected results. Dynamic Worksheets introduce additional configuration options that make border management more flexible and robust.

Basic Border Logic in Worksheets (Separated Borders)

In Dynamic Worksheets, borders are rendered using a separated model. This means that if adjacent cells each have their own border configurations, both borders will be displayed side by side. This applies whether you're viewing a worksheet on an entity (e.g., Test, Batch, etc.) or examining the worksheet from the configuration interface.

In the example below, cells D4:E5 each have double borders applied, and you can see those borders rendered adjacent to one another with no collapsing.

Basic Border Logic When Rendering  (Collapsed Borders)

When a Dynamic Worksheet is rendered in a report, borders use a collapsed model. This helps maintain a cleaner, more polished appearance suitable for finalized reports. In this mode, overlapping borders between adjacent cells are collapsed into a single border, and precedence rules are applied to determine which border is shown.

Border Precedence Rules:

1. Border Width
   Higher widths take precedence over lower widths.

2. Border Style (if widths are equal)
   Precedence follows this order:
   double > solid > dashed > dotted > ridge > outset > groove > inset

3. Position (if style and width are equal)

   • Left borders take precedence over right borders.

   • Top borders take precedence over bottom borders.

Using the worksheet above, the rendered report will show a single border according to these rules—typically the left or top border of the adjacent cells.

Creating and Testing Your Borders  

Legacy Behavior

In older Dynamic Worksheets and regular spreadsheets, a thin gray border is rendered around all cells in reports by default. To achieve a truly borderless look, users can apply white borders manually to each cell—being careful not to unintentionally conflict with other border settings.

New Improvements

Newer Dynamic Worksheets offer a checkbox setting to enable or disable these default gray borders in reports. This simplifies border customization significantly:

• You no longer need to apply white borders to every cell.

• You avoid unintentional border conflicts introduced by default cell outlines.

Best Practice: Use Report Preview

When configuring borders in a worksheet, it is highly recommended to use the built-in report preview feature. This tool allows you to see exactly how your worksheet borders will appear when rendered in a report, making it easier to fine-tune your styling and avoid surprises.

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.