Mats Bryntse
24 April 2024

Migrating from an Excel Schedule to a Bryntum Scheduler

You can use versatile spreadsheet editor Microsoft Excel to create project management tools like a schedule. But if you’re dealing […]

You can use versatile spreadsheet editor Microsoft Excel to create project management tools like a schedule. But if you’re dealing with complex schedules, Excel is not the best choice as its functionality is limited. If you need a feature-rich scheduler that can integrate with your web tech stack, Bryntum Scheduler is a better option. Bryntum Scheduler features that Excel lacks, or that would be difficult to implement include:

In this tutorial, we’ll show you how to get schedule data from an Excel file into a Bryntum Scheduler. The example schedule shows tasks (or “events”) for a team of workers, taking weekends and unavailable days for each worker into consideration. Here’s what we’ll cover:

You can adjust the migration code for different Excel schedules. Take a look at our tutorial Migrating from an Excel Gantt chart to a Bryntum Gantt chart to see more examples of converting Excel data to JSON data compatible with a Bryntum component.

Creating a migration script to convert data from an Excel scheduler to a JSON file

Start by cloning the Excel to Bryntum Scheduler server starter GitHub repository. In this starter repository is a file called server.js that contains an Express server with CORS set up for GET requests from our client-side Bryntum Scheduler to an API endpoint we’ll add at the "/download" route.

The scheduler.xlsx Excel file in the root directory is an Excel schedule template from 7 Free Resource Planning Templates For Project Teams.

You can find the completed code for this tutorial in the completed-server branch of the starter repository.

Once you’ve cloned the repository, install the dependencies:

npm install

Converting Excel file data to a JavaScript object

We’ll read the Excel data from the scheduler.xlsx file and convert the data to JSON format in the migration.js file.

First, import the xlsx library:

import xlsx from "xlsx";

We’ll use this library to read the Excel schedule file data and convert it to JSON format.

Add the following lines of code:

// read the Excel file
const workbook = xlsx.readFile("./scheduler.xlsx");
const sheetName = workbook.SheetNames[1]; // select the sheet you want
const worksheet = workbook.Sheets[sheetName];
const jsonData = xlsx.utils.sheet_to_json(worksheet);

The readFile method reads the data from the Excel file and parses it into a workbook object. The Excel file has two sheets, but we only want data from the second sheet. The workbook.SheetNames array is an ordered list of the sheets in the Excel workbook. We get the name of the second sheet from the ordered lists of sheets, and then we get the worksheet data from the Sheets object using the name of the second sheet.

We use the sheet_to_json utility method to convert the worksheet object into an array of JavaScript objects.

Add a console.log for the jsonData variable at the bottom of the file:

console.log({ jsonData });

Now run the script:

node migration.js

You’ll see a large array of objects logged in your terminal. This is the Excel data converted to JSON format. Here’s a snippet of the data:

