Building a Leave Request Workflow with Scripts (Alternative Date Suggestion)

In a previous article, the enhancement of a leave request workflow through a Script Task to automatically verify calendar availability prior to managerial review was explored.

The core logic was defined as follows:

If the requested dates were available → proceed to approval
If not → return the request to the user

While effective, this approach presented a usability constraint.

If a leave request was rejected because the dates were too busy, the requester still had to manually search for alternatives and resubmit the request themselves.

Could the workflow suggest alternative leave dates automatically?

The answer is yes – and the process of building it highlighted some interesting workflow design considerations along the way.

Transitioning from Validation to Recommendation

The original Script Task focused on a simple YES/NO result:

			
Availability: YES
Availability: NO

To improve the process, the workflow required the capacity to identify specific available dates when the requested period was blocked.

When requested dates are unavailable, alternative available dates must be identified.

The function of the Script Task was reclassified from:

A simple validation step
to a lightweight recommendation engine

The First Attempt

The original version of the workflow simply checked whether the requested leave dates were available.

If the number of calendar events exceeded the defined threshold, the request was rejected and returned to the requester.

This method resulted in user friction.

A rejected requester would then need to:

Manually search for alternative dates
Guess which periods might be available
Repeatedly resubmit requests until something worked

The workflow could identify a problem, but it could not help solve it.

A necessary improvement was identified:

Alternative leave periods should be suggested automatically rather than issuing a simple rejection.

Initial experimentation focused on suggesting individual dates; however, this was found to be ineffective for multi-day requests.

For example:

			
Requested leave:
2026-06-01 → 2026-06-14

Suggesting isolated single dates was not particularly useful, because the requester still needed a continuous leave period of the same duration.

The workflow was subsequently updated to:

Preserve the original leave length
Search nearby periods
Suggest complete alternative leave ranges instead

For example:

			
Suggested alternative:
2026-06-03 → 2026-06-16

The efficiency and practicality of the workflow from a user perspective were enhanced by these modifications.

Instead of simply rejecting requests, the workflow could now actively help users find workable alternatives automatically.

Rethinking the Logic

The initial script logic relied on multiple Google Calendar API requests—specifically, one request for every candidate date.

That approach had limitations:

Unnecessary API calls
Scaling issues for longer leave periods
Difficulty handling continuous multi-day blocks

Note: Script Tasks are limited to 10 HTTP requests per automatic execution.

The maximum HTTP request limit was frequently reached when individual calendar dates were checked sequentially.

The optimized version performs the following:

Retrieves calendar events once for the entire search window
Counts events locally per day
Tests candidate leave periods against the availability threshold

These changes resulted in a workflow that is:

More efficient
Easier to scale
Better suited to longer leave requests
Still effective for single day leave requests

Searching Before and After the Requested Dates

The expansion of the search range constituted a further design improvement.

The current workflow avoids limiting the search to dates following the requested period and instead:

Searches both before and after the requested dates
Avoids suggesting dates in the past
Avoids suggesting leave starting today
Preserves the original leave duration

For example:

			
Requested leave:
2026-06-10 → 2026-06-14

A more practical user experience was established through these parameters.

The resulting script is in the code block below.

Script (click to open)


/*
 * Questetra Script Task
 * Suggest alternative available leave periods
 *
 * Handles both:
 * - single-day leave requests
 * - multi-day leave requests
 *
 * Input:
 *   q_start_date
 *   q_end_date
 *
 * Output:
 *   q_suggested_dates  String / Text data item
 */
const CALENDAR_ID = "INSERT_YOUR_CALENDAR_ID";
const AUTH_SETTING_NAME = "INSERT_YOUR_AUTH_SETTING_NAME";
const THRESHOLD = 3;          // fewer than 3 events per day = available
const DAYS_BEFORE = 14;        // search up to 14 days before requested start date
const DAYS_AFTER = 30;         // search up to 30 days after requested start date
const MAX_SUGGESTIONS = 5;    // return up to 5 suggested periods
// 1) Read dates
const startDate = engine.findDataByVarName("q_start_date");
const endDate   = engine.findDataByVarName("q_end_date");
if (startDate === null || endDate === null) {
  throw new Error("q_start_date or q_end_date is empty.");
}
// 2) Helper functions
function toJsDate(qDate) {
  return new Date(qDate.toString() + "T00:00:00+09:00");
}
function formatDate(date) {
  const y = date.getFullYear();
  const m = ("0" + (date.getMonth() + 1)).slice(-2);
  const d = ("0" + date.getDate()).slice(-2);
  return y + "-" + m + "-" + d;
}
function addDays(date, days) {
  const newDate = new Date(date.getTime());
  newDate.setDate(newDate.getDate() + days);
  return newDate;
}
function daysBetweenInclusive(start, end) {
  return Math.round((end - start) / (1000 * 60 * 60 * 24)) + 1;
}
function isWeekend(date) {
  const day = date.getDay();
  return day === 0 || day === 6;
}
// 3) Convert requested dates
const requestedStart = toJsDate(startDate);
const requestedEnd   = toJsDate(endDate);
if (requestedEnd < requestedStart) {
  throw new Error("q_end_date is earlier than q_start_date.");
}
const leaveLengthDays = daysBetweenInclusive(requestedStart, requestedEnd);
// 4) Decide search window
const MIN_ADVANCE_FOR_BACKWARD_SEARCH = 5;
const today = new Date();
today.setHours(0, 0, 0, 0);
const earliestAllowedSuggestion = addDays(today, 1);
// Only search backwards if the requested leave starts
// at least MIN_ADVANCE_FOR_BACKWARD_SEARCH days from today
const thresholdDate =
  addDays(today, MIN_ADVANCE_FOR_BACKWARD_SEARCH);
