1Dance Back-end

gistfile1.txt

///////////////
// CONSTANTS
///////////////
var DATE = 0,
    FB_LINK = 1,
    HOSTS = 2,
    DJS = 3,
    INSTRUCTORS = 4,
    DOOR = 5,
    NOTES = 6,
    LEVEL_TWO = 7,
    LEVEL_ONE = 8,
    AVAILABLE_INSTRUCTORS = 9,
    AVAILABLE_HOSTS = 10,
    AVAILABLE_DJS = 11;

var ACTIVE = 0,
    NAME = 1,
    PHONE = 2,
    EMAIL = 3,
    SCHEDULING_NOTES = 4,
    OTHER_NOTES = 5;

var ROLE = {
  HOSTS: 2,
  DJS: 3,
  INSTRUCTORS: 4,
  DOOR: 5,
};

var DAYS = {
  HOST: 1,
  INSTRUCTOR: 1,
  DJ: 20,
  REMINDER: 3,
  SECOND_REMINDER: 2
};

var SCHEDULERS = {
  HOST: '[email protected]',
  DJ: '[email protected]',
  INSTRUCTOR: '[email protected]'
};

var VENUES = {
  FNB: {
    longName: 'Friday Night Blues',
    nextDay: getNextFriday_,
  },
  BTB: {
    longName: 'Beat the Blues',
    nextDay: getNextWednesday_,
  }
};
    

sendOptions = {
  'from': '[email protected]',
  'name': 'GGB Scheduling'
};

// Set to true to not actually send e-mails or create events, etc
var DEBUG = false;


////////////////
// UTILITIES
////////////////
function mod_(n, m) {
  return ((n % m) + m) % m;
}

function pluralList_(arr) {
  if (arr.length == 1) {
    return arr[0];
  }
  
  var lastElement = arr.pop();
  var result = [arr.join(', '), lastElement].join(' and ');
  arr.push(lastElement);
  return result;
}

function uniquify_(arr) {
  var output = [];
  for (var i = 0; i < arr.length; i++) {
    if (output.indexOf(arr[i]) < 0) {
      output.push(arr[i]);
    }
  }
  return output;
}

function joinDateAndTime_(date, time) {
  date = new Date(date);
  date.setHours(time.getHours());
  date.setMinutes(time.getMinutes());
  return date;
}


///////////////////
// DATE UTILITIES
///////////////////
function fdate_(date, format) {
  return Utilities.formatDate(date, SpreadsheetApp.getActive().getSpreadsheetTimeZone(), format);
}

function shortDate_(date) {
  return fdate_(date, 'MMMM d');
}

function toDateString_(date) {
  return fdate_(date, "yyyy-MM-dd");
}

function getNextFriday_() {
  return getNextDay_(5);
}

function getNextWednesday_() {
  return getNextDay_(3);
}

function getNextDay_(day) {
  var date = new Date();
  var msUntilDay = mod_(day - (new Date()).getDay(), 7) * 24 * 60 * 60 * 1000;
  return new Date(Date.now() + msUntilDay);
}

function getNextMonth_() {
  var msUntilNextMonth = (40 - (new Date()).getDate()) * 24 * 60 * 60 * 1000;
  return new Date(Date.now() + msUntilNextMonth);
}

function isNextMonth_(date) {
  var aDayNextMonth = getNextMonth_();
  return (date.getMonth() == aDayNextMonth.getMonth()) && (date.getFullYear() == aDayNextMonth.getFullYear());
}

function getEventsNextMonth_(venue) {
  venue = venue || 'FNB'
  var ss = SpreadsheetApp.getActive();
  var values = ss.getSheetByName(venue).getDataRange().getValues();
  
  var eventsNextMonth = [];
  for (var i = 0; i < values.length; i++) {
    var date = new Date(values[i][0]);
    
    if (isNextMonth_(date)) {
      eventsNextMonth.push(values[i]);
    }
  }
  
  return eventsNextMonth;
}

function getNextEvent_(venue) {
  venue = venue || 'FNB';
  return getNextEvents_(1, venue)[0];
}

