1

Data:

Row 1 - Have some Formulas and Format
Row 2 - Have the column name

  • A3 = XXXXXX (Subjet)
  • B3 = Call to Customer (Description)
  • C3 = 00:15:00 (Duration for the Event)
  • F3 = 5/12/20013 18:41:00 (StartDate)
  • I3 = 5/12/2013 18:56:00 (EndDate) (EndDate = sum from F3 + C3)
  • J3 = EventID (As read other topic to avoid duplicate is need identify each event with an ID.)
  • H3 = "Yes" (Create or Update Event) "Delete" (Delete the Event)

The appoiment will increase or decrease depend of the day for example one day many will have 20 and other 3.

All event will create & updated from the spreadsheet because we have created a exclusive Calendar for it.

Situation:

We have a lot of calls per day and is very estressfull create an event using calendar. That why we have an spreadsheet where all information is updateded automatically when we modify the Column B.

  • We want to create a Calendar Event for each row > 3
  • We want to prevent duplicated
  • If in the the column H the content is "Yes" the script should be Create or Update the event with the new date for that row. If said "Delete" should be only delete the EventID associated with that row.

Important: We update the start & end date every 15 or 30 min because we manage critical incident.

Test:

  • We try to use the script uploaded in this answer Here but the function only work for first two rows.
  • When we add new Subject and we run the script, the scrip not update the calendar.
  • Also we not if the first event is automatically updated with the information for the last row.

SCRIPT (1):

  • This Script Delete & Re-Create every time the Event. I want to update the existing event if exist or create it is doesn't exist.
  • This Script run for every row in the sheet. I want to run only just for the row modificated.

  • Didn't check Column H

    /**
 * 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
 */
function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "Export Events",
    functionName : "exportEvents"
  }];
  sheet.addMenu("Calendar Actions", entries);
};

/**
 * Export events from spreadsheet to calendar
 */
function exportEvents() {
  var sheet = SpreadsheetApp.getActiveSheet();
  if (sheet.getName() == "Sheet1"){
    var activeCell = sheet.getActiveCell(); //Detec the ActiveCell
    var row = activeCell.getRow(); //Detect the ActiveRow
    var headerRows = 2;  // Number of rows of header info (to skip)
  var range = sheet.getDataRange();
  var data = range.getValues();
  var calId = "8au195cppi3smt6m138cgjpr3o@group.calendar.google.com";
  var cal = CalendarApp.getCalendarById(calId);
  for (i in data) {
    if (i < headerRows) continue; // Skip header row(s)
    var row = data[i];
    var title = row[0];           // [0] First column// [1] Second column
    var tstart = row [5];
    var tstop = row [8];
    var desc = row[1];
    var id = row[9];              // Nine column == eventId
// Check if event already exists, delete it if it does
try {
  var event = cal.getEventSeriesById(id);
  event.deleteEventSeries();
  row[9] = '';  // Remove event ID    
}
catch (e) {
  // do nothing - we just want to avoid the exception when event doesn't exist
}
//cal.createEvent(title, new Date("March 3, 2010 08:00:00"), new Date("March 3, 2010 09:00:00"), {description:desc,location:loc});
var newEvent = cal.createEvent(title, tstart, tstop, {description:desc}).getId();
row[9] = newEvent;  // Update the data array with event ID
debugger;  }
  // Record all event IDs to spreadsheet
  range.setValues(data);
}
}
Community
  • 1
  • 1
LAD Service Desk
  • 289
  • 5
  • 14
  • 27

1 Answers1

0

try changing

data = range.getValues();

to

data = range.getDisplayValues();

From my knowledge getDisplayValues will retrieve string value of the cell

Sarimu
  • 39
  • 5