CODEWITHSUNDEEP
google-calendar-apicurrent-time
RoosterMagic22
RoosterMagic22

Reputation: 69

Automating a script to pick up the local current time

How can I write the following script so that it picks up the local current time rather than me having to input the date each day? Note July 1 is the correct start date it's the second date that I have to change each time, as I'm importing a range of data from Gcal to Gsheets to review.

function export_gcal_to_gsheet() {
    var mycal = "email";
    var cal = CalendarApp.getCalendarById(mycal);
    var events = cal.getEvents(new Date("July 01, 2022 00:00:00 UTC"), new Date ("September 15, 2022 23:59:59 UTC"));
    var sheet = SpreadsheetApp.getActiveSheet();
    sheet.clearContents();  
    var header = [["Calendar Address", "Event Title", "Event Description", "Event Location", "Event Start", "Event End", "Calculated Duration", "Visibility", "Date Created", "Last Updated", "MyStatus", "Created By", "All Day Event", "Recurring Event"]]
    var range = sheet.getRange(6, 1, 1, 14);
    range.setValues(header);
    for (var i = 0; i < events.length; i++) {
        var row = events.length + 6 - i;
        var myformula_placeholder = '';
        var details=[[mycal,events[i].getTitle(), events[i].getDescription(), events[i].getLocation(), events[i].getStartTime(), events[i].getEndTime(), myformula_placeholder, ('' + events[i].getVisibility()), events[i].getDateCreated(), events[i].getLastUpdated(), events[i].getMyStatus(), events[i].getCreators(), events[i].isAllDayEvent(), events[i].isRecurringEvent()]];
        var range=sheet.getRange(row,1,1,14);
        range.setValues(details);
        var cell=sheet.getRange(row,7);
        cell.setFormula('=(HOUR(F' +row+ ')+(MINUTE(F' +row+ ')/60))-(HOUR(E' +row+ ')+(MINUTE(E' +row+ ')/60))');
        cell.setNumberFormat('.00');
    }
}

Upvotes: 0

Views: 50

Answers (1)

RoosterMagic22
RoosterMagic22

Reputation: 69

I wasn't able to get it to do local time but I brought in the following for today;

I replaced the second "(new Date("July 01, 2022 00:00:00 UTC")" with "new Date ());"

So now I have;

function export_gcal_to_gsheet() {
    var mycal = "Email";
    var cal = CalendarApp.getCalendarById(mycal);
    var events = cal.getEvents(new Date("July 01, 2022 00:00:00 UTC"), new Date ());
    var sheet = SpreadsheetApp.getActiveSheet();
    sheet.clearContents();  
    var header = [["Calendar Address", "Event Title", "Event Description", "Event Location", "Event Start", "Event End", "Calculated Duration", "Visibility", "Date Created", "Last Updated", "MyStatus", "Created By", "All Day Event", "Recurring Event"]]
    var range = sheet.getRange(6, 1, 1, 14);
    range.setValues(header);
    for (var i = 0; i < events.length; i++) {
        var row = events.length + 6 - i;
        var myformula_placeholder = '';
        var details=[[mycal,events[i].getTitle(), events[i].getDescription(), events[i].getLocation(), events[i].getStartTime(), events[i].getEndTime(), myformula_placeholder, ('' + events[i].getVisibility()), events[i].getDateCreated(), events[i].getLastUpdated(), events[i].getMyStatus(), events[i].getCreators(), events[i].isAllDayEvent(), events[i].isRecurringEvent()]];
        var range=sheet.getRange(row,1,1,14);
        range.setValues(details);
        var cell=sheet.getRange(row,7);
        cell.setFormula('=(HOUR(F' +row+ ')+(MINUTE(F' +row+ ')/60))-(HOUR(E' +row+ ')+(MINUTE(E' +row+ ')/60))');
        cell.setNumberFormat('.00');
    }
}

Seems to work pretty well - set up a trigger every time my calendar is updated to run this script and it brings everything over up to the current date automatically - works really well - only posting as I thought others might use it.

Upvotes: 0

Related Questions