Tricks with Awesome Tables, Progress Bars, custom css and filtering Numbers

I am a huge fan of Romain Vialard's work especially his "Awesome Table Gadget" and use these extensively in my site for reporting.  This blog post shows some enhancements I have made to the out of the box gadget - my aim was to add coloured progress bars to demonstrate achievement to KPIs, to change the look and feel with some custom css, and to enable the table to be filtered by searching in a number field.

A demo of the completed table is here, which contains some dummy data.

Progress Bars

As the Awesome Tables can take HTML code and render this in the table, I wanted to produce a sample output such as this

The HTML code for this bar would be
"<div class=""PercentageBar_amber"">
<div class=""percent"" style=""width: 73&#37;;""><div class=""number"">73%</div></div>"

(The #37; is an encoded character for a % symbol which will render correctly in the table. The bar will have the class PercentageBar_amber - which allows for some custom css (which will be entered later) to target this div and change the background colour.)

To build this string up in the source sheet, I just used a formula to join together the sheet data and some HTML strings.

Custom CSS

For some tables, I wanted to make tweaks such as make the font size smaller, to enable more data to fit in to the view - for this example, I wanted to make two changes, firstly to create and colour the progress bars according to their rating, and secondly to change the colour of the header to look a bit better with the red template on my site.

To do this, I took first some sample css from Romain such as this one and then added some custom css at the end.  

/* Colour the header in red at the top and bottom */

.google-visualization-table-tr-head .gradient, .google-visualization-table-tr-head-nonstrict .gradient, .google-visualization-table-div-page .gradient {
    background: linear-gradient(to bottom, rgba(255, 78, 77, 0.95) 2%, rgba(255, 78, 77, 0.01) 7%, rgba(255, 78, 77, 0.04) 95%, rgba(255, 78, 77, 0.88) 6%) repeat scroll 0 0 rgba(42, 93, 41, 0) !important;
}


/* Progress Bars */

			.PercentageBar_red {position: relative; font-size: small; width: 100px; height: 14px; margin: 1px;}
			.PercentageBar_red DIV {height: 9px; line-height: 6px;}
			.PercentageBar_red .percent {position: relative; background-color: #ee7980; left: 0px top: 0px; z-index: 0;border: 1px solid;}
			.PercentageBar_red .caption {position: relative; text-align: center; color: #000; z-index: 1;}
			
			
			.PercentageBar_green {position: relative; font-size: small; width: 100px; height: 14px; margin: 1px;}
			.PercentageBar_green DIV {height: 9px; line-height: 6px;}
			.PercentageBar_green .percent {position: relative; background-color: #55e84c; left: 0px top: 0px; z-index: 0;border: 1px solid;}
			.PercentageBar_green .caption {position: relative; text-align: center; color: #000; z-index: 1;}


			.PercentageBar_amber {position: relative; font-size: small; width: 100px; height: 14px; margin: 1px;}
			.PercentageBar_amber DIV {height: 9px; line-height: 6px;}
			.PercentageBar_amber .percent {position: relative; background-color: #fcc564; left: 0px top: 0px; z-index: 0;border: 1px solid;}
			.PercentageBar_amber .caption {position: relative; text-align: center; color: #000; z-index: 1;}
			
						.PercentageBar_black {position: relative; font-size: small; width: 100px; height: 14px; margin: 1px;}
			.PercentageBar_black DIV {height: 9px; line-height: 6px;}
			.PercentageBar_black .percent {position: relative; background-color: #000000; left: 0px top: 0px; z-index: 0;border: 1px solid;}
			.PercentageBar_black .caption {position: relative; text-align: center; color: #000; z-index: 1;}
			
						.PercentageBar_blue {position: relative; font-size: small; width: 100px; height: 14px; margin: 1px;}
			.PercentageBar_blue DIV {height: 9px; line-height: 6px;}
			.PercentageBar_blue .percent {position: relative; background-color: #91C0EF; left: 0px top: 0px; z-index: 0;border: 1px solid;}
			.PercentageBar_blue .caption {position: relative; text-align: center; color: #000; z-index: 1;}
			
	.number {
    font-size: small;
    position: absolute;
    text-align: right;
}

In the Awesome table setup screen, you can add this custom css in. I just saved the css file to my Google Drive in a public folder and found the published URL. Please see my blog post here if you need more details on how to do this (it works the same for css as gadgets)

Searching a Number field

One small annoyance with Awesome tables, is that if you have a numerical field, you can only have a filter that is a range filter (like a slider) or not filter it at all.  This may be appropriate sometimes, but for situations where you need to look for something like a Study ID or a part number for example, it really does not work well.  The workaround I have found works is as follows

In my Google Sheet, I have new column with a  formula which concatenates some text e.g. "StudyID" and the actual number. So instead of 123 this becomes StudyID123

Filter terms in an Awesome table can be combined, for example,  "CategoryFilter - Hidden" will do a dropdown filter for that column, but wont show that column in the table.   The trick therefore is to have your original number column as "NoFilter"  (so it displays in the table)  and for the newly created concatenated column, the filter "StringFilter - Hidden".  

With this in place, you can then search for rows containing a specific number