Why every all-day event is represented as 2 day event

[nagastar]

I am using a script to download events from Google Calendar into a spreadsheet. I am having an issue where 'all day events' are coming in as 2 days, 

e.g.: all day event on 5/9/2023 downloads into the sheet as 5/9/2023-5/10/2023. 

 this is my code :

function getEvents(){

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var sheet = ss.getSheetByName("sheet1");

 var start_time = sheet.getRange("L5").getValue();

  var end_time = sheet.getRange("N5").getValue();

  var cal = CalendarApp.getCalendarById("calender ID");

  var events = cal.getEvents(new Date(start_time), new Date(end_time));

  for(var i = 1;i<events.length+1;i++){

    var title = events[i-1].getTitle();

    var start_time = events[i-1].getStartTime();

    var end_time = events[i-1].getEndTime();

    var loc = events[i-1].getLocation();

    var des = events[i-1].getDescription();

    sheet.getRange(i+1,1).setValue(start_time);

    sheet.getRange(i+1,2).setValue(end_time);

    sheet.getRange(i+1,4).setValue(title);

    sheet.getRange(i+1,5).setValue(loc);

    sheet.getRange(i+1,6).setValue(des);

  }

  Logger.log("Events have been added to the Spreadsheet");

}

[Jose armando Jara Osores]

The script seems to be written correctly in terms of syntax and logic. However, there is a small bug in the iteration of the for loop. Since array indices start at 0, the line `for(var i = 1; i < events.length + 1; i++)` should be changed to `for(var i = 0; i < events.length; i++) `. In this way, it will correctly iterate over all the events obtained.

Also, make sure to replace "calender ID" with the actual ID of the calendar you want to get the events from.

If you don't find any other problems or errors, and the script meets your requirements and is configured correctly, it should work as expected. As always, it is advisable to carry out tests and verify that the results are as expected

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/1d2e28b5-ff14-4acf-9f24-008d6bd30909n%40googlegroups.com.

[nagastar]

I've replaced it but the problem still persists is still downloading the whole day's events into 2 days counting in the spreadsheet.

My calendar id is not displayed here for privacy purposes only.

here my code:

function getEvents2(){

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var sheet = ss.getSheetByName("sheet1");

 var start_time = sheet.getRange("L5").getValue();

  var end_time = sheet.getRange("N5").getValue();

  var cal = CalendarApp.getCalendarById("ID calender");

  var events = cal.getEvents(new Date(start_time), new Date(end_time));

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

    var title = events[i].getTitle();

    var start_time = events[i].getStartTime();

    var end_time = events[i].getEndTime();

    var loc = events[i].getLocation();

    var des = events[i].getDescription();

    sheet.getRange(i+2,1).setValue(start_time);

    sheet.getRange(i+2,2).setValue(end_time);

    sheet.getRange(i+2,4).setValue(title);

    sheet.getRange(i+2,5).setValue(loc);

    sheet.getRange(i+2,6).setValue(des);

  }

  Logger.log("Events have been added to the Spreadsheet");

}

[Emerson Maia]

An alternative follows.

function getEvents(){
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("sheet1");
  const start_time = new Date(sheet.getRange("L5").getValue());
  const end_time = new Date(sheet.getRange("N5").getValue());
  const cal = CalendarApp.getCalendarById("calender ID");
  const events = cal.getEvents(start_time, end_time);

  events.forEach((event, i) => {
    const title = event.getTitle();
    let start_time = event.getStartTime();
    let end_time = event.getEndTime();
    const loc = event.getLocation();
    const des = event.getDescription();

    // Check if the event is an all-day event
    if (start_time.getHours() === 0 && start_time.getMinutes() === 0 && end_time.getHours() === 0 && end_time.getMinutes() === 0) {
      // Adjust the end time back by one day
      end_time.setDate(end_time.getDate() - 1);

    }

    sheet.getRange(i+2,1).setValue(start_time);
    sheet.getRange(i+2,2).setValue(end_time);
    sheet.getRange(i+2,4).setValue(title);
    sheet.getRange(i+2,5).setValue(loc);
    sheet.getRange(i+2,6).setValue(des);

  });

  Logger.log("Events have been added to the Spreadsheet");
}