Google Sheets for AI assistant

In this article, we’ll explore how a bot can read data from Google Sheets and use it within an AI assistant

Thanks to a feature developed by the Mavibot team, the AI chatbot accurately interprets the values entered into the sheet and then saves them to a system variable in the project settings.

For example, you may need this for an AI assistant who will advise your customers on the available range of products in your online store, as well as to advertise your capabilities with a smart bot.

Exactly how to use a bot to read a Google sheet is up to you, and we, in turn, will show you how to create a sheet and configure the bot to work with it.

Preparing google sheet

Creation and filling

Open the sheet editor:

You need to fill in the rows with the column categories:

Since the AI can read data, it will use the function to write values from the sheet to a variable in the project settings, so we recommend adding concise and understandable column category names for your convenience and understanding of the bot's logic.

Next, specify the necessary names in the columns:

Let’s look at the "Unit of measure" column category:

The unit of measure can include:

1. A quantitative indicator in the form of pieces/units (one unit of goods, two units of goods, one piece, etc.):

1. Единицы измерения величин: литры, килограммы, граммы, метры:

The bot will perfectly understand what you meant and use the values embedded in the slots.

The final step in filling out the Google Sheet will be entering the price per unit of measure.

At this stage, the google sheet is complete.

Access Settings

In order for the AI bot can take values from the sheet, you need to open the access settings:

Click on settings, and the following window will open:

Here you need to change the setting from "Access is restricted" to "Everyone who has a link":

ID sheet

To work with the get_info_from_table(!sheet_id, number_sheet, sheet_json_keys) function, we will need the sheet ID, since the function takes sheet_id as a required parameter.

To get ID, just click on the address bar:

You will see a line with the page address:

From which we will need a set of symbols and letters, which is just the ID of the sheet:

This completes the work with the sheet and its configuration.

Working in the funnel constructor

Using the function get_info_from_table()

In order to use the get_info_from_table() function, you will need one block in the funnel constructor.

Go to the appropriate tab in Mavibot and create a primary condition verification block:

Instead of #{none}, we will write in the message a notification for the test mode after launching this block and a embedded variable through the #{} construct:

Pay attention!

The variable embedded in the message is necessary to verify that the bot is reading the function.

When running the block in testing mode, the bot will show the values that will be nested in the variable.

Pay attention!

After configuring the block call in test mode and saving the variable in the project settings, do not delete this block.

You may need it: for example, if you have added any more values to the table, just run the block in test mode. Then the data in the project variable will be updated.

The variable name can be any name that suits you.

Next, we need get_info_from_table(sheet_id, number_sheet, sheet_json_keys) function, - a function which is designed to read data from a table.

параметры	описание
! sheet_id	id google-таблицы
number_sheet	Номер листа в таблице; по умолчанию = 1
sheet_json_keys	Необязательный параметр, ключ доступа к данным таблицы

Open the calculator and enter the ID of the table, enclosing it in a variable (for example, sheet):

Next, assign the variable specified in the block message and assign it a value in the form of the get_info_from_table() function with the required parameter sheet_id (instead of which insert the variable sheet, where is the table ID):

Attention! Before the product_pay_info variable in the calculator, write project. This is necessary in order for the VARIABLE and the VALUES from the table to be entered into the project settings (cached):

Now write any word/numbers in the block condition:

Data transmission block

Next, create a second Status block:

This Status block will be needed to transfer variables to the client's card.

You can also send a final message from the bot in this block, for example, "Thank you for ordering! I'm sending the data to the communications manager!":

Next, copy the block ID - you will need it to configure AI assistant in the command "start_block_from_ai 31241050", where instead of the ID from the example, you need to insert your block ID.

Sheet reading

Now launch the block in the test version so that the chatbot reads the values from the sheet and puts them in your variable in the project settings:

Thus, the block worked out correctly: you see the values that you read and which are in the variable in the project settings.

You can verify this by going to the appropriate section:

Next, you will see your variable with the values embedded in it from the table:

So now, inside your project, there is a variable with goods, cost, and everything that you specified in the sheet.

Working as AI assistant

Go to the "AI assistant" section and start configuring artificial intelligence.

Assistant role: salesperson

We will also set the "Buttons" and "Step-by-step data entry" options:

We will need buttons for visually designed responses from the bot, as well as for the convenience of the client (then your user does not need to manually enter words in the dialog); step-by-step data entry is for the bot to survey the order being generated.

AI Assistant Settings

In the settings of the bot we write the following data:

1. The position and instructions for the bot.

Example:

<You're a store seller. "All the little things. Your task is to ask the client which product he wants to choose, how many units of the product, and then calculate the total cost of the product, equal to one unit of the product multiplied by the cost.

After you have interviewed the customer about his purchase, duplicate the order for him and ask "Is the order correct?">.

Pay attention on the line "Calculate the total cost of the product, equal to the unit of the product multiplied by the cost" - this line will display the final result of the cost of all selected categories by the client:

Example:

The client selected three kg of one product. The bot, in turn, calculates the total cost:

1. The command to call the block and transfer variables to the system is written as follows:

<If the customer wrote "The order is correct" or "Correct", then write "start_block_from_ai N">, where instead of N, the block ID is written with the final message: the Status block transmits data to the customer's card:

The commands in the AI assistant settings look like this:

Bot knowledge

In the "Bot Knowledge" field, pass the variable that was previously cached by the condition's Primary Verification block:

Bot Knowledge:

This way, the bot will take information from a variable in the project settings and advise your clients on the available product range.

Data format

In the format of the transmitted data, you specify the data you are collecting yourself (for example, a product), then enter the phrase with the "key" and assign a value to the key:

Explanation

Line 1: "The format of the transmitted data: product with the product key, unit of measure with the thing key, price with the count key, the total cost of all selected products with the result key" - makes it clear to the bot what data and with what value needs to be transmitted to the system.

These variables will be displayed in the client's card:

1. If you need each product selected by the customer to be displayed separately, add a second line:

"If the customer has selected several products, then add the first product with the product1 key, the second product with the product2 key, the third product with the product3 key, and so on".

This completes the setup of the AI assistant.

The remaining parameters can be set at your discretion:

Testing bot

Use the condition constructor to add the bot's operation in the test window:

Step 1. Click on the condition constructor

Step 2. Select the tab with the text box and click "Add":

Now, in the startup condition, you see the following value:

This way, the bot will launch only in the test window and nowhere else.

After debugging the bot, remove this condition.

Now let's test bot:

1. Let's ask a security question to the bot about the product range:

The bot showed an variety of products that were entered in the google sheet, capturing values from a variable contained in the project settings.

1. Next, we will select the product and talk to the smart bot:

1. Let's choose another product:

The bot also displays correctly, showing the customer the product categories for later selection.

1. The customer selected several products and selected their quantity:

The bot calculated the cost of the selected items and calculated the total cost.

1. Sending the data to the system:

Result: after the bot duplicated the order and the client replied that the order was correct, the chatbot, based on the settings, called the "Dialog status" block:

After that, the data generated by the bot in the dialogue with the client was sent to the client's card:

The bot worked everything out correctly.

This way you can create an online store of any kind and use any necessary values inside the table.

The main limitation - is only your imagination!

Video Guide