Building a Mock Data Generator with Google Sheets, Gemini AI & ToolJet ⚙️

Introduction

This tutorial will guide you through the process of building an AI-driven Mock Data Generator using ToolJet, a low-code visual app builder, and the Gemini API, a powerful natural language processing API. We'll also use ToolJet's build-in integration with Google Sheets to store our mock data. The resulting application will enable users to generate mock data based on the sample format data present in the spreadsheet. We'll use ToolJet's visual app builder to create a user-friendly UI, and ToolJet's low-code query builder to connect it to the Gemini API endpoints and our Google Sheets data source.

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.
• Gemini API Key : The Gemini API is an advanced AI service provided by Google AI Studio. It enables developers to integrate powerful content generation capabilities into their applications.
• Google account with access to Google Sheets: Log into Google Sheets using your Google account and create a new spreadsheet. Add column names to define the structure of your data. Additionally, you can create at least one row of data.

Here is a quick preview of our final application:

Step 1: Prepare your Google Sheets Document

We will be starting this tutorial by setting up the Google Sheets document with the following data.

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.

• On the ToolJet dashboard, locate the Data Sources section on the left sidebar. Click on the +Add button under the Google Sheets plugin.
• Choose the Read and write option since we will be adding the mock data to our Google Sheet.
• 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 Mock Data Generator. Now that we’ve set up our App, it’s time to create the UI.

Step 3: Building the UI

• Drag and drop the Container component onto the canvas from the component library on the right side. Adjust the height and width of the Container component appropriately.
• Similarly, drag and drop the Icon and the Text component onto your canvas. We'll use them as our logo and header.
• For the Icon component, navigate to the properties panel on the right and select the appropriate icon under the Icon property.
• Change the color of the Icon and Text component according to your preference.
• Drag and drop the Dropdown component inside your container. We'll use this dropdown to choose between the available sheets. Rename this component to selectSheet.
• Similarly, drag and drop two Button components inside the container. We'll use these buttons for generating mock data and saving the data to the Google Sheet.
• Next, add a Table Component to display the generated mock data.

Step 4: Setting up Queries

1. Fetching the Sheets

• Expand the Query Panel at the bottom and click the Add button to create a query - rename this query to getSheets.
• Choose Data Source as googlesheets, and Operation as Get spreadsheet info.
• 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.
• To ensure that this query runs every time the application loads, toggle Run this query on application load?
• Enable the Transformations toggle and enter the following code:

return data.sheets.map(item => item.properties.title);

This will return all the sheet names in an Array. We'll use this to populate the values in our dropdown component.

2. Fetching initial sample data

• Similarly, create another query and rename it to getInitialData.
• Choose the Operation as Read data from a spreadsheet.
• Enter the following code in the Sheet field:

{{components.selectSheet.value}}

3. Generating Mock Data using Gemini API

• Using ToolJet's Workspace Constants feature, create a new constant named GEMINI_API_KEY with your Gemini API key.
• In the query panel, click on the + Add **button and choose the **REST API option. Rename the query to generateMockData.
• In the Request parameter, choose POST as the Method from the drop-down and paste the following URL. https://generativelanguage.googleapis.com/v1beta/models/gemini-1.5-pro:generateContent?key={{constants.GEMINI_API_KEY}}
• Navigate to the Body section of getSqlQuery. Toggle on Raw JSON and enter the following code:

 {{
  `{
   "contents": [{
     "parts": [{
       "text": "Sample Data: ${JSON.stringify(queries.getInitialData.data[0]).replace(/\\?"/g, '\\"')}, Text Prompt: Based on the sample data, only return an Array with 10 objects with same type of mock data without any code highlighting, formatting or backticks"
      },],
    },],
  }`
}}

4. Inserting data into our Google Sheet

• Create another query and choose Data Source as googlesheets, and Operation as Append data to a spreadsheet.
• Enter the following code in the Sheet field:

{{components.selectSheet.value}}

• Enter the following code in the Rows field:

{{JSON.parse(queries.generateMockData.data.candidates[0].content.parts[0].text)}}

Step 5: Binding Queries to the UI Components

Now that we have successfully built our UI and queries, the next step is to integrate them.

• Select the Dropdown component, under the Properties section, and enter the following code for both Option values and labels fields: {{queries.getSheets.data}}
• Select the Generate Data Button component, under the Properties section, click the New event handler button to create a new event.
• Choose On click as the Event, Run Query as the Action, and select getInitialData as the Query.
• Select the getInitialData query, and click the New event handler button to create a new event.
• Choose Query Success as the Event, Run Query as the Action, and select generateMockData as the Query.
• Next, Select the Table component. In the properties panel on the right, enter the following code in the Data field.

{{JSON.parse(queries.generateMockData.data.candidates[0].content.parts[0].text)}}

• Select the Save to Google Sheets Button component, under the Properties section, click the New event handler button to create a new event.
• Choose On click as the Event, Run Query as the Action, and select insertData as the Query. We have successfully integrated our queries into our UI.

Now let's test the application with the following sample data format:

• Table Preview:
  

• Click on the Save to Google Sheets Button.
  

Conclusion

Congratulations on successfully building an AI-driven Mock Data Generator using ToolJet and Gemini API.

To learn and explore more about ToolJet, check out the ToolJet docs or connect with us and post your queries on Slack.