Automating a script to pick up the local current time

Question

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');
    }
}

How do you know the `start date` & `end date`? If **July 1** is the `start date` & **Sept [current day]** is the `end date` for this month, does this mean that for the next month it will be **Sept 1** as `start date` & **Oct [whatever current day]** as `end date`? Or do you have a specific process in identifying your date ranges? — SputnikDrunk2, Sep 15 '22 at 22:24
@irvin Jag G. It's based on a financial year mate, so the 2023 financial year is the period I'm looking at however we develop stats based on year-to-date work. If I bring in future dates (which I've already tried) it brings in future reminders etc and throws everything out. Everyone updates their calendar at the end of the day so everything up to that date is correct. And 01 July is the start date all the way through, untill next year where we will reset it. — RoosterMagic22, Sep 16 '22 at 03:41
Very good questions though, i didn't think to look at it that way - but does what I said make sense? — RoosterMagic22, Sep 16 '22 at 03:43
Can you provide a sample expected output. Like for e.g. what would be the specific `start` & `end` dates for the next financial year? TBH I have very limited knowledge about this & upon researching, there are actually different fiscal year/financial years, e.g. Australia’s FY starts on July 1 and ends on June 30. & United States Federal Government’s FY starts on October 1 and ends on September 30. So it is safe to assume that you're following the Australia’s FY, correct? — SputnikDrunk2, Sep 16 '22 at 04:00
Hey mate - I think we are going in the wrong direction, don't worry about periods or financial years etc I'm happy to use use 1 July - I just want the end date to be today local time. I was hoping to use something like this; const now = new Date(); and then somehow incorporate the following "console.log(Utilities.formatDate(date, 'Australia/Brisbane', 'MMMM dd, yyyy HH:mm:ss Z'))" But I don't know how to write it — RoosterMagic22, Sep 18 '22 at 20:12

score 0 · Accepted Answer · answered Sep 19 '22 at 11:27

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.

Automating a script to pick up the local current time

1 Answers1