function getNextEvents_(n, venue) {
  venue = venue || 'FNB';
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName(venue);
  var range = sheet.getDataRange();
  
  var nextEvents = [];
  
  var values = range.getValues();
  for (var i = 0; i < values.length; i++) {
    var date = new Date(values[i][0]);
    
    var nextDay = venue == 'FNB' ? getNextFriday_() : getNextWednesday_();
    
    if (toDateString_(date) == toDateString_(nextDay)) {
      nextEvents.push(values[i]);
    } else if (nextEvents.length > 0) {
      nextEvents.push(values[i]);
    }
    
    if (nextEvents.length == n) {
      break;
    }
  }
  
  return nextEvents;
}
      


//////////////////
// PEOPLE + ROLE UTILITIES
//////////////////
function hasPeople_(string) {
  return !!string.trim() && !string.match(/\?/);
}

function parsePeople_(string) {
  // TODO - make this better
  string = string.replace(/\(.*?\)/g, '').trim();
  var people = string.split(/[\+\/,&]| and | w | w\/ /);
  people = people.filter(function(str) {
    return str.trim() !== '';
  }).map(function(name) {
    return name.trim();
  });
  return people;
}

function lookupEmail_(name, kind) {
  var emails = [];
  var directory = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(kind + 's').getDataRange().getValues();
  for (var i = 1; i < directory.length; i++) {
    if ((directory[i][NAME].indexOf(name) >= 0) || (name == '*' && directory[i][ACTIVE].trim() != '')) {
      emails.push(directory[i][EMAIL]);
    }
  }
  
  if (emails.length == 1) {
    return emails[0];
  } else {
    return emails;
  }
}

function getEmails_(row, kind) {
  var field = row[ROLE[(kind + 's').toUpperCase()]];
  if (!hasPeople_(field)) {
    return;
  }
  
  var people = uniquify_(parsePeople_(field));
  var recipients = [];
  for (var i = 0; i < people.length; i++) {
    var email = lookupEmail_(people[i], kind);
    if (email) {
      recipients.push(email);
    }
  }
  return recipients;
}


//////////////////////
// TEMPLATE UTILITIES
//////////////////////
function render_(template, vars) {
  var tags = template.match(/\[\[\w+\]\]/g);
  for (var i = 0; i < tags.length; i++) {
    var key = tags[i].match(/[\w+-]+/)[0];
    var re = new RegExp('\\[\\[' + key + '\\]\\]', 'g');
    if (typeof vars[key] != 'undefined') {
      template = template.replace(re, vars[key]);
    }
  }
  
  return template;
}

function renderById_(id, vars) {
  var template = DocumentApp.openById(id).getBody().getText();
  return render_(template, vars);
}




/**
 * A special function that inserts a custom menu when the spreadsheet opens.
 */
function onOpen() {
  var menu = [
    {name: "Make FB events from selected rows", functionName: 'setUpEvent'},
    {name: "Send reminders for " + shortDate_(getNextFriday_()), functionName: 'sendReminders'},
    {name: "Send confirmation requests to instructors for selected rows", functionName: "sendRequests"}
  ];
  
  SpreadsheetApp.getActive().addMenu("Golden Gate Blues", menu);
}


////////////////////////////////////////////////////////////////////
// Creates gCal events for one or more rows
// - If rows are highlighted, creates events for all of them
// - If rows aren't highlighted, creates and event for the next row
/////////////////////////////////////////////////////////////////////
function setUpEvent() {  
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName('FNB');
  var range = sheet.getActiveRange();
  var processAll = true;
 
  if (range.getRow() == 1 || range.getWidth() == 1) {
    Logger.log('No highlighted cells found, converting next scheduled event.');
    setUpCalendar_(getNextEvent_());
  } else {
    Logger.log("Converting all " + range.getNumRows() + " rows in selection.");
    var values = range.getValues();
    for (var i = 0; i < values.length; i++) {
      Logger.log('Adding event for dance on ' + toDateString_(values[i][0]));
      setUpCalendar_(values[i]);
    }
  }
}

