Cleo Espiritu

Using the Hangout Chat Bot API for an App Maker App

On Episode 20 of Totally Unscripted, I joined Martin and Steve to talk about integrating Hangouts Chat Bot and App Maker.

You can learn more about the Hangouts Chat API here. In this post I’ll go over the steps on creating the bot I showed in the episode.

Adding a Chat bot component creates a lightweight conversational UI that your users may find quicker/easier to sue, especially if you have a more complex App Maker app and there are smaller tasks that user can complete without loading up your entire app. Plus, on mobile, I find using the Chat app is a lot quicker than loading Chrome to get to my App Maker app!

App Maker + Hangouts Chat Bot

 

 

To build a quick chat bot for an App Maker app, we are going to build a quick Apps Script project that connects to the same Cloud SQL database as the App Maker app, so we can run the queries required to build the bot’s response to a message.

Walkthrough: Build a Hangouts Chat Bot for the Document Approval App

If you haven’t seen it yet, check out the App Maker template for a Document Approval app. It works great right out of the box and it allows you to specify approval workflow for a Google Drive document.

To demonstrate how to build a chat bot component to an App Maker app, I’m going to go over how to create a bot that will allow the user to ask for the list of documents that are waiting for their approvals.

What you’ll need to get started

  • A G-Suite Business/Enterprise/Education account (required for App Maker)
  • Access to App Maker with a Google Cloud SQL database setup
    • For this tutorial we will cover the use of the default Cloud SQL db for the App Maker app
  • Knowledge of Apps Script
  • Knowledge of SQL
  • The instance name of your default Cloud SQL database, as well as a username/password to connect to the database
    • You can find the instance name in the Google Cloud Console.
      • The format will look like this: project-name:us-central1:instance-name
      • You can also set up a username/password from the console.
    • If you don’t have access to this information, you will need to ask your G-Suite Administrator for the instance name as well as setting you up with a username & password

Step 1. Create and deploy the App Maker Document Approval App

if you have an existing Document Approval app deployed already that you want to use, you can skip this step.

Open the template in App Maker; optionally, rename the project once it loads.

Now hit [Publish] > [Publish New Deployment]. Give your deployment a name and hit [Publish] to deploy the application.

Once your app is published, go to the application to create some test data – add a few Documents to the app, and add yourself as the approver for the documents.

Step 2. Get the Database Key for the Deployment

While still in App Maker, go to [Publish] > [Edit Deployment]. Expand your deployment and then go to [Edit]. Under Google Cloud SQL Address, look for the database key – it should be a random string of characters. Copy this string somewhere – you’ll need this in Step 4.

Step 3. Create the Hangouts Chat Bot Apps Script Project

If you have never built a Chat Bot before, I recommend going through the codelab first to learn how to build and publish your first bot with Google Apps Script.

Use the template mentioned in step 3 of the codelab to start your project, which pre-builds the functions you need.

You can change the onAddToSpace and onRemoveFromSpace functions for the bot to respond with a message of your choosing when your users start/ends interaction with the bot.

Step 4. Change the OnMessage function to find documents waiting for user’s approval

The onMessage function is where the magic happens.  Replace the code in that function with the following (replace INSTANCE_NAME, DATABASE_KEY and USERNAME/PASSWORD with your own information):


function onMessage(event) {
  var message = event.message.text;
  // get the current user's email for our query
  var email = event.user.email;
  
  var compare = message.toLowerCase();
  // determine user's action - we are looking for the word "approval"
  if(compare.indexOf("approval")>=0){
    // connect to the SQL database
    var conn = Jdbc.getCloudSqlConnection("jdbc:google:mysql://INSTANCE_NAME/DATABASE_KEY",
                                          {user: "USERNAME",
                                           password: "PASSWORD" } );
    var stmt = conn.createStatement();
    // query for documents that has a WorkflowStage that lists the current user's email as the approver.
    // for each document found, we are getting the document name, the URL, the ID and the owner of the doc
    var query="SELECT r.DocumentName, r.DocumentUrl, r.Id, r.Owner FROM Approver a join WorkflowStage w on a.WorkflowStage_fk = w.Id join Request r on r.Id = w.Request_fk where a.Email = '"+email+"' and w.Status=1;";
    var result = stmt.execute(query);
    
    // now we want to display the results as Cards
    // loop through the query result and create a card for each Document found
    var cards = [];
    while(stmt.getResultSet().next()){
        cards.push(createApprovalCard(stmt.getResultSet().getString(3), stmt.getResultSet().getString(1), stmt.getResultSet().getString(4), stmt.getResultSet().getString(2)));
      
    }

    // close the connection
    stmt.close();
    conn.close();
    
    // if there's no results, return a message; else return the cards generated.
    return cards.length > 0 ? { 'cards': cards } : { "text" : "There is nothing waiting for your approval. Have a good day!" };
  }

  // if user didn't say "approval" our bot will return a message
  return {"text":"Sorry, I do not understand"};

}

You’ll notice there’s a createApprovalCard function used in the code above, which leads to the next step…

Step 5: Styling the Result

Copy the following function to your code – just ensure it’s somewhere above the OnMessage function. Replace LINK_TO_YOUR_APP with the URL to your deployed App Maker application.


function createApprovalCard(rId, docTitle,from, docLink){

  return {"header": {
    "title": docTitle,
    "subtitle": "from "+from  },
          "sections": [
            {
              "widgets": [
                {
                  "buttons": [
                    {
                      "textButton": {
                        "text": "VIEW DOCUMENT",
                        "onClick": {
                          "openLink": {
                            "url": docLink
                          }
                        }
                      }
                    },
                    {
                      "textButton": {
                        "text": "APPROVE",
                        "onClick": {
                          "openLink": {
                            "url": "LINK_TO_YOUR_APP?requestId="+rId+"#Approve/"
                          }
                        }
                      }
                    }
                  ]
                }
              ]
            }
          ]
         };
}

For each Document from the query, we use this function, which takes the Document ID, Document Title (Name), the Owner it is from, and the URL to the document and create the JSON to format them into a card that looks like this:

The title of the card is the Document Name, and the subtitle of the card is the Owner’s email.
The View Document button links directly to the Document to be approved. The Approve button links to the “My Approval” page on the App, which is part of the Document Approval template.

Step 6. Deploy the Bot!

Follow step 4 of the codelab to deploy your bot and try it out! You can say “List my approvals”, “See docs waiting for my approval” or something similar to see its response – it should list the documents you’ve created in Step 1.

Step 7. Experiment!

You can go farther with this bot if you want to. E.g. have the approval button actually approve the document by using Update queries, integrate Dialogflow so it’s not just looking for a simple “approval” word, or integrate with other apps (as shown in the Totally Unscripted video where I also incorporate the Project Tracker app).

A bot can be a great complimentary component/interface to your App Maker app, and as you can see, it is not incredibly difficult to create!

Leave a Reply