let searchStart;
if (requestedStart >= thresholdDate) {
  searchStart = addDays(requestedStart, -DAYS_BEFORE);
  // Never suggest dates in the past
  if (searchStart < earliestAllowedSuggestion) {
    searchStart = earliestAllowedSuggestion;
  }
} else {
  searchStart = requestedStart;
}
// Need to extend search end enough to fit a full replacement leave period
const latestCandidateStart = addDays(requestedStart, DAYS_AFTER);
const searchEnd = addDays(latestCandidateStart, leaveLengthDays - 1);
engine.log("Requested leave length: " + leaveLengthDays + " day(s)");
engine.log("Search start: " + formatDate(searchStart));
engine.log("Search end: " + formatDate(searchEnd));
// 5) Fetch all events once for the whole search window
const timeMin = formatDate(searchStart) + "T00:00:00+09:00";
const timeMax = formatDate(searchEnd)   + "T23:59:59+09:00";
const url =
  "https://www.googleapis.com/calendar/v3/calendars/" +
  encodeURIComponent(CALENDAR_ID) +
  "/events";
const authSetting = httpClient.findAuthSetting(AUTH_SETTING_NAME, false);
let request = httpClient.begin();
request = request.authSetting(authSetting);
request = request.queryParam("timeMin", timeMin);
request = request.queryParam("timeMax", timeMax);
request = request.queryParam("singleEvents", "true");
request = request.queryParam("orderBy", "startTime");
const response = request.get(url);
const status = response.getStatusCode() + "";
const body = response.getResponseAsString() + "";
engine.log("Calendar API status: " + status);
if (status !== "200") {
  throw new Error("Google Calendar API error: " + status + "\n" + body);
}
const json = JSON.parse(body);
const events = json.items || [];
engine.log("Total events found: " + events.length);
// 6) Count events per day
const countsByDate = {};
function incrementDateCount(dateString) {
  if (!countsByDate[dateString]) {
    countsByDate[dateString] = 0;
  }
  countsByDate[dateString]++;
}
for (let i = 0; i < events.length; i++) {
  const event = events[i];
  let eventDate;
  if (event.start && event.start.date) {
    eventDate = event.start.date;
  } else if (event.start && event.start.dateTime) {
    eventDate = event.start.dateTime.substring(0, 10);
  } else {
    continue;
  }
  incrementDateCount(eventDate);
}
// 7) Check whether a candidate period is available
function isPeriodAvailable(candidateStart, candidateEnd) {
  let cursor = new Date(candidateStart.getTime());
  while (cursor <= candidateEnd) {
    const dateString = formatDate(cursor);
    // Optional: reject leave periods that include weekends
    if (isWeekend(cursor)) {
      return false;
    }
    const count = countsByDate[dateString] || 0;
    if (count >= THRESHOLD) {
      return false;
    }
    cursor = addDays(cursor, 1);
  }
  return true;
}
// 8) Search candidate leave periods
const suggestions = [];
const candidateStartEnd = latestCandidateStart;
let candidateStart = new Date(searchStart.getTime());
while (candidateStart <= candidateStartEnd) {
  const candidateEnd = addDays(candidateStart, leaveLengthDays - 1);
  if (isPeriodAvailable(candidateStart, candidateEnd)) {
    if (leaveLengthDays === 1) {
      suggestions.push(formatDate(candidateStart));
    } else {
      suggestions.push(formatDate(candidateStart) + " to " + formatDate(candidateEnd));
    }
  }
  if (suggestions.length >= MAX_SUGGESTIONS) {
    break;
  }
  candidateStart = addDays(candidateStart, 1);
}
// 9) Save suggested dates
let outputText;
if (suggestions.length === 0) {
  outputText =
    "No available leave periods found between " +
    formatDate(searchStart) + " and " + formatDate(searchEnd) + ".";
} else {
  outputText =
    "Suggested available leave periods:\n- " +
    suggestions.join("\n- ");
}
engine.setDataByVarName("q_suggested_dates", outputText);
engine.log(outputText);

The article is intended for both technical and non-technical audiences to demonstrate the utility of the provided script regardless of programming expertise. In instances where ECMA script is not understood, the code may be integrated into a workflow platform for evaluation. If execution failures occur, processing logs may be analyzed via artificial intelligence to identify underlying issues.

Workflow Design Perspective

The workflow utility was increased while maintaining structural clarity.

The structure utilizes two Script Tasks situated around a Gateway:

Enter Leave Dates

↓

Check Availability

↓

Gateway

├─ Available → Manager Approval  
└─ Unavailable → Suggest Alternative Dates → Return to Requester

The first Script Task checks whether the requested leave period is available.

The Gateway then controls the flow based on that result.

If dates are unavailable, the second Script Task identifies alternative periods and records the suggestions in the corresponding data item.

Two automated logic points are now present in the process:

One for checking availability
One for suggesting alternatives

This separation is beneficial for workflow design. Responsibilities are clearly defined for each Script Task, and the Gateway routes the process according to the availability outcome.

System capability is increased while maintaining ease of comprehension due to the specialized purpose of each component.

A Quick Note on Script Development and Troubleshooting

The development of workflow automation involving AI-assisted scripting is frequently an iterative process.

The final script required multiple stages of refinement before achieving its current form.

The following challenges were addressed:

Authentication issues
API request problems
Logic refinements
Edge cases around date ranges
Several rounds of testing and adjustment

In particular, moving from simple availability checking to suggesting complete alternative leave periods required several changes to the script structure and search logic.

This iterative process provided significant value.

AI-generated code was treated as a tool for rapid prototyping and troubleshooting rather than a finished product.