Google Apps Script - Create Calendar Event from Spreadsheet

Question

I have near 0 experience in programming. I would like to import a range from Google Spreadsheets as an array, check each value in the array for True or False, and if True, create an All Day Calendar Event with the Title of the All Day Calendar Event being the value from the same row in a different array.

Example:

Table

_: 5/11, 5/12, 5/13
A: True, False, True
B: True, False, False
C: True, True, True

Should create Calendar Events named "A" on 5/11 and 5/13, "B" on 5/11, and "C" on 5/11, 5/12, and 5/13

So far, this has worked, but is not efficient. Can someone help me optimize this?

var Checked = spreadsheet.getRange("C5:I20").getValues();
var Person = spreadsheet.getRange("B5:B20").getValues();

var mon = new Date(Object(spreadsheet.getRange("C3").getValues()));
var tue = new Date(Object(spreadsheet.getRange("D3").getValues()));
var wed = new Date(Object(spreadsheet.getRange("E3").getValues()));
var thu = new Date(Object(spreadsheet.getRange("F3").getValues()));
var fri = new Date(Object(spreadsheet.getRange("G3").getValues()));
var sat = new Date(Object(spreadsheet.getRange("H3").getValues()));
var sun = new Date(Object(spreadsheet.getRange("I3").getValues()));      

for (x=0; x<Checked.length; x++) {  
  for (x=0; x<Person.length; x++) {
      var shift = Checked[x];
      var Names = Person[x];

      if(shift[0] = 1)
      eventCal.createAllDayEvent(Object(Names), mon);
      if(shift[1] = 1)
      eventCal.createAllDayEvent(Object(Names), tue);
      if(shift[2] = 1)
      eventCal.createAllDayEvent(Object(Names), wed);
      if(shift[3] = 1)
      eventCal.createAllDayEvent(Object(Names), thu);
      if(shift[4] = 1)
      eventCal.createAllDayEvent(Object(Names), fri);
      if(shift[5] = 1)
      eventCal.createAllDayEvent(Object(Names), sat);
      if(shift[6] = 1)
      eventCal.createAllDayEvent(Object(Names), sun);
      Utilities.sleep(1000)

It's hard to tell where you going with this. Perhaps an image of your spreadsheet might help and a simple explanation of what you want to do. — Cooper

Cooper Cooper · Accepted Answer · 2020-05-18T18:49:31

Maybe this will help you to get started:

This function will create all day events for all of the checked rows in the spreadsheet at the bottom.

function myfunction() {
  const cal=CalendarApp.getCalendarById('id');
  const ss=SpreadsheetApp.getActive();
  const sh=ss.getSheetByName('Sheet1');
  const rg=sh.getRange('B5:E20');
  var vA=rg.getValues();
  vA.forEach(function(r,i){
    if(r[0]) {
      cal.createAllDayEvent(r[1], new Date(r[2]), new Date(r[3]))
    }
  }); 
}

Spreadsheet:

Google Apps Script - Create Calendar Event from Spreadsheet

1 Answers