////////////////////////////////////////////////////////////////////
// Creates a gCal event with all the event details
// - Uses some default start and end times
// - Constructs the event description from a template
// - Prompts the user if it looks like an event has already
//     been created for that night
/////////////////////////////////////////////////////////////////////
function setUpCalendar_(row) {
  Logger.log(row);
  
  var cal = CalendarApp.getCalendarById('[email protected]');
  
  var people = [].concat(parsePeople_(row[INSTRUCTORS], row[DJS]));
  
  var date = row[DATE];
  
  var title = shortDate_(date) + ' Friday Night Blues with ' + pluralList_(uniquify_(people));
  Logger.log(title);
  
  var description = renderById_('1d-y-VAx4HO3KwJV49BZzpVY_BfahsTIQXtg0phO0Hkw', {
    instructors: pluralList_(uniquify_(parsePeople_(row[4]))),
    djs: pluralList_(uniquify_(parsePeople_(row[3]))),
    levelTwo: row[LEVEL_TWO],
    levelOne: row[LEVEL_ONE]
  });
  
  var start = joinDateAndTime_(date, new Date(2014, 1, 1, 20, 0, 0)); // Default to 8pm
  var end = joinDateAndTime_(date, new Date(2014, 1, 1, 2, 0, 0)); // Default to 2am the next day
  end = new Date(end.getTime() + 1 * 24 * 60 * 60 * 1000);
  
  var options = {
    location: '19 Heron St, San Francisco, California 94103',
    description: description
  };
  
  var createEvent = true;
  Logger.log('Script properties: ' + PropertiesService.getScriptProperties().getKeys());
  if (PropertiesService.getScriptProperties().getProperty(fdate_(date, 'MMMM d, YYYY'))) {
    createEvent = Browser.msgBox("Looks like an event has already been created for that date.  Create anyway?", Browser.Buttons.YES_NO);
    createEvent = (createEvent == 'yes') ? true : false;
  }
  
  if (createEvent) {
    if (!DEBUG) {
      var event = cal.createEvent(title, start, end, options).setGuestsCanSeeGuests(true);
    }
    Browser.msgBox("Created event on " + shortDate_(date) + '. Please wait up to 15 minutes for it to appear on Facebook.');

    // Store the ID for the Calendar, which is needed to retrieve events by ID.
    PropertiesService.getScriptProperties().setProperty(fdate_(date, 'MMMM d, YYYY'), true);
  }
}  


////////////////////////////////////////////////////////////////////
// Send reminders to people working a dance 4 days in advance
// - Get people's e-mails from their entries in the role-specific spreadsheet
// - Send a gentle reminder that they're signed up for the night
//     and what they're signed up for.
/////////////////////////////////////////////////////////////////////
function sendReminders() {
  var row = getNextEvent_();
  var date = shortDate_(row[DATE]);
  
  var subject = "[REMINDER] Working at FNB on " + date;
  
  var hosts = uniquify_(parsePeople_(row[HOSTS]));
  var instructors = uniquify_(parsePeople_(row[INSTRUCTORS]));
  var djs = uniquify_(parsePeople_(row[DJS]));
  
  var body = renderById_('1hyKapH909rImfoVY6mE3kBjCfdytnOEijBxpV6AZFVE', {
    date: date,
    levelTwo: row[LEVEL_TWO],
    levelOne: row[LEVEL_ONE],
    hosts: pluralList_(hosts),
    instructors: pluralList_(instructors),
    djs: pluralList_(djs)
  });
  
  var hostEmails = getEmails_(row, 'Host');
  var instructorEmails = getEmails_(row, 'Instructor');
  var djEmails = getEmails_(row, 'DJ');
  
  var recipients = [].concat(hostEmails, instructorEmails, djEmails).join(',');
  
  Logger.log(recipients + '\n\n' + subject + '\n\n' + body);
  
  if (!DEBUG) {
    GmailApp.sendEmail(recipients, subject, body, sendOptions);
  }
}


////////////////////////////////////////////////////////////////////
// Confirm the next event 6 days in advance
// - Make sure none of role fields are blank or have ?'s
// - Send e-mails to the role mailing list ([email protected]) 
//     and the host for that night, if applicable
/////////////////////////////////////////////////////////////////////
function validateNextEvent() {
  var row = getNextEvent_();
  var hostEmails = [];
  var recipients, subject, body;
  
  if (hasPeople_(row[HOSTS])) {
    hostEmails = getEmails_(row, 'Host');
  }
  
  var roles = [
    {
      name: 'host',
      index: HOSTS
    },{
      name: 'dj',
      index: DJS
    },{
      name: 'instructor',
      index: INSTRUCTORS
    }
  ];
  
  for (var i = 0; i < roles.length; i++) {
    var role = roles[i];
    
    if (!hasPeople_(row[role.index])) {
      Logger.log('Need ' + role.name + 's! ' + row[role.index]);
      
      recipients = [role.name + '[email protected]'].concat(hostEmails).join(',');
      subject = 'Need ' + role.name + 's for Friday Night Blues on ' + shortDate_(row[DATE]);
      body = renderById_('12lKzOPAZgaKmyP8QAaMVdcY0OTOvvpInrYa6pAfeS5s', {
        date: shortDate_(row[DATE]),
        val: row[role.index],
        role: role.name
      });
      
      if (!DEBUG) {
        GmailApp.sendEmail(recipients, subject, body, sendOptions);
      } else {
        Logger.log(recipients + '\n\n' + subject + '\n\n' + body);
      }
    }
  }
}



