Creating a Dynamic Employee Directory using SheetBest: A Comprehensive Guide

Danny Chu Danny Chu
Written by Danny Chu
6 minutes read

In today’s interconnected business landscape, a well-structured employee directory serves as the backbone of effective communication and collaboration within organizations. The advent of innovative tools has made it easier than ever to create and manage employee directories. In this guide, we’ll walk you through the process of building an efficient employee directory using SheetBest (/), a powerful platform that leverages Google Sheets and APIs. By the end of this article, you’ll be equipped with the knowledge to craft a professional-grade employee directory tailored to your organization’s unique needs.

1. Set Up Google Sheets:

a. Create a New Spreadsheet:

b. Add Column Headers:

  • In the first row (Row 1), add headers for the employee information you want to include in your directory. For example, you could have columns like:Name
  • Job Title
  • Department
  • Email
  • Phone Number
  • Photo URL (if you want to include profile pictures)

c. Enter Employee Data:

  • Starting from Row 2, enter the employee data in each column. Fill in the corresponding information for each employee.

d. Formatting Tips:

  • You can use Google Sheets’ formatting options to make your data more visually appealing. For example, you can use bold formatting for headers or apply cell background colors for different departments.

e. Share the Spreadsheet:

  • Click the “Share” button in the top-right corner of Google Sheets.
  • Adjust the sharing settings to make the spreadsheet accessible to anyone with the link (if you’re planning to use the data on a public website).

f. Obtain the Google Sheets URL:

  • Copy the URL of your Google Sheets document from the browser’s address bar. It should look something like this: https://docs.google.com/spreadsheets/d/SPREADSHEET_ID/edit

Now that you have your Google Sheets set up with employee information, you can proceed to the next steps of integrating SheetBest and creating your employee directory website.

2. Install SheetBest:

a. Visit the SheetBest Website:

Go to the SheetBest website at /.

b. Sign Up or Log In:

  • If you’re a new user, sign up for a SheetBest account using your email or a social media account. If you already have an account, log in.

c. Create a New Project:

  • After signing in, you’ll be directed to your dashboard. Click on the “Create Project” button to start a new project.

d. Connect Google Sheets:

  • Follow the prompts to connect your Google Sheets to the SheetBest project. You’ll likely need to provide authorization for SheetBest to access your Google Sheets data.

e. Map Columns:

  • In your SheetBest project dashboard, you’ll see a list of columns from your Google Sheets. Map these columns to the appropriate fields in your data model.
  • For example, if you have a “Name” column in your Google Sheets, you would map it to a “name” field in your data model.

f. Generate API Endpoint:

  • Once you’ve mapped the columns, SheetBest will generate an API endpoint for you to use. This endpoint will allow you to retrieve the employee data from your Google Sheets in a structured format.

g. Test the Endpoint:

  • Before proceeding, it’s a good idea to test the API endpoint using a tool like Postman or a web browser. When you access the API endpoint URL, you should see a JSON response containing the employee data.

h. Copy the API Endpoint URL:

  • Copy the API endpoint URL as you’ll need it in the next steps to fetch and display the employee data on your website.

You have successfully set up SheetBest and obtained the API endpoint URL, you’re ready to proceed with creating your employee directory website and fetching data using JavaScript. 

3. Map Columns

After connecting your Google Sheets to your SheetBest project, you’ll need to map the columns from your Google Sheets to the fields in your data model. This step is crucial to ensure that SheetBest understands how to interpret and format the data when it’s fetched via the API.

a. Navigate to the Columns Mapping Section:

  • In your SheetBest project dashboard, find the section where you can map columns. This is usually a visual interface where you’ll see a list of columns on the left and fields in your data model on the right.

b. Map Columns to Fields:

  • For each column in your Google Sheets, select the corresponding field in your data model from the right side. This tells SheetBest how to structure the data when it’s retrieved.

c. Data Types and Formatting:

  • Pay attention to data types and formatting. Make sure to map numeric columns to number fields, text columns to string fields, and so on. If you have date columns, you might need to specify the date format.

d. Save the Mapping:

  • Once you’ve mapped all the columns, save the mapping configuration. This ensures that SheetBest knows how to interpret your data.

After mapping the columns, SheetBest will provide you with an API endpoint that you can use to retrieve the employee data from your Google Sheets.

e. Locate the API Endpoint:

  • Look for the API endpoint URL in your SheetBest project dashboard. It’s usually prominently displayed for you to copy.

f. API Endpoint Structure:

  • The API endpoint URL should be a unique URL that, when accessed, fetches the structured data from your Google Sheets based on your column mapping.

g. Test the Endpoint

  • To ensure that the API endpoint is working correctly, you can test it using tools like Postman or a web browser. When you access the endpoint URL, you should see a JSON response containing the employee data in the format you’ve specified.

h. Copy the API Endpoint URL:

  • Copy the API endpoint URL, as you’ll use it in your website’s JavaScript code to fetch and display the employee data.

4. Fetch Data Using JavaScript:

In this step, you’ll use JavaScript to make a request to the API endpoint provided by SheetBest and retrieve the employee data. You’ll then dynamically create HTML elements to display the employee information on your website.

a. HTML Structure:

  • In your HTML file, create an empty container where you want to display the employee directory. You can use an element with a specific ID for this purpose.

For example:

<div id="employee-directory"></div>

b. JavaScript Fetch Request:

  • In your JavaScript code, you’ll use the Fetch API to make a GET request to the API endpoint URL.
const endpoint = "YOUR\_SHEETBEST\_API\_ENDPOINT";
fetch(endpoint)
  .then(response => response.json())
  .then(data => {
    const employeeDirectory = document.getElementById("employee-directory");
    data.forEach(employee => {
      const employeeCard = document.createElement("div");
      employeeCard.className = "employee-card";
      employeeCard.innerHTML = `
        ${employee.name}
        ${employee.jobTitle}
        ${employee.department}
      `;
      employeeDirectory.appendChild(employeeCard);
    });
  });
  • Replace “YOUR_SHEETBEST_API_ENDPOINT” with the actual API endpoint URL you obtained from SheetBest.

c. Create Employee Cards:

  • For each employee in the retrieved data, create an HTML element (e.g., a ) to represent an employee card.
  • Populate the card with the employee’s information (name, job title, department, etc.) using template literals.

d. Append Cards to Container:

  • Use the appendChild method to add each employee card to the employee directory container you defined in your HTML.

e. Styling:

  • You can use CSS to style the employee cards and the overall appearance of your directory. Apply classes and styles to ensure that the directory looks visually appealing and aligns with your website’s design.

f. Error Handling:

  • Implement error handling in your JavaScript to handle situations where the fetch request fails or the data format is incorrect.

By following these steps, you’ll be able to dynamically fetch data from your Google Sheets using SheetBest’s API endpoint and display it on your website as an employee directory. Remember that this example is a simplified demonstration, and you can customize it to meet your specific design and functionality requirements. 

5. Deploy Your Website and Access Your Employee Directory:

  • Host your HTML, JavaScript, and CSS files on a web server. You can use services like GitHub Pages, Netlify, or Vercel for easy deployment.
  • Once your website is deployed, you can access your employee directory by visiting the URL where your website is hosted.
Danny Chu

Danny Chu

Product lead

Danny has led many successful growth and product teams, including Sheet Best for over 2 years