This tutorial is meant to show an alternative way to display wide table data in portrait mode.
It uses YAML.
YAML is a data serialization language that is often used for writing configuration files. Depending on whom you ask, YAML stands for yet another markup language or YAML ain't markup language (a recursive acronym), which emphasizes that YAML is for data, not documents.
This tutorial is browse-oriented, and centers around a value procedure that can take a table (row of rows) with a header row, and transform that into an Elements list suitable for use in a List Picker, List Viewer, or Spinner.
The sample data for this exercise is a half baked set of Google Sheets centered around ordering Pizza from a small Pizza shop.
This app is meant to be general purpose, with 2 restrictions:
- a sheet named Index should list the other sheet names in column A
- Each sheet should have a header row, with decent column names.
Given this setup, this app can browse all the sheets in a Google Sheet with no further customization of the app.
Sample data:
This is my Index sheet, with my sheet names in the first column. The other columns are for possible future use in a permission system.
This is a sheet meant to hold pizza orders, that has suffered some row deletions and lost any link to the order detail sheet ...
Order detail sheet:
Notice how this sheet holds detail rows that refer back to long lost rows 8 and 9 from the OrderData sheet. This happens because Google Sheets don't enforce relational integrity. Given that lack of enforcement, one way to work around that is to use Google Sheets as a logging mechanism, with no deletion, and by denormalizing (widening) detail sheets with extra columns copied from their master sheets.
Sample runs:
Just loaded Menu sheet, before opening its Elements in a List Picker
Fragment of an opened List Picker Elements list for the Menu sheet, rows 7-12.
Notice how some or the rows returned by the Sheets component are short, lacking a Comment value. For lack of appropriate default values, they get left out of the YAML conversion in some of the Elements.
Here is the app, with a sheet you can browse.
sheets_to_YAML.aia (7.4 KB)
The SheetsDict global is for future use as a cache.
It can be ignored for now.
YAML_Elements is the heart of the app.
It takes the table returned by the AI2 ReadSheet block and extracts the header, with some validation. It then intermixes the headers with the ensuing row items to create YAML Elements.
Note several features of the conversion:
- the row number is taken by item number and added to the YAML output, for future use in updates
- Extra data columns without headers are ignored.
- Missing end of row columns are also ignored.
Credits to Taifun for the parse procedure used to switch sheets:
Things not covered in this post:
- How to update from a YAML Element, given lack of data type hints or relational integrity hints.
- How to add rows to a sheet
Summary
Tables are wide, and there have been many attempts to show them lined up in Web Viewers.
This is a blocks only approach using List Elements in Portrait mode.
