How to Build an Employee Directory Using Google Sheets and ToolJet: Complete Guide

Introduction

Creating an employee directory is essential to keep employee information organised and accessible. ToolJet, an enterprise-grade ai native open-source low-code platform, simplifies this task by allowing you to build apps using various data sources, including Google Sheets. This tutorial will guide you through creating an employee directory using Google Sheets as the data source.

1. Prerequisites:

ToolJet (https://github.com/ToolJet/ToolJet): An open-source, low-code business application builder. Sign up for a free ToolJet cloud account or run ToolJet on your local machine using Docker.
A Google account with access to Google Sheets.

Here’s the preview of what we’re going to build by the end of this tutorial:

Step 1: Prepare your Google Sheets Document

We will be starting this tutorial by setting up the Google Sheets document, which will act as the database for our Employee Directory. For this tutorial, we will use the following spreadsheet.

Feel free to add columns according to your needs.

Step 2: Connecting Google Sheets to ToolJet

Once the spreadsheet is ready, let’s connect our Google Sheet to ToolJet. Follow the steps mentioned below.

Sign up for a free ToolJet cloud account if you haven’t already and login.
On the ToolJet dashboard, locate the Data Sources section on the left sidebar. Click on the +Add button under the Google Sheets plugin.
Now choose the Read only option since you only need to read the data in your Google Spreadsheet.
Once you click on Connect Data source, you will be redirected to grant access to ToolJet to your Google Sheets; grant the access and click Save data source.

Now that you have successfully connected Google Sheets to your ToolJet account, click the Apps icon on the left sidebar and select Create an app. Let’s name our app Employee Directory and click on Create app.

Now that we’ve set up our App, it’s time to create the UI.

Step 3: Building the UI

Building UI for our apps is simple with ToolJet, thanks to its app-builder and built-in components.

We will start creating the UI by adding the Table first.

Drag and drop the Table Component from the components library on the right and rename it to employeeDataTable. Adjust the size of the table and place it slightly in the left corner.

Renaming components is an effective way to refer to components as the app grows.

Now that the Table is in place let’s create an employee details card, which will display the employee details selected from the table. Follow the steps below:

Drag and drop a Container component and rename it to employeeCard. The Container component is used to group related components.
Drag and drop the Text component inside the Container and rename it to employeeDetailsText. In the Data section, under Properties, enter “Employee Details”, and under the Styles tab, change the Text Size to 18 and the Weight to bold. This text will act as the heading of the card.

Now, place anImage Component right below the employeeDetailsText, and rename it to employeeImage. Adjust its Box shadow properties according to your needs. For now, in the Properties panel, let’s leave the URL to default.

Now, we will add the full employee details to our card. For that, drag and drop the Text Component and rename it to employeeName. For now, add some dummy text to the Data property, let’s say ‘Name: John Doe’; this will be used to fetch the name of our employee as the tutorial progresses. Under the Styles section, change the text Size to 14 and Weight to bold.
To add more details about an employee, we must add other sections. For this tutorial, we’ll add Name, Email, Department, Contact, Title, Salary and Country.
Copy the employeeName Text Component and paste it one below the other six more times and rename them to employeeMail, department, contact, jobTitle, salary and country, respectively. Add some dummy data in the Data property for each of them.

This card will display the details of the selected employee from the employeeDataTable Table later as the tutorial progresses.

Now that our employee details card is ready, it’s time to add charts to our app for data visualisation.

Here are the steps to be followed to add Charts.

On the top of your app, drag and drop the Chart Component; since this will be a bar chart representing the number of employees per department, let’s rename it to employeeCountChart.
In the Properties panel of the Chart, change the Title to Employees per Department.
Under Plotly JSON Chart Schema, toggle Use Plotly JSON schema.

Next, place two more Chart components besides employeeCountChart and rename them to countryDistributionChart and salaryChart, respectively.
For countryDistributionChart, replace the Title with Countrywise Distribution, and for the salaryChart replace the Title with Department Salary Distribution.
For both the charts, under Ploty JSON Chart Schema Property, toggle Use Plotly JSON schema.
These two charts will be used as pie charts to display employees distributed in various countries and the total salary spent in each department.

With that, the UI is now ready! Let’s start working on fetching the data from Google Sheets and display and visualise it through our app.

Step 4: Linking the UI with Google Sheets to Fetch and Visualise the Data

Now, let’s use the data that we have in our Google Sheet to display it in the table and the charts.

To fetch the data from the Google Sheet into our ToolJet app, we will use ToolJet Queries.

First, create a new query to fetch the employee data from the Google sheet.

1. Fetching the Employee Data

Expand the Query Panel at the bottom and click the Add button to create a query – rename this query to fetchEmployeeData.
Choose Data Source as googlesheets, and Operation as Read data from a spreadsheet.
In the Spreadsheet ID section, enter the spreadsheet ID of your sheet. To access the spreadsheet ID, check your Google Sheet’s URL, the format should be: https://docs.google.com/spreadsheets/d/<SPREADHEET_ID>/edit#gid=0.
The Range and Sheet parameters can be left blank.
To ensure that this query runs every time the application loads, toggle Run this query on application load?
Click on the Run button, and you can now fetch the data from the sheet.

2. Binding the Data to the employeeDataTable Table

Now that we are fetching the employee data from our Google Sheet, it’s time to display it on the employeeDataTable component.

Select the employeeDataTable component, and in the Data property, add the following code: {{queries.fetchEmployeeData.data}}. In ToolJet, double curly braces are used to access or refer to dynamic values.
The data from the sheet is now populated on our employeeDataTable component.
Let’s keep the Name, Email, Department and Country columns in our table. Remove the remaining columns from the Columns section in the Properties panel.
In the Row Selection property, disable Allow selection.

Let’s work on the employee card once the data is displayed on the table.

To ensure the employee details get reflected in the employeeCard component, once we select a particular row from employeeDataTable, follow the steps below:

Select the employeeImage component, and in the Properties section, add the URL: {{components.employeeDataTable.selectedRow.Image}}.
Select the employeName component and replace the text under the Data property with Name: {{components.employeeDataTable.selectedRow.Name}}.
For the employeeMail component, under the Data property, replace the text with Email: {{components.employeeDataTable.selectedRow.Email}}.
For department, replace the Data text with Department: {{components.employeeDataTable.selectedRow.Department}}.
contact with : Contact: {{components.employeeDataTable.selectedRow.Contact}}
title with: Title: {{components.employeeDataTable.selectedRow.Title}}
salary with: Salary: {{components.employeeDataTable.selectedRow.Salary}}
country with: Country: {{components.employeeDataTable.selectedRow.Country}}.

If you followed the above steps correctly, you should now be able to see the details of the employee selected from the employeeDataTable.

Now our employeeCard is working perfectly, let’s bring the charts in our app to life.

3. Binding the Data to the Charts

a.) Employees per Department Bar Chart

