Introduction: Gform2adafruitIO: Automatic Marquee Driven by Google Form/Adafruit IO

By https://dargen.io/
About: Hello, my name is Michael. I am an electrical and computer engineer turned educator. I have a lot of experience in embedded software and hardware design. I am taking my technical experiences and leveraging the…

Python script to automatically publish data from Google Form to Adafruit IO.

This program provides a framework to easily link a Google Form to Adafruit IO feeds that can be used for any number of projects. Instead of allowing just anyone to access your feeds directly, this program acts as a middleware between a public Google Form and a private feed. Anyone can complete your Google Form. This program will periodically check the form for new submissions, vet the submission data to confirm the data is valid, then push the data to your Adafruit IO feed.

The example I give in this tutorial uses the Adafruit Matrix Portal to make a Marquee Sign. I came up with this idea as a way to allow my students to easily submit text to the Matrix Portal in the lab. The Google Form asks the user for text and a color. The program ensures the color is valid and the text is profanity-free (as profanity-free as possible). The program then publishes the data to Adafruit IO. The Matrix Portal pulls this information down from Adafruit IO to scroll across the display. This code can easily be adapted for any application.

Supplies

  • Raspberry Pi or Computer (Windows/Mac/Linux) with internet and Python to run script
  • Adafruit IO account (free)
  • Google Account (free)

Step 1: Setting Up Environment

Python 3 Installation

If you don't already have Python installed, you will need to install it. Go to https://www.python.org/downloads/ and download and run the correct installation for your operating system. You will need to make sure that you install pip and add python/pip to Path.

Git Project Files

Now you will need to retrieve Python script and other files from my Github repository. You can either install a git client and clone the repository or you can download a zip file of the repository from your browser.

https://github.com/mjdargen/gform2adafruitIO

git clone  https://github.com/mjdargen/gform2adafruitIO.git

Installing Python Packages

To run this program, you will need to install the following packages: requests, webcolors, adafruit-io, python-dotenv. These can be installed easily by navigating to the cloned repository directory and running the following command:

pip3 install -r requirements.txt

If you have issues with any of the packages, you can downgrade them to these versions (the most current at the time of publishing). These are the specific versions I tested: python == 3.7.7, requests == 2.24.0, webcolors == 1.11.1, adafruit-io == 2.4.0, python-dotenv == 0.14.0.

Step 2: Setting Up Google Form

Create Google Form/Google Sheet

First, you will need to create a Google Form. Open up Google Drive and create a new form. Here is a link to my sample form for this project: https://forms.gle/MUWWtGKMeH4RmXeq6.

You will need to make the Google Form automatically publish to a Google Sheet. To do so, click on the "Responses" tab of your Google Form and click the Sheets icon (shown in the image above). This will bring you to a linked spreadsheet. Any time someone submits a new form, it will automatically show up in the linked Google Sheet.

Publish Sheet to Python Script

Complete the following steps to publish the Google Sheet as a .csv so it can be retrieved by the Python Script.

Step 3: Adafruit IO Setup

https://io.adafruit.com/

Adafruit IO is an incredible cloud service that allows you to push and pull data back and forth from many different devices. In the example I provided, I pushed data from my Raspberry Pi to Adafruit IO and my Matrix Portal pulled the data back down from Adafruit IO. Adafruit IO also gives you the ability to create dashboards and connect to other webservices. For our purposes, we will be primarily concerned with sending and retrieving data from a private feed on Adafruit IO.

If you would like to implement the specific example of the Matrix Portal Marquee, follow this guide here: https://learn.adafruit.com/aio-quote-board-matrix-display/. On the "Code the Quote Board" page, it walks you through step-by-step how to create a feed for your sign.

In my case, I created two feeds: one for text (called signtext) and one for color (called signcolor). Once I created these 2 feeds, I assigned them to a group I made called "matrix portal quotes". You will need these names to refer to later, so keep them in mind!

--

If you would like to use gform2adafruitIO for a different project, there are many other helpful guides Adafruit has published about Adafruit IO that will help you with whatever no matter what your specific requirements. Here is a series of helpful Adafruit IO guides: https://learn.adafruit.com/series/adafruit-io-basics.

Step 4: Environment Variable Setup

