CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-calendar-api
bdkauff
bdkauff

Reputation: 245

Incorrect End Date for event

I've implemented a script in my google sheet that grabs gcal calendar info and displays it in various tabs of the sheet. However, the end date of each event as displayed in the sheet is one day LONGER than what is displayed in the calendar itself. Code below. Through logging, I can't figure out why its happening. Is there something to do with the formatting of the date? Any help would be appreciated!


function populateAllTabs() {
  var id = "[MY CAL ID HERE]";   // id is currently set to bookings calendar. NB: Takes a string!
  var cal = CalendarApp.getCalendarById(id);
  var startPeriod = new Date('January 1, 2020');
  startPeriod.setHours(0, 0, 0, 0);
  var endPeriod = new Date(startPeriod);
  endPeriod.setDate(endPeriod.getDate() + 365); // looks for all events in the range one year
  var ss = SpreadsheetApp.getActive();
  for(var n in ss.getSheets()){// loop over all tabs in the spreadsheet
    var sheet = ss.getSheets()[n];// look at every sheet in spreadsheet
    var name = sheet.getName();//get name

    if(name != 'List'){ 

        var gig = sheet.getRange(1,1);
        var gigName = gig.getValue();
        var events = cal.getEvents(startPeriod, endPeriod, {search:gigName});


        // find the title of each event in the calendar
        var eventTitles = [];
         for (var i = 0; i < events.length; i++) { 
           eventTitles.push([events[i].getTitle()]);    
         }

        // find the start date of each event in the calendar
       var starttime = [];
         for (var i = 0; i < events.length; i++) { 
           starttime.push([Utilities.formatDate(events[i].getStartTime(), "GMT", "MM/dd/yy")]);    
         }

        // find the end date of each event in the calendar
       var endtime = [];
         for (var i = 0; i < events.length; i++) { 
           endtime.push([Utilities.formatDate(events[i].getEndTime(), "GMT", "MM/dd/yy")]);    
         }

       var cell = sheet.getRange("B3");

       cell.setValue(starttime + ' - ' + endtime);
    }
  }
}

Upvotes: 0

Views: 355

Answers (1)

Cooper
Cooper

Reputation: 64140

This works for me. No problem with the dates

function populateAllTabs() {
  var cal = CalendarApp.getCalendarById('id');
  var startyear=2019;
  var startPeriod = new Date(startyear,0,1);
  var endPeriod = new Date(startyear+1,1,1);
  var ss=SpreadsheetApp.getActive();
  var shts=ss.getSheets();
  for(var n=0;n<shts.length;n++){
    var sheet=shts[n];
    var name=sheet.getName();
    if(name!='List'){ 
      var gigName = sheet.getRange(1,1).getValue();
      var ev=cal.getEvents(startPeriod, endPeriod, {search:gigName});
      var gigs=[]
      for (var i=0;i< ev.length;i++) { 
        gigs.push([ev[i].getTitle(),Utilities.formatDate(new Date(ev[i].getStartTime()),Session.getScriptTimeZone(),"MM/dd/yy"),Utilities.formatDate(new Date(ev[i].getEndTime()),Session.getScriptTimeZone(),"MM/dd/yy")]);    
      }
      if(gigs) {
        sheet.getRange(3,2,gigs.length,gigs[0].length).setValues(gigs);
      }
    }
  }
}

Upvotes: 1

Related Questions