Teja Kummarikuntla for ToolJet

Posted on Jul 24, 2023 • Updated on Jul 31, 2023 • Originally published at blog.tooljet.com

Build an Inventory and Order Management App with Google Sheets and ToolJet

#webdev #javascript #beginners #programming

Efficient supply chain management is vital for businesses aiming to optimize operations and drive productivity. When it comes to building a cost-effective supply chain management tool, the combination of Google Sheets as a database and ToolJet as a powerful low-code platform can be a game-changer.

In this tutorial, we will delve into the process of leveraging Google Sheets and harnessing ToolJet's capabilities to construct a robust and streamlined inventory and order management app which can be tailored to your specific

What is ToolJet?

ToolJet is an open-source low-code framework that enables us to build full-stack web applications within a few minutes. With ToolJet, you can create standalone fully-functional full-stack applications or embed applications into other websites.

ToolJet allows you to build applications that use relational and non-relational databases, REST APIs, OpenAI technologies, and cloud storage like Google Cloud Storage, AWS S3, and Minio. It is an excellent development tool helping individuals, developers, and businesses create and ship products faster.

Before we continue
I would be super happy if you could give us a star! And let me also know in the comments section. ❤️

https://github.com/ToolJet/ToolJet

Overview

To give you a glimpse of what awaits you, let's start by showcasing the complete application.

Dashboard

The Dashboard page provides a comprehensive overview of your orders and inventory. It offers a visually appealing glimpse of your overall order status and inventory counts. With this at-a-glance view, you can quickly assess the health of your supply chain and make informed decisions.

Inventory Manager

The Inventory Manager page allows you to efficiently manage your products and track their status. A table displays the products along with their quantity, price, and category. You have full control over your inventory, with options to add, delete, and edit products seamlessly. This ensures that you always have accurate information about your stock and can easily make necessary updates.

Orders Manager

The Orders Manager page enhances the way to handle orders. It presents a Kanban view, allowing you to visualize and track orders in different statuses, such as todo, in progress, and completed. This intuitive interface enables you to effortlessly manage orders, add new ones, update their status, and delete them if needed. With the Orders Manager, you can ensure timely order fulfillment and provide exceptional customer service.

Throughout the application, we leverage ToolJet components and the powerful Google Sheets integration provided by ToolJet. This integration allows you to connect your Google Sheets data to the application seamlessly. Given this tutorial brings a wide range of functionalities to implement, we'll be covering the essentials which can be followed to address the tailored needs.

Create a ToolJet Application

To kickstart, let's guide you through creating a new ToolJet application. Whether you are using the cloud-based version of ToolJet or the self-hosted version, the steps are straightforward.

Sign up or Sign in: Visit tooljet.com and sign up for a free account if you don't have one. If you already have an account, sign in.
Dashboard: After signing in, you will be taken to the ToolJet dashboard. Here, you will find an overview of your existing applications and access to other functionalities.
Create a New App: To create a new application, click on the "Create New App" button, which will take you to the 'Application Builder'.
Name the application: You can name the application of your choice from the top left corner of the application builder; we can call it "Inventory and Orders Manager."

Connect to Google Sheets

Return to the ToolJet Dashboard.
From the left sidebar, navigate to "Global Datasources."
Click on "Add new Datasource."
In the gallery of data sources, search for "Google Sheets" and select it.
Choose "Read only" or "Read and write" based on your requirements. For this tutorial, we'll choose "Read and Write" to enable reading and writing data from Google Sheets.
Proceed to authorize ToolJet to access your Google Sheets by following the prompts.
Once the authorization process is completed, you can return to the Application Builder to continue the application development.

Aspects of the Application Builder

Let's explore the major areas of the Application Builder that will ease your development process with ToolJet.

Canvas: Canvas is your playground for building your application's user interface (UI). Here, you can visually design and arrange the components of your application's interface. Drag and drop components onto Canvas to create a seamless and user-friendly UI for your apps.
Components Manager: Located on the right side of the Application Builder, the Components Manager provides you with a comprehensive list of components you can utilize in your application. These pre-built components serve various purposes, such as buttons, forms, tables, etc. With a wide range of components, you can quickly assemble a powerful and visually appealing UI for your app.
Query Panel: The expandable Query Panel is at the bottom of the Application Builder. This panel plays a crucial role in connecting your application to data sources. It allows you to list and access the connected data sources, seamlessly integrating your Google Sheets data and other data sources. By selecting a data source from the Query Panel, you can create queries to perform specific operations and retrieve data dynamically.
Left sidebar: The Left Sidebar provides convenient access to features such as creating and managing pages, inspecting components, and the debugger for efficient development and debugging of applications.