[
  { __EMPTY_2: 'PROJECT RESOURCE PLAN' },
  {
    __EMPTY_1: 'MONTH',
    __EMPTY_2: 45566,
    __EMPTY_8: 'PROJECT NAME',
    __EMPTY_12: 'ACME Website Design'
  },
  {
    __EMPTY_2: 'AVL. DAYS',
    __EMPTY_3: 'Tue',
    __EMPTY_4: 'Wed',
    __EMPTY_5: 'Thu',
    __EMPTY_6: 'Fri',
    __EMPTY_7: 'Sat',
    __EMPTY_8: 'Sun',
    __EMPTY_9: 'Mon',
    __EMPTY_10: 'Tue',
    __EMPTY_11: 'Wed',
    
    ...

Each object in the array is a row of Excel data. Let’s create a function to extract the resources, tasks, time ranges, and resource time ranges data and convert the data to the format required by the Bryntum Scheduler.

Converting the Excel data to a JSON file compatible with Bryntum Scheduler

Later in this tutorial, we’ll use the Bryntum Scheduler Crud Manager to simplify fetching data from our API. The Crud Manager expects the data it receives from an API endpoint to have a specific structure. We need to extract the data we need from the jsonData variable and format it appropriately.

The month date value in the Excel scheduler (in columns C to G, row five) is an Excel date number. This is the 45566 value of the __EMPTY_2 property in the jsonData array. Excel stores dates as a number format, with the number representing the offset from an epoch. To convert Excel numbers to JavaScript Date objects, add the following function to the top of the migration.js file:

function excelToJSDate(excelDate) {
  const dateObj = xlsx.SSF.parse_date_code(excelDate);
  return new Date(
    dateObj.y,
    dateObj.m - 1,
    dateObj.d,
    dateObj.H,
    dateObj.M,
    dateObj.S
  );
}

The xlsx.SSF (SpreadSheet Format) object has a parse_date_code method that parses the Excel number to an SFF date object. We construct a JavaScript Date object from this date object, subtracting one from the m (months) property, as months are zero indexed in JavaScript.

Now we need a function to add days to a string date. Add the following function below the excelToJSDate function:

function addDays(date, days) {
  const result = new Date(date);
  result.setDate(result.getDate() + days);
  return result.toISOString().slice(0, 10);
}

This function converts a string date to a JavaScript Date object. We use the getDate method to get the day of the month from the date and add the number of days that have passed to it, then use the sum as an argument in the setDate method to set the day of the month of the Date object. We remove the time portion of the date.

Let’s add some functions to create arrays of resources, events, resource time ranges, and time ranges data that can be read by Bryntum Scheduler. Add the following function below the addDays function:

function createBryntumRows(data) {
  const monthDate = excelToJSDate(data[1]["__EMPTY_2"]);
  let resourcesStore = [];
  let eventsStore = [];
  let resourceTimeRangesStore = [];
  const timeRangesStore = [
    {
      id: 1,
      name: "",
      recurrenceRule: "FREQ=WEEKLY;BYDAY=SA,SU;",
      startDate: addDays(monthDate, 1),
      endDate: addDays(monthDate, 2),
    },
  ];
  const monthDays = data[3];
  const firstResource = "Jane";
  let state = {
    isFirstResourceFound: false,
    resourceId: 0,
    eventId: 0,
    resourceTimeRangesId: 0,
  };
  state = resourcesLoop(
    data,
    monthDate,
    monthDays,
    firstResource,
    state,
    resourcesStore,
    eventsStore,
    resourceTimeRangesStore
  );
  return {
    resourcesStore,
    eventsStore,
    resourceTimeRangesStore,
    timeRangesStore,
  };
}

This function initializes and returns some arrays to store data:

We’ll use the time ranges to show weekends and the resource time ranges to show the days a person is unavailable to work. These are the red cells with an “X” label in the Excel schedule. We’ll only start adding to the resourcesStore, eventsStore, and resourceTimeRangesStore arrays once the first resource is found.

The state variable contains ID counters that will be used to give the data store objects IDs. The resourcesLoop function loops through an array of Excel data objects, the data variable, which we’ll pass in as an argument. The data objects represent Excel rows. This resourcesLoop function adds values to the data stores.

Let’s define the resourcesLoop function now. Add the following lines of code below the createBryntumRows function:

function resourcesLoop(
  data,
  monthDate,
  monthDays,
  firstResource,
  state,
  resourcesStore,
  eventsStore,
  resourceTimeRangesStore
) {
  for (let i = 0; i < data.length; i++) {
    if (
      data[i].hasOwnProperty("__EMPTY_1") &&
      data[i]["__EMPTY_1"] === firstResource
    ) {
      state.isFirstResourceFound = true;
    }
    if (!state.isFirstResourceFound) {
      continue;
    }
    if (!data[i].hasOwnProperty("__EMPTY_1")) {
      break;
    }
    const resource = createResource(data, i, state.resourceId);
    state.resourceId = resource.id;
    resourcesStore.push(resource);
    // Inner loop
    state = eventsLoop(
      data,
      i,
      monthDate,
      monthDays,
      state,
      eventsStore,
      resourceTimeRangesStore
    );
  }
  return state;
}

Once the first resource has been found, which is “Jane” in this case, we create a resource for each row of Excel data using the createResource function. We then add the resource data object created to the resourcesStore array. We also update the resourceId property of the passed-in state variable so that the resourceId value increases for each iteration of the for loop. The nested eventsLoop function loops through the Excel row objects and adds data objects to the eventsStore and resourceTimeRangesStore.

Let’s define the createResource function below the resourcesLoop function:

function createResource(data, index, resourceId) {
  return {
    id: ++resourceId,
    name: data[index]["__EMPTY_1"],
    availableDays: data[index]["__EMPTY_2"],
  };
}

Now let’s define the eventsLoop function below it:

function eventsLoop(
  data,
  rowIndex,
  monthDate,
  monthDays,
  state,
  eventsStore,
  resourceTimeRangesStore
) {
  let event = {};
  let lastDay = -1;
  let lastEventName = "";
  let index = 0;
  for (const [key, value] of Object.entries(data[rowIndex])) {
    if (key === "__EMPTY_1" || key === "__EMPTY_2") {
      continue;
    }
    const { isStartOfEvent, isEndOfEvent, dayOfMonth, eventName } =
      processEventDetails(
        data,
        rowIndex,
        key,
        value,
        monthDays,
        lastDay,
        lastEventName,
        index
      );
    if (isStartOfEvent) {
      event = startEvent(
        eventName,
        monthDate,
        dayOfMonth,
        state.eventId,
        state.resourceTimeRangesId,
        state.resourceId
      );
      if (eventName === "X") {
        state.resourceTimeRangesId = event.id;
      } else {
        state.eventId = event.id;
      }
    }
    if (isEndOfEvent) {
      event.endDate = addDays(monthDate, dayOfMonth + 1);
      if (event.name === "X") {
        event.name = "";
        resourceTimeRangesStore.push(event);
      } else {
        eventsStore.push(event);
      }
      event = {};
    }
    lastEventName = eventName;
    lastDay = dayOfMonth;
    index++;
  }
  return state;
}

We loop through the Excel row objects to create the events and resource time ranges. The Excel row object’s key-value pairs represent Excel cells containing an event or resource time range. We use the processEventDetails helper function to determine the day of the month for each row object key-value pair using the monthDays object. The monthDays object is the fourth object of the passed in data in this case. It maps the key names to the day of the month values.

The processEventDetails helper function also determines the event name and whether an event is starting or ending. If the event is starting, we use the startEvent helper function to create the event object. If the event name is “X”, it’s a resource time range. If the event is ending, we add the endDate property to the event object. If the event is a resource time range, we add it to the resourceTimeRangesStore. If not, we add it to the eventsStore.

Let’s define the processEventDetails and startEvent helper functions. Add the following function below the eventsLoop function: 

function processEventDetails(
  data,
  rowIndex,
  key,
  value,
  monthDays,
  lastDay,
  lastEventName,
  index
) {
  const eventsObjectValues = Object.entries(data[rowIndex]);
  const eventsObjectLength = eventsObjectValues.length;
  const dayOfMonth = monthDays[key];
  const eventName = value;
  const nextEventName = eventsObjectValues[index + 3]
    ? eventsObjectValues[index + 3][1]
    : "";
  const nextEventDay = eventsObjectValues[index + 3]
    ? monthDays[eventsObjectValues[index + 3][0]]
    : -1;
  const isStartOfEvent =
    eventName !== lastEventName ||
    (eventName === lastEventName && dayOfMonth - 1 !== lastDay);
  const isEndOfEvent =
    index === eventsObjectLength - 1 ||
    eventName !== nextEventName ||
    dayOfMonth + 1 !== nextEventDay;
  return {
    isStartOfEvent,
    isEndOfEvent,
    dayOfMonth,
    eventName,
    nextEventName,
    nextEventDay,
  };
}

We use the event name, the next event name, the day of the month, and the day of the month for the next event to determine if an event is starting or ending. If we’ve reached the end of the month, then the event is ending in this case, as our data only covers one month.

Now let’s define the startEvent helper function below the processEventDetails function:

function startEvent(
  eventName,
  monthDate,
  dayOfMonth,
  eventId,
  resourceTimeRangesId,
  resourceId
) {
  let event = {};
  if (eventName === "X") {
    event.id = ++resourceTimeRangesId;
    event.timeRangeColor = "red";
  } else {
    event.id = ++eventId; 
  }
  event.name = eventName;
  event.startDate = addDays(monthDate, dayOfMonth);
  event.resourceId = resourceId;
  return event;
}

This helper function is called if the event is starting. If the event has a name of “X”, it’s a resource time range, so we set its timeRangeColor to red to match the styling in the Excel schedule. We use our addDays() helper function to determine the event start date.

Now let’s call the createBryntumRows function to create the Bryntum Scheduler data. Add the following line below the jsonData variable declaration:

const rows = createBryntumRows(jsonData);

If you console.log the rows, you’ll see the following array:

{
  resourcesStore: [
    { id: 1, name: 'Jane', availableDays: 13 },
    { id: 2, name: 'John', availableDays: 16 },
    { id: 3, name: 'Tim', availableDays: 10 },
    { id: 4, name: 'Sarah', availableDays: 17 },
    { id: 5, name: 'Eliza', availableDays: 15 },
    { id: 6, name: 'Jake', availableDays: 19 },
    { id: 7, name: 'Mo', availableDays: 22 },
    { id: 8, name: 'Targus', availableDays: 19 }
  ],
  eventsStore: [
    {
      id: 0,
      name: 'Scope',
      startDate: '2024-10-01',
      resourceId: 1,
      endDate: '2024-10-04'
    },
    {
      id: 0,
    ...

Now create an object for the data that will be sent to the Bryntum Scheduler:

// convert JSON data to the expected Bryntum Scheduler load response structure
const schedulerLoadResponse = {
  success: true,
  events: {
    rows: rows.eventsStore,
  },
  resources: {
    rows: rows.resourcesStore,
  },
  resourceTimeRanges: {
    rows: rows.resourceTimeRangesStore,
  },
  timeRanges: {
    rows: rows.timeRangesStore,
  },
};

Next we’ll convert this object to JSON format and save it in a JSON file.

Saving the data to a JSON file

Add the following lines of code below the schedulerLoadResponse variable:

// save the data to a JSON file
const dataJson = JSON.stringify(schedulerLoadResponse, null, 2); // convert the data to JSON, indented with 2 spaces
// define the path to the data folder
const __filename = fileURLToPath(import.meta.url);
const __dirname = dirname(__filename);
const dataPath = path.join(__dirname, "data");
// ensure the data folder exists
if (!fs.existsSync(dataPath)) {
  fs.mkdirSync(dataPath);
}
// define the path to the JSON file in the data folder
const filePath = path.join(dataPath, "scheduler-data.json");
// write the JSON string to a file in the data directory
fs.writeFile(filePath, dataJson, (err) => {
  if (err) throw err;
  console.log("JSON data written to file");
});

Import the required Node modules:

import fs from "fs";
import path from "path";
import { fileURLToPath } from "url";
import { dirname } from "path";

We convert the JavaScript object to a JSON string. We then create a JSON file called scheduler-data.json in the data folder using the Node.js file system (fs) module and add the Excel JSON data to the new file.

To run this script, use the following command:

node migration.js

This will create a scheduler-data.json file in the data folder.

For this tutorial, we keep it simple and save the Excel schedule data to a JSON file on the server. If you want to persist the data to a database, you can take a look at the saving data section of our docs.

Setting up an Express server with an API endpoint

Now we’ll set up an API endpoint that will send the JSON data to the Bryntum Scheduler we’ll create later in the tutorial.

In the server.js file, add the following API endpoint at the "/download" route for GET requests, above the app.listen method:

app.get("/download", async (req, res) => {
  try {
    const data = await fs.readFile("./data/scheduler-data.json", "utf-8");
    const jsonData = JSON.parse(data);
    res.json(jsonData);
  } catch (error) {
    console.error("Error: ", error);
    res.status(500).send("An error occurred.");
  }
});

Import the Node.js file system module with promise-based functions:

import fs from "fs/promises";

We read the JSON data from the file system, parse it, and then return it from the API as a JSON response.

Now start the server using the following command:

npm run start

If you make a GET request to your API endpoint at http://localhost:3000/download, you should see the following response:

{
  "success": true,
  "events": {
    "rows": [
      {
        "id": 0,
        "name": "Scope",
        "startDate": "2024-10-01",
        "resourceId": 1,
        "endDate": "2024-10-04"
      },
      {
        "id": 0,
        "name": "Review",
        "startDate": "2024-10-04",
        "resourceId": 1,
        "endDate": "2024-10-05"
      },
      ...

Now we can create a Bryntum Scheduler that will get data from this API endpoint.

Creating a client-side Bryntum Scheduler

Clone the Excel to Bryntum Scheduler client starter GitHub repository. The completed-scheduler branch of the repo contains the code for the completed tutorial.

The starter uses Vite, a development server and JavaScript bundler. Install the Vite dev dependency:

npm install

Now install the Bryntum Scheduler component. First follow the guide to accessing the Bryntum private npm registry and then the guide to installing the scheduler component.

The main.js file contains code for a basic Bryntum Scheduler. The project config property is used to configure the data stores that the scheduler uses. The eventsData and resourcesData properties contain some example inline data.

Start the development server by running the following command:

npm run dev

If you visit http://localhost:5173/ in your browser, you should see a scheduler with one event and one resource.

Getting the data from the API endpoint

First we’ll configure the Bryntum Scheduler Crud Manager to fetch the Excel data from our API endpoint, and then we’ll create a custom resource store model for the “available days” custom field and a custom model for the time ranges that show weekends.

Configuring the Crud Manager

Add the following crudManager config property below the project config property:

  crudManager: {
    autoLoad: true,
    validateResponse: true,
    transport: {
      load: {
        url: "http://localhost:3000/download",
      },
    },
  },

The transport property is used to populate the Bryntum Scheduler data stores. We configure the load property to get data from our server API endpoint. We set the autoLoad and validateResponse properties to true to initiate a data load when the store is instantiated and to check the response structure of requests made by the Crud Manager, respectively.

You can also configure the transport property to sync data changes to a specific URL. To learn more, read the saving data section of the Crud Manager guide in our docs.

Configuring the project and creating custom project stores

Replace the project config property with the following:

  project: {
    // use our store for time ranges (crudManager will load it automatically among other project stores)
    timeRangeStore: myTimeRangeStore,
    resourceStore: {
      modelClass: Resource,
    },
  },

To create the custom time range store, import the following from the Bryntum Scheduler library:

import {
  RecurringTimeSpan,
  TimeSpan,
  RecurringTimeSpansMixin,
  Store,
} from "@bryntum/scheduler";

At the top of the file, add the following lines of code:

class MyTimeRange extends RecurringTimeSpan(TimeSpan) {}
class MyTimeRangeStore extends RecurringTimeSpansMixin(Store) {
  static get defaultConfig() {
    return {
      modelClass: MyTimeRange,
      storeId: "timeRanges",
    };
  }
}
const myTimeRangeStore = new MyTimeRangeStore();

We define a new model called MyTimeRange that extends the TimeSpan class and has recurrence support added using the RecurringTimeSpan mixin. We use this model class to define a new store called MyTimeRangeStore that extends the Store class and has recurrence support added using the RecurringTimeSpansMixin mixin. We then instantiate our custom time range store.

To define the custom resource model, create a lib folder in the root folder and a file called Resource.js inside it. Add the following lines of code to the new file:

import { ResourceModel } from "@bryntum/scheduler";
// Simple resource class with an extra available days field 
export default class Resource extends ResourceModel {
  static get fields() {
    return [{ name: "availableDays", defaultValue: 0 }];
  }
}

This custom Resource model extends the Bryntum Scheduler ResourceModel class and adds a custom availableDays field to show available work days.

Import this custom Resource model into the main.js file:

import Resource from "./lib/Resource.js";

Adding an available days column

Now we’ll add an “Available Days” column to the scheduler to replicate the UI of the Excel schedule. Replace the columns config property with the following:

  columns: [
    {
      type: "resourceInfo",
      text: "Workers",
      image: false,
      width: 150,
    },
    {
      text: "Available days",
      field: "availableDays",
      width: 120,
    },
  ],

Run the client and server, and visit http://localhost:5173 to see the Bryntum Scheduler containing data from the Excel file:

The Bryntum Scheduler has a lot of out-the-box functionality, such as:

Note that we disabled some zooming in the scheduler using the following config properties to more closely replicate the Excel schedule:

  zoomOnTimeAxisDoubleClick: false,
  zoomOnMouseWheel: false,

You’ll notice that the weekends and unavailable days are not displayed in the Bryntum Scheduler. Let’s add them.

Displaying weekends and unavailable days

Add the following features config below the zoomOnMouseWheel config:

  features: {
    resourceTimeRanges: {
      // Enable the resource time range elements to be reachable in the DOM (to show tooltips etc.)
      enableMouseEvents: false,
    },
    timeRanges: {
      showCurrentTimeLine: false,
      showHeaderElements: false,
    },
  },

Some Bryntum Scheduler features, like eventResize, are enabled by default. The resourceTimeRanges (to show unavailable days) and timeRanges (to show weekends) features are disabled by default, so we enable them by setting some of their properties. You’ll now be able to see the weekends and unavailable days in the Bryntum Scheduler:

Preventing dragging events onto other events and unavailable days

Let’s improve the Bryntum Scheduler by preventing events from overlapping and preventing dragging events onto unavailable days.

Add the following property to the scheduler config below the viewPreset config property:

  allowOverlap: false,

We set the allowOverlap config property to false to prevent events from having overlapping times. If you try to drag and drop an event onto a time when a person already has an event, the drag and drop won’t succeed.

To prevent dragging events onto unavailable days, we’ll add validation to the eventDrag feature using the validatorFn function.

Add the following eventDrag feature config below the timeRanges feature config:

    eventDrag: {
      validatorFn({ newResource, startDate, endDate }) {
        const eventStart = new Date(startDate).getTime();
        const eventEnd = new Date(endDate).getTime();
        const name = newResource.name;
        const resourceId = newResource.id;
        const timeRanges = scheduler.resourceTimeRangeStore
          .getRange()
          .map((item) => item.data);
        let isValid = true;
        // Loop through each time range to check for overlaps
        for (const timeRange of timeRanges) {
          const timeRangeStart = new Date(timeRange.startDate).getTime();
          const timeRangeEnd = new Date(timeRange.endDate).getTime();
          const timeRangeResourceId = timeRange.resourceId;
          if (resourceId === timeRangeResourceId) {
            if (
              (eventStart > timeRangeStart && eventStart < timeRangeEnd) ||
              (eventEnd > timeRangeStart && eventEnd < timeRangeEnd) ||
              (eventStart < timeRangeStart && eventEnd > timeRangeEnd) ||
              (eventStart === timeRangeStart && eventEnd === timeRangeEnd) ||
              (eventStart === timeRangeStart && eventEnd > timeRangeEnd) ||
              (eventEnd === timeRangeEnd && eventStart < timeRangeStart)
            ) {
              isValid = false;
              break;
            }
          }
        }
        return {
          valid: isValid,
          message: !isValid ? `${name} is not available` : "",
        };
      },
    },

We use the arguments of the validatorFn to determine the event start and end dates, and the name and resource ID of the worker the event is assigned to. We get the resource time range data from the resourceTimeRangeStore and use the isValid flag variable to determine if the drag and drop operation is valid. It will be valid if there are no overlaps with the resource time ranges, which show unavailable days.

We loop through the resource time ranges and check for any time overlaps. If there are overlaps, we set isValid to false and break out of the loop. This prevents the drag and drop and displays a tooltip error message.

You can also prevent events from being added on unavailable days when an event is resized or created by adding a validatorFn to eventDragCreate and eventResize. Take a look at our validation demo to see this in action.

Dynamically calculating the number of available days

The last feature we’ll add to make our Bryntum Scheduler have the same functionality as the Excel scheduler is dynamic available days values that change if you add or delete events, or change the number of unavailable days.

We’ll use the dataChange event to determine when data in the store changes. This event also occurs on page load. Add the following method below the features config property:

  onDataChange() {
    const totalDaysInMonth = getDaysInMonth(schedulerStartDate);
    // Initialize available days for each resource
    const availableDays = {};
    const resourceStore = scheduler.store.resourceStore
      .getRange()
      .map((item) => item.data);
    resourceStore.forEach((resource) => {
      availableDays[resource.id] = totalDaysInMonth;
    });
    // Calculate days occupied by events
    const eventStore = scheduler.store.eventStore
      .getRange()
      .map((item) => item.data);
    eventStore.forEach((event) => {
      const startDate = new Date(event.startDate);
      const endDate = new Date(event.endDate);
      const daysOccupied = (endDate - startDate) / (1000 * 60 * 60 * 24);
      availableDays[event.resourceId] -= daysOccupied;
    });
    // Calculate days occupied by resource time ranges with empty name - unavailable days
    const resourceTimeRangeStore = scheduler.store.timeRangesStore
      .getRange()
      .map((item) => item.data);
    resourceTimeRangeStore.forEach((timeRange) => {
      const startDate = new Date(timeRange.startDate);
      const endDate = new Date(timeRange.endDate);
      const daysOccupied = (endDate - startDate) / (1000 * 60 * 60 * 24);
      availableDays[timeRange.resourceId] -= daysOccupied;
    });
    // Calculate the available days for each resource
    Object.keys(availableDays).forEach((resourceId) => {
      if (availableDays[resourceId] < 0) {
        availableDays[resourceId] = 0;
      }
      // Update the resource store
      const resource = scheduler.store.resourceStore.find(
        (record) => record.id === parseInt(resourceId)
      );
      if (resource) {
        resource.set({
          availableDays: availableDays[resourceId],
        });
      }
    });
  },

This method will be called when a dataChange event occurs.

We first determine the total number of days in the month using the getDaysInMonth function (we’ll define this function next). We create an availableDays object that stores the number of available days for each resource and set the initial number of available days for each resource to the total number of days in the month.

We then loop through the events in the eventStore using the getRange method. We determine the duration of each event and subtract it from the number of available days for the resource with the event. We then subtract the unavailable days that are stored in the resource time range store from the number of unavailable days.

Finally, we update the resource store records using the set method.

Let’s define the getDaysInMonth function now. Add the following function definition above the myTimeRangeStore variable declaration:

function getDaysInMonth(date) {
  const year = date.getFullYear();
  const month = date.getMonth(); 
  // Create a Date object for the first day of the next month
  const nextMonthFirstDay = new Date(year, month + 1, 1);
  // Subtract one day to get the last day of the current month
  nextMonthFirstDay.setDate(nextMonthFirstDay.getDate() - 1);
  return nextMonthFirstDay.getDate();
}

This function takes in a Date object and determines the number of days in the passed-in month by setting the date to the first day of the next month, subtracting one day from the date, and then getting the day of the month.

Now when you change an event duration or move it to another resource, you’ll see the number of available days update.

Next steps

Using the code in this tutorial as a guide, try to migrate your own Excel schedule to a Bryntum Scheduler to take advantage of it’s features, performance, and customization.

You can see a full list of the Bryntum Scheduler features here.

You can also check out the Bryntum Scheduler demos page to see many Bryntum Scheduler features in action, such as:

Mats Bryntse

Bryntum Scheduler