////////////////////////////////////////////////////////////////////
// Update published schedule with current event
////////////////////////////////////////////////////////////////////
function updatePublishedSchedule() {
  ['FNB', 'BTB'].forEach(function(venue) {
    var rows = getNextEvents_(5, venue);
    var sheet = SpreadsheetApp.openById('1oIcYCDQU874oZ3DaEIZ4Tm2aWgII7gK8D2D0iSwF5f4').getSheetByName(venue);
    
    rows = rows.map(function(row) {
      row[DATE] = fdate_(row[DATE], 'M/d/YYYY');
      return row;
    });
    
    sheet.deleteRows(2, 10);
    
    for (var i = 0; i < rows.length; i++) {
      sheet.appendRow(rows[i]);
    }
  });
}



////////////////////////////////////////////////////////////////////
// Send scheduling solicitation emails to check availability
////////////////////////////////////////////////////////////////////
function schedule() {
  ['Host', 'Instructor', 'DJ'].forEach(function(role) {
    if (DEBUG || (new Date()).getDate() == DAYS[role.toUpperCase()]) {
      scheduleNextMonth_(role);
    }
  });
}

function scheduleNextMonth_(role) {
  var rows = getEventsNextMonth_();
  
  var options = ['Yes', 'No', 'If necessary'];
  
  var choices = [];
  for (var i = 0; i < rows.length; i++) {
    choices.push(fdate_(rows[i][DATE], 'MMM d'));
  }
  
  var formTitle = 'FNB ' + role.toLowerCase() + ' availability for ' + fdate_(rows[0][DATE], 'MMMM YYYY');
  
  var form = FormApp.create(formTitle).setTitle(formTitle);
  form.setAllowResponseEdits(false).setProgressBar(false).setPublishingSummary(true).setShowLinkToRespondAgain(false);
  form.addTextItem().setTitle('Full Name').setRequired(true);
  form.addGridItem().setTitle('Availability').setRows(choices).setColumns(options);
  
  PropertiesService.getScriptProperties().setProperty(role + ' availability for ' + fdate_(rows[0][DATE], 'MMMM YYYY'), form.getId());
  
  var recipients = lookupEmail_('*', role);
  var subject = formTitle;
  var body = renderById_('1QTs7Kp5WzSqVAOtyVeaweQgccdZHVXN0doGV6dOEwcI', {
    month: fdate_(rows[0][DATE], 'MMMM'),
    formUrl: form.getPublishedUrl()
  });
  
  ScriptApp.newTrigger('updateSSWith' + role + 'Availability').forForm(form).onFormSubmit().create();
  
  if (!DEBUG) {
    GmailApp.sendEmail(recipients, subject, body, sendOptions);
  } else {
    Logger.log(recipients + '\n\n' + subject + '\n\n' + body);
  }
}



////////////////////////////////////////////////////////////////////
// Send scheduling reminders
////////////////////////////////////////////////////////////////////
function scheduleReminders() {
  // Simple date adding is ok since we're always in the same month.
  ['Host', 'Instructor', 'DJ'].forEach(function(role) {
    if ((new Date()).getDate() == DAYS[role.toUpperCase()] + DAYS.REMINDER) {
      sendRemindersForNextMonth_(role);
    }
    
    if ((new Date()).getDate() == DAYS[role.toUpperCase()] + DAYS.REMINDER + DAYS.SECOND_REMINDER) {
      sendRemindersToScheduler_(role);
    }
  });
}

