Implementation for a "Daily Notices" for a school that wants to use GAFE exclusively. Use a google form to populate the entries.

Daily Notices.md

Manage your daily notices, or bulletin, entirely within your GAFE, no servers or databases required. This is done by creating a Google Form, and then adding the following scripts to the responses sheet that the form populates onto. Optionally create a Google Site to publish the notices.

Workflow:

• Users enter into a Google form, and they get picked up and sent to staff either:
  • published onto a Google Site
  • sent out by email
  • both (published onto a site, and then email with link sent out)
• Users can enter notices in advance
• Users can enter notices for a date range
• Have any number of sections ("Whole School", "Secondary", etc.), and notices are collated by those sections
• Users (or secretaries) can edit them at any time

Collation:

• Notices that are newer appear higher than older notices
• Notices posted by priority usernames appear higher than those that are not

Format:

• Notices are organized by sections
• Users can use enter HTML into their notices, using Markdown syntax
• Response sheet gets an "Easy Editor" sidepanel to edit notices

Extras:

• Response sheet indicates notices that have expired (so they can be deleted / sorted out easily)
• Response sheet upon opening sorts by expiry, then by start date, simplifying finding most recent notices
• Response sheet provides a sidebar panel for easy editing of the notices
• Response sheet gives html preview of what how the notice will be published

Admin.gs

function onOpen() {  
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Actions')
      .addItem('Easy Editor', 'openSidebar')
      .addToUi();
      
  ui.alert(
     'How this works:',
     'Items can be freely edited, and changes will be reflected when nnotices are sent out. There is an "Easy Editor" option in the "Actions" menu, giving a more intuitive way to change the content of notices.\n\nAlso, the database keeps track of which are expired items (past the end date)\n\nUpon opening it automatically sorts, first by expiry, then by start date.',
      ui.ButtonSet.OK);

  getSpreadsheetRange().sort( [
    { column : indexPast + 1 },
    { column : indexStartDate + 1 }
  ]);
}

function openSidebar() {
  var html = HtmlService.createTemplateFromFile('Sidebar')
      .evaluate()
      .setSandboxMode(HtmlService.SandboxMode.IFRAME)
      .setTitle('Easy Editor')
      .setWidth(300);
  SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
      .showSidebar(html);
}

// This function defines the sort order for sections
function defineSectionOrder() {
  var scriptProperties = PropertiesService.getScriptProperties();
  var sections = {};
  sections['Whole School'] = 1;
  sections['Administration'] = 2;
  sections['Secondary School'] = 3;
  sections['Elementary School'] = 4;
  sections['Leave'] = 10;
  scriptProperties.setProperty('sectionsOrder', JSON.stringify(sections));
}

function sectionPrompt(section) {
 // Display a dialog box with a title, message, input field, and "Yes" and "No" buttons. The
 // user can also close the dialog by clicking the close button in its title bar.
 var ui = SpreadsheetApp.getUi();
 var response = ui.prompt('Section: ' + section, 'Enter the priority number', ui.ButtonSet.OK_CANCEL);

 // Process the user's response.
 if (response.getSelectedButton() == ui.Button.OK) {
   Logger.log('Section priority is %s.', response.getResponseText());
 } else if (response.getSelectedButton() == ui.Button.CANCEL) {
   Logger.log('The user didn\'t want to provide a section.');
 } else {
   Logger.log('The user clicked the close button in the dialog\'s title bar.');
 }
}

// TODO: Create a dialog/interface to change the sort order instead of programmagically as above
function interfaceSectionOrder() {
  var range = sheet.getRange(2, 1, sheet.getLastRow(), sheet.getLastColumn());
  var values = range.getValues(), section, sections = [];

  // get unique values that appear in section
  values.forEach(function (item, index, arr) {
      section = item[indexSection];
      Logger.log(section);
      if (section && sections.indexOf(section) == -1) {
        sections.push(section);
      }
  });

  // Problem with this is that it doesn't block
  for (var index=0; index < sections.length; i++) {
    section = sections[index];
    sectionPrompt(section);
  };
}

CSS.html

<link rel="stylesheet" href="https://ajax.googleapis.com/ajax/libs/jqueryui/1.11.4/themes/smoothness/jquery-ui.css">
<style>

.noDisplay {
  display: none;
}

p, label, legend, input, textarea, div {
  font-family: Calibri;
  font-size: 12pt;
}

.title {
  font-weight: 700;
  margin-bottom: 10px;
}

textarea {
  border: 1px solid #eee;
  padding: 10px;
  width: 90%;
}

.label {
  margin-top: 20px;
  font-weight: bold;
}

