Google Script: Google Form > Google Sheets > Google Calendar

google_form_to_calendar_script.gs.js

/**
 * Adds a custom menu to the active spreadsheet, containing a single menu item
 * for invoking the exportEvents() function.
 * The onOpen() function, when defined, is automatically invoked whenever the
 * spreadsheet is opened.
 * For more information on using the Spreadsheet API, see
 * https://developers.google.com/apps-script/service_spreadsheet
 * FROM: https://stackoverflow.com/questions/15788897/create-google-calendar-events-from-spreadsheet-but-prevent-duplicates
 */
function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name: "Export Events",
    functionName: "exportEvents"
  }];
  sheet.addMenu("Calendar Actions", entries);
  Logger.log(SpreadsheetApp.getActiveSheet().getName());
}


//https://stackoverflow.com/questions/16216868/get-back-a-string-representation-from-computedigestalgorithm-value-byte
function md5(str) {
  return Utilities.computeDigest(Utilities.DigestAlgorithm.MD5, str).reduce(function(str,chr){
    chr = (chr < 0 ? chr + 256 : chr).toString(16);
    return str + (chr.length==1?'0':'') + chr;
  },'');
}

// https://stackoverflow.com/questions/24785987/google-apps-script-find-row-number-based-on-a-cell-value
function rowOf(containingValue, sheet, columnToLookInIndex) {
  var data = sheet.getDataRange().getValues();
  
//  Logger.log (md5(containingValue));
  for (var i = 0; i < data.length; i++) {
    if (md5(data[i][columnToLookInIndex]) == md5(containingValue)) {
//      Logger.log("Row {" + (i+1) + "} found: " + md5(data[i][columnToLookInIndex]));
      return i+1;
      break;
    } else {
//      Logger.log("looked in: " + (i+1) + "; found: " +md5(data[i][columnToLookInIndex]));
    }
  }
  return -1;
}

/**
 * Export events from spreadsheet to calendar
 *
 * Column Reference:
 * Not all columns are for the script, some are for the form manager's reference
 * 1  Program Title
 * 2  Host Institution
 * 5  Program Description
 * 6  What does this program cost the participant?
 * 7  Age range of participants
 * 8  Location where program will be held
 * 9  Street address
 * 10 City
 * 11 Zip Code
 * 12 Contact Person
 * 13 Email address of contact person
 * 14 Phone number of contact person
 * 15 Program Category
 * 16 Repeating Program?
 * 17 Event Date
 * 18 Event Start
 * 19 Event End
 * 20 How often?
 * 21 Day(s) of the week
 * 22 End repeating after __ times
 * 23 Repeat only on the _ week of the month
 * 27 Row in the 'All Events' sheet
 * 28 Event ID (The event ID column gets filled in by the script
 *    when new events are created, and is then used in later invocations
 *    to retrieve events from the calendar, thereby avoiding duplication.)
 */