Once you have setup your Adafruit IO account, you should have a username and a key. I will show you how to safely store these as an environment variable so you don't accidentally publish this private information to the web. This program uses the python-dotenv package to do this.

To set up the environment variables, create a file in the cloned repository's directory called ".env". Open this file in your favorite text editor and provide your username and key in the following format:

ADAFRUIT_IO_KEY=<put_your_adafruit_io_key_here>
ADAFRUIT_IO_USERNAME=<put_your_adafruit_io_username_here>

You're all done! The Python script will successfully import your information into the code. If you ever publish your code to GitHub, the default .gitignore file will automatically know to exclude your .env file so that your private key is not shared with the masses!

Step 5: Modifying the Code

Now, you will need to do some slight modifications to the code to make it work for your specific situation.

  1. Update the URL for your .csv file.
    • Modify the variable CSV_URL to point to the URL where your Google Sheet published the .csv file.
    • CSV_URL = "<your_URL_here>"
  2. Set the maximum number of quotes you want to store.
    • Modify the variable MAX_QUOTES to control the maximum number of quotes you want to store in your Adafruit IO Feed
    • MAX_QUOTES = <your_integer_value_here>
    • If you specify 10, it will keep the 10 most recent quotes/colors
  3. Set up the names for Adafruit IO feeds.
    • In my case, I had two feeds: one for text and one for color.
    • I grouped them together in the Adafruit IO portal.
    • When grouped, the name format for feeds is: "group_name.feed_name".
      • TEXT_FEED = 'matrix-portal-quotes.signtext'
      • COLOR_FEED = 'matrix-portal-quotes.signcolor'

If you are using this code for an entirely different project, you will also need to modify the processing() function. My processing function organizes the form data into a labeled dictionary structure. It then checks to make sure that the color is valid and that the text is free of profanity. You will need to change the processing() to suit whatever your needs are.

Step 6: Executing the Program

To view the source code, visit this Github repository.

Program Description

Here is a description of the functionality per function of the program:

  • fetch_form_data()
    • Uses requests library to download .csv file of Google Sheet.
    • Compares downloaded file with last retrieved file to see if there is new submission data.
    • If there is new data, to process it proceeds. Otherwise, the program ends.
  • processing()
    • Organizes form data into dictionary structure.
    • Calls color_check() to process color data and see if it is valid.
    • Calls profanity_check() to process text data and see if it is free of profanity.
  • adafruitIOaccess()
    • Connects with Adafruit IO using Rest API.
    • Pulls existing values from text & color feeds.
    • Adds the new values to those feeds.
    • Removes values from those feeds if they exceed the maximum limit of values.
  • update_files()
    • After successfully completing all other tasks, overwrites previous downloaded .csv with current downloaded .csv for next execution.

Here is a description of 2 different ways to run the code.

  • 1st if __name__ == "__main__": option:
    • Program executes exactly once then exists. Great for scheduling script to run.
    • My script is running on a Raspberry Pi and scheduled using cron. Use this tool to figure out scheduling.
    • Below I show how to add a task to cron to schedule the script to run every 30 minutes. 
    sudo crontab -e
*/30 * * * * /usr/bin/python3 /home/pi/Documents/gform2adafruitIO/gform2adafruitIO.py
  • 2nd if __name__ == "__main__": option:
    • Program executes repeatedly in a loop forever. There is a sleep interval between executions.
    • Set sleep interval (in seconds) by modifying this line: INTRVL = 1800
    • Below I show how to call the program.
    python3 gform2adafruitIO.py

Step 7: Adafruit Matrix Portal Info

If you want to run this specific Marquee Sign example with the Adafruit Matrix Portal, check out the links below. These guides will walk you through how to set up the Matrix Portal and how to set up the feeds in Adafruit IO.

Adafruit Matrix Portal Product Page: https://www.adafruit.com/product/4745

General Adafruit Matrix Portal Info: https://learn.adafruit.com/adafruit-matrixportal-m4

Custom Scrolling Quote Tutorial: https://learn.adafruit.com/aio-quote-board-matrix-display

Step 8: More Projects

For more projects, visit these links:

Step 9: Source Code:

To view the source code, visit this Github repository.