#markedDownText {
  border: 1px solid #eee;
  padding: 10px;
  width: 90%;
}

#saveButton {
  background-color: #DEA82A;
  padding: 10px;
  width: 100%;
  cursor: pointer;
}

#loadButton {
  width: 100%;
}

#displayNameOtherInput {
  font-size: 9pt;
  padding: 3px;
  color: #999;
}


</style>

Globals.gs

// We use a library here called "Moment"
// It's a dependency, so the below won't work until you have done this:
// Go to Resources -> Libraries and enter this product key: MHMchiX6c1bwSqGM1PZiW_PxhMjh3Sh48
var moment = Moment.load();

// We use a library here called "Underscore"
// It's a dependency, so the below won't work until you have done this:
// Go to Resources -> Libraries and enter this product key: M3i7wmUA_5n0NSEaa6NnNqOBao7QLBR4j
var _ = Underscore.load();

var DOMAIN = "";    // your school's GAFE domain
var SITENAME = "igbis-daily-notices"; // optional: the site name according to the url (at the end)
var site = SitesApp.getSite(DOMAIN, SITENAME);

function createShowdownConverter(optValue){
  return optValue ? new GASShowdown.createShowdownConverter(optValue) : new GASShowdown.createShowdownConverter();
}

// We use a library here called "GASShowdown"
// It provides the conversion from plain text to html using markdown
// It's a dependency, so the below won't work until you have done this:
// Go to Resources -> Libraries and enter this product key: M6WggW1B7uEj1Nu0p7S6Pf-Mffa6w-w2J
var mdConverter = createShowdownConverter();

var gl = true;
var priorityUsernames = [];

// Setup the sheet and spreadsheet variables
// Confusing thing about GAS is that a Spreadsheet is the document but the sheet is the ... sheet
// Anyway it always confuses me ;)
var sheet = SpreadsheetApp.getActiveSheet();
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

// Setup the variables we need for date comparisons
// "now" represents the time the script is run
var today = moment(new Date());
var tomorrow = today.add(1, 'days');
var dateFormat = "dddd, MMMM-Do-YYYY";
//var dateFormat = "YYYY-MMMM-Do";
var timestampFormat = "M/DD/YYYY H:m:ss";

// Start Date and End Date indexes
var indexTimestamp = 0;
var indexUsername = 1;
var indexSection = 2;
var indexStartDate = 3;
var indexEndDate = 4;
var indexContent = 5;
var indexEmbedded = 6;
var indexPast = 7;

var scriptProperties = PropertiesService.getScriptProperties();
var sectionsOrder = JSON.parse(PropertiesService.getScriptProperties().getProperty('sectionsOrder'));

Helpers.gs

function include(filename) {
  return HtmlService.createHtmlOutputFromFile(filename)
      .setSandboxMode(HtmlService.SandboxMode.IFRAME)
      .getContent();
}

function getIdFromUrl_(url) { 
  return url.match(/[-\w]{25,}/)[0]; 
}

function htmlify_(text) {
   return mdConverter.makeHtml(text).replace(/<p>|<\/p>/g, '')
}

function getSectionOrder(section) {
  return sectionsOrder[section] ? sectionsOrder[section] : 100;
}

function getPageByName(name) {
 var templates = site.getTemplates();
 var names = _.invoke(templates, 'getName');
 return templates[names.indexOf(name)];
}

function getSpreadsheetRange() {
  return range = sheet.getRange(2, 1, sheet.getLastRow(), sheet.getLastColumn());
}

function getSpreadsheetValues() {
  var range = getSpreadsheetRange();
  return range.getValues();
}

function getMyEntries() {
  var values = getSpreadsheetValues();
  var user = Session.getActiveUser();
  var myEntries = _.filter(values, function(row) {
    return row[indexUsername] == user.getEmail();
  });
  return myEntries;
}

function getCurrentRange() {
  var row = SpreadsheetApp.getActiveRange().getRow();
  return sheet.getRange(row, indexContent+1);
}

function getCurrentEntry() {
  var range = getCurrentRange();
  return range.getValue();
}

function getCurrentEntryMD() {
  var value = getCurrentEntry();
  return htmlify_(value);
}

function setCurrentEntry(rawText) {
  var range = getCurrentRange();
  range.setValue(rawText);
  return htmlify_(rawText);
}

function selectRowForUser() {
  var range = getCurrentRange();
  var newRange = sheet.getRange(range.getRow(), 1, 1, indexContent+1);
  sheet.setActiveSelection(newRange);
}

Javascript.html

<!-- Different tags to ensure dependencies -->
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.4/jquery.min.js"></script>
<script src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.11.4/jquery-ui.min.js"></script>

