Google Apps Script endpoint for Hangouts Chat bot using Sheets as a message queue
This project is primarily geared towards use with PSGSuite and PoshBot in PowerShell to create a bot framework without incurring the extra costs associated with setting up and maintaining a public API endpoint or a Cloud Pub/Sub subscription.
If you have a different bot framework you'd like to use, this will work fine with it as well, assuming your chosen framework can interact with Google Sheets. Here's a quick overview of the message processing flow once setup:
- User sends a DM to the bot or tags the bot in a Room in Google Chat
- Google Apps Script on the Sheet backend adds the following details to the Sheet:
ID
: Tracking ID specific to the queue.Event
: This is the actual JSON payload of the event. This includes the full details of the message, including the message text, the Space where the message was sent and who sent itAcked
: When new messages are received, they are inserted as new rows on the sheet with this value set toNo
. During the ReceiveMessage job on the bot framework, it should iterate through the rows one by one and update this cell's value toYes
once acknowledged. Any rows marked as Acked will be removed from the Queue Sheet immediately.Method
: This is the method that was called on Google Apps Script based on the message type. Current supported events are:onMessage
: Normal messageonAddToSpace
: New DM opened or Bot added to roomonRemoveFromSpace
: DM closed or Bot removed from roomonCardClick
: User clicked a button, image, etc on the card.
Bots frameworks integrating with this setup should follow the following workflow:
RecieveMessage
Job polls the following Range on the Sheet for Data to get the top event row in queue:Queue!A1:D2
- Polling frequency needs be no more frequent than 1 second, otherwise you risk hitting the default rate limit for Sheets Reads
- If a new event is found, the
ReceiveMessage
Job sends the event to the output stream and marks the row as Acked by updating cellC2
with a value ofYes
CommandParser
parses the messages sent in the output stream from theReceiveMessage
job and invokes them- Invocation results are sent back to Google Chat via
SendMessage
job
Here's how to set up Sheets MQ with your own account.
- 1. Add Sheet & Google Apps Script template to your account
- 2. Add the
cleanupSheet
trigger to the Apps Script project to run when the on Sheet change - 3. Enable the Sheets API for the Apps Script project
- 4. Run the
cleanupSheet
function to prepare the Sheet - 5. Enable and Configure the Hangouts Chat API
- 6. Deploy the Apps Script project from the manifest and copy the deployment ID
- 7. Finalize Hangouts Chat configuration
- 8. Validating Configuration
You have 2 options for adding the Sheet and Google Apps Script to your account:
- [Preferred / Easiest] Make a copy of this Sheet using the following link: Click here to make a copy of the template Sheet
- [Harder / Necessary if your G Suite domain has external sharing disabled] Create a new Sheet and copy the Google Apps Script to the it.
If you are copying the Sheet using the link, proceed to the next section: 2. Add the cleanupSheet
trigger to the Apps Script project
If you need or want to do option 2, you'll need to do the following:
- Create a new Google Sheet and open it in a new tab (if not already open).
- In the menu bar on top of the Sheet, select
Tools > Script Editor
to open the Script Editor. - Clear the default code in
Code.gs
. - Copy the raw contents of the
Code.js
file in this repo and paste it in the Script Editor pane on theCode.gs
file. - In the Script Editor's menu, select
View > Show project manifest
to show the project manifest in your file list. You should see a new file namedappsscript.json
in your Script Editor file list after. - Open the
appsscript.json
file in the Script Editor. - Copy the raw contents of the
appsscript.json
file in this repo and paste it in the Script Editor pane on theappsscript.json
file. - Save both the
Code.gs
andappsscript.json
files in the Script Editor. - Update the Apps Script project name to something meaninful/useful (i.e.
Google Chat Bot - Sheets MQ
), as this will be the name displayed to users in the event that authorization is needed.
If you copied the Sheet using the template link during the previous step, open the Script Editor by selecting Tools > Script Editor
from the menu bar on top of the Sheet.
With the Script Editor now open, you'll need to add 1 trigger to the project so that Sheet cleanup is automated:
- In the Script Editor's menu, select
Edit > Current project's triggers
. - Click the blue link
No triggers set up. Click here to add one now.
to add a new trigger. - Choose the following options to build the trigger as needed:
- Run:
cleanupSheet
- Events:
From spreadsheet
On change
- Run:
- Click the
Save
button to save the trigger. - You will receive a pop-up advising
Authorization required
; click theReview Permissions
button to continue.- This is to allow Apps Script to update
- Choose your Google account that owns the Sheet.
- Click the
Allow
button to allow this Apps Script project to manage the Message Queue Sheet for you.
Once you have the Sheet and Apps Script project code set up, you'll need to enable the Sheets API on the Apps Script project from the Google API Console:
- In the Script Editor's menu, select
Resources > Advanced Google services
. If theappsscript.json
file was updated correctly, you should only see the Google Sheets API switchedon
in the list. - On the bottom of the Advanced Google Services pop-up, click the blue link to go to the
Google API Console
for this project. This should take you to the API Dashboard for this Apps Script project. - At the top of the Dashboard, click the blue link to
Enable APIs and Services
- Search for
Sheets
and click theGoogle Sheets API
to open it. - Click the blue
Enable
button to enable the Sheets API for your Apps Script project. You should be taken back to the API Dashboard once enabled. - You can close this tab and ignore the "you may need credentials" warning on top of the page as they are not needed for this project.
This step will ensure that your Sheet is ready to start acting as your Chat Bot Message Queue. If you copied the Sheet using the template link during Step 1, you can skip to Step 5.
- Open the Script Editor from the Sheet.
- In the Script Editor's menu, select
Run > Run function > cleanupSheet
.
In order to have your bot send messages and events to Sheets MQ via Apps Script, you need to enable and configure the Hangouts Chat API in the Developer's Console in the project that your bot framework's service account is in. If you are using PSGSuite, for example, this would be the project where you created the P12 Key and Service Account that PSGSuite is configured with.
- Open the Developer's Console.
- Select the blue link to
Enable APIs and Services
. - Search for
Hangouts Chat API
, then click the Hangouts Chat API from the search results to open. - Click the blue
Enable
button to enable the API for your project. - You should now be taken to the dashboard with the Hangouts Chat API focused.
- If you have the Hangouts Chat API dashboard open, click the
Configuration
tab on the left, otherwise open the API Dashboard for your project and click onHangouts Chat API
from the list. - Enter a name for your bot in the
Bot name
field. - Enter a URL for your bot's avatar. I use this one personally: http://helpdev.com.br/wp-content/uploads/2016/11/gson.png
- Enter a description for your bot.
- Check the boxes under
Functionality
where you want your bot to be available:- Bot works in rooms
- Bot works in direct messages
- Select "Apps Script Project" from
Connection Settings
Stop here, open a new tab and navigate back to the Script Editor from the Google Sheet.
This is necessary to create the
Deployment ID
needed to use as the Apps Script endpoint when configuring the Hangouts Chat API later on. You cannot use theLatest Version (Head)
deployment ID for this and the Hangouts Chat API configuration will return an error if you try to do so.
To deploy from manifest, open the Script Editor and...
- Open the
Code.gs
file. - Select
Publish > Deploy from manifest...
from the Script Editor menu. - Click the red
Create
button. - Enter a useful name in the
Deployment name
field. If you'd like to enter a description for the deployment version, you can do so but it is not necessary. - Click the blue
Save
button to deploy your project. You will be returned to the Deployments list. - Next to your new deployment, click the
Get ID
link. - Copy the full
Deployment ID
shown to your clipboard and switch back to the Hangouts Chat API configuration page in the Developer's Console.
Now that you have your Apps Script deployed from the manifest and the Deployment ID
on your clipboard, you can finalize the Hangouts Chat API configuration:
- Under
Connection settings
, select the radio button next toApps Script project
- Paste your
Deployment ID
in the field below the selection. - Choose
Who can install
according to your preferences. This will determine who can add the bot to Spaces within Google Chat or send it Direct Messages. You can limit it to yourself for testing. - Click the blue
Save changes
button.
You will then be able to choose your bot status from the following options: * LIVE - available to all users (recommended/default) * DISABLED
This setting is restricted by the Who can Install
permissions.
To validate that everything is configured correctly, you can do the following:
- Open a Direct Message with your new bot in Hangouts Chat.
- Send a message.
- Verify a response has been recorded in the
Google Apps Script Sheet MQ
If no response was recorded verify that the correct Deployment ID
has been associated with your bot during step 7, and that the Google Sheets API is enabled in your project from step 3.
If you are a PSGSuite user and are working on configuring the PoshBot.GChat.Backend
, follow this link to view the documentation on getting PoshBot set up with Google Chat: