Thursday, 24 July 2014

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





25 comments:

  1. i am actually very interested in how you made those buttons for page number, instead of the default next page, next page...

    ReplyDelete
    Replies
    1. Hi - the css used is here - just add to your Awesome Table via the "Custom CSS (URL)" option in the table setup https://342e852e291c4ec2d6331fba2de5db72377c1ee3.googledrive.com/host/0BysVSZMkrof5OWlibzRnVkVEMTg/lifecyclebarsforExternalDemo.css

      Delete
    2. works like a charm, you guys are awesome!

      Delete
  2. This is a really great enhancement for Awesome Tables!

    I'd like to adapt it for a project I'm working on, but I'm having trouble.

    I have "Status" in column D - values are "Active", "Proposed", etc. "Health" is in column H - values are "Green", "Yellow", or "Red". I want to display the "Status" against the background color of "Health" for each row in the Awesome Table - similar to what you've done with the Progress Bar, only not numbers.

    Could you outline the array formula and the css I would use? I'm not a programmer, but I use Awesome Tables and other apps all the time. I'm picking up some of the code and syntax and can usually get minor adjustments to work...not having luck with this one, though.

    ReplyDelete
    Replies
    1. Hi Michelle - glad it is useful. For your request, is is better these days to take advantage of the 'Template' feature of Awesome Tables, where you don't need to use formulae on each line, as the template can be used to do this for all lines. Here's a rough and ready demo with the columns and data you outlined in your post https://awesome-table.com/-K14_OWugp76edauc2C2/edit - the table settings and the demo sheet should be visible for you there. All you would need to do for your site would be to add some more categories to the style column in the template (using HTML color codes if you prefer) and it should all work. Hope this helps!

      Delete
  3. Hi James, really nice work. I tried the extra css, looks great. The only issue I found is that the pagination at the bottom side does not seem to work. The one at the top of the table works fine. Any idea how to fix. Thanks in advance, Luit

    ReplyDelete
    Replies
    1. Hi Luit, glad it is of use. Think this may be a bug with Awesome Tables rather than the extra css being used. I have reported this here: https://plus.google.com/+JamesPearson_Information_Manager/posts/SxKxmw7gomG

      Delete
  4. Hi James,

    May I know how to change the StringFilter to filter data with 'prefix' MatchType?
    Thank you.

    ReplyDelete
  5. Hi - its best these days to use Awesome Table's new template method
    https://sites.google.com/site/scriptsexamples/home/announcements/awesometabletemplatesmoreawesomeness
    This will enable you for example in your template to do for example ABCDE ${"columnwithdata"} so that this transforms into e.g. ABCDE 123456. The StringFilter will then work with the prefix, with no need to add an extra column. More info can be found in the AT community
    https://plus.google.com/communities/117434057513505498243
    Hope this helps

    ReplyDelete
    Replies
    1. Hi, thanks for the reply.

      The default MatchType for StringFilter is set as 'ANY' where it matches any substring. However, I would like to change the MatchType to 'PREFIX' where it matches prefixes starting from the beginning of the value, e.g. when I type 'nic' it should return employees' name 'Nicole' or 'Nicholas' instead of 'Monica'.

      Any ideas how to change the MatchType of the StringFilter?

      Thanks.

      Delete
    2. Hi, I understand what you require, I am afraid though there doesn't seem to be this functionality at the moment and it does not appear possible to change the behaviour of the filter via a template or another means, My recommendation would be post on the Awesome table forum, which is monitored by AT developers https://plus.google.com/communities/117434057513505498243 There is also a link on that forum to a functionality request board where if the functionality is not currently possible, you can request this feature. Hope this helps

      Delete
    3. Hi, thanks and appreciate the explanations.
      I will try to post on the forum.
      Thank you very much.

      Delete
  6. James do you do any freelance work to help with google sheet i have?

    ReplyDelete
    Replies
    1. Hi - sure, please drop me an email - jim.pearson@gmail.com

      Delete
  7. This comment has been removed by the author.

    ReplyDelete
  8. This comment has been removed by the author.

    ReplyDelete
  9. Hi James,

    Thanks for sharing your work! I'm interested in something that might be pretty mundane. When I filter by Division in your table, that limits the possible values for MainSpecialty. I would like to set up a relationship like this in my Awesome Table, but I do not know how to do this. Could you give me some guidance?

    Thanks,
    Jeremy

    ReplyDelete
    Replies
    1. Hi Jeremy - sure this is an out of the box feature of Awesome tables, instructions are on this page under the heading "Dependent Category Filters"
      https://sites.google.com/site/scriptsexamples/available-web-apps/awesome-tables/documentation/basic-configuration/available-filters#TOC-Dependent-Category-Filters

      Delete
  10. First of all... thanks James for such a wonderful and useful article!

    Is there a code available to change stringfilter box height and width?

    Also for category filter box height,width and default label text font size increse.

    ReplyDelete
    Replies
    1. Actually i have created separate webpage on my site and my mobile app external link is referring to this page

      And i want to display my awesome table page with increased size so that my mobile users will see data properly in increased size

      Delete
    2. Please could you help tweak this code for me and suggest if we tweak any classes or subclasses for string and category filter.

      I could achieve few customization to my string and category filter and increased its label font size and input box size.

      But still i am not satisfied with this customization. When we finish entering search text and go to another filter, the first filter searched text becomes so small and it is difficult to read that.

      Code i have tweaked is as below.


      =========================

      .google-visualization-controls-label {
      color:#333;

      font-size:25pt!important;
      cursor: pointer;



      }




      /** StringFilter **/
      .google-visualization-controls-label{
      border:3px solid #d9d9d9!important;
      color:#333;
      height:70px;
      font-size:35pt!important;
      }


      .google-visualization-controls-stringfilter {
      border:3px solid #d9d9d9!important;
      color:#333;;
      height:70px;
      width:800px;
      font-size:25pt!important;
      }

      .google-visualization-controls-stringfilter INPUT {
      border:1px solid #ffffff!important;
      color:#000000;
      font-size:25pt!important;
      width:795px;
      height:50px;


      }

      .google-visualization-controls-stringfilter INPUT:focus {
      border: 000.1px #ffffff;
      height:50px;
      width:790px;
      font-size:35pt!important;
      background:#ffffff;
      color:#000000;
      }

      ========================================


      div.awt-vis-controls-csvFilter .awt-csvFilter-button {
      background-color: #d9d9d9;
      border:3px solid #d9d9d9;
      }

      .awt-csvFilter-button{
      color: #222 !important;

      background-color:#ffffff;
      text-align: center !important;
      height:80px;
      font-size:25pt!important;
      width=800px;
      }

      .charts-menu{
      border:3px solid #333;
      overflow-y:scroll;
      width:800px;
      font-size:35pt!important;

      }

      .charts-menuitem-highlight {
      border:3px solid #FFFFCC!;
      background-color:#FFFFCC!important;
      font-size:35pt!important;
      }

      .awt-vis-controls-csvFilter .awt-csvFilter.charts-menu-inner-box {
      background-color: #44444F!important;
      }

      =============================================================


      Delete
  11. Hi James, I have a table where Column H contains text. This is where I user StringFilter on. I have DateFilter and Category Filter on Columns A - G, as well as a few other columns after H. So the result is that the string filter is buried in the middle of Category Filters - which is not very pleasing to the eye.

    Any suggestion / workaround on how I can move the String Filter at the very first without changing the table column?

    See screenshot here:
    https://www.dropbox.com/s/ahwn1z2p19o328z/Filters.PNG?dl=0

    ReplyDelete
    Replies
    1. Hi - certainly. In your awesome table setup, go to advanced parameters, then in the Query box, you can put in a select query to change the order of the columns. e.g. (without the quotes) "Select F,A,B,C,D,E" Its a useful trick, as you can also use 'Where' clauses etc to hardcode filter views.

      Delete
    2. Hi - certainly. In your awesome table setup, go to advanced parameters, then in the Query box, you can put in a select query to change the order of the columns. e.g. (without the quotes) "Select F,A,B,C,D,E" Its a useful trick, as you can also use 'Where' clauses etc to hardcode filter views.

      Delete