BOTNOI Academy

AI Chatbot By BOTNOI

Feb 19, 2024

Tutorial on How to Create Dynamic Flex Messages Using Google Sheets and Botnoi.ai EP.3 [Step-by-Step Guide with Codes]

Welcome back, everyone! Ready for another fun episode of chat bot adventures? Today, we’re going to learn how to create dynamic Flex Messages using data from Google Sheets and show them off with the Botnoi.ai platform. This method is super convenient and saves time because it automatically updates the Flex Message whenever the data in the Google Sheet is modified. No more manual updates! And the best part? Users just need to type a keyword we’ve set up, and the bot provides a random piece of data in a sleek Flex Message. We bet you can’t hardly wait to start!

What Does a Flex Message Displaying Data from Google Sheets Look Like?

In this episode, our Flex Message will pull random data to showcase examples or services. This is perfect if you want to provide users with a variety of sample data without them having to type in specifics. Just like before, we’ll use Google Sheets to store and manage our data.

Step 1: Understanding the Concept and Principles

Here’s the game plan: If a user wants to watch a random YouTube clip from Botnoi’s channel or clicks on a special Rich Menu, we’ll use an API to grab some random data and slot it into the Flex Message’s body. The bot will instantly reply with this Flex Message showing off our randomly picked data.

Since this example doesn’t need to answer specific or follow-up questions, there’s no need for a Dialogue. Instead, we’ll use Intents to manage this process.

For those using a Rich Menu to trigger this, you’ll also need to create an Intent because Rich Menu supports only one object at a time, but in this example, we’re using two objects: an API and a Flex Message. The trick is to set the Action as a Message trained in the Expected Phrase of the Intent.

If you’re triggering this with a Rich Menu, you’ll also need to create an Intent because Rich Menus only support one object at a time. But here, we’re using two: an API and a Flex Message. To make this work, we’ll set the Action as a pre-trained Message in the Expected Phrase of the Intent.

This setup is super flexible and can be adapted for lots of different uses: showing nearby places for a hotel bot, today’s menu for a restaurant bot, or even random English words for a language learning bot. Your imagination is the limit!

Step 2: Preparing the API for Data Exchange with Google Apps Script

function doGet(request) {
  var ss = SpreadsheetApp.openByUrl("YOUR_SHEET_URL");
  var sheet = ss.getSheetByName('YOUR_SHEET_NAME');


  const dataRange = sheet.getDataRange();
  const values = dataRange.getValues();
  const headerRow = values.shift();
  const randomIndex = Math.floor(Math.random() * values.length);
  const rowData = values[randomIndex];
  const col1 = rowData[0]; // ใช้ตัวแปร col1Value แทน col1value
  const col2 = rowData[1];
  const col3 = rowData[2]; // ใช้ตัวแปร col3Value แทน col3value
  const col4 = rowData[3];


  const selectedColumns = {
    column1: col1,
    column2: col2,
    column3: col3,
    column4: col4
  };


  var output = ContentService.createTextOutput(JSON.stringify(selectedColumns));
  output.setMimeType(ContentService.MimeType.JSON);
  return output;
}

Here's how the code works:

1. Open the Spreadsheet and Select the Designated Sheet:

- Start by opening your Google Sheet and choosing the specific sheet to work with.

2. Fetch All Data from the Sheet and Remove the Header Row (Row 1):

- Retrieve all the rows of data and exclude the header row since it contains column names.

3. Randomly Select the Index of a Row:

- Pick a random row index to decide which data row will be displayed.

4. Fetch Data from the Selected Row:

- Retrieve the entire row of data that was randomly selected.

5. Extract Data from Each Column of the Selected Row:

- Pull data from each column in the selected row to prepare it for display.

6. Store the Extracted Data in an Object:

- Organize the data into an object, which is a structure that holds the data together.

7. Create a TextOutput in JSON Format from the Object and Set MIME Type to JSON:

Query Parameter

When you run and deploy the Google Apps Script web application, you don’t need to add query parameters to the URL because our code doesn’t need them. Just put the deployment link in your URL bar and hit Enter.

If the code is working right, you’ll see a random row of data from your Google Sheet displayed in this format. Try refreshing the page a few times to see different data pop up that proves the API is up and running. If you want to display something specific, like a YouTube link in column 3, remember that this data is stored under the parameter `column3`.

Customizing and Modifying Flex Messages

Unlike our first example, here the parameter values come from the API instead of a Dialogue. Therefore, the way you call these values differs slightly. Since the parameter values come from the API, we’ll use the format `<<API_NAME.PARAMETER>>`.

Where API_NAME is the name of the API, prefixed with `API_`, while PARAMETER is the parameter name defined in the code. In this example, these are the clip name, details, and YouTube link, corresponding to `column1`, `column2`, and `column3`.as follows. The sequence can be edited according to the image below:

For the confirmation button, set its type to URI. If you see a warning pop up like in the image, just ignore it, it just concerns the data format. When you click "View as JSON," it updates the code to match what you wrote. You can then copy this JSON code and use it to create the Flex Message in Botnoi.ai.

This Flex Message doesn’t change its image – it keeps the default one each time. If you want to use a specific video thumbnail, just tweak the code and set the image parameter accordingly.

Step 3: Creating Intents on Botnoi.ai

To set up Intents, add the response as shown in the image above. Start with the API, followed by the Flex Message, because the system processes them in order from top to bottom. And that’s, you’re done!

How was that? Pretty straightforward, right? These steps are easy to follow and flexible enough that even if you’re new to coding, you can still create dynamic Flex Messages that show off various data!

Stay in the loop with Botnoi Group and Botnoi Ai through our website and Facebook page. Join the AI chatbot by the BOTNOI community to chat about AI and chatbot technology!

Follow our news and activities at: Botnoi Group
Platform for creating chatbots: Botnoi.ai
Previous video tutorial: How to extract data with Dynamic Flex Message

กลับ

BOTNOI AI Products

Enhance HR Efficiency and Upskill Employees with BOTNOI Voice

BOTNOI Academy

Canva: What You Should Know Before Getting Started

BOTNOI Academy

Get to Know Gemini: Google's Largest AI Model

BOTNOI AI Products

Enhance HR Efficiency and Upskill Employees with BOTNOI Voice

BOTNOI Academy

Canva: What You Should Know Before Getting Started

Collaborate to Innovate

Together, We Build the Future.

Join us as a partner

Collaborate to Innovate

Together, We Build the Future.

Join us as a partner

Tutorial on How to Create Dynamic Flex Messages Using Google Sheets and Botnoi.ai EP.3 [Step-by-Step Guide with Codes]