Spreadsheet to calendar

Question

I manage bookings for my business on Google Spreadsheet. I discovered a wonderful script to update my calendar according to the bookings on the spreadsheet: https://github.com/Davepar/gcalendarsync So one row = one booking = one event in the calendar. It works perfectly, but I need a new feature.

As of right now, the script will update one specific calendar, and I would like to update two separate calendars, depending on the value of a specific column.

Example: Let's say I manage a limo business, and I have two limos, a blue one and a red one. I put the bookings in a worksheet, specifying for each row if i'll assign the blue or red limo. I would like to have 2 separate calendars to check the availability of each one separately.

I'm no programmer, but I looked around and apparently this can be done fairly easily, by adding a function: "if value of column C is "blue", assign this calendar ID, if value of column C ir "red", assign this calendar ID." I just don't know exactly how to do it.

So, here is the script: https://raw.githubusercontent.com/Davepar/gcalendarsync/master/gcalendarsync.js

According to me, what has to be modified is this:

var calendarId = '<your-calendar-id>@group.calendar.google.com';

// Synchronize from spreadsheet to calendar.
function syncToCalendar() {
  // Get calendar and events
  var calendar = CalendarApp.getCalendarById(calendarId);
  if (!calendar) {
    errorAlert('Cannot find calendar. Check instructions for set up.');

I replaced by:

var calendarId1 = 'calendaridofbluelimo@group.calendar.google.com';
var calendarId2 = 'calendaridofredlimo@group.calendar.google.com';

// Synchronize from spreadsheet to calendar.
function syncToCalendar() {
  // Get calendar and events
  var calendar = CalendarApp.getCalendarById(calendarId1);
  if (!calendar) {
    errorAlert('Cannot find calendar. Check instructions for set up.');

I haven't modified much at all, I still need to add the function that determines which calendar has to be modified.

I tried to do it according to this response: Create events in multiple Google Calendars from Spreadsheet But I couldn't figure it out (it is for a different script)

Note: I removed all the "Calendar to Spreadsheet" syncing function - I don't want a modification on the calendar to affect the spreadsheet.

Any help would be very appreciated!

Thanks

Answer 1

You need a conditional statement to check the value of the Spreadsheet cell. Something like the following could work:

var calendarId1 = 'calendaridofbluelimo@group.calendar.google.com';
var calendarId2 = 'calendaridofredlimo@group.calendar.google.com';

function syncToCalendar() {

  // Open the Spreadsheet and get the active page.
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  // Then, you need to get the data range to use in the conditional.
  // This gives you an array you can loop through and test each row.
  // For the sake of illustration, we'll say it's in col B

  var data = sheet.getRange("B:B").getValues();

  // Test each row in the conditional
  for(var i=0; i<data.length; i++) {
    if(data[i] == "blue") {
      var calendar = CalendarApp.getCalendarById(calendar1);
    } else if(data[i] == "red") {
      var calendar = CalendarApp.getCalendarById(calendar2);
    } else if(!data[i]) {
      errorAlert('Cannot find calendar. Check instructions for set up.');
    }
    // rest of your function...
  }

Answer 2

Thanks a lot for your answer. I tested your code, and it doesn't seem to work. The script works, but it will only update the first calendar. Here is the code I put (the Red/blue variable is in the column R:R)

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data = sheet.getRange("R:R").getValues();

  // Test each row in the conditional
  for(var i=0; i<data.length; i++) {
    if(data[i] == "Blue") {
      var calendar = CalendarApp.getCalendarById(calendarId1);
    } else if(data[i] == "Red") {
      var calendar = CalendarApp.getCalendarById(calendarId2);
    } else if(!data[i]) {
      errorAlert('Cannot find calendar. Check instructions for set up.');
    }

  }

I tested a few things to eliminate potential issues. Like i put B:B (where there is no Rosa/Negro variable) instead of R:R, and the script doesn't work. So that's good. Also, more interesting, i tried to switch the ids of both calendars. For example, i had

var calendarId1 = 'calendaridofbluelimo@group.calendar.google.com';
var calendarId2 = 'calendaridofredlimo@group.calendar.google.com';

And i put:

var calendarId2 = 'calendaridofbluelimo@group.calendar.google.com';
var calendarId1 = 'calendaridofredlimo@group.calendar.google.com';

What happened is that it added all the events in the Red Limo calendar, and it didn't delete the events off the blue calendar (like it's supposed to do). So I tried many other combinations (after deleting each event in the calendar) and I couldn't find the logic, sometimes it adds all the events to the blue calendar, sometimes all the events to the red.