Now that you are familiar with the Application Builder, let's dive deeper into each area and explore how you can leverage its features to build a robust and efficient application for your Supply chain management.

Build the Application.

Now that we have explored the ToolJet Application Builder, it's time to dive into building the application step by step.
As an initial step, let's create two pages for the "Inventory Manager" and the "Orders Manager." Additionally, we will design the left sidebar of the application to include navigation that allows users to switch between these pages effortlessly. To proceed with creating the pages and designing the left sidebar, follow these steps:

Open the ToolJet Application Builder and navigate to the "Pages" section in the Left Sidebar.

Click on "Pages" option in the left side bar to create a new page for the "Inventory Manager".
Set the page name of your choice, you can name it as "Inventory Manager"
Repeat the process to create a new page for the "Orders Manager".
Toggle the "Disable Menu" in the Pages settings to hide the page navigation sidebar in the view mode.
Once both pages are created, you can set the application layout from the Global settings.
Also, you can set the Background color of the Canvas to completely white, #ffffff

Now, let's create a sidebar for the app to enhance navigation and provide an intuitive user experience. The sidebar will allow users to switch between different pages seamlessly. Follow these steps to add the sidebar:

Drag and drop the "Container" component from the Components Manager onto the Canvas.
Position the container on the left side of the page and adjust its height as needed.
Customize the appearance of the container by setting its color. For example, you can set the color to a vibrant green shade like #237A6F.
To represent the different pages of the application, search for the "Icon" component in the Components Manager and add three icons to the sidebar container. These icons will serve as visual cues for navigation.
Consider adding a logo or branding image using the Image component at the top of the sidebar container. This will provide a cohesive and professional look to your application.

To enable navigation between pages by clicking on the icons in the sidebar, follow these steps:

Click on the desired Icon component in the Container. The Components Manager will display the options to set properties and styles for the component in separate tabs.

In the Properties tab, expand the "Events" section.
Add an event handler for the "On click" event.
Specify the action as "Switch page"
Select the appropriate page from the dropdown list. For example, you can choose "Home" for the Home icon.

Repeat these steps for the other pages you want to link to the sidebar icons. Adding event handlers to the icons establishes a connection between the click event and the respective pages, enabling smooth navigation within your application.

By following these steps, you will successfully add a sidebar to your application, enhancing its visual appeal and providing users with a convenient way to navigate between pages.

Moving on, we'll first focus on building the "Inventory Manager" page. This page will allow you to manage your products and track their status effectively. Let's get started.

Inventory Manager Page

The Inventory Manager page offers a comprehensive view of product counts, with a table displaying product details and the "Add New Product" button that enables one to create/add a new product to the list of products. We will build the page in two steps, creating the UI and then connecting it to the Google Sheets data source.

Building the UI

To start building the UI of the Inventory Manager page, let's divide the design into two segments. In the first segment, we'll focus on the page title and the interface to display the counts of products in different statuses.

Search for the "Text" component in the Component Manager and add it at the top left corner of the application. Once added, modify the default text to "Product Inventory" from properties and set its color to the same vibrant green used for the left sidebar design.
Next, create count holders using the "Container" component. Place them side to the previously added text component with a distance, adjusting their size accordingly to accommodate the counts. Inside each container, add a "Text" component to display labels such as "Qty in Total," "Qty in Stock," "Low in Stock," and "Out of Stock." Further, add another "Text" component to hold the respective number values for each count.
To separate the title and count sections visually, insert a "Divider" component below the "Product Inventory" text and the count containers.

By following these steps, you will have successfully created the first segment of the Inventory Manager page. To continue building the second section of the Inventory Manager page, which includes a search input with a button and a button to open a modal, follow these steps:

