Building a Mock Data Generator with Google Sheets, Gemini AI & ToolJet ⚙️
Tags: googlesheets, ai, javascript, lowcode
Published: 2024-06-25 13:33:12

## Introduction
This tutorial will guide you through the process of building an AI-driven Mock Data Generator using [ToolJet](https://github.com/ToolJet/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](https://www.tooljet.com/signup) for a free ToolJet cloud account or [run ToolJet on your local machine](https://docs.tooljet.com/docs/setup/try-tooljet/) using Docker.
- **Gemini API Key** : The Gemini API is an advanced AI service provided by [Google AI Studio](https://aistudio.google.com/app/apikey). 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:
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/y4m7a4r6dnegk1jv9v4a.png)

---

## Step 1: Prepare your Google Sheets Document
We will be starting this tutorial by setting up the Google Sheets document with the following data.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/t7yoxcewyjsuqkm2m7n3.png)

---

## 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.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wy3ubx5ota81yl4qzrce.png)

---

## 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](https://docs.tooljet.com/docs/org-management/workspaces/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:
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/eek2o7qduujovg6hjwv0.png)

- **Table Preview**:
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/t1dg2h8s11umapq96ow5.png)

- Click on the **Save to Google Sheets** Button.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qr3r6t6b6v0b6znxykqx.png)


---

## 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](https://docs.tooljet.com/docs/) or connect with us and post your queries on [Slack](https://join.slack.com/t/tooljet/shared_invite/zt-2ij7t3rzo-qV7WTUTyDVQkwVxTlpxQqw).