weeklymenucalendarsync.js

/*
 * This script is intended to be used as an Apps Script attached to a Google Sheet formatted like:
 * https://docs.google.com/spreadsheets/d/1oiHQopo6KsdJIyRnPDBPNmWx2RVMtpiN6XoZItjmneo/edit?usp=sharing
 */
var WEDNESDAY = 3;

function startup() {
  checkCalendarEvents();
};

function edit(e) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var cells = sheet.getRange('A2:G2');
  var c = e.range.getColumn();
  if (e.range.getHeight() > 1 || e.range.getWidth() > 1 || e.range.getRow() != cells.getRow() || c < cells.getColumn() || c > cells.getLastColumn()) {
    return;
  }
  var newtext = e.range.getValue();
  var which = e.range.getColumn() - cells.getColumn();
  var event = getEventForDay(getCalendar(), getDays()[which]);
  if (event) {
    event.setTitle(newtext.replace(/\n/g, ", "));
  }
}

function getCalendar() {
  return CalendarApp.getCalendarById('7tqadorppqj73co8c0pplu447c@group.calendar.google.com');
}

function getDays() {
  var day = new Date();
  // Find next Sunday
  if (day.getDay() > 0) {
    day.setDate(day.getDate() + (7 - day.getDay()));
  }
  var days = [];
  for (var i=0 ; i < 7; i++) {
    days.push(day);
    day = new Date(day);
    day.setDate(day.getDate() + 1);
  }
  return days;
}

function getEventForDay(calendar, day) {
  var events = calendar.getEventsForDay(day);
  var tz = calendar.getTimeZone();
  // getEventsForDay is kind of terrible with all-day events.
  events = events.filter(function(e) {
    var d = e.isAllDayEvent() ? e.getAllDayStartDate() : e.getStartTime();
    return d.getDay() == day.getDay();
  });
  if (events.length == 1) {
    return events[0];
  } else if (events.length == 0) {
    // Create an event for this day
    var e;
    /*
    if (day.getDay() == WEDNESDAY) {
      var start = new Date(day);
      start.setHours(11, 0, 0, 0);
      var end = new Date(day);
      end.setHours(12, 0, 0, 0);
      e = calendar.createEvent('Slow Cooker', start, end);
      e.addPopupReminder(0);
    } else {
    */
      var title = day.getDay() > 4 ? 'Out/Pizza' : 'dinner';
      e = calendar.createAllDayEvent(title, day);
    //}
    return e;
  }
  var fmt = Utilities.formatDate(day, tz, 'yyyy-MM-dd');
  Logger.log('Too many events for %s: %s', fmt, events.length);
  return null;
}

function checkCalendarEvents() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var cells = sheet.getRange('A2:G2');
  var values = cells.getValues()[0];
  var calendar = getCalendar();
  var days = getDays();
  for (var i = 0; i < days.length; i++) {
    var event = getEventForDay(calendar, days[i]);
    if (event) {
      values[i] = event.getTitle();
    }
  }
  cells.setValues([values]);
}