Expand the Query Panel, click the +Add button, and create a new Run JavaScript code query – rename the query to employeeCountQuery.
In the code editor, paste the following code:

// fetch the data from the fetchEmployeeData query
await queries.fetchEmployeeData.run();

let data = queries.fetchEmployeeData.getData();

function createPlotlyDepartmentChart(data) {
  // Extract departments from the data
  const departments = data.map(employee => employee.Department);
  
  // Count occurrences of each department
  const departmentCounts = departments.reduce((counts, department) => {
    counts[department] = (counts[department] || 0) + 1;
    return counts;
  }, {});
  
  // Prepare data for Plotly
  const plotlyData = [
    {
      x: Object.keys(departmentCounts),
      y: Object.values(departmentCounts),
      type: 'bar'
    }
  ];
  
  
  
  // Return Plotly JSON schema
  return plotlyData;
}

const barJSON = createPlotlyDepartmentChart(data);
const finalData = JSON.stringify(barJSON);

return finalData;

The JavaScript code above converts the data fetched from the fetchEmployeeData query to a Plotly JSON Chart Schema for a barchart.

To ensure this query runs every time the application loads, toggle Run this query on application load?
Click on the Run button to see the data formatted, that is accepted by a bar chart.
Next, select the employeeCountChart component, and in the JSON Description in the Properties panel, add {{queries.employeeCountQuery.data}} to the data value.

If you followed the above steps correctly, you should see the bar chart populated based on the number of employees per department.

