Tuesday, 24 November 2015

Adding comments to an Awesome Table via a toggle sidebar

This solution came about from a requirement we had for colleagues to easily add updates and comments to a monthly report which was itself refreshed each month with new data. As the data changed in the report it was necessary to find a way to get the existing comments to be kept and to show against the appropriate matching lines in the new data set. To do this, I used an Awesome Table, with a sidebar, linked to a form which could be toggled to show and hide. The form has a few fields which are pre-filled depending on the line of data, so that the comments then get matched up and shown in the table.

A demo of the solution is below, using some dummy data  (a copy of the spreadsheet can be found at the bottom of the post)



To start I created a Google Sheet with the data in and a Google form to collect the comments

To get a unique value per row that can match against the appropriate comment, I concatenated the first three columns of my data  (Trust, site and study ID).  The formula puts the title of the column in the first cell, 'Nofilter' in the second cell (used to define the Awesome Table filter) and in the remaining rows with data, the concatenation:

=ArrayFormula(IF(ROW(A:A)=1,"Concatenate",IF(ROW(A:A)=2,"NoFilter",IF(LEN(D1:D),(D1:D&"|"&C1:C&"|"&B1:B),IFERROR(1/0)))))

with a similar formula on the form result sheet, any matching results can be brought in with a query, but with a join function, to combine multiple matching entries, sorting the matching comments by most recent.

=join("",query(Comments!A$2:H,"select G Where A = """&A3&""" order by B desc"))

(NB - one annoyance is that the query cannot be combined with an array formula, so this formula is filled down in a standard way. If the data is submitted via a form as well, then an Importrange or a query function can always be used to bring in that data to another sheet which would then be used for the remaining calcs.)

The returned column combines the date stamp, formatted to show just the date, with the comment, and some HTML line breaks so that this displays well in the Awesome Table, which was obtained by the following formula.

=ArrayFormula(TEXT(B2:B," dd/mm/yy - ")&F2:F&"<br/><br/>")


Awesome Tables now use Templates  which is a fantastic new feature.  In the template I added
what would appear in the sidebar, which is an iframe of the form, prefilling three of the fields using the data in the rows.

<iframe Height="610px" src="https://docs.google.com/a/nihr.ac.uk/forms/d/abcdefghijklmnop123456789/viewform?entry.1027802729=${"Study ID"}&entry.1197506384=${"Site Name"}&entry.1805865420=${"Trust Name"}"></iframe>

I then added some JavaScript to the template so that when a button is clicked, the sidebar is shown or hidden, with the table width expanded or reduced accordingly.

function changeClass()    {  
document.getElementById("sidebar").className = "sideactive";
document.getElementById("parentChart1").className = "mainactive"; 
document.getElementById("parentChart1").setAttribute('style', 'width: 74%!important;height 1200px!important'); 
 }   
function changeClassBack(){
document.getElementById("sidebar").className = "sideinactive";
document.getElementById("parentChart1").className = "maininactive"; 
document.getElementById("parentChart1").setAttribute('style', 'width: 100%!important'); 
}

The spreadsheet used for this demo can be found here
https://docs.google.com/spreadsheets/d/1qFr7dJ9ibdz4p2T6iJeD1u2pVmsrFMmEu2MCB1Ho7pQ
Please feel free to take a copy.