Skip to main content

Tutorial - A Google sheets powered FAQ bot

This tutorial shows how to build a Dialogflow FAQ chatbot using Narratory and a Google Sheet as CMS.

Source of the final app is available on Github

CMSClients
  • Google Sheets as CMS:

    Showcase
  • Phone:

  • Facebook messenger:

    Showcase

Goals of tutorial#

The goals of the bot as of this tutorial are:

  • The bot should accurately provide answers to general questions about the Corona crisis
  • Non-programmers should be able to add questions and answer pairs to the bot using a Google Sheet

In addition, the following goals will be fulfilled in the next tutorial:

  • The bot should be accessible through phone, Facebook messenger and on a website
  • The bot shoud be able to provide real-time answers on the amount of infected and deceased people in different countries
  • The bot should allow users to help verify the accuracy of the bot after each question
  • Non-programmers should be able to act as AI-trainers and continuously improve the bot using the same Google Sheet

Writing a script#

As a first step, we decide on a rough script of a happy flow through the bot:

Bot: Hi and welcome to CoronaGuidenBot: I can answer questions about the Corona crisis and also give you the latest news.Bot: What can I do for you?> User: What is Corona?Bot: Corona is a type of viruses and COVID-19 is the particular virus that ...Bot: Do you have any other questions?> User: How many are infected in Stockholm?Bot: As last reported yesterday, 2000 people have been infected now, of which 150 yesterdayBot: Do you wonder anything else?> User: NoBot: Thanks for now. Reach out again if you have more questions! Bye!

With this script as a goal, we start mapping our script to the Narratory building blocks: bot and user turns.

Determining a narrative#

Based on the desired script above, the narrative of this dialog will be quite short and consist of the following BotTurns:

  1. Greeting
  2. Intro
  3. QueryQuestion
  4. Goodbye

If the user asks a question, the bot should answer it and then go back to the QueryQuestion bot turn. It should only proceed to the final goodbye BotTurn when the user doesn't have any more question.

Each question and answer pair will be considered UserInitiatives. This means that they will be available at any point in the dialog - even if the bot has asked a direct question. In a later tutorial, you will understand why we make this design-choice.

Setting up Narratory#

Sign up, install Narratory and create an agent as of the setup guide.

Building the narrative#

Once we have created our agent as described in the setup guide, we replace the narrative in the file src/narrative.ts with the following:

