## Tuesday, 24 November 2015

****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
Please feel free to take a copy.

1. Nice Work my Friend (y)

2. 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?

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

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

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

Thank you very much,

Brian.

5. 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:

The syntax I put in my template looks like this:

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.

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

6. Great. i'm an amateur, but i got it by following your key points.