First Attempt! Tried to Share Schedules with Garoon using Google Apps Script

This is the Day 10 article for the Mamezou Developer Site Advent Calendar 2024.

Have you ever thought about things like:

• "I wrote a script using an API, but setting up a server and environment just for this seems like a waste of money..."
• "I've heard that you can use scripts with Google's spreadsheets, but I don't really understand how."

This time, by writing the processing in JavaScript locally and uploading it to Google Apps Script, and setting up triggers, I was able to run event synchronization periodically.

The repository I am currently operating personally is here.

By utilizing this, it is possible to synchronize from Cybozu Garoon to Google Calendar.

It also includes the contents I will explain from now on, so please refer to it.

1. nodejs

   npm install -g @google/clasp
   npm install -g @google-cloud/storage
   

2. Create a Google Apps Script
   This time, we'll create the Google Apps Script from the CLI as well.

   You can use any script name you like for myScript.

   mkdir myScript
   cd myScript
   clasp login # Log in to your Google account and grant permissions
   clasp create --type api # A gas file is created directly under Google Drive (it can be moved as scripts are identified by id)
   

3. Upload the script from local

   clasp push
   clasp open # Open the pushed script
   

Libraries

Once the script is generated, appsscript.json is created, so here we define that we can use the Google Calendar library.

{
    "timeZone": "Asia/Tokyo",
    "dependencies": {
        "enabledAdvancedServices": [
            {
                "userSymbol": "Calendar",
                "version": "v3",
                "serviceId": "calendar"
            }
        ]
    },
    "exceptionLogging": "STACKDRIVER",
    "runtimeVersion": "V8"
}

Properties (treated like environment variables)

function setScriptProperties() {
  PropertiesService.getScriptProperties().setProperties({
    TimeZone: 'Asia/Tokyo',
    CalendarName: 'Garoon',

    GaroonDomain: '***.cybozu.com',
    GaroonUserName: 'mei-sei',
    GaroonUserPassword: '***',

    GaroonProfileType: 'USER',
    GaroonProfileCode: 'mei-sei',

    WorkTimeStart: '08:00:00',
    WorkTimeEnd: '21:00:00',
    SyncDaysBefore: '60',
    SyncDaysAfter: '180',
  });
}

• TimeZone: This becomes the default time zone when creating a new Google Calendar.
• CalendarName: This is any calendar name. If it doesn't exist, it will be created.
• GaroonDomain, GaroonUserName, GaroonUserPassword: Set these according to your company's environment. Especially GaroonUserName may vary by company.
  Warning

  Since there's GaroonUserPassword, be careful not to include this properties file in the repository.

• GaroonProfileType, GaroonProfileCode: Definitions to identify yourself within the schedule.
• WorkTimeStart, WorkTimeEnd: We will set synchronization to execute during these times.
• SyncDaysBefore, SyncDaysAfter: The range of schedules to synchronize.

Main

let now;
let properties;

let garoonUser;
let garoonProfile;

let workTerm;
let syncTargetTerm;
let gCal;

let syncEventService;
let garoonEventService;
let gCalEventService;
let garoonDao;
let gCalDao;

function initialize() {
    // omitted
    // Retrieve Property values and create instances, create instances of service classes
}

function sync() {
  initialize();
  if (!workTerm.isInTerm(now)) return;

  const garoonAllEvents = garoonEventService.getByTerm(syncTargetTerm);
  const gCalAllEvents = gCalEventService.getByTerm(syncTargetTerm);

  const garoonEditedEvents = garoonEventService.getEditedEvents(
    garoonAllEvents,
    gCalAllEvents,
  );
  const gCalEditedEvents = gCalEventService.getEditedEvents(garoonAllEvents);

  syncEventService.syncGaroonToGCal(garoonEditedEvents, gCalAllEvents);
  syncEventService.syncGCalToGaroon(gCalEditedEvents, garoonAllEvents);

  // Lastly, update the sync token before finishing
  gCalEventService.getCreatedEvents(true);
}

We execute the above sync method periodically.
The processing steps are roughly as follows.

1. Check if the trigger is within WorkTime.
2. Retrieve all schedules within SyncDays (both Google and Garoon)
3. Extract events that have been added, updated, or deleted (both Google and Garoon)
4. Synchronize to the destination according to the extracted events (currently only Garoon->Google)
5. Update the sync token of Google Calendar to the latest and finish

Service Classes

• The most important thing is the ID management to uniquely obtain events.

  • Since Garoon's schedule includes recurring events, we construct IDs as follows

    src/service/ScheduleEventService.js/GaroonEventService.js

    createUniqueId(garoonEvent) {
        const repeatId = garoonEvent.repeatId ? '-' + garoonEvent.repeatId : '';
        return garoonEvent.id + repeatId;
    }
    

  • Add the above ID as a property to GaroonEvent.

  • Tag GCalEvent with the above ID.

Data Access for Google Calendar

Reference is here

• Create Calendar

  createCalendar(name) {
      const option = {
          timeZone: properties.getProperty('TimeZone'),
          color: CalendarApp.Color.PURPLE,
      };
      const retCalendar = CalendarApp.createCalendar(name, option);
      console.info('Creating GCal calendar...');
      Utilities.sleep(API_COOL_TIME * 5);
      console.warn('Created GCal calendar: ' + 'please notify, color setting');
      return retCalendar;
  }
  

  • We specify the default color in option, but in the end, unless you manually reset it, the text color remains black and is very hard to see.
  • We place a cool time (wait time) after construction before ending the method.

