Using Messenger as Google sheets interface using Python Flask

Dealing with google sheets is an essential skill not everyone is comfortable with. At Ebtsama, we are all volunteer-based students, so asking volunteers to learn such skill was not really practical although we needed to keep track of distributed tasks and mentor its progress. so we decided to use the most familiar interface to everyone, Messenger, to do that.

To give you an overview of what this system does, the task is to have around 20 students retrieve borrowed books and report that to a central sheet that is mentored by administrators.

In order to do that, you need to build a webhook, which is simply a piece of software that lives on a web server that receives and responds to requests from Messenger API. we will use Python Flask to do that. then, the webhook will forward the information received by Messenger to the Google Sheets API to record it. As simple as that!

let’s start by building the webhook:

in order to do that you’ll need to install Flask, pymessenger. it’s recommended to do that in a virtual env, use anyone you want. I use conda

pip3 install Flask
pip3 install pymessenger

Now you need to build the web app that will process requests from messenger and to sheets. you just need to know the minimal code to start a web app with flask. in a python file called app.py type the following:

from flask import Flask, request
app = Flask(__name__)
@app.route('/', methods=['GET', 'POST'])
def receive_message():
    return "Hello World!"

if __name__ == '__main__':
    app.run()

To test it run the development server by navigating to the project in your terminal and running

python3 app.py

before we implement the logic to forward the messages from Messenger to Sheets API they both require authentication code to make sure they are talking to the right endpoint, not someone who’s pretending to be you

to authenticate a webhook by Messenger, it will send a GET request to the webhook the contains a “verify_token” and a “challenge”. the webhook needs to make sure that the “verify_token” is correct and respond with the “challenge”. Now, let’s implement the messenger authentication logic in the webhook.

edit app.py to the following:

VERIFY_TOKEN = 'access_token'
@app.route("/", methods=['GET', 'POST'])
def receive_message():
    if request.method == 'GET':
        """Before allowing people to message your bot, Facebook has implemented a verify token
        that confirms all requests that your bot receives came from Facebook."""
        sent_verify_token = request.args.get("hub.verify_token")
        if sent_verify_token == VERIFY_TOKEN:
            return request.args.get("hub.challenge")

feel free to change the access token to a more random and secure string

Now we need to create a Facebook page and an app to link it to the webhook:

On your Facebook main page press the + and choose ‘Page’

now navigate to Facebook developers, click on my apps in the top right corner then create an app and finish the process of creating the app

in the products section of your new app activate the webhook and messenger APIs.

now the app is ready to connect to the webhook app on your local computer. but first, we need your computer to act as a web server for testing purposes. for that, you will need to download and install ngrok. it’s super easy you just download a zip file, unzip it and run it.

Ready to connect your webhook to the app? let’s do it:

first, you need to restart your Flask development server to run the new code. in your terminal press ctrl+c and run python3 app.py again.

Second, you need to make ngrok listen to HTTP requests on the same port that your web app is listening to. If your flask server is running on port 5000 then you need to run

ngrok http 5000

now the web app is ready to receive requests from the internet

navigate back to Facebook developers, choose messenger from the sidebar, and scroll down to webhooks section, click on add callback URL

from your ngrok terminal copy the URL starts with https:// ,it’s usually the last one, and past in the URL box. also, copy your access_token from app.py and past it in the access token box

Note: if you get bad gateway response 502 that’s because you’re not running the flask development server

if everything is ok you should be able to verify successfully. Congrats, you’re almost there.

While you’re still on Facebook developers, on the same page, scroll up to the access token section, link the page you created to your app. after that you should be able to generate an access token. generate one, copy it, and save it in a global variable in app.py

...
ACCESS_TOKEN = 'EAAbUj4ZBUjyQBAEABelhIxcl4lr3IgQVhbAzlGko1zGF'
VERIFY_TOKEN = 'access_token'
...

we will use the access token to send responses back to our app or bot

Now you need to authorize sheets API, you can find instructions in the quick start guide.

And that’s about it. now you when you send a messenger message it will be forwarded to the webhook and then the webhook will forward it to google sheets.

you can access the full code here