Using BotSheetAPI in your Chatbot

In this article, you'll learn to use BothSheetAPI in your chatbot.

1. Go to Facebook & Instagram > Chatbot Flow Builder

   

2. Select the Page > Create Messenger Flow

   

3. Enter Flow Name and click OK

   

4. Now we have dummy data in our bot sheet and we're going to perform BotSheet actions in our chatbot flow. It'll include Find, Insert Row, Read, Replace Row, Update Cell and Delete Row.

---

Using the "Find" operation

In this, we need to run a find operation based on the inputs on Messenger and provide the correct response.

Example 1

Case: A user wants to confirm his/her registered number in the system.

Solution:

• We'll create a chatbot and trigger using the "Change Number" keyword

• In the 2nd step, we'll ask for the number and save it in a custom variable.

• We'll configure Find Operation in BotSheet and provide the correct message as per the response.

Step 1: Let's create a Custom Variable to store the entered mobile number.

• Go to Utilities > Custom Variable > Create New Variable

• Enter custom variable name > Select user specific > Enter fallback value

  Note: You can also manually enter the fallback value on your custom variable. For example, if your custom variable is {{search_entry}} and you want to fallback the entry to “Cabinet", what you can do is enter this {{search_entry|cabinet}}

  The system will use the fallback value when the custom variable doesn't have any value yet from user input.

• Click on Confirm Changes

Step 2: Build Chatbot Flow using Find Operation

The custom variable is created successfully, let's jump into the chatbot flow builder and build the flow.

1. Add a Text Element asking for the user's email id.

   

2. Add a User Input Element > Capture the entered email id into a custom variable.

   

3. Add a BotSheetsAPI Element > Click on it to configure

   

4. Select Google Sheet > Enter Sheet Name

   

   How to find sheet name? - Go to your sheet > Copy your worksheet name from the bottom.

5. Select Find Operation > Add Custom Variable for Find Operation and also include the Default value override syntax

   

   Syntax: {{custom_variable|<Your Override Here>}}

6. Add the Search column where you want to search the value > Enter your Sheet's full table range

   

7. Click on the Test Request button > Skip the Subscriber data option

   

8. Select the value from the response and save it into a custom variable

   

9. Click the "+" button to add > Click Confirm Changes

   

10. Add a Text Element to show the mobile number and enter the message accordingly.

    

11. Add a Trigger Element > Set "Change Number" as the trigger keyword.

    

12. Connect Node sockets correctly and Save the flow.

    

13. Let's test the find operation: Go to Messenger and enter the trigger keyword.

    

    The find operation is working perfectly.

---

Using the "Read" operation

In this, we need to run a read operation and give a response from Google Sheets.

Example 1

Case: A user is asking for the pricing details and we'll share a pre-built message stored in google sheet.

Solution:

• We'll create a chatbot and trigger using the "Plan" keyword

• In the 2nd step, we'll configure Read Operation in BotSheet.

1. Add a Trigger and BotSheet Element

   

2. Select Google Sheet > Add Sheet Name > Choose Operation > Choose Read Type > Set Range and Click on Test Request

   

3. Select the value from the response and save it into a custom variable > Click the "+" button to add > Click Confirm Changes

   

4. Add a Text Element and show the Plan variable

   

5. Connect Node sockets correctly and Save the flow.

   

6. Let's test the Read operation: Go to Messenger and enter the trigger keyword.

The Read operation is working perfectly.

---

Using the "Insert Row" operation

In this, we need to insert some data shared in the messenger chat into a Google Sheet.

Example 1

Case: User data and contact details will be inserted into Google Sheet.

Solution:

• We'll create a chatbot and trigger using the "Registration" keyword

• Name, Email Id, Mobile Number, and Country will be stored in custom variables using UserInput element and will be inserted into Google Sheet through BotSheet API.

1. Add a trigger element and Set the "Registration" keyword

2. Add 3 Text elements and 3 UserInputs for Email, Phone Number, and Country

   Note: Make sure you have already made custom variables for the above.

3. Add BotSheetAPI Element > Start to Configure > Select Sheet > Enter Sheet Name > Select Insert Row Operation

   

4. Add custom variables within each column and Click on Test Request

   

5. Skip Subscriber Data > Click on Confirm Changes

6. Add a Text Element to show the confirmation message

   

7. Connect Node sockets correctly and Save the flow.

   

8. Let's test the Insert Row operation: Go to Messenger and enter the trigger keyword.

   

   Congrats, The Insert Row operation is working perfectly.

---

Using the "Update Cell" operation

In this, we are going to update the contact number. We'll ask Email id first to confirm in the system and then we'll ask for New Number.

1. Add a Trigger Element and add the keyword "Update".

2. Add 2 Text Elements and 2 UserInput Elements for Email Id and New Number Input.

   

3. Now, we have to perform 3 BotSheetAPI Actions: Find the email Id, Update the Cell, and Read operation to show the updated number.

4. Find the Email to confirm whether the user is valid or not:

   1. Run a basic find operation

      

   2. Capture Row Id into a custom variable for Update Cell Operation

      

5. Update the New Number: Run update cell operation

   1. Add Mobile Number Custom Variable within Set Value and Add Row Id Variable and Coulmn

      

6. Run a Read operation to update the number on Mobile Number Variable

   1. Choose Row as Read Type > Add Row Variable in Row Index > Set Range

      

   2. Click Test Request > Map the Mobile Number Value with Mobile Number Variable

      

   3. Click Confirm Changes

7. Add a Text Element to show the confirmation message.

   

8. Connect Node sockets correctly and Save the flow.

   

9. Let's test on Messenger.

Congrats, The Update Cell operation is working perfectly.

---

Using the "Replace Row" operation

In this, we are going to replace the entire row. We'll ask Email id first to confirm in the system and then we'll ask for New Email, New Number, and New Country.

1. Add a Trigger Element and add the keyword "Replace".

2. Add a Text Element and UserInput to capture the email id for validation.

   

3. Add a BotSheetAPI Element to validate email id and Configure it

   

4. Map Response Row and add it to RowId Variable > Click Confirm Changes

   

5. Add 3 Text Elements and 3 UserInput for New Email, Number, and Country.

   

6. Add a BotSheetAPI Element and Configure it for Replace Row operation.

   1. Choose Replace Row Operation > Enter Custom Variables in the columns

      

   2. Set Row Index and Click Confirm Changes

      

7. Add a Text Element to show the confirmation message.

   

8. Connect Node sockets correctly and Save the flow.

   

9. Let's test on Messenger.

   

   Congrats, The Replace Row operation is working perfectly.

---

Using the "Delete Row" operation

In this, we are going to delete the entire row. We'll ask for an email id for confirmation.

1. Add a Trigger Element and add the keyword "Delete".

2. Add a Text Element and UserInput to capture the email id for validation.

3. Add a BotSheetAPI Element to validate email id and Configure it

   

4. Map Response Row and add it to RowId Variable > Click Confirm Changes

   

5. Add a BotSheetAPI Element > configure it for Delete Row > Enter RowId Variable in the Row Index

   

6. Add a Text Element to show the confirmation message.

   

7. Connect Node sockets correctly and Save the flow.

   

8. Let's test on Messenger.

The row is deleted successfully.