Daily Notices with Awesome Table Gadget

Teachers need a simple method of creating a message to be delivered to a particular audience in the school. The message needs to be available for a range of days and disappear when those dates have passed. The format of the messages needs to be uniformly attractive and readable so I needed a way to apply my design aesthetic to the teachers' messages automatically after they have been submitted.

My solution has been to use a Google Form to capture the message to a GSheet, and use the Awesome Table Gadget to present the notices to the school on a Google Sites page.



I have made use of the edit message script supplied by Romain Vialard and his team, along with their new template functionality. The template has allowed me to use CSS to style the messages and make them look uniform, but staff with a basic understanding of embedding and tags can make their messages more interesting if desired by embedding images or GSlides set to loop or static for a poster.

The CSS below is used to build the messages shown above from the "Form responses 1" sheet created from the Form submission. The tags in bold are the column headings from the Form submissions.

Notice

<div style="margin:10px"> <span style="font-size:2em;font-weight: bold;color:#008d4f;">${"Title of Notice"}</span><br> <span style="font-size:1.2em;font-weight: normal;color:#008d4f;">A message for <span style="font-weight: bold;">${"Notice for the attention of..."}</span> from </span><span style="font-size:1.2em;font-weight: normal; color:red;">${"Teacher Name"}</span> </div> <div style="margin:10px"> <span style="font-size:1.5em;font-weight: normal;">${"Notice Information"}</span><br><br> </div>

Awesome Table Gadget shows all the rows within the set range i.e. it lists every row in the sheet and therefore, eevry notice. For the daily notices to work, I needed the messages that were out of date to be removed. Awesome Table doesn't show the rows that are hidden so I used the script below, triggered nightly, to hide the out-of-date messages; I chose not to delete them in case of any issues with the system.

function hideNotice() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Form responses 1");
var datarange = sheet.getDataRange();
var lastrow = datarange.getLastRow();
var values = datarange.getValues();// get all data in a 2D array
var dNow = new Date();
var curDate = new Date(dNow.getFullYear(), dNow.getMonth(), dNow.getDate(), 0, 0, 0, 0);
for (i=lastrow;i>=2;i--) {
var enddate = values[i-1][7];// arrays are 0 indexed so row1 = values[0] and col3 = [2]
if(enddate < curDate)
{
  sheet.hideRows(i);
}
}
}

The GSite shows teachers the tabs they need in order to create notices and edit their previously posted, and still visible, messages. The GSite also has another tab for students that uses Awesome Table to present the same message but without the column with the edit button. This is achieved by using 2 instances of Awesome Table Gadget: one for the teachers with the edit button column visible, and one for the students without the edit button column. By using the Page Level Permissions, I can show the teachers the tabs they need to create a message, while showing the students only the messages.