How can I sync different, MULTIPLE calendars from ONE google sheet?

Question

I would like to sync / update different calendars (bonus points for iCal but appreciate it will likely have to be Google Cal) for different clients from one google sheet

From this sheet:

https://docs.google.com/spreadsheets/d/1f6qUjGYHZtRRGCbyh5oNzM7o64B-wlkrTpB1ac64I2U/edit#gid=0

I would like info from columns C, D, E, F, G to input into a calendar entry / sync with respective calendars for clients A, B and C from column B according to the date in A

So for client A on date 06-01-2017 the following information would be added as an all day event:

Event: ABC Location: ABC, DEF, GHI Capacity: 5000 Seated Cap: 100 Link: www.google.com/abc

Client B on date 08-02-2017 the following would be added to Client B's calendar (but not Client A's etc)

Event: ABC Location: ABC, DEF, GHI Capacity: 2500 Seated Cap: 200 Link: www.google.com/abd

And as you can see there are multiple entries for the respective clients and their calendars

I have successfully got singular calendar working according to the following link but need an "if / then" specification and don't know where it goes or what code to use:

https://cloud.google.com/blog/products/g-suite/g-suite-pro-tip-how-to-automatically-add-a-schedule-from-google-sheets-into-calendar

Answer 1

You need to maintain a mapping of client to calendar ID somewhere so that each time you process an event row you can do:

var clientEventCal = CalendarApp.getCalendarById(clientCalendarId);

to make sure you're adding the event to the right calendar.

Note: If you share the Apps Script code you're using to create events to a single calendar using your spreadsheet structure, I can provide specific instructions on the exact changes to make!

For the sake of outlining a complete solution, let's assume you create a new sheet named "Client Calendars" containing Client or Project in column A and that client's Google Calendar ID in column B.

After you create this new client-to-client calendar ID table, you have a few options of how to use it:

• Option A: Read the table directly in your code at runtime and look up the right Calendar ID to use in Apps Script code.
• Option B: Add a new column to your main data sheet that uses each row's Client column value to populate the calendar ID directly onto your source data row using VLOOKUP.

(B) is probably easier, as it will require less Apps Script code modification.

A:

function createAllDayEvent(calendarId, eventName, date, location, description) {
   var eventCal = CalendarApp.getCalendarById(calendarId);
   var event = eventCal.createAllDayEvent(eventName, date, date, {location: location, description: description});

   return event;
}

function createClientEvents(event) {
  var sheet = event ? event.source.getSheets()[0] : SpreadsheetApp.getActiveSheet();

  var startRow = 4;
  var dataRange = sheet.getRange('A' + startRow.toString() + ':G');
  var data = dataRange.getDisplayValues();

  var clientCalendarSheet = ss.getSheetByName('Client Calendars');
  var clientCalendarData = clientCalendarSheet.getDataRange().getValues();

  // Date (Entry) | Client / Project | Event | Location | Capacity | Seated Cap | Link
  var dateStr, client, eventName, location, capacity, seatedCap, link;

  for (var i = startRow - 1; i < data.length; i++) {
    [dateStr, client, eventName, location, capacity, seatedCap, link] = data[i];
    if (!eventName) continue;

    var calendarId = null;
    for (var j = 0; j < clientCalendarData.length; j++) {
      if (clientCalendarData[i][0] == client) {
        calendarId = clientCalendarData[i][1];
        break;
      }
    }

    var date = new Date(dateStr);
    var description = "Capacity: " + capacity + ", Seated Cap: " + seatedCap + ", Link: " + link;

    createAllDayEvent(calendarId, eventName, date, location, description);
  }
}

B:

This solution assumes data sheet column M contains client's Calendar ID (you could populate M using =IF($B4="",, VLOOKUP($B4, 'Client Calendars'!$A:$B, 2, FALSE)) assuming you've created the "Client Calendars" sheet as outlined above).

function createAllDayEvent(calendarId, eventName, date, location, description) {
   var eventCal = CalendarApp.getCalendarById(calendarId);
   var event = eventCal.createAllDayEvent(eventName, date, date, {location: location, description: description});

   return event;
}

function createClientEvents(event) {
  var sheet = event ? event.source.getSheets()[0] : SpreadsheetApp.getActiveSheet();

  var startRow = 4;
  var dataRange = sheet.getRange('A' + startRow.toString() + ':G');
  var data = dataRange.getDisplayValues();

  var calendarIdColumn = sheet.getRange('M' + startRow.toString() + ':M').getValues();

  // Date (Entry) | Client / Project | Event | Location | Capacity | Seated Cap | Link
  var dateStr, client, eventName, location, capacity, seatedCap, link;

  for (var i = startRow - 1; i < data.length; i++) {
    [dateStr, client, eventName, location, capacity, seatedCap, link] = data[i];
    if (!eventName) continue;

    var calendarId = calendarIdColumn[i][0];
    var date = new Date(dateStr);
    var description = "Capacity: " + capacity + ", Seated Cap: " + seatedCap + ", Link: " + link;

    createAllDayEvent(calendarId, eventName, date, location, description);
  }
}