Building a Leave Request Workflow with Scripts (Calendar Verification)

In the previous article, a Leave Request Workflow was created with the following functionality:

Verifying whether requested dates exceed a specific availability threshold before being sent to a manager for approval.

In theory the concept was relatively straightforward. In practice however, the implementation of this feature required the configuration of Script Tasks, API integrations, and OAuth protocols.

This documentation outlines the methodology for developing a functional solution and identifies critical technical considerations.

Technical Note: The associated code was generated via AI based on specific technical prompts. While code samples are provided for reference, the material is intended for a general audience. No advanced programming proficiency is required for comprehension.

The Goal

The operational requirements were defined as follows:

• If a requested date has fewer than 3 calendar entries → allow the request
• If a date has 3 or more entries → reject and send back to the requester

To implement this, I used a Script Task to query Google Calendar and return a simple YES/NO result to the workflow.

Step 1: Writing the Script

The scripting process involves the following sequence:

• Get the requested dates
• Call the Google Calendar API
• Count events
• Return YES/NO

The transition from initial draft to a functional Script Task requires several precise technical adjustments.

First Attempt: Basic Structure

The following pattern is applied in the script architecture:

• Read q_start_date and q_end_date
• Build a request to the Google Calendar API
• Parse the response
• Count events

For example, the script begins by retrieving the workflow data:

const startDate = engine.findDataByVarName("q_start_date");

const endDate   = engine.findDataByVarName("q_end_date");

The API endpoint is constructed as follows:

const url =

  "https://www.googleapis.com/calendar/v3/calendars/" +

  encodeURIComponent(CALENDAR_ID) +

  "/events";

Execution of this initial logic immediately exposed issues.

Step 2: Authentication

OAuth2 authentication is required for Google Calendar API interactions.

The configuration process consisted of:

• Creating a Google Cloud project
• Enabling the Google Calendar API
• Configuring the OAuth consent screen
• Creating OAuth credentials (Client ID & Secret)
• Setting up a HTTP Authorization Setting in Questetra BPM Suite

A Google Workspace account is necessary for connection. Client credentials must be generated within the Google Cloud Console under the following URL:

https://console.cloud.google.com/auth

Immediate retention of the Client Secret is mandatory, as it is displayed only once by the provider. Loss of this secret necessitates the creation of a new client.

The following parameters must also be specified:

• Authorization Endpoint URL
• Token Endpoint URL
• Scope

In all cases, ChatGPT was able to recommend suitable values for each of these, and when failures were experienced during the debugging process, by sharing details of the error with the AI, it could correct the value to one that worked as desired.

This demonstrates that even with no knowledge of APIs, AI can be utilised in troubleshooting and through a process of trial-and-error a working model can be constructed for relatively complex operations.

A common issue involved the absence of a refresh token:

The returned token does not have a refresh token

The solution was to adjust the Authorization Endpoint URL to include:

access_type=offline&prompt=consent

This ensures that Google returns a refresh token, which Questetra needs to maintain the connection.

Step 3: The “Unauthenticated” Error

After getting a token successfully, the debug process still returned this message:

Unauthenticated

At that point, the request was written like this:

request.bearer("Google Calendar-request");

The problem was that:

• “Google Calendar-request” is the name of the Authorization Setting
• Not the actual access token

So the request was effectively sending:

Authorization: Bearer Google Calendar-request

Which was rejected by Google.

The Fix

Token handling should be delegated to the Questetra engine as follows:

request.authSetting("Google Calendar-request");

This method automates the following:

• Retrieve the correct token
• Attach it to the request
• Refresh it automatically when needed

Step 4: Debugging the Response

Debugging is facilitated by logging the API response:

engine.log(body);

Logging allows for the verification of data returned by Google and confirms request accuracy.

However, the majority of feedback that could be passed to the AI for troubleshooting came from Questetra’s Auto-Processing Log function, which is accessible by switching to Administrator Mode in the case details screen.

Step 5: Counting Events Per Day

Initially, the Script Task was configured to check the total number of events in the entire date range.

Before: Counting total events in the range

// Parse response
const json = JSON.parse(body);
const events = json.items || [];

// Simple rule: total events in range
const availableFlag = (events.length < 3) ? "YES" : "NO";

engine.log("Total events found: " + events.length);

This approach presented a logical flaw:

A multi-day request could be approved even if an individual day within the range exceeded the availability threshold.

The implementation was revised to evaluate event counts on a daily basis.

This required:

• Extracting the date from each event
• Grouping events by date
• Applying the threshold (e.g. 3 events per day)

After: Counting events per day

// Parse response
const json = JSON.parse(body);
const events = json.items || [];

// Count events per day
const countsByDate = {};

for (let i = 0; i < events.length; i++) {
  const event = events[i];

  let eventDate;

  if (event.start.date) {
    // All-day event
    eventDate = event.start.date;
  } else if (event.start.dateTime) {
    // Timed event
    eventDate = event.start.dateTime.substring(0, 10);
  } else {
    continue;
  }

  if (!countsByDate[eventDate]) {
    countsByDate[eventDate] = 0;
  }

  countsByDate[eventDate]++;
}

// Apply rule per day
let availableFlag = "YES";

for (const date in countsByDate) {
  engine.log("Events on " + date + ": " + countsByDate[date]);

  if (countsByDate[date] >= 3) {
    availableFlag = "NO";
    break;
  }
}

This version provides a more accurate assessment of scheduling availability:

• Each day is evaluated independently
• A busy day blocks the request
• A spread of light activity does not

Step 6: Unexpected Behavior in Built-in Steps

The built-in Google Calendar: Insert Event step was utilized to automate event creation upon request approval.

This would automatically create the newly approved leave request event in Google Calendar.

Technical observations indicate that Script Tasks and built-in Google Calendar steps utilize distinct authentication mechanisms:

• The Script Task worked fine
• But the Insert Event step failed with the message:

invalid_grant

This highlights an important detail:

Script Tasks and built-in Google Calendar steps use different authentication mechanisms

• Script Task: HTTP Authorization Settings
• Built-in Google steps: Google Connectivity (user-based connection)

Successful execution of one does not guarantee the other.

Questetra provides Google Connectivity settings within both System and Account Settings. The latter is required for this implementation. Connectivity is established by selecting the appropriate account and authorizing access.

The following procedure refreshes the token:

• Click “disconnect”
• Once disconnected, click “connect” again, and follow the steps above

Key Takeaways

1. Script Task is powerful – but requires proper setup

While the core logic is uncomplicated, the majority of development effort is directed toward authentication and integration protocols.

2. OAuth configuration is often the hardest part

Omission of specific parameters, such as prompt=consent, results in authentication failure.

3. Don’t confuse “names” with “tokens”

The distinction between Authorization Setting names and actual access tokens must be maintained.

4. Built-in steps and custom scripts may behave differently

Built-in steps and custom scripts operate under different authentication contexts.

5. Design first, implement second

The technical complexity of individual Script Tasks is managed within a simple overall workflow design:

• System checks availability
• Gateway routes based on result
• Human approves if valid

Summary

The implementation of calendar verification represents the most technical phase of the workflow development.

But importantly:

Encapsulating this complexity within a single step preserves the integrity of the workflow architecture.

This is highly beneficial for workflow design.

These points are provided to assist in the troubleshooting of future workflow implementations.

Articles in This Series

• Building a Leave Request Workflow with Scripts (Overview)
• Building a Leave Request Workflow with Scripts (Calendar Verification) (this article)
• Building a Leave Request Workflow with Scripts (Alternative Date Suggestion)