Here is the code I have to add the sheets events to the calendar, and I have also linked the sheets document, I am looking to add some code that will allow me to change the color of my calendar events that are completed (True in column E) to gray when I run my google script program. If it matters, by default the calendar color is yellow. Thanks!

Google Sheets:

function oneDate(){
  let sheet = SpreadsheetApp.getActiveSheet()
  let id = SpreadsheetApp.getActive().getRange("D1").getValue()
  // assignments needs to include all the info for the events, not just the title
  let assignments = SpreadsheetApp.getActive().getRangeByName("fullList").getValues().filter(array => array.slice(0, -1).some(value => value !== ''));

  // console.log(id)
  // console.log(assignments)


  assignments.forEach(function(e,index){
    // added e[1] so it only includes events WITHOUT a checkbox and WITH a title
    if(!e[0] && e[1]){
      CalendarApp.getCalendarById(id)
      .createAllDayEvent(
        // gets title from column B (referenced by e[1])
        e[1],
        // gets date from column C (referenced by e[2])
        e[2]);
        // keeps track of which row loop is on
        let newIndex = index+5
        // checks the box in column A after event created
        sheet.getRange("A"+ newIndex).setValue(true);

      
    }
  })
}

I've tried integrating .setColor(Color) to my .createAllDayEvent, but could not figure out if that was the correct way to do it, any way I tried adding it I was getting an error that .setColor was not a function of .createAllDayEvent. I also tried adding .setColor as another line as a {description} of .createAllDayEvent, but got the same error

Here is the code I have to add the sheets events to the calendar, and I have also linked the sheets document, I am looking to add some code that will allow me to change the color of my calendar events that are completed (True in column E) to gray when I run my google script program. If it matters, by default the calendar color is yellow. Thanks!

Google Sheets: https://docs.google/spreadsheets/d/1_txF7ooW9Ty9gQ9a9wLMBlfg5phzGcPjJ_UP-ehNQmM/edit?usp=sharing

function oneDate(){
  let sheet = SpreadsheetApp.getActiveSheet()
  let id = SpreadsheetApp.getActive().getRange("D1").getValue()
  // assignments needs to include all the info for the events, not just the title
  let assignments = SpreadsheetApp.getActive().getRangeByName("fullList").getValues().filter(array => array.slice(0, -1).some(value => value !== ''));

  // console.log(id)
  // console.log(assignments)


  assignments.forEach(function(e,index){
    // added e[1] so it only includes events WITHOUT a checkbox and WITH a title
    if(!e[0] && e[1]){
      CalendarApp.getCalendarById(id)
      .createAllDayEvent(
        // gets title from column B (referenced by e[1])
        e[1],
        // gets date from column C (referenced by e[2])
        e[2]);
        // keeps track of which row loop is on
        let newIndex = index+5
        // checks the box in column A after event created
        sheet.getRange("A"+ newIndex).setValue(true);

      
    }
  })
}

I've tried integrating .setColor(Color) to my .createAllDayEvent, but could not figure out if that was the correct way to do it, any way I tried adding it I was getting an error that .setColor was not a function of .createAllDayEvent. I also tried adding .setColor as another line as a {description} of .createAllDayEvent, but got the same error

• javascript
• google-apps-script

1 Answer 1

You are trying to set the colour of an event by adding .setColor(Color) to createAllDayEvent. But you get an error message.

The problem is that you are confusing "calendar" with "event"

• .createAllDayEvent is a calendar method NOT an event method.
• .setColor(Color) is an event method; as used in this scenario, it is unrelated to creation of the event.

Consider this script. The key changes compared to the OP's code are in two parts:

Change#1

• var calendar = CalendarApp.getCalendarById(id) - a variable is assigned for the calendar
• var event = calendar.createAllDayEvent( - a variable is assigned for the created event.

Change#2

The last three lines of the script are new:

• var newEventId = event.getId() - gets the Id for the newly created event; using the variable.
• var newEvent = calendar.getEventById(newEventId) - gets the newly created event
• newEvent.setColor(CalendarApp.EventColor.GREEN) - changes the color of the newly created event.

function oneDate(){
  let sheet = SpreadsheetApp.getActiveSheet()
  let id = SpreadsheetApp.getActive().getRange("D1").getValue()
  
  // assignments needs to include all the info for the events, not just the title
  let assignments = SpreadsheetApp.getActive().getRangeByName("fullList").getValues().filter(array => array.slice(0, -1).some(value => value !== ''));

  // console.log(id)
  // console.log(assignments)


  assignments.forEach(function(e,index){
    // added e[1] so it only includes events WITHOUT a checkbox and WITH a title
    if(!e[0] && e[1]){
      var calendar = CalendarApp.getCalendarById(id)
      var event = calendar.createAllDayEvent(
        // gets title from column B (referenced by e[1])
        e[1],
        // gets date from column C (referenced by e[2])
        e[2]
      );
      // keeps track of which row loop is on
      let newIndex = index+5
      // checks the box in column A after event created

      // get the ID of the newly created event
      var newEventId = event.getId()
      // get the new created event
      var newEvent = calendar.getEventById(newEventId);
      // change the colour of the newly created event.
      newEvent.setColor(CalendarApp.EventColor.GREEN);
    }
  })
}

           
    
          
        }
      })
    }