Troubleshooting script to amend Google Calendar from Google Sheets

Question

I have a GAS script run from a Google Sheet with two functions. The first function downloads calendar event details in a named calendar between two dates. The name of the calendar, the start date and the end date are read in from cells A2, B2, and C2 respectively. This function is working fine:

//Function to list certain events in your GCalendar.

function listMyEvents() {

  var sheet = SpreadsheetApp.getActiveSheet();
  var calid = sheet.getRange("A2").getValue();

    if(calid == null) {
      SpreadsheetApp.getActiveSpreadsheet().toast('Please enter a valid calendar name!');
      return;
      }

  var startTime = sheet.getRange("B2").getValue();

    if(startTime == 0) {
      SpreadsheetApp.getActiveSpreadsheet().toast('Please enter a start date!');
      return;
      }

    if(isNaN(startTime)) {
      SpreadsheetApp.getActiveSpreadsheet().toast('Please enter a valid start date!');
      return;
      }

  var endTime = sheet.getRange("C2").getValue();

    if(endTime == 0) { //If the cell doesn't contain anything send an error prompt
      SpreadsheetApp.getActiveSpreadsheet().toast('Please enter an end date');
      return;
      }

    if(isNaN(endTime)) { //If the cell holds text rather than a number then send an error prompt
      SpreadsheetApp.getActiveSpreadsheet().toast('Please enter a valid end date!');
      return;
      }

  var calendar = CalendarApp.getCalendarsByName(calid)[0]; //get the calendar name 
  var events = calendar.getEvents(startTime, endTime); 
  var data; //declare a variable to hold all the information.

  for(var i = 0; i < events.length;i++){
    var event = events[i];

  data = [
    event.getTitle(),
    event.getDescription(),
    event.getStartTime(),
    event.getEndTime(),
    event.getLocation(),
    event.getId(),
    "N"

    ];

  sheet.appendRow(data);

    }

sheet.autoResizeColumn(1); 
sheet.autoResizeColumn(2);
sheet.autoResizeColumn(3);
sheet.autoResizeColumn(4);
sheet.autoResizeColumn(5);
sheet.autoResizeColumn(6);
sheet.autoResizeColumn(7);

}

The second function tries to read in the event Id from column F, in order to allow individual events to be amended within the sheet before being written back to Google Calendar. However, the script errors on the line calendarEvent.getId() with the error Cannot call method "getId" of undefined. (line 118, file "Code").

I did read that you need to use the .getEventSeriesById() method - but the events aren't in a series! Is it possible to amend individual events with a script or do I need to delete all events and recreate them with amendments? My code for the second function is here:

function amendCalEvents() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 6;  // First row of data to process
  var numRows = sheet.getLastRow();   // Number of rows to process
  //var maxRows = Math.min(numRows,200); //Limit the number of rows to prevent enormous number of event creations
  var calid = sheet.getRange("A2").getValue(); //putting the calendar name in double quotes creates double entries. Who knew?
  var dataRange = sheet.getRange(startRow, 1, numRows, 7); //startRow, startCol, endRow, endCol
  var data = dataRange.getValues();
  var cal = CalendarApp.getCalendarsByName(calid)[0]; //get the calendar name 


  for (i in data) {
    var row = data[i];
    var title = row[0];      // column A
    var desc = row[1];       // column B
    var tstart = row[2];     // column C
    var tstop = row[3];      // column D
    var loc = row[4];        // column E
    var evId = row[5];       // column F
    var evDelFlag = row[6];  // column G

    var event = cal.calendarEvent.getId(evId);

    if (evDelFlag == "N" || evDelFlag == "n") {
          event.setTitle(title);
          event.setDescription(desc);
          event.setTime(tstart, tstop);
          event.setLocation(loc);
      } else  {
          cal.getId(evId).deleteEvent;
      } 

 }
}

Any help or pointers are most gratefully accepted.

Gerardo Gerardo · Accepted Answer · 2014-11-11T22:36:53

There are some changes you can do in the function amendCalEvents

1.- numRows variable, you are setting the value of getLasRow but you are not subtracting the rows used for the first information (e.g. if you have just 1 calendar and this calendar has one event, when you run the first function it'll add a new row and this variable will have 3 as value. so when you u call the function getRange, it will bring the information from 3 rows and not just the one that has the information.

2.- a workaround of using the getEventSeriesById, you can probably call again the function getEvents as you did in the first fucntion. As you are already looping in the for with a similar amount of data you could access the element inside the array o events and set the desired values.

var event = events[i];

3.- to delete the event now you can call:

event.deleteEvent();

Hope this helps

Troubleshooting script to amend Google Calendar from Google Sheets

1 Answers