<!-- We can assume jquery/jqueryui/tagbox is all good -->
<script>

$(function () {
  // This runs on start
  beginEditing();
});

$('#loadButton').on('click', function (e) {
  beginEditing();
});

$('#saveButton').on('click', function (e) {
  google.script.run.withSuccessHandler(changeMD)
        .setCurrentEntry($('#bodyText').val());
});

function beginEditing() {
  google.script.run.selectRowForUser();
  google.script.run.withSuccessHandler(changeBodyText)
      .getCurrentEntry();
  google.script.run.withSuccessHandler(changeMD)
       .getCurrentEntryMD();
}

function changeBodyText(selection) {
  $('#bodyText').val(selection);
}

function changeMD(selection) {
  $('#markedDownText').html(selection);
}

</script>

Main.gs

// This builds the body of the email

function test_buildNoticesForDate() {
   buildNoticesForDate(today);
}

function buildNoticesForDate(targetDate) {

  // Grab the data from the sheet
  
  var values = getSpreadsheetValues();
  var obj, row, docId, before, after;

  // Make the "body" a string that contains the message we want to send

  var body = "";
  var incidents = 0;   // tally of how many incidents

  gl && Logger.log("Today is %s", today.format(dateFormat));

  // forEach function loops through after filtering out bad stuff
  
  notices = {};
  
  // index will definitely be the row index
  values.forEach(function (row, index, arr) {
    if (row[0] === "") {
        return;
    }
    obj = new Object();
    obj.timestamp = moment(row[0]);
    obj.username = row[indexUsername];
    obj.user = ContactsApp.getContact(obj.username);
    obj.by = '<br />(' + obj.user.getFullName() + ')';
    obj.startDate = moment(row[indexStartDate]);
    obj.endDate = row[indexEndDate];
    if (!obj.endDate) {
      obj.endDate = obj.startDate;
    } else {
      obj.endDate = moment(obj.endDate);
    }
    obj.content = row[indexContent];
    obj.section = row[indexSection];
    obj.embededUrl = row[indexEmbedded];

    // Calculate the priority by determining how many days it has been published
    // Increase the priority if it is a priority username, that way they appear higher up
    obj.priority = targetDate.diff(obj.startDate, 'days') * 10;
    if (priorityUsernames.indexOf(obj.username)) {
      obj.priority += 1;
    }
    
    // Derive an internal index of each notice so we can sort by it and can figure out what order they appear on the page
    // We'll use that when outputting the attachments, below
    obj.index = parseInt(getSectionOrder(obj.section).toString() + obj.priority.toString() + index.toString());

    if (obj.embededUrl) { 
      docId = getIdFromUrl_(obj.embededUrl);
      obj.embededDoc = DocumentApp.openById(docId);
      obj.embededText = ' <a href="#embedded' + obj.index.toString() + '">[Attachment]</a> ';
    } else {
      obj.embededDoc = undefined;
      obj.embededText = "";
    }

    obj.fullContent = htmlify_(obj.content) + obj.embededText + obj.by;

    obj.htmlContent  = '<li style="padding-bottom:10px;"><a name="notice' + obj.index.toString() + '"></a>';
    obj.htmlContent += obj.fullContent + '</li>';

    // Ensure that today is a weekday
    //if (timestamp.weekday() == 0 || timestamp.weekday() > 5) {
    //   return
    //}

    // Another filter to only collate items that fall within our desired range

    gl && Logger.log("End date is %s", obj.endDate.format(dateFormat));

    if (obj.endDate.isBefore(targetDate, 'day')) {

        // We have already passed it, so mark it as such and move on....
        sheet.getRange(index + 2, indexPast + 1).setValue('Yes');
        gl && Logger.log("Set as past");

    } else {
        sheet.getRange(index + 2, indexPast + 1).setValue('No');
        before = obj.startDate.isBefore(targetDate, 'day') || obj.startDate.isSame(targetDate, 'day')
        after =  obj.endDate.isAfter(targetDate, 'day') || obj.endDate.isSame(targetDate, 'day');
        
        if ( before && after ) {
            // Process it
            if (!(obj.section in notices))  {
                notices[obj.section] = new Object();
                notices[obj.section].title = obj.section;
                notices[obj.section].order = getSectionOrder(obj.section);
                Logger.log("Section %s order %s", obj.section, notices[obj.section].order);
                notices[obj.section].items = [];
                notices[obj.section].html = "";
            }
            notices[obj.section].items.push(obj);
    
            if (obj.embededDoc !== undefined) {
                if (!('Attachments' in notices)) {
                    notices['Attachments'] = [];
                }
                notices['Attachments'].push(obj);
            }
        }
     }
  });

  notices.html = "";
  _.sortBy(notices, 'order').forEach(function (notice, index, arr) {
    if (notice.title === "Attachments") {
      // Don't do embedded docs the normal way, because we'll handle it differently
      return;
    }

    if (notice.items === undefined) { 
      return;
    }
    notice.html += '<div><strong>' + notice.title + '</strong></div>';
    notice.html += '<ul class="unilist">';
    _.sortBy(notice.items, 'priority').forEach(function (item, index, obj) {
        notice.html += item.htmlContent;
    });
    notice.html += '</ul>';
    
    notices.html += notice.html;
  });

  // Now handle the attachments:
  if (notices["Attachments"]) {
    notices.html += '<br /><div><strong>Attachments</strong></div><br />';
    
    // Sort by index, so that attachments appear in the same order as given on the page
    _.sortBy(notices['Attachments'], 'index').forEach(function (item, index, obj) {
      notices.html += '<a name="embedded' + item.index.toString() + '"></a>' + htmlify_(item.content) + ' [<a href="#notice'+ item.index.toString() + '">Back to notice</a>]';
      notices.html += '<div><b><div><div class="sites-embed-align-left-wrapping-off"><div class="sites-embed-border-on sites-embed sites-embed-full-width" style="width:100%;"><h4 class="sites-embed-title">' + item.embededDoc.getName() + '</h4><div class="sites-embed-object-title" style="display:none;">' + item.embededDoc.getName() + '</div><div class="sites-embed-content sites-embed-type-writely"><iframe src="https://docs.google.com/document/preview?hgd=1&amp;id=' + item.embededDoc.getId() + '" width="100%" height="400" title="' + item.embededDoc.getName() + '" frameborder="0"></iframe></div><div class="sites-embed-footer"><div class="sites-embed-footer-icon sites-writely-icon">&nbsp;</div><a href="https://docs.google.com/document/edit?hgd=1&amp;id=' + item.embededDoc.getId() + '" target="_blank">Open <i>'+ item.embededDoc.getName() + '</i></a></div></div></div></div><br></b></div>';
    });
    notices.html += '';
  }

  gl && Logger.log(notices.html);
  return notices;
}
 
