Question 51188 in Javascript Question posted in
A very good W3school tutorial can be found here.

App Script – only get events from the calendar of a specific color – Javascript

Writing a program in Google’s App Script to sync a Calendar to a Sheet.
The program successfully adds events from the Calendar into the spreadsheet whenever the Calendar is updated however I want to make it more specific.

Is there a way to set it so that the script only works for specific colours of the calendar?
I just want it to add the blue coloured events that are added to the calendar and not the red – to avoid it from uploading everyone’s annual leave to the events spreadsheet.

I’d like to keep everything to the same Calendar but only run the function for particular colours.

Also having problems with if statements. The below seems to work:

//DOES WORK :)
 if (description.includes("ASM Salford")) {
  asmsalfordsheet.getRange(i+12,1).setValue(startTime);
  asmsalfordsheet.getRange(i+12,2).setValue(title);
}

Which is great however if I try to make it more user-friendly by adding in an OR (||) clause or wildcards it just doesn’t work but doesn’t throw up any error message. Example:

//DOES NOT WORK BUT NO ERROR MESSAGE :(
 if (description.includes("ASM Salford" || "Salford")) {
  asmsalfordsheet.getRange(i+12,1).setValue(startTime);
  asmsalfordsheet.getRange(i+12,2).setValue(title);
}

// ALSO DOES NOT WORK BUT NO ERROR MESSAGE :(
 if (description.includes("*ASM*")) {
  asmsalfordsheet.getRange(i+12,1).setValue(startTime);
  asmsalfordsheet.getRange(i+12,2).setValue(title);
}

Any ideas? I want to add in some wildcards and || clauses so that it has more chance of picking up that description if the person entering the details into the calendar makes a spelling mistake, doesn’t fill it out fully, writes it in caps etc.

Any help would be greatly appreciated as I’m new to Apps Script.

My full code:

