How to Start Workflow by Events of Google Calendar

*The name of “Office 365” was changed into “Microsoft 365” in April 2020
*The name of “G Suite” was changed into “Google Workspace” in October 2020

Hi, there!

I am often asked by customer users of Questetra BPM Suite about linkage with Google Calendar.
Current features of Questetra support linkage such as;
Questetra BPM Suite > Google Calendar
However, there is not the opposite direction yet.
Moreover, I also hear that they say;
‘Registration to the calendar has been done precisely, while works related to it will be missed. So I need to do something.’

• Even though schedules of visiting have registered to calendar properly, reporting on them are missed often…
• Even though I registered my work schedule to the calendar, I was too busy to notice it…

Therefore, today, I will introduce how to Start a Workflow automatically by the event registered to Goole Calendar.

In this post, we assume a case where a salesperson registers a schedule of visiting a customer and report on the visit afterward.

1. Once an appointment is made, register the schedule on Google Calendar. (Include ‘Visit’ as a keyword to its title upon registering)
2. When the day comes, the Visit Report flow is automatically Started in Questetra BPM Suite
3. Enter the report after the visit.

The similar mechanisms will correspond with;

• In the IT system division, the maintenance work schedule of the system is registered in the calendar, and the flow for system maintenance is automatically started when the scheduled date comes
• A staff in the Marketing department registers the schedule of e-magazine distribution in the calendar, and the flow for e-mail distribution is automatically started when the preparation date comes.

1. Prepare a Google Apps Script that will be launched at a fixed time every day, like early in the morning.
2. When that Google Apps Script is launched, it goes to see whether there is an event on the day registered in Google Calendar.
3. If there is an event, it will check whether the keyword “visit” is included in its title.
4. If the keyword is included, it will send an HTTP request to Start the target App of Questetra BPM Suite

Prepare an App to be launched when data is sent from Google Apps Script.
A receptacle for receiving data referred to as “Message Start Event (HTTP)” is needed.
* Related manual M221: M221: Auto Starting Triggered by HTTP Request

* Please note that the connection with Google Apps Script will not be finalized unless you release the App. Once you released the App, click on [URL / Parameter Details] on the property of “Message Start Event (HTTP)” to open a screen like the following. Proceed the following the preparation of Google Apps Script referring to the values indicated on the screen.

Prepare Google Apps Script that launches Questetra BPM Suite referring to events registered in Google Calendar.

* I have prepared a simple App archive (Visit Report) corresponding to this example. To download it, click HERE.You need to revise at least URL, KEY, PROCESS_MODEL_INFO_ID, and USER_EMAIL to fit the Questetra BPM Suite environment to which App is imported, even if you are going to run the example App as it is.

* We will skip the details of the method of setting up Google Apps Script here, since it is rather a common procedure. This website would be helpful. In the explanation of this site, it is launching the type of Google Apps Script that works with Google Spreadsheet (container-bound script), but it will work even as a standalone Google Apps Script if it is content of this example.

Code example

var URL = "https://xxxxx.questetra.net/System/Event/MessageStart/start";
var KEY = "xxxxx";
var PROCESS_MODEL_INFO_ID = "xxx";
var NODE_NUMBER = "0";

var USER_EMAIL = "xxx@xxx";
var KEYWORD = "visit";

function startWorkflow() {
  var today = new Date();
  var cal = CalendarApp.getCalendarById(USER_EMAIL);
  var evts = cal.getEventsForDay(today); //Retrive today's Calendar Event

  //Looping within the retrieved Calendar Event
  if (evts.length > 0){
    for (var i in evts) {
      var evt = evts[i];
      var eventTitle = evt.getTitle();
      //Start Process only on containing KEYWORD
      if (eventTitle.indexOf(KEYWORD) >= 0) {
        var url = URL;
        var payload = "processModelInfoId=" + PROCESS_MODEL_INFO_ID;
        payload += "&nodeNumber=" + NODE_NUMBER;
        payload += "&key=" + KEY;
        payload += "&title=" + eventTitle;
        payload += "&data[0].email=" + USER_EMAIL;
        payload += "&data[1].input=" + Utilities.formatDate(evt.getStartTime(), "JST", "yyyy-MM-dd");
        var params = {
          method: 'post',
          payload: payload
        };
        var httpResponse = UrlFetchApp.fetch(url, params);
      }
    }
  }
}

The part you need to edit is as follows.

• Line 1 to 4: Modify to fit your Queatetra BPM Suite environment

var URL = "https://xxxxx.questetra.net/System/Event/MessageStart/start";
var KEY = "xxxxx";
var PROCESS_MODEL_INFO_ID = "xxx";
var NODE_NUMBER = "0";

• Line6: Modify to your Email address.

var USER_EMAIL = "xxx@xxx";

• Line 7: Modify the KEYWORD to anything you want

var KEYWORD = "訪問";

Script execution by manually can be done by specifying the function in the part indicated in the following figure of the script editor and clicking on the triangle button.
When you run this Google Apps Script for the first time, “approval” will be requested for calendar access. So, grant your approval and go on.

In addition, in order to make this Google Apps Script processing run every day, the following settings are required.

1. Select “Current project’s triggers” in “Edit” menu

2. Click on the hyperlink in the middle; No triggers set up. Click here to add one now.

3. As in the following figure, select “startWorkflow” for function, “Time-driven”, “Day timer”, and specify the execution time you want. Then “Save”.

If you want to check the calendar of multiple users, you need to prepare Google Apps Script with an authorized account and loop through the parts to process by referring to the calendar.
If you are referring to multiple users’ calendars, you’d better have data on Google Sheet and prepare Google Apps Script bound to that Sheet.

Furthermore, using Google Apps Script, not only browse events in Google Calendar, but also you can register events in Google Calendar.
For example, it would be a situation where in the Process of making a review request, the review deadline date will be automatically entered in the calendar. I will mention it on another occasion.

If you have any questions, please feel free to Contact us.

By applying for your Free account of your own Questetra, you will be able to use all the Questetra features including what I mentioned above.
Please click HERE to go to the Web form.