function emailAgents() {
  // Determine who should get the email, and actually send it
  // No duplicate sends

  var agents = [];
  
  var notices = buildNoticesForDate(today);

  //spreadsheet.getViewers().forEach(function (user, index, obj) {
    // Add this user's email only if not already there
    if (agents.indexOf(user.getEmail()) <= -1) {
      agents.push(user.getEmail());
    }

  spreadsheet.getEditors().forEach(function (user, index, obj) {
    // Add this user's email only if not already there
    if (agents.indexOf(user.getEmail()) <= -1) {
      agents.push(user.getEmail());
    }
  });

  // For debugging, define agents here and test
  // agents = ['[email protected]'];
  
  // Now email them out to everyone who is a viewer or editor
  agents.forEach(function (value, _, obj) {
      // TODO: send notices.html, but as html 
      //MailApp.sendEmail(value, 'Notices for: ' + now.format(dateFormat), body);
  });
}

function updateSite(title, notices) {
  var noticesPage = site.getChildByName(title);
  if (noticesPage === null) {
      gl && Logger.log("Creating site %s", title.shortNameToLongName());
      site.createWebPage(title.shortNameToLongName(), title, notices.html)
  } else {
    noticesPage.setHtmlContent(notices.html);
  }
}

function onDailyTrigger(e) {
  var notices = buildNoticesForDate(tomorrow);  
  updateSite('staff-notices-' + tomorrow.format(dateFormat), notices);
}

function onSubmit(e) {
  var notices = buildNoticesForDate(today);
  updateSite('staff-notices', notices);
}

Prototype.gs

String.prototype.toTitleCase = function () {
    return this.replace(/\w(\S|$)*/g, function(txt){return txt.charAt(0).toUpperCase() + txt.substr(1).toLowerCase();});
};

String.prototype.shortNameToLongName = function () {
    return (this.split('-').join(' ')).toTitleCase();
};

Sidebar.html

<!DOCTYPE html>

<?!= include('CSS'); ?>


<input id="loadButton" type="button" value="Re-load from Selection" />
<div class="label">Edit:</div>
<textarea rows="10" id="bodyText">Loading….</textarea>
<input id="saveButton" type="button" value="Save!" />
<div class="label">HTML Preview (save to refresh):</div>
<div id="markedDownText"></div>




<?!= include('Javascript.html'); ?>