How do I get this script to re-order so that when it exports the data the most recent entries are at the top? At the moment it puts the oldest data a the top.

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 12, 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(1, 1, 1, 14);
    range.setValues(header);
    for (var i = 0; i < events.length; i  ) {
        var row=i 2;
        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');
    }
}

CodePudding user response：

Modify the Formula in your For Loop

In your for loop, modify the formula for your row from:

var row=i 2;

to:

var row = events.length 1 - i;

This will display the most recent entry the top of the row.

CodePudding user response：

In your situation, how about the following modification?

Modified script:

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 12, 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 values = [header, ...events.map((e, i) => [mycal, e.getTitle(), e.getDescription(), e.getLocation(), e.getStartTime(), e.getEndTime(), , (''   e.getVisibility()), e.getDateCreated(), e.getLastUpdated(), e.getMyStatus().toString(), e.getCreators().join(","), e.isAllDayEvent(), e.isRecurringEvent()]).reverse()];
  sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
  sheet.getRange(2, 7, values.length - 1).setFormula('=(HOUR(F2) (MINUTE(F2)/60))-(HOUR(E2) (MINUTE(E2)/60))');
}

• In this modification, the values are retrieved from the Event object and an array is created. By this, the process cost can be reduced, and also reverse can be used. By this modification, the retrieved events are arranged in descending order.

• I modified it to put the formulas in column "G".