I am excited about a new feature that has landed in chtoolbox! As I demoed recently at a Dev Hour, I have added a new chtx form format command that updates the formatting/styling of a specified .xlsx file with the goals of:
Improve readability/navigation of the form configuration via consistent styling/structuring.
Give real-time feedback to the user as they edit the spreadsheet regarding the validity of their configuration and their options for editing it.
The chtoolbox script achieves this by adding conditional formatting rules to the .xlsx file that dynamically adjust the styling as the configuration in the form changes. You do not have to re-format the file after each edit since the styling changes in real-time while you update the spreadsheet. The formatting is completely passive. Nothing that affects the functionality of the form will be changed/set by running chtx form format.
To try it out, install the latest version of chtoolbox and get started by running chtx form format --help.
Feedback welcome!
Features
Consistent colors
The styling is optimized for readability on either a dark or light theme. The formatting has been tested on both LibreOffice Calc and Google Sheets.
As shown in the screenshots above, the borders of a group or repeat are automatically indicated with a blue or purple boarder respectively. These borders are automatically applied when adding a new group and removed when deleting it.
Prompt options for column header and row type
You can select the value for a new column header or for a new row type from a list of valid options. Headers/types used in the form that are not supported by the CHT are highlighted in red.
The dynamic formatting of the sheet can apply a red style to cells that it knows contain an invalid (or unrecommended) value. This error style will be automatically cleared when the cell’s value is corrected.
See an example here of adding a new text question. The name and label columns are required for text rows and are colored red until a value is set.
See empty columns
Column headers for columns that have no values in any rows (so the column is unused) are marked with italics text.
This is super cool! Anything that makes it easier for developers knee deep in confusing error messages from CHT Conf is most welcome. Nice work!
Feedback:
it would be awesome if each level of a nested group had a different color for the background - this would make the nesting really obvious. colors are hard, so a more simple approach would be to just do a “in a group” vs “not in a group” background color. Another riff on this would be to choose a new background color for each new group (disregarding nesting). this would also be hard for colors that work in both light and dark themes (see current death form, very bottom of this post for alternating group color example)
maybe have hard failure colors (red) and soft failure colors (um…pink?). see my test notes below for when I saw red but CHT Conf converted and uploaded it fine (CHT Conf 6.5.1 to CHT Core @ master)
What a killer feature Prompt options is!!! this really avoids typos by having a known good list of values to use. Excellent work.
I ran this on the existing (and presumably out of date) death_report.xlsxfile. It marked a number of fields as red because they were missing required values or had invalid values:
However, when I converted and uploaded this form, it uploaded fine but did give me a few warnings:
WARN Converted death_report.xlsx with warnings:
WARN Language 'default' is missing the survey columns constraint_message, hint, label.
Language 'en' is missing the survey image column.
Language 'es' is missing the survey image column.
Language 'fr' is missing the survey image column.
Language 'hi' is missing the survey image column.
Language 'id' is missing the survey image column.
Language 'ne' is missing the survey image column.
Language 'sw' is missing the survey image column.
WARN The "NO_LABEL/DELETE_THIS_LINE" value is deprecated and will be removed in a future version of cht-conf. For groups, a label is not required. If the field should not be visible, use the "hidden" or "calculate" type.
deleting all the NO_LABEL , deleting group labels and deleting hidden labels then made all the red go away in the spreadsheet, but the I different errors when I went to upload it:
WARN Language 'default' is missing the survey columns constraint_message, hint, label.
Language 'en' is missing the survey image column.
Language 'es' is missing the survey image column.
Language 'fr' is missing the survey image column.
Language 'hi' is missing the survey image column.
Language 'id' is missing the survey image column.
Language 'ne' is missing the survey image column.
Language 'sw' is missing the survey image column.
WARN [row : 12] Group has no label: {'name': 'parent', 'type': 'begin_group'}
WARN [row : 14] Group has no label: {'name': 'parent', 'type': 'begin_group'}
WARN [row : 40] Group has no label: {'name': 'group_review', 'type': 'begin_group'}
WARN [row : 52] Group has no label: {'name': 'data', 'type': 'begin_group'}
WARN [row : 57] Group has no label: {'name': 'meta', 'type': 'begin_group'}
for reference, here’s how the colors started - these colors are opaque so work in both light and dark themes
Yeah I agree there is a lot of room for improvement on this side of things. It boils down to:
Is it simple enough to implement dynamically in xlsx conditional formatting? (Using chtx to programmatically produce the formatting is a game-changer here, but there still is a point of diminishing returns for complexity.
Does it actually make it easier to read? I purposely avoided background colors on anything but the header row because I found it just added too much extra noise/weight.
If the background colors are consistent, they can make it easier to quickly skim a form, but for a heavy editing session it just slowed me down. YMMV In the past I experimented with different colors across nested groups and it just ended up feeling more confusing.
Having different colors for inside vs outside a group would probably not be super useful either since 95% of fields are in some kind of group (besides top-level calculates).
One approach that does interest me is having a single background color for groups (e.g. blue) and for top-level groups it is a very faint shade. Then, with every nested group layer the shade of blue becomes more and more intense, giving a sort of heat-map effect where the deepest nested rows are the brightest. I think that could really help with quickly groking the form structure without getting lost in a rainbow of color. The main downside I see with that is it would be radically more complicated than my current approach since the background for each cell would be context-dependent on all the other cells (vs my current approach which just looks to see if the type of the current row…).
This is a good idea!
Yeah, as you pointed it, it might be more helpful to have a “Warning” color for things that are deprecated/unnecessary, but not broken. A lot of our default forms are a mess of deprecated stuff. I am working on a PR to clean it up, but it would be more helpful as a user to see the severity at a glace instead of just a sea of red (alert fatigue anyone?).
Technically the “Group has no label” messages are warnings, not errors. But, yeah, we are a bit premature on this. I need to get us updated to the latest pyxform version because they have now removed that warning (thanks to my prompting). (We are already somewhat committed to this direction, though, because the WARN The "NO_LABEL/DELETE_THIS_LINE" value is deprecated. message is coming from a cht-conf validation…)
Thanks a bunch for taking the time to dig in on this and share detailed feedback! It is super helpful. I am very interested to do more experimenting with group formatting as well as well as to implement a Warning level color.
But, yeah, we are a bit premature on this. I need to get us updated to the latest pyxform version because they have now removed that warning (thanks to my prompting). 