• Retrieve Events

  selectEventByTerm(term) {
      return gCal.getCalendar().getEvents(term.start, term.end);
  }
  

  It's simple to specify a period.

• Create Event

  createEvent(garoonEvent) {
      let gCalEvent;
      const title = garoonEventService.createTitle(garoonEvent);
      const term = garoonEventService.createTerm(garoonEvent);
      const option = garoonEventService.createOptions(garoonEvent);
  
      if (garoonEvent.isAllDay) {
      gCalEvent = gCal
          .getCalendar()
          .createAllDayEvent(title, term.start, term.end, option);
      } else {
      gCalEvent = gCal
          .getCalendar()
          .createEvent(title, term.start, term.end, option);
      }
  
      gCalEventService.setTagToEvent(
      gCalEvent,
      garoonEvent.uniqueId,
      garoonEvent.updatedAt,
      );
      console.info('Create GCal event: ' + garoonEvent.uniqueId);
      Utilities.sleep(API_COOL_TIME);
  }
  

  You can create events by specifying the following:

  • Calendar to create the event in
  • Event title
  • Event duration
    • Depending on whether it's an all-day event, the method changes.
    • Whether it's an all-day event is a property held by GaroonEvent.
    • In the case of an all-day event, since the end time comes back as 23:59:59 of the same day, we set it to 00:00:00 of the next day on Google Calendar.
  • Options
    • Garoon's attendees
    • Garoon's notes

• Update Event

  updateEvent(eventArray) {
      this.deleteEvent(eventArray[0]);
      this.createEvent(eventArray[1]);
  }
  

  • Since it seemed tough to obtain the difference in changes from Garoon, we delete once and recreate.
  • The reason the argument is an array element is because deleting an event only requires gCalEvent and creating an event only requires the information of garoonEvent.

• Delete Event

  deleteEvent(gCalEvent) {
      gCalEvent.deleteEvent();
      console.info(
          'Delete GCal event: ' + gCalEvent.getTag(TAG_GAROON_UNIQUE_EVENT_ID),
      );
      Utilities.sleep(API_COOL_TIME);
  }
  

  Delete based on the tag.

Data Access for Garoon

Reference is here^[2]

• For Garoon, we will be hitting the REST API.

  • There are Cloud Edition and Package Edition, and the code uses the Cloud Edition.

  • The API's basic construction has a dedicated class.

    src/service/GaroonApiService.js

    class GaroonApiService {
    createEventApiUri() {
        return 'https://' + garoonUser.getDomain() + '/g/api/v1/schedule/events';
    }
    
    createPresenceApiUri() {
        return (
        'https://' +
        garoonUser.getDomain() +
        '/g/api/v1/presence/users/code/' +
        encodeURIComponent(garoonUser.getUserName())
        );
    }
    
    createApiHeader() {
        return {
        'Content-Type': 'application/json',
        'X-Cybozu-Authorization': Utilities.base64Encode(
            garoonUser.getUserName() + ':' + garoonUser.getUserPassword(),
        ),
        };
    }
    }
    

    • The username and password are Base64 encoded and embedded in the header.
    • API execution uses UrlFetchApp in the apiAction method.

• Retrieve Events

  selectEventByTerm(queryParam) {
      const queryUri =
      garoonApiService.createEventApiUri() +
      '?' +
      Utility.paramToString(queryParam);
      const option = {
      method: 'GET',
      headers: garoonApiService.createApiHeader(),
      };
      const response = this.apiAction(queryUri, option);
      return JSON.parse(response.getContentText('UTF-8')).events;
  }
  

1. Confirm that the project runs manually without errors.
2. Deploy > New Deployment > Web App
   • Confirm that "Who has access" is "Only Myself"
3. Click the Deploy button.
4. Check the deployed version.
   Information

   Since you cannot change the version later, if you redeploy with a new version, you need to redo this procedure.

5. Triggers > Add Trigger
   • Function to Run: sync
   • Select Deployment to Run: Deployed Version
   • Select Event Source: Time-driven
     • "From Calendar" causes many inconveniences, so we won't use it.
   • Select Type of Time-based Trigger: As per your preference
   • Select Time Interval: As per your preference
   • Error Notification Settings: As per your preference
     Information

     Even if it operates normally, errors seem to occur once or twice a week.

6. Click Save
   

• This was my first time using Google Apps Script, but the biggest difficulty was the part of the local development environment and pushing its results.
  • I wanted to be able to use TypeScript, but I didn't want to go check Garoon's schedule each time, so I prioritized implementation. (But now that I've written this much, I don't want to transition to TypeScript.)
  • Also, since it seemed we couldn't get Google's libraries like nodejs, perhaps it can't be helped.
  • Understanding about clasp, and that there's no scope within Google Apps Script, it was more difficult to use than I expected, but this is free, right? In that case, it's acceptable.
• I made it possible to synchronize Garoon's schedule to Google Calendar, but since it's not bidirectional, I'm still managing with Garoon as the master, so there's a sense of security (not causing trouble for others).
  • I'd like to make it possible to synchronize bidirectionally eventually.