function sendRemindersForNextMonth_(role) {
  var date = getNextMonth_();
  var currentFormId = PropertiesService.getScriptProperties().getProperty(role + ' availability for ' + fdate_(date, 'MMMM YYYY'));
  var form = FormApp.openById(currentFormId);
  
  var recipients = lookupEmail_('*', role);
  
  var subject = "[REMINDER] " + form.getTitle();
  var body = renderById_('1O9hil0fL4Qt_BViZ156NaZlptr0uHoB53ysW6Od2jg8', {
    month: fdate_(getNextMonth_(), 'MMMM'),
    deadline: fdate_(new Date(Date.now() + DAYS.SECOND_REMINDER * 24 * 60 * 60 * 1000), 'EEEE'),
    formUrl: form.getPublishedUrl()
  });
  
  if (!DEBUG) {
    GmailApp.sendEmail(recipients, subject, body, sendOptions);
  } else {
    Logger.log(recipients + '\n\n' + subject + '\n\n' + body);
  }
}
  
function sendRemindersToScheduler_(role) {
  var recipient = SCHEDULER[role.toUpperCase()];
  var subject = "[REMINDER] Schedule " + role + "s";
  var body = "Everybody has submitted their responses to the scheduling survey. Go ahead with scheduling.";
  
  if (!DEBUG) {
    GmailApp.sendEmail(recipient, subject, body, sendOptions);
  } else {
    Logger.log(recipients + '\n\n' + subject + '\n\n' + body);
  }
}   
 


////////////////////////////////////////////////////////////////////
// Once the availability form has been submitted, put the response
// into the BB Staff Scheduling spreadsheet
////////////////////////////////////////////////////////////////////
function updateSSWithInstructorAvailability(e) {
  return updateSSWithAvailability(e, AVAILABLE_INSTRUCTORS);
}

function updateSSWithHostAvailability(e) {
  return updateSSWithAvailability(e, AVAILABLE_HOSTS);
}

function updateSSWithDJAvailability(e) {
  return updateSSWithAvailability(e, AVAILABLE_DJS);
}

function updateSSWithAvailability(e, INDEX) {
  var itemResponses = e.response.getItemResponses();
  var name = itemResponses[0].getResponse();
  if (typeof itemResponses[1] === 'undefined') {
    // No availability
    return;
  }

  var dates = itemResponses[1].getItem().asGridItem().getRows();
  var availability = itemResponses[1].getResponse();
  
  var ss = SpreadsheetApp.getActive()
  var range = ss.getSheetByName('FNB').getDataRange();
  var values = range.getValues();
  
  for (var i = 2; i < values.length; i++) {
    var row = values[i];
    if (isNextMonth_(row[DATE])) {
      var index = dates.indexOf(fdate_(row[DATE], 'MMM d'));
      if (index > -1 && availability[index] !== 'No') {
        var availableRole = row[INDEX].split(',').map(function(name) { return name.trim(); });
        
        if (availableRole[0] == '') { 
          availableRole.shift();
        }
        
        if (availability[index] == 'If necessary') {
          name += '?';
        }
        
        availableRole.push(name);
        availableRole = uniquify_(availableRole).join(', ');
        if (!DEBUG) {
          range.getCell(i+1, INDEX+1).setValue(availableRole);
        } else {
          Logger.log('Editing cell ' + (i+1) + (INDEX+1));
          Logger.log(availableRole);
        }
      }
    }
  }
}



////////////////////////////////////////////////////////////////////
// Once potential instructors have been populated into the spreadsheet
// Send confirmations to them so they know when they're actually teaching
////////////////////////////////////////////////////////////////////
function sendRequests() {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName('FNB');
  var range = sheet.getActiveRange();
  var values = range.getValues();
 
  if (range.getRow() == 1 || range.getWidth() == 1) {
    Logger.log('No highlighted cells found. Sending requests for all of next month');
    values = getEventsNextMonth_();
  }
  
  for (var i = 0; i < values.length; i++) {
    var row = values[i];
    
    var names = pluralList_(parsePeople_(row[INSTRUCTORS]).map(function(name) {
      return name.trim().split(' ')[0];
    }));
    
    var recipients = getEmails_(row, 'Instructor').join(',');
    var subject = "Teaching at FNB on " + fdate_(row[DATE], 'EEEE, MMM d');
    var body = renderById_('1XNNgoy9MwvzTwJm002wXJ84uspnlvPRvkRUzj4vhy88', {
      names: names,
      date: fdate_(row[DATE], 'EEEE, MMM d'),
      level1: row[LEVEL_ONE],
      level2: row[LEVEL_TWO]
    });
    
    if (!DEBUG) {
      GmailApp.sendEmail(recipients, subject, body, sendOptions);
    } else {
      Logger.log(recipients + '\n\n' + subject + '\n\n' + body);
    }
  }
}