function exportEvents() {
  Logger.log("Exporting events");
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Approved Events");
  //var sheet = SpreadsheetApp.getActiveSheet();

  var headerRows = 1; // Number of rows of header info (to skip)
  var range = sheet.getDataRange();
  var data = range.getValues();
  var calId = "jecc.org_vm4ofkie13g62i2hb064af7ubo@group.calendar.google.com";
  var cal = CalendarApp.getCalendarById(calId);

  for (i = 0; i < data.length; i += 1) {
    
    // Skip header row(s)
    if (i < headerRows) {
      continue;
    }
    
    var column = data[i]; // each column[i]
    
    var updated_event = false;
    
    if (column[0] == "Updated") {
      Logger.log("Updating: " + i );
      var cell = sheet.getRange(i+1,28);
      cell.setValue("");
      var all_events_sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("All Events");
      
      var cell_to_update = all_events_sheet.getRange(rowOf(column[25], all_events_sheet, 25),1);
      
      cell_to_update.setValue("Yes");
      column[0] = "Yes";
      updated_event = true;
    }
    // Skip rows that aren't approved.
    if (column[0] != "Yes") {
    	continue;
    } else if ( Math.abs(i % 2) ) {
      // add some wait-time so we don"t get dinged by API limits.
      //Utilities.sleep(50);
    }
     
    var title = column[1] + "(" + column[15] + ")";

    var date = new Date(column[17]);
    var start_time = new Date(column[18]);
    start_time.setDate(date.getDate());
    start_time.setMonth(date.getMonth());
    start_time.setYear(date.getYear());

    var stop_time = new Date(column[19]);
    stop_time.setDate(date.getDate());
    stop_time.setMonth(date.getMonth());
    stop_time.setYear(date.getYear());

    var location = column[8] + ": " + column[9] + " " + column[10] + ", " + column[11];

    var description = column[5] + "\n\nHosted by: " + column[2] + "\nFor ages: " + column[7] + "\nCost: " + column[6] +
      "\n\nIf you have questions, contact: " + column[12] + " " + column[13] + " " + column[14] + "\nCategory: " + column[15];

    var event_id = column[28];
        
    // Check if event already exists, we could update it if it does
    try {
      var event = cal.getEventSeriesById(event_id);
      Logger.log("Event exists:" + event_id);
      
    } catch (e) {
      // do nothing - we just want to avoid the exception when event doesn't exist
    }
    if (!event || updated_event == true) {

      var options = {
        description: description,
        location: location
      };

      // repeating program
      if (column[16] == "Yes" || column[16] == true) {
        
        var repeat_times = column[22];
        if (repeat_times == "")
          repeat_times = 1;
        
        
        // https://stackoverflow.com/questions/42938147/google-apps-script-create-a-recurring-event-that-repeats-monthly-on-the-third-
        var numbers = [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31]; 
        var my_date = date.getDate();
        var mod = date % 7 - 1;
        var monthly_days = numbers.slice(my_date-mod, my_date-mod+7)
        
  
        
        
        Logger.log (column[2] + ":" + repeat_times);
        var recurrence;

        switch (column[20]) {
          case "Daily":
            recurrence = CalendarApp.newRecurrence().addDailyRule().times(column[22]);
            break;
          case "Weekly":
            Logger.log ("weekly event");
            var weekday = column[21].split(", ");
            var my_weekdays = [];

            for (var a = 0; a < weekday.length; a++) {
              if (weekday[a] == "SU")
                my_weekdays.push(CalendarApp.Weekday.SUNDAY);
              else if (weekday[a] == "MO")
                my_weekdays.push(CalendarApp.Weekday.MONDAY);
              else if (weekday[a] == "TU")
                my_weekdays.push(CalendarApp.Weekday.TUESDAY);
              else if (weekday[a] == "WE")
                my_weekdays.push(CalendarApp.Weekday.WEDNESDAY);
              else if (weekday[a] == "TR")
                my_weekdays.push(CalendarApp.Weekday.THURSDAY);
              else if (weekday[a] == "FR")
                my_weekdays.push(CalendarApp.Weekday.FRIDAY);
              else if (weekday[a] == "SA")
                my_weekdays.push(CalendarApp.Weekday.SATURDAY);
              
              //Logger.log ("weekday#"+a + " ["+my_weekdays+"]");
            };

            Logger.log(weekday + "= " + my_weekdays + " {" + title + "} -- " + repeat_times);
            
            
            
            recurrence = CalendarApp.newRecurrence().addWeeklyRule()
            .onlyOnWeekdays(my_weekdays)
            //.onlyOnMonthDays(monthly_days)
            .times(repeat_times);
            
            break;
          case "Monthly":
            Logger.log ("monthly event");
            if (column[21] != "" && column[21] != null) {
              
              var weekday = column[21].split(", ");
              var my_weekdays = [];
              
              for (var a = 0; a < weekday.length; a++) {
                if (weekday[a] == "SU")
                  my_weekdays.push(CalendarApp.Weekday.SUNDAY);
                else if (weekday[a] == "MO")
                  my_weekdays.push(CalendarApp.Weekday.MONDAY);
                else if (weekday[a] == "TU")
                  my_weekdays.push(CalendarApp.Weekday.TUESDAY);
                else if (weekday[a] == "WE")
                  my_weekdays.push(CalendarApp.Weekday.WEDNESDAY);
                else if (weekday[a] == "TR")
                  my_weekdays.push(CalendarApp.Weekday.THURSDAY);
                else if (weekday[a] == "FR")
                  my_weekdays.push(CalendarApp.Weekday.FRIDAY);
                else if (weekday[a] == "SA")
                  my_weekdays.push(CalendarApp.Weekday.SATURDAY);
                
                //Logger.log ("weekday#"+a + " ["+my_weekdays+"]");
              };
              recurrence = CalendarApp.newRecurrence().addMonthlyRule().onlyOnWeekdays(my_weekdays).onlyOnMonthDays(monthly_days).times(repeat_times);
            } else {
             recurrence = CalendarApp.newRecurrence().addMonthlyRule().times(repeat_times);
            }
            //Logger.log(repeat_times);
            //recurrence = CalendarApp.newRecurrence().addMonthlyRule().times(repeat_times);
            break;
          default:
            recurrence = {};
        }
             
        var new_event_series = cal.createEventSeries(title, start_time, stop_time, recurrence, options).getId();
        var cell = sheet.getRange(i+1,29);
        cell.setValue(new_event_series);
        
        // column[28] = new_event_series;

        Logger.log("Created repeating event: " + new_event_series);

      } else {
        //cal.createEvent(title, new Date("March 3, 2010 08:00:00"), new Date("March 3, 2010 09:00:00"), {description:desc,location:loc});
        var new_event = cal.createEvent(title, start_time, stop_time, options).getId();
        // column[28] = new_event; // Update the data array with event ID
        var cell = sheet.getRange(i+1,29);
        cell.setValue(new_event);
      }
    } else {
      // the event already exists, we"re going to do nothing. Could instead add a recurrence:
      //      event.setTitle(title);
      //      event.setDescription(desc);
      //      event.setLocation(loc);
      //      // event.setTime(tstart, tstop); // cannot setTime on eventSeries.
      //      // ... but we CAN set recurrence!
      //      var recurrence = CalendarApp.newRecurrence().addDailyRule().times(1);
      //      event.setRecurrence(recurrence, tstart, tstop);
    }
    debugger;  
    
  }
  // Record all event IDs to spreadsheet
  // range.setValues(data);
  SpreadsheetApp.getUi().alert("Done adding events to calendar!");
}