b.) Country Wise Distribution of Employees Pie Chart

Expand the Query Panel again, click the +Add button, and create a new Run JavaScript code query – rename the query to employeeCountryQuery.
In the code editor, paste the following code:

// fetch the data from the fetchEmployeeData query
await queries.fetchEmployeeData.run();

let data = queries.fetchEmployeeData.getData();

function createCountryDistributionData(data) {
    const countryCounts = {};

    data.forEach(employee => {
        const country = employee.Country;
        if (countryCounts[country]) {
            countryCounts[country]++;
        } else {
            countryCounts[country] = 1;
        }
    });

    const plotlyData = {
        type: 'pie',
        labels: Object.keys(countryCounts),
        values: Object.values(countryCounts),
        textinfo: 'label+percent',
        insidetextorientation: 'radial',
        marker: {
            colors: [
                '#636EFA',  // vivid blue
                '#EF553B',  // red orange
                '#00CC96',  // teal
                '#AB63FA',  // purple
                '#FFA15A',  // orange
                '#19D3F3',  // light blue
                '#FF6692',  // pink
                '#B6E880',  // light green
                '#FF97FF',  // magenta
                '#FECB52'   // yellow
            ]
        }
    };

    return plotlyData;
}

const pieJSON = createCountryDistributionData(data);
const finalData = JSON.stringify(pieJSON);

return finalData;

The JavaScript code above converts the data fetched from the fetchEmployeeData query to a Plotly JSON Chart Schema for a piechart.

To ensure this query runs every time the application loads, toggle Run this query on application load?
Click on the Run button to see the data formatted.
Then, select the countryDistributionChart component, and in the JSON Description in the Properties panel, add {{queries.employeeCountryQuery.data}} to the data value.

If you followed the above steps correctly, you should see the pie chart populated based on the employees from different countries.

c.) Salary Distribution Pie Chart

Expand the Query Panel, click the +Add button, and create a new Run JavaScript code query – rename the query to salaryDistributionQuery.
In the code editor, paste the following code:

// fetch the data from the fetchEmployeeData query
await queries.fetchEmployeeData.run();

let data = queries.fetchEmployeeData.getData();

function createDepartmentSalaryData(data) {
    const departmentSalaries = {};
    // Updated vibrant color palette
    const colors = [
        '#0088FE', // Vivid Blue
        '#FFBB28', // Vibrant Yellow
        '#00C49F', // Aqua Green
        '#FF8042', // Bright Orange
        '#A52A2A', // Dark Brown
        '#B03060', // Maroon
        '#FF6347', // Tomato
        '#4682B4', // Steel Blue
        '#6A5ACD', // Slate Blue
        '#32CD32'  // Lime Green
    ];

    data.forEach(employee => {
        const department = employee.Department;
        const salary = parseFloat(employee.Salary.replace('$', '').replace(',', ''));
        if (departmentSalaries[department]) {
            departmentSalaries[department] += salary;
        } else {
            departmentSalaries[department] = salary;
        }
    });

    const plotlyData = {
        type: 'pie',
        labels: Object.keys(departmentSalaries),
        values: Object.values(departmentSalaries),
        marker: { // Setting the colors for each slice
            colors: colors.slice(0, Object.keys(departmentSalaries).length)
        },
        textinfo: 'label+percent',
        insidetextorientation: 'radial'
    };

    return plotlyData;
}

const pieJSON = createDepartmentSalaryData(data);
const finalData = JSON.stringify(pieJSON);

return finalData;

The JavaScript code above converts the data fetched from the fetchEmployeeData query to a Plotly JSON Chart Schema for a piechart.

To ensure this query runs every time the application loads, toggle Run this query on application load?
Click on the Run button to see the data formatted.
Select the salaryChart component, and in the JSON Description in the Properties panel, add {{queries.salaryDistributionQuery.data}} to the data value.

If you followed the above steps correctly, you should see the pie chart populated based on the salary distribution per department.

Conclusion

Congratulations! You’ve successfully built an Employee Directory using Google Sheets and ToolJet. By leveraging ToolJet’s low-code platform and the versatility of Google Sheets, organizations can maintain an efficient and up-to-date employee directory that caters to their dynamic needs. To explore more, check out ToolJet docs or join us on Slack.