Apps Script getEventById() returns null

Question

I am new to Apps Script and struggling with the "getEventById()" function. My goal is to delete an event entry on Google Calendar via Google Sheets when you press a button. I already managed to get the event id via Apps Script and it´s Google API V3, but when I hand it over to "getEventById" as parameter, it returns null, even when I "hardcode" the id.

Here´s my code. I removed some parts since those aren´t important I think:

function calDate(){

  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getActiveSheet();

  var calId = spreadsheet.getRange("N1").getValue();
  var calEvent = CalendarApp.getCalendarById(calId);
  var ui = SpreadsheetApp.getUi();


  var selection = spreadsheet.getSelection();
  var selectedRow = selection.getActiveRange().getA1Notation();

  var rowRange = sheet.getRange(selectedRow);
  var rowNumber = rowRange.getRow();

  var colRange = sheet.getRange(selectedRow);
  var colNumber = colRange.getColumn();

  if (colNumber !== 15){
    //wait for showtime
  }
  else{
    // its showtime

    var combinedRange = "O" + rowNumber;

    var sheetData = sheet.getRange(rowNumber, 3, 1, 15).getValues();

    if(sheetData[0][12] == false){

      var dateStart = new Date(sheetData[0][7]);
      var dateEnd = new Date(sheetData[0][8]);
      var KdName = sheetData[0][0];
      var BV = event_id[0][4];
      var combinedNames = KdName + " - " + BV;


      var items = Calendar.Events.list(calId, {
        timeMin: dateStart.toISOString(),
        timeMax: dateEnd.toISOString(),
        q: combinedNames
      }).items;

    }
    else{
      var testVar = calEvent.getEventById(/*This is where I would put the htmlLink (the event-id)*/);
    console.log(testVar);
    }

    }
  }

Hopefully those informations are enough and if not, feel free to ask for more. I really hope you guys can help me out!

Kind regards

EDIT & SOLUTION

Okay guys, thanks to Mateo Randwolf, who kindly opened an issue at Google about this, I was able to figure it out. This is the link with an example how to get the the ID from the event and hand that id over to the "getEventById()" function. Or here as a code-block:

    function findEventID() {
      var now = new Date();
      var nextEvent = new Date(now.getTime() + (2 * 60 * 60 * 1000));
      var event = CalendarApp.getDefaultCalendar().getEvents(now, nextEvent);
      ID = event[0].getId();

      Logger.log('EventID: ' + event[0].getId());
      Logger.log(CalendarApp.getDefaultCalendar().getEventById(ID));
   }

Now it gets funny. This line:

Logger.log('EventID: ' + event[0].getId());

returns the event-id like it should.

But this one:

Logger.log(CalendarApp.getDefaultCalendar().getEventById(ID));

doesn´t show anything except "{}", which is weird. But if you apply "deleteEvent()" on it, like so:

calEvent.getEventById(ID).deleteEvent(); //calEvent is a CalendarApp, see above

It actually deletes the event from the calendar! With that, I´d say we found the solution or at least a bypass.

Answer 1

Using the Calendar API search query to find events in a calendar

function calDate(){
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var sh=ss.getActiveSheet();
  var calId=ss.getRange("N1").getValue();
  var calEvent=CalendarApp.getCalendarById(calId);
  var row=ss.getActiveRange().getRow();
  var col=ss.getActiveRange().getColumn()
  if (col!=15){
    //wait for showtime
  }else{
    var vs=sh.getRange(row, 3, 1, 15).getValues();
    if(vs[0][12] == false){
      var dateStart=new Date(vs[0][7]);//col J
      var dateEnd=new Date(vs[0][8]);//col K
      var KdName=vs[0][0];//col 3

event_id below is not defined

      var BV=event_id[0][4];//col G
      var combinedNames=KdName + " - " + BV;
      var items=Calendar.Events.list(calId, {timeMin: dateStart.toISOString(),timeMax: dateEnd.toISOString(),q: combinedNames}).items;
    }
    else{
      var testVar=calEvent.getEventById(/*This is where I would put the htmlLink (the event-id)*/);
      console.log(testVar);
    }
  }
}

Since you couldn't share your spreadsheet I share mine with an example

One thing that helps a lot is playing with the API explorer to figure what works and what doesn't. If you want to display all of the fields you can use * and this example proved very helpful as well

Here's the code:

function myOwnEventSearch() {
  var calendarId='***********calendar id**************';
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Sheet235');
  var sr=2;
  var sc=2
  var rg=sh.getRange(sr,sc,sh.getLastRow()-sr+1,sh.getLastColumn()-sc+1);
  var vA=rg.getValues();
  var hA=sh.getRange(sr-1,sc,1,sh.getLastColumn()-sc+1).getValues()[0];
  var idx={};//locates the data index from column header names
  hA.forEach(function(h,i){idx[h]=i;});
  var cal=CalendarApp.getCalendarById(calendarId);
  var html='<style>td,th{}</style><table><tr><th>Summary</th><th>Start</th><th>End</th><th>Id</th></tr>'
  for(var i=0;i<vA.length;i++) {
    if(!vA[i][idx['Id']] && vA[i][idx['DateFrom']] && vA[i][idx['DateTo']] && vA[i][idx['SearchString']]) {
      var request={timeMin:new Date(vA[i][idx["DateFrom"]]).toISOString(),timeMax:new Date(vA[i][idx["DateTo"]]).toISOString(),q:vA[i][idx["SearchString"]],showDeleted: false,singleEvents:true,maxResults:10,orderBy:"startTime"};
      var resp=Calendar.Events.list(calendarId, request);
      if(resp.items.length>0) {
        var idA=[];
        resp.items.forEach(function(item,j){
          html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>', item.summary,item.start,item.end,item.id);
          idA.push(item.id);
        });
        sh.getRange(i+sr,idx['Id']+sc).setValue(idA.join(', '))
      }
    }
  }
  html+='<table>';
  SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutput(html).setWidth(800), "My Events")
}

Here's the spreadsheet before the script runs.

Here's the dialog that displays the search results.

Here's what the spreadsheet looks like after running script:

The Event Ids were copied into the Id Column

And these were the four events I created on my calendar::

Answer 2

Issue

Hi ! So it seems to me that getEventById() has a bug that returns null instead of the event object as I was getting the exact same behaviour you were reporting in this question. I have filed this behaviour to the public issue tracker, you can find it here with all the discussion on this behaviour.

I hope this has helped you. Let me know if you need anything else or if you did not understood something. :)

Answer 3

Here is how I worked around this. I stored all the events (from the range I was interested in) in a JavaScript Map() so I can find them later:

  var cal = CalendarApp.getCalendarById("/* supply your calendar ID here*/");
  if (!cal) {
    Logger.log("Calendar not found for ID:" + calendarID);
  } else {
    var calEvents = cal.getEvents(new Date("March 8, 2010"), new Date("March 14, 2025"));
    // Store them by eventId for easy access later
    var calEventMap = new Map();
    for (var j in calEvents) {
      calEventMap.set(calEvents[j].getId(), calEvents[j]);
    }

    /* Later when you need to look up by iCalID... */

    var calEvent = calEventMap.get(eventID);

  }

Answer 4

Works for me when you get the calendar by id like this:

const calendar = CalendarApp.getCalendarById("theCalendarId");

const event = calendar.getEventById("someEventId");

Now the event is not null, but the actual event, and you can do whatever you want with it from here!