function getCalendarEvents() {

  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var monitoringsheet = spreadsheet.getSheetByName("Monitoring- Apr 23-Mar 24");
  var asmsalfordsheet = spreadsheet.getSheetByName("ASM Salford");
  var asmmcrsheet = spreadsheet.getSheetByName("ASM Mcr"); 
  var ccetamesidesheet = spreadsheet.getSheetByName("CCE Tameside");
  var lenjohnsonsheet = spreadsheet.getSheetByName("Len Johnson");
  var wmwsalfordsheet = spreadsheet.getSheetByName("WMW Salford");
  var radleedssheet = spreadsheet.getSheetByName("Rad- Leeds");
  var consentsheet = spreadsheet.getSheetByName("Consent");
  var bandbleedssheet = spreadsheet.getSheetByName("B&B Leeds, Enhanced (Creative Response)");
  var radsheffieldsheet = spreadsheet.getSheetByName("Rad- Sheffield");
  var lancssheet = spreadsheet.getSheetByName("Rad- Lancs");
  var radmcrsheet = spreadsheet.getSheetByName("Rad- Mcr");
  var radliverpoolsheet = spreadsheet.getSheetByName("Rad- Lpool");
  var radcalderdalesheet = spreadsheet.getSheetByName("Rad- Calderdale");
  var radderbysheet = spreadsheet.getSheetByName("Rad-Derby");
  var radbradfordsheet = spreadsheet.getSheetByName("Rad- Bradford");
  var stcatherinessheet = spreadsheet.getSheetByName("St Catherines");
  var radburnleyblackpoolsheet = spreadsheet.getSheetByName("Rad- Burnley Blackpool");
  var cal = CalendarApp.getCalendarById('HIDDEN FOR SECURITY');
  var events = cal.getEvents(new Date("4/6/2023 1:00 AM"), new Date("4/5/2024 11:59 PM"));


  for (i=0; i<events.length; i++) {
    var title = events[i].getTitle();
    var startTime = events[i].getStartTime();
    var endTime = events[i].getEndTime();
    var location = events[i].getLocation();
    var description = events[i].getDescription();

  monitoringsheet.getRange(i+3,2).setValue(title);
  monitoringsheet.getRange(i+3,3).setValue(startTime);
  monitoringsheet.getRange(i+3,4).setValue(endTime);
  monitoringsheet.getRange(i+3,6).setValue(location);
  monitoringsheet.getRange(i+3,7).setValue(description);

  if (description.includes("ASM Salford")) {
  asmsalfordsheet.getRange(i+12,1).setValue(startTime);
  asmsalfordsheet.getRange(i+12,2).setValue(title);
  asmsalfordsheet.getRange(i+12,4).setValue(location);
  asmsalfordsheet.getRange(i+12,7).setValue(startTime);
  asmsalfordsheet.getRange(i+12,8).setValue(endTime);
  }

  if (description.includes("ASM Mcr")) {
  asmmcrsheet.getRange(i+13,1).setValue(startTime);
  asmmcrsheet.getRange(i+13,2).setValue(title);
  asmmcrsheet.getRange(i+13,4).setValue(location);
  asmmcrsheet.getRange(i+13,7).setValue(startTime);
  asmmcrsheet.getRange(i+13,8).setValue(endTime);
  }

  if (description.includes("CCE Tameside")) {
  ccetamesidesheet.getRange(i+9,1).setValue(startTime);
  ccetamesidesheet.getRange(i+9,2).setValue(title);
  ccetamesidesheet.getRange(i+9,4).setValue(location);
  ccetamesidesheet.getRange(i+9,7).setValue(startTime);
  ccetamesidesheet.getRange(i+9,8).setValue(endTime);
  }

  if (description.includes("Len Johnson")) {
  lenjohnsonsheet.getRange(i+11,1).setValue(startTime);
  lenjohnsonsheet.getRange(i+11,2).setValue(title);
  lenjohnsonsheet.getRange(i+11,4).setValue(location);
  lenjohnsonsheet.getRange(i+11,7).setValue(startTime);
  lenjohnsonsheet.getRange(i+11,8).setValue(endTime);
  }

  if (description.includes("WMW Salford")) {
  wmwsalfordsheet.getRange(i+6,1).setValue(startTime);
  wmwsalfordsheet.getRange(i+6,2).setValue(title);
  wmwsalfordsheet.getRange(i+6,4).setValue(location);
  wmwsalfordsheet.getRange(i+6,7).setValue(startTime);
  wmwsalfordsheet.getRange(i+6,8).setValue(endTime);
  }

  if (description.includes("Rad- Leeds")) {
  radleedssheet.getRange(i+12,1).setValue(startTime);
  radleedssheet.getRange(i+12,2).setValue(title);
  radleedssheet.getRange(i+12,4).setValue(location);
  radleedssheet.getRange(i+12,7).setValue(startTime);
  radleedssheet.getRange(i+12,8).setValue(endTime);
  }

  if (description.includes("Consent")) {
  consentsheet.getRange(i+3,1).setValue(startTime);
  consentsheet.getRange(i+3,2).setValue(title);
  consentsheet.getRange(i+3,4).setValue(location);
  consentsheet.getRange(i+3,7).setValue(startTime);
  consentsheet.getRange(i+3,8).setValue(endTime);
  }

  if (description.includes("B&B Leeds, Enhanced (Creative Response)")) {
  bandbleedssheet.getRange(i+22,1).setValue(startTime);
  bandbleedssheet.getRange(i+22,2).setValue(title);
  bandbleedssheet.getRange(i+22,4).setValue(location);
  bandbleedssheet.getRange(i+22,7).setValue(startTime);
  bandbleedssheet.getRange(i+22,8).setValue(endTime);
  }

  if (description.includes("Sheffield")) {
  radsheffieldsheet.getRange(i+10,3).setValue(startTime);
  radsheffieldsheet.getRange(i+10,4).setValue(title);
  radsheffieldsheet.getRange(i+10,6).setValue(location);
  radsheffieldsheet.getRange(i+10,9).setValue(startTime);
  radsheffieldsheet.getRange(i+10,10).setValue(endTime);
  }

  if (description.includes("Lancs")) {
  lancssheet.getRange(i+8,1).setValue(startTime);
  lancssheet.getRange(i+8,2).setValue(title);
  lancssheet.getRange(i+8,4).setValue(location);
  lancssheet.getRange(i+8,7).setValue(startTime);
  lancssheet.getRange(i+8,8).setValue(endTime);
  }

  if (description.includes("Rad- Mcr")) {
  radmcrsheet.getRange(i+12,1).setValue(startTime);
  radmcrsheet.getRange(i+12,2).setValue(title);
  radmcrsheet.getRange(i+12,4).setValue(location);
  radmcrsheet.getRange(i+12,7).setValue(startTime);
  radmcrsheet.getRange(i+12,8).setValue(endTime);
  }

  if (description.includes("Lpool")) {
  radliverpoolsheet.getRange(i+11,4).setValue(startTime);
  radliverpoolsheet.getRange(i+11,5).setValue(title);
  radliverpoolsheet.getRange(i+11,7).setValue(location);
  radliverpoolsheet.getRange(i+11,10).setValue(startTime);
  radliverpoolsheet.getRange(i+11,11).setValue(endTime);
  }

  if (description.includes("Calderdale")) {
  radcalderdalesheet.getRange(i+8,1).setValue(startTime);
  radcalderdalesheet.getRange(i+8,2).setValue(title);
  radcalderdalesheet.getRange(i+8,4).setValue(location);
  radcalderdalesheet.getRange(i+8,7).setValue(startTime);
  radcalderdalesheet.getRange(i+8,8).setValue(endTime);
  }

  if (description.includes("Derby")) {
  radderbysheet.getRange(i+10,2).setValue(startTime);
  radderbysheet.getRange(i+10,3).setValue(title);
  radderbysheet.getRange(i+10,5).setValue(location);
  radderbysheet.getRange(i+10,8).setValue(startTime);
  radderbysheet.getRange(i+10,9).setValue(endTime);
  }

  if (description.includes("Bradford")) {
  radbradfordsheet.getRange(i+10,1).setValue(startTime);
  radbradfordsheet.getRange(i+10,2).setValue(title);
  radbradfordsheet.getRange(i+10,4).setValue(location);
  radbradfordsheet.getRange(i+10,7).setValue(startTime);
  radbradfordsheet.getRange(i+10,8).setValue(endTime);
  }

  if (description.includes("St Catherines")) {
  stcatherinessheet.getRange(i+7,1).setValue(startTime);
  stcatherinessheet.getRange(i+7,2).setValue(title);
  stcatherinessheet.getRange(i+7,4).setValue(location);
  stcatherinessheet.getRange(i+7,7).setValue(startTime);
  stcatherinessheet.getRange(i+7,8).setValue(endTime);
  }

  if (description.includes("Burnley")) {
  radburnleyblackpoolsheet.getRange(i+17,1).setValue(startTime);
  radburnleyblackpoolsheet.getRange(i+17,2).setValue(title);
  radburnleyblackpoolsheet.getRange(i+17,4).setValue(location);
  radburnleyblackpoolsheet.getRange(i+17,7).setValue(startTime);
  radburnleyblackpoolsheet.getRange(i+17,8).setValue(endTime);
  }

}
  

}

[SCREENSHOT](https://i.stack.imgur.com/ndLAs.png)
Tags:

2

Answers


  1. 0

    On your event objethod and filter eventct, you can get color using getColor() method
    and filter colors based on it

    Login or Signup to reply.
  2. 0

    Try something like this

    const color = "#ff0000";
var cal = CalendarApp.getCalendarById('HIDDEN FOR SECURITY');
var events = cal.getEvents(new Date("4/6/2023 1:00 AM"), new Date("4/5/2024 11:59 PM")).filter(ev => ev.getColor() == color)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search