Google script to create calendar event for selected multiple rows-CodePudding

I tried to put together a script which would create calendar events only for the check marked rows in a spreadsheet, but if I had 3 rows checked, it would create 3 events based on the first checked row (so 3 same events). Console log identifies the 3 different rows, but .createEvent takes the first checked row only, does not move onto the second and the third. Here's the code, what have I messed up?


function createThickEvents() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("formhelper");
  var data = sheet.getRange("A1").getDataRegion().getValues()
  var approveData = data.filter(r => r[10] === true && r[11] === "")

  var cal1 = "calendarID";
  var cal2 = "calendarID";
  var cal3 = "calendarID";
  var cal4 = "calendarID"            
  var calendars = {
  Name1: CalendarApp.getCalendarById(cal1),
  Name2: CalendarApp.getCalendarById(cal2),
  Name3: CalendarApp.getCalendarById(cal3),
  Name4: CalendarApp.getCalendarById(cal4)}


  approveData.forEach(r => {

  var emailAddress = approveData[0][1];
  var title = approveData[0][2];
  var tstart = new Date(approveData[0][3]);
  var tstop = new Date(approveData[0][4]);
  var usingDays = approveData[0][6];
  var remainingDays = approveData[0][9];
  var desc = approveData[0][5];
  var calComp = approveData[0][8];
  var newEventComp = calendars[calComp].createEvent(title, tstart, tstop, {description:desc})

  console.log(approveData)
})
}

CodePudding user response：

I would do it like this:

function createThickEvents() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sh = ss.getSheetByName("formhelper");
  const vs = sh.getRange("A1").getDataRegion().getValues();
  const cal1 = "calendarID";
  const cal2 = "calendarID";
  const cal3 = "calendarID";
  const cal4 = "calendarID"
  const calendars = {
    Name1: CalendarApp.getCalendarById(cal1),
    Name2: CalendarApp.getCalendarById(cal2),
    Name3: CalendarApp.getCalendarById(cal3),
    Name4: CalendarApp.getCalendarById(cal4)
  }
  vs.forEach(r => {
    if(r[10] === true && r[11] =="") {
      let emailAddress = r[1];
      let title = r[2];
      let tstart = new Date(r[3]);
      let tstop = new Date(r[4]);
      let usingDays = r[6];
      let remainingDays = r[9];
      let desc = r[5];
      let calComp = r[8];
      calendars[calComp].createEvent(title, tstart, tstop, { description: desc })
    }
  })
}