Tuesday, 24 November 2015

Adding comments to an Awesome Table via a toggle sidebar

****Update August 2017 - Due to recent changes by Awesome Table to their background code, this solution no longer will work.   Ill try to revisit this in future to see if a revision to the code below can get this working again, time permitting*****

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.

13 comments:

  1. Wow this is really nice. I'm working on my Awesome Table view. It has a Sidebar as well with a link to Form to update the entry, but right now it opens the form in a new Tab which I don't like. I'm definitely going to use the Javascript trick to do what you did! Also, i'm hitting a snag using the Proxy Script where the Date column formats are returned using their full version (eg, 2/25/2015 12:00:00 AM) rather than just the short version like how my Spreadsheet is formatted. Maybe I can use Javascript to fix that too, using the Utilities.formatDate() func?

    ReplyDelete
  2. Hi. Can you help me. How could I add a comment without concatenating other cells.

    ReplyDelete
  3. Firstly, James, I thank you for the very many useful things that you post! We are using several.

    Over the weekend, I adapted the 'add comments' to an student attendance "interventions" site for our administrators. I have everything working, but I am wondering how to do add one thing.

    Is there a way that I can add the person's name who adds comments to the added comments so that they appear side-by-side?

    I would think that doing so is a straightforward task, but I seem to be missing something.

    Thank you very much,

    Brian.

    ReplyDelete
  4. Hi,

    I would like to track the number of clicks on the links in the Awesome-Table "Card views". I have configured the settings according to the:

    https://sites.google.com/site/scriptsexamples/available-web-apps/awesome-tables/documentation/advanced-tips/link-analytics-to-the-gadget

    The syntax I put in my template looks like this:
    onClick=”ga(‘send’, ‘event’, 'Links', 'click', ‘${"Name"}’);”

    Unfortunately, I couldn't see any event tracking in the Google Analytics.
    Did I miss out something here?
    How do I track the number of clicks on links in Awesome Table?

    Thank you.

    ReplyDelete
    Replies
    1. Hi - Please post up your question to the AT community, and one of the devs there should be able to advise you https://plus.google.com/u/0/communities/117434057513505498243

      Delete
  5. Great. i'm an amateur, but i got it by following your key points.
    i'll try to add hide & expand function in case overloading..
    Thanks JP,

    ReplyDelete
  6. Thanks! But when I use the demo spreadsheet, the table and the google form are too narrow. the position of the close button is in the bottom and the transition of google form window when click on edit is not shown. Any suggestions?

    ReplyDelete
    Replies
    1. Apologies, I have updated the blog now to reflect the fact that this solution no longer works, due to recent changes made by Awesome Table to their background code.

      Delete
    2. Thanks James for clarifying this! I was thinking about the same as I found from other templates that the table has a certain width for sidebar, and it seems cannot be changed. Thanks for the updating.

      Delete
  7. James I knew you were a time traveller updating in the future :August 2018 :)

    I wish this still worked... If i create the vizulisation in html and put the code in a css file think it will still work?

    ReplyDelete
    Replies
    1. lol - cheers, corrected now ;-) I think it is worth a go - I wish I had more time at the moment to explore this myself. Hopefully Ill get a chance to revisit this soon. Let me know how you get on

      Delete