BOTNOI Academy

AI Chatbot By BOTNOI

Mar 29, 2024

Tutorial on how to make a Dynamic Carousel, pulling data from Google Sheet to display via Botnoi.ai EP.4 [step-by-step guide with codes]

Hello again, our dear budding coders and tutorial enthusiasts! Ready to level up your chatbot game? Today, we’re going to create a Dynamic Carousel that pulls data from Google Sheets and displays it on Botnoi.ai. No more manually designing each card. All you need to do is create one, and it automatically replicates based on the data rows in your Google Sheet. It’s so efficient, but remember that LINE allows up to 10 cards in a Carousel. So, let’s just get started!

In our previous tutorials that we’ve explored together, creating Dynamic Flex Messages required adding both Flex Messages and APIs, which can be quite a hassle. Especially for frequently changing data like room listings or product inventories, constantly tweaking JSON code is no fun.

But don’t you worry your pretty little heads because this time around we will teach you how to create an API for making a Dynamic Carousel that handles everything. It pulls data from Google Sheets, stores it in variables, inserts those variables into Flex Messages, duplicates them based on the number of rows, and outputs a payload that you can call directly through the API.

Step 1 : Data preparation

If you want to follow along with our example, you can grab the same code we used here. This code is courtesy of our awesome UX/UI intern at Botnoi. For more cool things from our team, check out our Facebook: Botnoi Academy.

First, prep the data you want to show in each Flex Message card. Knowing your data upfront makes designing easier. Today, we’ll use a room listing as our example for the Carousel room show.

Step 2 : Writing the API code

function doGet() {
  let room_list = format_data(readDataFromGoogleSheet('YOUR_SHEET_URL'));
  let room_carousel = format_room_flex(room_list);  
  let out = botnoipayload(room_carousel);
  let result = JSON.stringify(out)
  return ContentService.createTextOutput(result).setMimeType(ContentService.MimeType.JSON);
}

function readDataFromGoogleSheet(url) {
  let ss = SpreadsheetApp.openByUrl(url);
  let sheet = ss.getSheetByName('YOUR_SHEET_NAME')
  return sheet.getRange(2, 1, sheet.getLastRow()-1, sheet.getLastColumn()).getValues()
}


function format_data(data) {
  let result = [];
  for (let i=0; i<data.length; i++) {
    res = {
      'col1': data[i][0],
      'col2': data[i][1],
      'col3': data[i][2],
      'col4': data[i][3]
    };
    result.push(res);
  };
  return result
}

function format_room_flex(data) {
  let bubblelist = [];
  data.forEach((item) => {
    let bubble = {
    };
  bubblelist.push(bubble)
  });
  carousel = {
    "type": "carousel",
    "contents": bubblelist
  };
  return carousel
}

function botnoipayload(flexdata) {
    let out = {
    "response_type": "object",
    "line_payload": [{
      "type": "flex",
      "altText": "CRS_NAME",
      "contents": flexdata
    }]
  }
  return out
}

How the codes work is as follows:

function doGet
This is the main function called when the API is requested via an HTTP GET request. It reads data from Google Sheets, formats the data for use in LINE's Flex Message, and creates a payload for the LINE API using the formatted Flex Message. Finally, it generates a JSON response for the API call.

function format_data
This function takes the data from Google Sheets and reformats it into an array of objects in the desired format.
Note: You can rename the columns in this section to simplify editing in subsequent steps.
function format_room_flex
This function takes the formatted data from `format_data()`, loops through each item to create bubble objects, and stores them in an array called `bubblelist` for building the Carousel. It then adds these bubbles to the payload for the LINE API response, setting the `response_type` as an object and embedding the Flex into `line_payload`.
Note: This section requires you to design a Bubble, not a Carousel. The Bubble design will be used for each card.

Finally, a JSON object is created using the function `botnoi payload` and sent back as a response through `ContentService.createTextOutput()`, setting the MIME type to JSON. This enables the API to display the Carousel directly when used in botnoi.ai.

Note: You can name the Carousel in this section under `CRS_NAME`.

Step 3 : Plugging the API into Botnoi.ai

After deploying the API in Google Apps Script, copy the URL and set the method to GET.
Create an Intent in Botnoi.ai, train the bot, and add the previously created API in the Existing Object section under Bot Response.

That's it! You’re done. It might take a moment for the bot to respond. If images don’t show up the first time, don't panic. The image file size might be too large. It's best to compress images before using their URLs.

Troubleshooting Tips

If your Carousel isn’t showing up via the API, try these tips:

Check Your Image Links: Ensure all image URLs are valid and accessible. If you're unsure where to get image links, you can upload images to your chatbot, save them, and copy the URLs for use.
Verify Flex Message Type: Make sure you're using a Flex Message and not a full Carousel. In our room list demo, we used a Carousel format. Only copy the part of the first card to replicate it correctly.

Watch the Step-by-Step Tutorial Video:

Stay updated with Botnoi Group and Botnoi Ai on our website and Facebook. Join the AI chatbot by the BOTNOI community to discuss AI and chatbot technology!

Follow our news and updates at: Botnoi Group
Chatbot platform: Botnoi.ai
Download the free user guide: here
Watch our previous tutorial: EP.3

กลับ

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 make a Dynamic Carousel, pulling data from Google Sheet to display via Botnoi.ai EP.4 [step-by-step guide with codes]