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)
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 FilesNow 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.gitTo 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.txtIf 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 FormCreate Google Form/Google SheetFirst, 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 ScriptComplete the following steps to publish the Google Sheet as a.csv so it can be retrieved by the Python Script.
- Go to "File -> Publish to the web".
- This will bring up the window shown above. Select the "Link" tab.
- Select the sheet that you want to publish: "Form Responses 1".
- Then select to publish it as "Comma-separated values (.csv)".
- Once you are ready, click Publish. A pop-up will appear asking if you are sure. Click "OK".
- The window will now show the link to the.csv file.
- You can open this link in a browser and it will download the.csv file so you can take a look.
- Here is the.csv published by my sample form: https://docs.google.com/spreadsheets/d/e/2PACX-1vQ2AwUbx6lsZK-H0WjvcF1Bu2VUlsN4ir8kMD10xSEkl-JkxXKlqLZfnJ5pgyNhYIDYMEOK6Ys4cEYK/pub?gid=1198589603&single=true&output=csv
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 SetupOnce 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 CodeNow, you will need to do some slight modifications to the code to make it work for your specific situation.
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>"
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
Set up the names for Adafruit IO feeds.When grouped, the name format for feeds is: "group_name.feed_name".
- TEXT_FEED = 'matrix-portal-quotes.signtext'
- COLOR_FEED = 'matrix-portal-quotes.signcolor'
- In my case, I had two feeds: one for text and one for color.
- I grouped them together in the Adafruit IO portal.
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 ProgramTo view the source code, visit this Github repository.
Program DescriptionHere 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.py2nd 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.pyIf 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 ProjectsFor more projects, visit my pages:
Comments