// Add the approval date/time when an event is approved  
// https://webapps.stackexchange.com/questions/33372/how-can-i-automatically-set-last-updated-cell-in-row-google-docs-spreadsheets


// could make this onEdit, but that's too often.
function UpdateApprovalDate() {
//  Logger.log("Starting.");
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("All Events");
  var data = sheet.getDataRange().getValues();
  
//  Logger.log ("data" + data.length);
  for (i = 0; i < data.length; i += 1) {
    var column = data[i]; // each column[i]
//    Logger.log ("Line" + i);
    // approved?
    if (column[0] == "Yes") {
      
      // timestamp doesn't exist
      if (!column[26] || column[26] == "") {
        time = new Date();
        column[26] = Utilities.formatDate(time, "US/Eastern", "YYY-MM-dd h:mm:ssa");
//        Logger.log ("Added Timestamp");
      } // if a timestamp exists, we leave it alone
//      else
//        Logger.log ("Yes, but timestamp already exists: " + i); 
        
    } else {
      column[26] = "";
//      Logger.log ("Removed timestamp");
    }
//    Logger.log ("Row: " + i);
  }
  sheet.getDataRange().setValues(data);
//  Logger.log ("Ending.");
  
}


//function onEdit() {
//  var s = SpreadsheetApp.getActiveSheet();
//  if( s.getName() == "All Events" ) { //checks that we're on the correct sheet
//    var r = s.getActiveCell();
//    if( r.getColumn() == 1 ) { //checks the column
//      if (r.getValue().toLowerCase().replace(/\s/g,"") === 'yes') {
//        var timestampCell = r.offset(0, 26); // use column 27 (first column + 26) for the timestamp
//        var time = new Date();
//        time = Utilities.formatDate(time, "US/Eastern", "YYY-MM-dd h:mm:ssa");
//        timestampCell.setValue(time);
//      } else {
//        var timestampCell = r.offset(0, 26);
//        timestampCell.setValue('');
//      }
//    };
//  }
//}