Untitled

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

function addEvents() {
  var range = SpreadsheetApp.getActive().getActiveSheet().getDataRange();
  var rawData = range.getDisplayValues();

  //Add events based on the header named 'Created'. If a row has an empty value, it will call the function 'addCurrentEvent'; otherwise, it will be ignored.
  var result = rawData.map(column => {
    return !column[11].match(/created/i) ? column.map((cell, index) => index == 11 ? addCurrentEvent(column) : cell) : column
  });

  //Sets the sheet range with updated values.
  range.setValues(result);
}

function addCurrentEvent(column) {
  var cal = CalendarApp.getCalendarById("YOURGOOGLECALENDARIDHERE");
  var startDate = new Date(`${column[1]} ${column[2]}`);
  var endDate = new Date(`${column[3]} ${column[4]}`);
  var visibility = column[8].toLowerCase() == 'true' ? CalendarApp.Visibility.PRIVATE : CalendarApp.Visibility.PUBLIC;

  //'addEvent' will be called to run when creating an EVENT.
  const addEvent = (data) => {

    cal.createEvent(data[0], startDate, endDate, {
      location: data[7],
      description: data[6]
    }).setVisibility(visibility).addPopupReminder(parseFloat(data[9])).addPopupReminder(parseFloat(data[10]));

    //Log for review
    console.log(`"${data[0]}" event has been added!\n\nDETAILS\n\nStart date: ${startDate}\nEnd date: ${endDate}\nLocation: ${data[7]}\nDescription: ${data[6]}\nPrivacy: ${visibility}`);

    return `created`; //returns a 'created' value once the sheet row has been added
  };

  //'addAllDayEvent' will be called to run when creating an ALL DAY EVENT.
  const addAllDayEvent = (data) => {

    cal.createAllDayEvent(data[0], startDate, {
      location: data[7],
      description: data[6]
    }).setVisibility(visibility).addPopupReminder(parseFloat(data[9])).addPopupReminder(parseFloat(data[10]));

    //Log for review
    console.log(`"${data[0]}" all day event has been added!\n\nDETAILS\n\nStart date: ${startDate}\nEnd date: ${endDate}\nLocation: ${data[7]}\nDescription: ${data[6]}\nPrivacy: ${visibility}`);

    return `created`; //returns a 'created' value once the sheet row has been added
  };

  //Check and run the current data if it is an 'All Day Event' or an 'Event'
  return column[5].toLowerCase() == 'true' ? addAllDayEvent(column) : addEvent(column);
}