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. 

Redefining the Exercise Book

I had the pleasure of presenting at the Deep Learning Malaysia conference where I talked about using Google Slides as an eBook. I paid attention mostly to the application of the SAMR model in order to demonstrate how I was using the technology to change the task and make possible the previously impossible. My main focus for the eBooks is to enhance tmy students' presentation and collaborative skills; you will find links to my rubrics and templates that I use with Doctopus and Goobric.

Google Sites as an LMS/VLE

I have seen various uses of Google Sites as a resource repository, but have yet to see it work as a true LMS where data is fed into a database and retrieved from a database the way most VLEs like Moodle, Firefly, and Schoology do. I decided that Sites needed more attnetion in regard to data management and have set myself the goal of replicating VLE behaviour using Sites, Sheets and whatever gadgets and coding is required. 

My first foray into using Gsheets as a database was with Andrew Stillman's Reportlet apps script (I had to request the code for Reportlet directly from Andrew). Reportlet does a nice job of representing info from a sheet and charting data, however it can take some 30 seconds to load. After communicating with Andrew with the hope that New Visions would invest effort into tuning the script, it seems that Reportlet is not part of their development program.

Over my next posts on this topic, I will demonstrate how I have used Awesome Table Gadget to pull data from Gsheets to present specific data to the logged in user. I will show how Apps Sheet can be used to build apps for users to log into the same data on Android, IOS and through a web app. Finally, I will demonstrate how to post data to a GSheet using a simple form (based on the work of Martin Hawksey.)

In order for Sites and Sheets to work like an LMS, the data in the GSheet for each student must be as up-to-date as possible; most VLEs do an extraction nightly so that data stored that day is available for the next morning. For this to work, my Student IMS will need to run a scheduled report each evening that exports as a spreadsheet or html file, so that the import functions in Gsheets can refresh each night automatically. The export and import of data from the IMS is the final phase of feasibility testing and should work in theory!