A Spreadsheet is enough to create and update your QnA bot. Check how!

A Spreadsheet is enough to create and update your QnA bot. Check how!

There is an update on this bot, and it is not available for new implementations. If you have implemented it already, it will work as expected. 
​​​​
Yes, now you can create and update your QnA bot with the help of a spreadsheet. 
We've built a Zobot that can answer FAQs with the help of Google Spreadsheet instead of scripts. Also, we have integrated the Zobot with Dialogflow to respond to other common questions that are not updated in the spreadsheet. 
 
For your benefit we're sharing the logic and scripts for the bot, do give it a try! 
 
Bot Requirements and Integration:   
  • Google Spreadsheet : To store main menu options, FAQs, and new questions that are handled neither in spreadsheet nor Dialogflow
  • Dialogflow  : To handle free-form questions that are not available in spreadsheet 
What does this bot do?
  • This bot will trigger a chat once the visitor hits your site, i.e. the bot is intended to be proactive. 
  • You should construct the main menu, questions and answers to the FAQs using the Google spreadsheet and the bot can dynamically respond according to the queries received. The bot can search the queries received from the visitors in the spreadsheet and assist the visitors with the exact answer.
Note:  The Google spreadsheet where the Question and answers are stored should be named as ‘ SalesIQ-ZobotSheet '. And the columns should be populated in the following order:                                                                   
  • Column 1: Question department (the menu option)
  • Column 2: Question
  • Column 3-7: Response 
  • If the bot doesn't find the question in the spreadsheet, then it automatically takes the help of Dialogflow integration to respond. 
  • If the queries received from the visitor is not available neither in the spreadsheet nor in the Dialogflow, then the bot will collect the queries in a separate google spreadsheet. So, you can write an answer and add them to Q&A bot spreadsheet later. Any response obtained from  Default Fallback Intent in Dialogflow will be treated as an unknown question and prompt the user to re-enter the question. The unknown question will be saved to another spreadsheet. 
Note : You should create a separate spreadsheet to collect the unknown question and name the sheet as ' SalesIQ-ZobotSheet-UnknownQuestions' . The sheet will be populated in the following order: 
  • Column 1: Unknown question
  • Column 2: Date and time in which the question was asked 
  • If you wish to add more questions to the bot or to remove the existing question, you don't have to make any changes to the code. You can just reflect the changes in the google spreadsheet or your Dialogflow account.
  • We have constructed this bot to read only the first 26 rows of the synced spreadsheet (SalesIQ-ZobotSheet.) If you wish to add more questions to the spreadsheet you can extend the limit by changing the upper limit in each Google spreadsheet connection URL.
Bot flow:

The bot initiates the chat with a welcome message and the main menu. Once, the visitor responds by choosing an option, and the flow continues as depicted



How to create the bot? 
  • Navigate to  Settings > Zobot > Add . Provide the required information and choose the  SalesIQ Script platform. 
  • Now, copy the script from  here  and paste in the Message Handler section.
To integrate with your  Dialogflow account  and Google spreadsheet you've created, make the changes marked in red in the message handler code:       
  • Replace the link of your  SalesIQ-ZobotSheet  spreadsheet in the URL
  • Replace your  Connection name
  • Replace the link of your  SalesIQ-ZobotSheet-UnknownQuestions  spreadsheet in the URL
  • Replace your  Connection name
  1.             apiresponse = invokeurl  
  2.             [ 
  3.                  url:"https : // sheets.googleapis.comv4/spreadsheets/ your-sheet-id-goes-here /values/SalesIQ-ZobotSheet!A2:A26?majorDimension=RO
  4. WS" 
  5.                   type :GET 
  6.                   connection:" your-connection-name-goes-here " 
  7.              ]; 
  8.  

  10.              apiresponseForInput = invokeurl 
  11.              [ 
  12.                   url:" https : //  sheets.googleapis.comv4/spreadsheets/y our-sheet-id-goes-here /values/ SalesIQ-ZobotSheet-UnknownQuestions!A2:E2:append?valueInputOption=USER_ENTERED"  
  13.                    type :POST  
  14.                    parameters:paramsSheet.toString() 
  15.                    headers:{"Content-Type":"application/json"} 
  16.                    connection:" your-connection-name-goes-here " 
  17.              ]; 

To connect your dialogflow account, replace the Authentication Id in the message handler: 
  1.                         headersMap = {"Authorization":"Bearer  your-dialogflow-client-id-goes-here ","Content-type":"application/json"}; 
  • Copy the script from  here , choose Context Handler from the drop-down and paste there.
  • Now, copy the script from  here,  then choose Trigger Handler from the drop-down and paste.    
To integrate with your Dialogflow account and Google spreadsheet you've created, make the changes marked in red in the Trigger Handler code: 
  • Replace the link of your  SalesIQ-ZobotSheet  spreadsheet in the URL 
  • Replace your  Connection name 
  • Replace the link of your  SalesIQ-ZobotSheet-UnknownQuestions  spreadsheet in the URL 
  • Replace your  Connection name
  1.             apiresponse = invokeurl 
  2.             [ 
  3.                  url:" https : // sheets.googleapis.comv4/spreadsheets/ your-sheet-id-goes-here /values/SalesIQ-ZobotSheet!A2:A26?majorDimension=RO
  4. WS" 
  5.                  type :GET 
  6.                  connection:" your-connection-name-goes-here " 
  7.             ]; 
  8.              
  9.              apiresponseForInput = invokeurl 
  10.              [ 
  11.                   url:" https : //  sheets.googleapis.com/v4/spreadsheets/ your-sheet-id-goes-here /values/SalesIQ-ZobotSheet-UnknownQuestions!A2:E2:append?valueInputOption=USER_ENTERED"  
  12.                    type :POST 
  13.                    parameters:paramsSheet.toString() 
  14.                    headers:{"Content-Type":"application/json"} 
  15.                    connection:" your-connection-name-goes-here " 
  16.             ]; 
 
 To connect your dialog flow account, replace the Authentication Id in the message handler: 
  1.       headersMap = {"Authorization":"Bearer  your-dialogflow-client-id-goes-here ","Content-type":"application/json"};   
  • Finally, Publish the bot.    
  Heads up!
  • Each option in the main menu/Question department can support a maximum of 30 characters. Hence the values in Column-1 of the SalesIQ-Zobot spreadsheet should not exceed 30 characters
  • To go to the next line, you can use the ‘ \n ’ in Dialogflow. In Google spreadsheet, you don't have to mention any next line char as the indentations explicitly and special chars in the cell will be reflected as it is in the chat.
  • To configure multiple responses in Dialogflow, you should create separate t ext respons  for each message.
  • To configure multiple responses in Google spreadsheet, you should configure each message as a separate column in the same row (maximum five responses.) 

To learn more about Zobot and its integrations, please visit our  Resources Section . 
Here is a brief visual on how the QnA bot works.



Happy Zoboting !!! 


Regards,
Fiaz