Untitled

mail@pastecode.io avatar
unknown
javascript
a year ago
2.3 kB
9
Indexable
Never
function clearCreatedColumn(){
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  ss.getRange("L2:L").clearContent();
}

function addEvents() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lr = ss.getLastRow();
  var cal = CalendarApp.getCalendarById("YOURGOOGLECALENDARIDHERE");
  var data = ss.getRange("A2:L" + lr).getValues();
  var dateANDTimeArr = ss.getRange("A2:L" + lr).getDisplayValues();
  while (data[data.length - 1][0] == '') data.pop();
  var rangeList = [];
  for (var i = 0; i < data.length; i++) {

    if (data[i][11] == "created") continue; // Modified: This is column "L".

    var theEventStartDate = dateANDTimeArr[i][1];
    var startHour = dateANDTimeArr[i][2].length == 8 ? dateANDTimeArr[i][2].substring(0, 2) : dateANDTimeArr[i][2].substring(0, 1);
    var startMin = dateANDTimeArr[i][2].length == 8 ? dateANDTimeArr[i][2].substring(3, 5) : dateANDTimeArr[i][2].substring(2, 4);
    var theEventEndDate = dateANDTimeArr[i][3];
    var endHour = dateANDTimeArr[i][4].length == 8 ? dateANDTimeArr[i][4].substring(0, 2) : dateANDTimeArr[i][4].substring(0, 1);
    var endMin = dateANDTimeArr[i][4].length == 8 ? dateANDTimeArr[i][4].substring(3, 5) : dateANDTimeArr[i][4].substring(2, 4);
    Logger.log("theEventStartDate : " + theEventStartDate);
    Logger.log("startHour : " + startHour);
    Logger.log("startMin : " + startMin);
    Logger.log("theEventEndDate : " + theEventEndDate);
    Logger.log("endHour : " + endHour);
    Logger.log("endMin : " + endMin);
    var startDate = new Date(theEventStartDate + " " + startHour + ":" + startMin + ":00");
    var endDate = new Date(theEventEndDate + " " + endHour + ":" + endMin + ":00");

    // --- I modified the below script.
    var event = cal[data[i][5] ? "createAllDayEvent" : "createEvent"](data[i][0], startDate, endDate, { location: data[i][7], description: data[i][6] });
    event.setVisibility(CalendarApp.Visibility[data[i][8] ? "PRIVATE" : "DEFAULT"]); // "DEFAULT" might be "PUBLIC"?
    if (data[i][9].toString()) event.addPopupReminder(data[i][9]);
    if (data[i][10].toString()) event.addPopupReminder(data[i][10]);
    rangeList.push(`L${i + 2}`);
    // ---

  }
  if (rangeList.length == 0) return;
  ss.getRangeList(rangeList).setValue("created");
}