Search for the "TextInput" component in the Component Manager and add it below the divider. Set the border radius of the input field 6 using the styles tab of the component.
Next to the search input, add a button component with an appropriate size, color, and border radius set to 6.
Search for and add the "Modal" component to the extreme right of the search button, configure the properties and style of the button, give it a name such as "Add a Product" and set the desired color and border-radius. We will design the UI of the modal in the next step.
Add a Table component below the search input and buttons that will hold all the product details. Set the table type to 'Classic' and the border radius to 12 using the styles tab of the table component.

Please refer to the provided image for the modal design and place the components inside the modal accordingly.

By following these steps, you will have successfully added the necessary components for the second section of the Inventory Manager page. The search input, buttons, and table will allow users to search for products, add new products, and view product details in a structured manner.

To fetch data from Google Sheets, follow these steps:

Obtain the Spreadsheet ID from the Google Sheets URL. Locate the ID by finding the string of characters after spreadsheets/d/ and before /edit#gid=.... For example, if the URL is https://docs.google.com/spreadsheets/d/1n_Sm_NYQMwBanxGrHJaKvBJuz94U-xlfFJK3Knf32343OZS8/edit#gid=1311251363, the Spreadsheet ID is 1n_Sm_NYQMwBanxGrHJaKvBJuz94U-xlfFJK3Knf32343OZS8.
In the Query pane click on the "Google Sheets" data source that you connected earlier. Choose the operation "Read data from a spreadsheet".
In the second field, enter the Spreadsheet ID obtained in Step 1.
Optionally, specify the range and sheet values to define the specific data you want to retrieve.
Give a name to this query, such as "getProducts" or any name you choose.
Click on the preview button to verify that the query successfully retrieves data from the spreadsheet.
To automatically execute this query as the application loads, enable the option "Run this query on application load?".
Save the query, and it will be successfully created for further use.

By following these steps, you have created a query that fetches all the product data from the connected Google Sheets. This data can now be displayed in the table component on the Inventory Manager page. Additionally, the query can be executed automatically as the application loads, ensuring the table is populated with the latest data.

Here's how you can populate the table with the data from the getProducts query

Click on the table component in the Application Builder.
Go to the properties section of the table component.
Locate the "Table data" field and update its value to {{queries.getProducts.data}}.
Once updated, the table should automatically populate with the data from the query.
If the data does not appear immediately, you can manually run the "getProducts" query from the Query Panel, ensuring the table is populated with the query data.
To display an image within the table component, select the corresponding column in the properties section. Within the column settings, set the column type to "Image". This configuration will automatically transform the URL values within the column into visible images.

Additionally, you can arrange and customize the columns displayed in the table from the same properties section.

To make the "Add a Product" modal functional and enable the addition of products to the configured Google Sheets, follow these steps:

Access the Query Panel and click on the Google Sheets Datasource to view the query options.
Set the operation to "Append data to spreadsheet" to indicate that you want to add new data to the spreadsheet.
Specify the Spreadsheet ID to identify the target spreadsheet where the data will be appended.

In the "Rows" section, you will define the data to be added in a JSON format. The field values within the JSON will be dynamically updated from the input components.

Here's an example of how the Rows value can be set: [{"Name": "{{components.textinput4.value}}", "Description": "{{components.textarea1.value}}", "Price": "{{components.textinput2.value}}", "Category": "{{components.textinput5.value}}", "Status": "{{components.textinput3.value}}", "Image":"{{components.textinput7.value}}"}] Using the {{}} notation, you can access the values from the respective input components filled in by the user.

Save this query as "addProducts" to use it for adding products to the spreadsheet.
To execute this query when the "Add Product" button is clicked, add an event to the button's properties. Set the event as "On click", the action as "Run Query", and select the "addProducts" query.
Similarly, for the cancel button, add an event to close the modal when clicked. You can also include the same event to close the modal after executing the "Run Query" event for the "Add Product" button.

By following these steps, you have set up the query to add new products to the Google Sheets. The "Add a Product" modal will capture the input from the user, and upon clicking the "Add Product" button, the data will be appended to the spreadsheet. The modal will also provide a cancel option to close the modal without adding the product.

Now to dynamically update the values of the counters in the Inventory Manager page, we will utilize JavaScript code and ToolJet's RunJS query functionality. By implementing a custom script, we can calculate the product counts based on their statuses and return the results. This approach allows for real-time updates and accurate representation of the inventory status. Let's dive into the step-by-step process of executing the JavaScript code using the RunJS query and connecting the count values to the respective text components.