import { BotTurn } from "narratory"import * as nlu from "./nlu"
const greeting: BotTurn = {  say: "Hi and welcome to CoronaGuiden",}
const intro = {  say: "I can answer questions about the Corona crisis and also give you the latest news.",}
const queryQuestions: BotTurn = {  say: [    {      // If it is the first time we state this question      cond: {        turnCount: 0, // An automatically set variable - set to 0 if it is the first time this turn is executed      },      text: [        "What can I do for you?",        "Do you have a question for me?",        "What do you wonder?",        "What are you curious about?",      ],    },    {      // On subsequent questions      text: [        "Do you have any other question?",        "What else do you wonder?",        "Are you curious about anything else?",      ],    },  ],  user: [    {      intent: nlu.yes, // Because the user might just answer "yes", in which case we stay in this turn - i.e we "repair" the turn      bot: {        say: "What do you wonder?",        repair: true,      },    },    {      intent: nlu.no, // Moving on to the next turn in the narrative      bot: {        say: "Okay",      },    },  ],}
const goodbye = "Thanks for now. Reach out again if you have more questions! Bye!"
// Finally, we export our narrative consisting of all the 4 BotTurns aboveexport default [greeting, intro, queryQuestions, goodbye]

If you paste in the above, your editor will likely warn that nlu.yes and nlu.no can't be found. As you see on the second row of the above snippets, we import all contents (through *) from the nlu.ts file as nlu, but we haven't yet created yes and no in this file. Let's head over to the file /src/nlu.ts and do that. Go into this file and replace the start-kit content with the following:

// in src/nlu.tsimport { Intent } from "narratory"
export const yes: Intent = {  examples: ["yes", "absolutely", "of course", "yep", "okay", "alright"],}
export const no: Intent = {  examples: ["no", "nope", "never", "I don't want to", "none"],}

With this done, we are ready to test-run our app:

First run

Naturally, our bot doesn't yet have an answer to the question we posed. Instead, the default fallback was triggered. Later on, we will add a specific fallback here instead to give additional help to the user.

Adding questions#

For the actual content of our bot - i.e our questions and answers - we want to use a CMS (content management system) to allow non-coders to add question and answer pairs. For this tutorial we choose to work with a Google sheet - but it could really be any type of CMS.

Setting up a Google Sheet#

First, we create a new Google sheet and set up columns as shown in the below screenshot. We define several question columns (named q1, q2 etc) since there are always many ways to ask the same question. Also take note of the Google Sheet Id (in the red box) and the Tab Id (in the green bot) here:

Google sheet setup

To give our Narratory agent access to the sheet, first get your Google service account email in your google_credentials.json file:

Google sheet setup

Then share your sheet with this email (read access is enough for now):

Google sheet share email

Now, our sheet is ready - feel free to add more question and answer pairs to the sheet.

Generating UserInitiatives from the sheet#

To have our Narratory agent generate UserInitiatives based on the questions and answers we add to the Google sheet, we will build a generate-script that uses the npm library google-spreadsheet to connect to our sheet.

Install the library with npm install google-spreadsheet.

Now, for generation of our FAQ, we create a file generate.ts in our src folder with the following contents:

import { UserTurn, Intent } from "narratory"import { GoogleSpreadsheet } from "google-spreadsheet"import googleCredentials from "../google_credentials.json"import * as fs from "fs"
// Our sheet and tab IDs, get it from the Google sheet URL. The format is: // https://docs.google.com/spreadsheets/d/<SHEET_ID>/edit#gid=<TAB_ID>// The below comes from the URL https://docs.google.com/spreadsheets/d/1_yE6vtI43VsLm8-bUvYedZf56lkageNJ4GYrTvJnS7Y/edit#gid=0const googleSheetId = "1_yE6vtI43VsLm8-bUvYedZf56lkageNJ4GYrTvJnS7Y"const googleSheetTabId = "0"
// Creates UserTurns from our hardcoded sheet urlsasync function getUserTurnsFromSheet() {  try {    // Load our sheet    const doc = new GoogleSpreadsheet(googleSheetId)    await doc.useServiceAccountAuth(googleCredentials)    await doc.loadInfo()    const sheet = doc.sheetsById[googleSheetTabId]
    // Get all lines from the sheet    const rows = await sheet.getRows()
    // Container for all our UserTurns    const userTurns: UserTurn[] = []
    // For each row, we create a new UserTurn    rows.forEach((row: any) => {      const intent: Intent = {        examples: [],      }
      // Loop over all columns in the row and add examples if the columns is called qx - where x is an integer      for (let i = 0; i < Object.keys(row).length; i++) {        const label = `q${i}`        if (label in row && row[label]) {          intent.examples.push(row[label])        }      }
      // If we have at least one question formulation and we have an answer, we create a UserTurn      if (intent.examples.length >= 1 && row.answer) {        const userTurn: UserTurn = {          intent,          bot: row.answer,        }        userTurns.push(userTurn)      } else {        console.log("Skipped userTurn due to missing examples or bot answer")      }    })    // Finally, we return our UserTurns    return userTurns  } catch (err) {    console.log("Error: " + err.message, err.stack)  }}
// Generates the file FAQ.ts in our /src folderconst generateFaq = async () => {  const importStr = `import { UserTurn } from "narratory"`
  const userTurns = await getUserTurnsFromSheet()
  const faqStr = `export const FAQ : UserTurn[] = ${JSON.stringify(    userTurns,    null,    2  )}`
  const str = [importStr, faqStr].join("\n\n")
  fs.writeFileSync("src/FAQ.ts", str)}
generateFaq()

The above script does three things:

  1. Fetches all rows from the Google sheet with the designated sheet and tab ID
  2. Creates UserInitiatives (each with an intent composed of all provided examples in the sheet and a bot answer also from the sheet)
  3. Generates a file called FAQ.ts in our src folder with these UserInitiatives

Add a command line script to generate the FAQ#

To be able to run the script conventiently, we add a npm run script that we can run from terminal by adding the row "generate:faq": "tsc && node out/src/generate.js" to the script object in our package.json file:

// package.json{  // ...  "scripts": {    "watch": "tsc --watch",    "generate:faq": "tsc && node out/src/generate.js"  }}

Finally, we run this script in terminal with npm run generate:faq. First time we do this, we will get an error saying:

Error: Google API error - [403] Google Sheets API has not been used in project 998831680502 before or it is disabled. Enable it by visiting https://console.developers.google.com/apis/api/sheets.googleapis.com/overview?project=998831680502 then retry

We need to enable the Google Sheets API in order to use this npm library. Follow the link provided in your error message to enable it as shown here:

Google sheet enable api

Run the script again and it should work (if not, wait a minute for the Google API enabling to probagate in their systems). Inspect the src/FAQ.ts file and you should see something like the following:

import { UserTurn } from "narratory"
export const FAQ: UserTurn[] = [  {    intent: {      examples: ["what is corona", "what does corona mean", "what is covid"],    },    bot: "Corona, or COVID-19 as it officially is called, is a mean virus",  },  {    intent: {      examples: ["what is your name", "who are you"],    },    bot: "I am CoronaGuiden",  },]

Add FAQ to UserInitiatives of your Agent#

Now, the final thing you need to do is to add your FAQ as UserInitiatives in your Agent. You do this by importing the FAQ and adding it to the userInitiatives array of your Agent object in src/agent.ts:

import { Agent, Language } from "narratory"import narrative from "./narrative"import userInitiatives from "./userInitiatives"import { FAQ } from "./FAQ"
const agent: Agent = {  agentName: "CoronaGuiden",  language: Language.English,  narrative,  userInitiatives: [    ...userInitiatives, // Adding old userInitiatives from userInitiatives.ts file    ...FAQ, // Adding FAQ from Google Sheet  ],  bridges: ["So", "Where were we", "Now"],  narratoryKey: require("../narratory_credentials.json").narratoryKey,  googleCredentials: require("../google_credentials.json"),}
export default agent

Here, we use a spread operator (the three dots - ...) two times to add the userInitiatives from the old userInitiatives.ts file and our new FAQ together in a concatinated array. Another way of doing this would be userInitiatives: FAQ.concat(userInitiatives).

Now we can build our bot again with narratory build and then start a chat with narratory chat (or use narratory start which combines the two):

Bot full runthrough

Removing bridges#

In the above run-through of the bot, you can note that the bot says "Where were we" after answering questions. This is since, normally, UserInitiatives are used as detours from the narrative and you might want to have a short "bridge" utterance (defined globally for each agent in the Agent object's bridges array) that lets the user know the bot is now continuing along the narrative. For this dialog, however, this is unnessesary since we expect the user to ask questions at this point of the dialog.

Thus, we can remove the bridges parameter from our src/agent.ts file and then re-run our chat.

Conclusion and next steps#

This concludes the first step of this tutorial series.

In the next tutorial, we will:

  • make the bot available on Websites, Facebook messenger and through phone
  • add dynamic, real-time statistics from an API
  • make the bot automatically verify the accuracy of the language model
  • add AI trainer functions to the Google sheet CMS

For a full code example, please see the app on Github.

Do you have feedback or questions? Reach out at info@narratory.io