To preface this, I have never taken a coding class in my life and the only things I know about Google Apps Script are things I've read on here or their reference page. So not incredibly technical or very detailed responses would be greatly appreciated.

    function scheduleShifts() {

var spreadsheet = SpreadsheetApp.getActiveSheet();
var calendarId = spreadsheet.getRange("K1").getValue();
var eventCal = CalendarApp.getCalendarById(calendarId);
var EmpLvl = spreadsheet.getRange("H2").getValue();
var count = spreadsheet.getRange("I1").getValue();

var signups = spreadsheet.getRange(2,2,count,7).getValues();


for (x=0; x<signups.length; x  ) {
    
    var shift = signups[x];
    
    var startTime = shift[4];
    var endTime = shift[5];
    var volunteer = shift[3];
   
    
    eventCal.createEvent(volunteer, startTime, endTime, {description: "Please make sure your PTO is submitted"}).addEmailReminder(1440).addGuest(spreadsheet.getRange("B2").getValue())

    if (EmpLvl == "Partner"){
      setColor("11");
    }else if (EmpLvl == "Principal"){
      setColor("6");
    }else if (EmpLvl == "Senior Manager"){
      setColor("9");
    }else if (EmpLvl == "Manager"){
      setColor("1")
    }else if (EmpLvl == "Senior"){
      setColor("10")
    }else if (EmpLvl == "Staff"){
      setColor("2")
    }else if (EmpLvl == "Intern"){
      setColor("5")
    }else if (EmpLvl == "Director"){
      setColor("7")
    }else if (EmpLvl == "Administration"){
      setColor("3")
    }if (EmpLvl == "Outside Consultant"){
      setColor("8")
    }

}

spreadsheet.deleteRows(2,count)
spreadsheet.getRange("I1").clearContent();
spreadsheet.getRange("I1").setFormula("=COUNT($A$1:$A$1103)")
}

Google Sheet for Reference

Apps Script Code w/ Error

Essentially what I've created so far is code that creates events in Google Calendar (GC) from information in a Google Sheet (GS) that is linked to a Google Form (GF). The process is as follows.

1. User fills out GF
2. Information automatically dumps into GS
3. The form submission triggers the function to run
4. The function creates an event in GC
5. The information is deleted from the GS after the the event has been created

-Rinse and Repeat-

The Issue: I'm trying to make the GC events different colors based on the form user's employee level. I'm currently trying to use an If else ladder to do it but I cant figure out how to reference the event I'm creating in line 21. Its giving me "ReferenceError: setColor is not defined scheduleShifts @ Code.gs:24" and I don't know how to define it or if this method will even work. I appreciate the help in advance!

Bonus Problem: I have no idea how to send a notification to a guest that they were added to the event with the code.

The base features of the code work just fine it's just those two little things that are making me lose sleep.

CodePudding user response：

.createEvent() returns a "CalendarEvent" resource as mentioned here, this can be stored in a variable to access its methods and apply changes to that specific instance, this is how it would look in your code:

var event = eventCal.createEvent(volunteer, startTime, endTime, {description: "Please make sure your PTO is submitted"}).addEmailReminder(1440).addGuest(spreadsheet.getRange("B2").getValue())

    if (EmpLvl == "Partner"){
      event.setColor("11");
    }else if (EmpLvl == "Principal"){
      event.setColor("6");
    }else if (EmpLvl == "Senior Manager"){
      event.setColor("9");
    }else if (EmpLvl == "Manager"){
      event.setColor("1")
    }else if (EmpLvl == "Senior"){
      event.setColor("10")
    }else if (EmpLvl == "Staff"){
      event.setColor("2")
    }else if (EmpLvl == "Intern"){
      event.setColor("5")
    }else if (EmpLvl == "Director"){
      event.setColor("7")
    }else if (EmpLvl == "Administration"){
      event.setColor("3")
    }if (EmpLvl == "Outside Consultant"){
      event.setColor("8")
    }

Now, to send invites using CalendarApp you'll need to specify the guests under the "options" parameter when the event is being created, and use the "sendInvites" flag like this:

var event = eventCal.createEvent(volunteer, startTime, endTime,
  {description: "Please make sure your PTO is submitted", 
  sendInvites: true, 
  guests: spreadsheet.getRange("B2").getValue()})
  .addEmailReminder(1440);

More info about the available parameters here. I found this other post related to calendar events and the ability to send invites after the event was created using the Advanced Calendar Service.