Open the Query Panel and select "RunJS" from the default datasource
This brings up the RunJS query options. Save the query with the name "getProductsCount".
Within the RunJS query, add the provided code snippet

const counts = queries.getProducts.data.reduce(
  (acc, item) => {
    acc.totalCount++;
    if (item.Status === "In Stock") {
      acc.inStockCount++;
    } else if (item.Status === "Low Stock") {
      acc.lowStockCount++;
    } else if (item.Status === "Out of Stock") {
      acc.outOfStock++;
    }
    return acc;
  },
  { inStockCount: 0, lowStockCount: 0, outOfStock: 0, totalCount: 0 }
);

return counts;

Save the "getProductsCount" query and ensure its execution on the successful completion of the "getProducts" query. To do that, On query success, add a event handler to the "getProducts" query to trigger "getProductsCount".

Congratulations on successfully building the Inventory Manager page! You have learned how to design the UI, connect to Google Sheets as a data source, populate the table with data, add new products, and dynamically update the count values.

Now that you have gained a solid understanding of how to build the UI and create queries in ToolJet, we will move forward at a slightly faster pace in the upcoming sections. With your newfound knowledge and proficiency, we will explore building the Orders Manager page.

Orders Manager Page

In the Orders Manager page, we will shift our focus to tracking and managing orders efficiently. To retrieve the order data, we will work with a different spreadsheet, where the product name of each order is indexed from the Product sheet using the product ID as a key. This process will involve utilizing query transformations to establish the necessary connections between datasets.
Regarding UI, we will introduce a new component called Kanban, which visually represents orders in different stages.

Building the UI for the Orders Manager page follows a similar process to the previous sections. However, as mentioned, we introduce a new component called the Kanban.

Add the Kanban component to the page, following the image provided in the blog.
Each card within the Kanban component should include three text components: one for the product name, one for the customer name, and one for the total price of the order.
Customize the styling and layout of the Kanban component to match your design preferences.
Top of this, the "Create an order" is a 'Modal' component and the text "All orders" is the text component.

Here's the UI of the modal for creating an orde.

By following the provided image and incorporating the necessary components, you can create a Kanban view that effectively displays order details and set the modal for adding inputs that can be used to append data to the Orders Spreadsheet.

Setting the Queries

We will utilize query transformations to combine information from different queries and enhance the data retrieval process. Specifically, we will focus on retrieving the orders data from a separate spreadsheet and associating the product name with each order using the product ID. This will enable us to present comprehensive and meaningful information within our application.

To connect to a new spreadsheet using the same datasource and retrieve the orders data, follow these steps:

Create a new Google Sheets query within the ToolJet Query Panel.
Set the operation for this query to "Read data from a Spreadsheet".
Specify the appropriate Spreadsheet ID to identify the target spreadsheet containing the orders data.
Save this query, you can name it as "getOrdersWithProductId"

This query will be able to bring the Orders data with product id in each object, to retrieve the product name for each order using the product ID and also to add a new key called "columnId" based on the status(will be needed for Kanban component) , we will utilize query transformations.

Toggle the "Enable transformations" option within the query options.
Add the provided JavaScript code snippet in the code editor

const products = queries.getProducts.data;
const orders = data;

const updatedData = orders.map(obj => {
  const productId = obj.ProductID;
  const product = products.find(prod => prod.Id === productId);
  const productName = product ? product.Name : "Unknown Product";

  return {
    ...obj,
    ProductName: productName,
    columnId: obj.Status
  };
});

return updatedData;

You can save the query and preview it, and here is how you can spot the difference with and without the query transformations.

{
  id: "2",
  OrderDate: "02/02/2023",
  Name: "Bob Smith",
  ProductID: "1",
  Phone: "9876543210",
  Address: "456 Elm St, Town",
  Comment: "Call before delivery",
  Status: "todo",
  Quantity: "3",
  TotalPrice: "343"
}

{
  id: "2",
  OrderDate: "02/02/2023",
  Name: "Bob Smith",
  ProductID: "1",
  Phone: "9876543210",
  Address: "456 Elm St, Town",
  Comment: "Call before delivery",
  Status: "todo",
  Quantity: "3",
  TotalPrice: "343",
  ProductName: "Fjallraven - Foldsack No. 1 Backpack, Fits 15 Laptops",
  columnId: "todo"
}

To connect the query response to the Kanban component and configure the column data and card data, follow these steps:

Set the Column Data field value of the Kanban component to the following JSON format: [{ "id": "todo", "title": "Orders Placed" },{ "id": "inprogress", "title": "Shipped" },{ "id": "done", "title": "Delivered" }] This configuration will define the columns as "Orders Placed," "Shipped," and "Delivered" within the Kanban component.
Set the Card Data field value of the Kanban component to {{queries.getOrdersWithProductId.data}}. This connects the query response to the Kanban component, ensuring that the data is displayed correctly.
To display the product name, customer name, and price in the respective card components, click on the text component within the first card of the first column. Set the text properties to {{cardData.ProductName}} to display the product name, {{cardData.Name}} to display the customer name, and $ {{cardData.TotalPrice}} to display the price.
On top of this, clicking on a specific card bring up an expanded view of the card; you can set the required components and display essential order details.

You can now try out the implementation of the "Create an order" modal and the respective queries. The process is quite similar to how we implemented the "Add a Product" modal in the previous section.

To get started, follow these steps:

Design the UI for the "Create an order" modal using the appropriate components, such as text inputs, dropdowns, and buttons.
Create a new query within the ToolJet Query Panel for appending data to the Google Sheets spreadsheet.
Set the operation of the query to "Append data to spreadsheet" and specify the appropriate Spreadsheet ID.
Configure the query to include the necessary input fields from the modal to capture the order details.
Add event handlers to the relevant buttons in the modal to execute the query on click.
Optionally, set up additional queries or query transformations to fetch updated order data and display it in real-time.

By following these steps, you can enable the creation of new orders through the modal and seamlessly update the Google Sheets spreadsheet with the order details. This allows for smooth data management and enhances the functionality of your Inventory and Order Management App.

Dashboard page

By now, you have gained valuable experience in designing UI components, creating queries, and integrating data into your application. Now, let's move on to the exciting task of building the Dashboard, which will provide a comprehensive overview of your inventory and orders.

Building the Dashboard is a fantastic opportunity to showcase your newfound skills. With the knowledge you have gained so far, you can confidently create a visually appealing and informative Dashboard that displays key counts and highlights from your application. You can make use of the application export share in the Overview section above.

To begin, refer to the provided image for the Dashboard layout with sections 1 and 2. As you have already built two complete pages, you are well-equipped to tackle this challenge; follow the image and place the components in the appropriate locations within the Canvas of the Application Builder.

One essential aspect of the Dashboard is displaying the counts of products in different statuses, similar to what you did in the Inventory Manager page. To achieve this, you can create queries that retrieve the necessary data and calculate the counts based on the status of the products. You can refer back to the section on creating queries for updating the count fields in the Product Inventory page to refresh your memory on this process.

By leveraging your previous experience and the knowledge you have gained, you have the skills and creativity to bring the Dashboard to life. Feel free to customize the components, styles, and layouts to match your desired design aesthetics Remember, the Dashboard serves as a visual snapshot of your application, providing a quick glimpse of the key metrics and insights. Focus on presenting the most relevant and impactful information to empower decision-making and streamline your business operations.

What's next

Congratulations on successfully building your Inventory and Order Management App with ToolJet! You have demonstrated your skills in UI design, query creation, and application development. Now that you have completed this tutorial, you might be wondering what's next.

To continue your journey and explore more possibilities with ToolJet, we encourage you to visit and try out our further tutorials here at blog.tooljet.com. Our blog is filled with tutorials, guides, and helpful resources that can further enhance your knowledge and help you unlock the full potential of ToolJet.

In addition to the blog, we also recommend referring to our comprehensive documentation available at docs.tooljet.com. The documentation covers a wide range of topics, including detailed explanations of components, advanced query techniques etc.

Furthermore, we invite you to join our vibrant community on our Slack workspace. By joining our community, you can connect with other active ToolJet users, share your experiences, and seek assistance when needed.

Once again, congratulations on your achievements so far! We sincerely thank you for reading this tutorial and choosing ToolJet for your application development needs. We hope this tutorial has provided you with valuable insights and empowered you to build powerful web applications with ease.

We look forward to seeing what you create next with ToolJet. Happy building!