St. Nick and Krampus work as a team. Gifts for the nice kids, punishment for the naughty. It’s important that they coordinate their efforts. Can’t bring a kid the latest game console, only to be stuffed in a basket, dragged to the underworld and devoured later the same evening…that would just be super awkward, you know?
The two of them can collaborate remotely from their respective workshop or dank cave on a single spreadsheet that organizes kids into two groups: naughty and nice. St. Nick and Krampus each have their own separate MagTag board, which lists only the names relevant to their particular task.
You can test this out with just one MagTag…decide if you’re on Team Santa or Team Krampus.
Making the Spreadsheet
How should we structure this? The most important idea here is that there’s no One Right Way™ to do it. Find a layout that collaborators find easy to manage, then write the CircuitPython code to work with that layout.
We could make one column a list of names, and a second column with a “naughty” or “nice” string for each. But…typos in the second column might be a problem, and all those strings make the data bulkier than needed.
Or we could make one vertical list of names, separated by “naughty” or “nice” spreadsheet cells…the CircuitPython code could look for those strings and make the switch at that point.
Or…since a spreadsheet is two-dimensional…we could make two columns, one for “naughty” and another for “nice.” This is good and compact. We’ll use something very similar for the next example.
For this one though…we’ll start with the “naughty” and “nice” columns, and each row gets just one name, placed in one column or the other. A “sparse” spreadsheet.
A third column, “notes,” has been added. Maybe a few kids are right on the naughty/nice fence, and this way St. Nick and Krampus can leave notes to each other to justify why they chose one way or the other.
The notes are only used while working on the spreadsheet though. The CircuitPython code we’ll write in a moment ignores that column, each MagTag will only display its respective curated name list.
Remember, you can use most any layout you like, so long as the CircuitPython code is written to match. If following along with this example, use the sparse 3-column format above.
Publish the Spreadsheet
Follow the directions on the “Spreadsheet Setup” page to get the data ready for MagTag. Publish to web, then copy the document’s URL link. You only need to do this part once, any document changes will use the same link.
CircuitPython Code
As mentioned on the “Spreadsheet Parsing” page, the spreadsheet layout and our CircuitPython code need to work hand-in-hand.
Here’s the Naughty-or-Nice CircuitPython code (naughty_nice.py). The vital bits were already explained on the prior page, and much of it is recycled in the next example. So it’s like 90% boilerplate, but below the code a couple of “tricks” are explained.
Update TSV_URL to point to your published spreadsheet, and rename the file to “code.py” to run automatically on the CIRCUITPY drive.
Installing Project Code
To use with CircuitPython, you need to first install a few libraries, into the lib folder on your CIRCUITPY drive. Then you need to update code.py with the example script.
Thankfully, we can do this in one go. In the example below, click the Download Project Bundle button below to download the necessary libraries and the code.py file in a zip file. Extract the contents of the zip file, open the directory MagTag_Google_Sheets/naughty_nice/ and then click on the directory that matches the version of CircuitPython you're using and copy the contents of that directory to your CIRCUITPY drive.
Your CIRCUITPY drive should now look similar to the following image:
# SPDX-FileCopyrightText: 2020 Phillip Burgess for Adafruit Industries
#
# SPDX-License-Identifier: MIT
"""
Google Sheets to MagTag example: Naughty or Nice?
Gets tab-separated-value (TSV) spreadsheet from Google, displays names from
one column or other.
"Smart cursive" font by Thomas A. Fine, helvB12 from Xorg fonts.
"""
# pylint: disable=import-error, line-too-long
import time
import rtc
from adafruit_display_shapes.rect import Rect
from adafruit_magtag.magtag import MagTag
# CONFIGURABLE SETTINGS and ONE-TIME INITIALIZATION ------------------------
TSV_URL = 'https://docs.google.com/spreadsheets/d/e/2PACX-1vTA8pXQodbEiz5idGT21YkL1Vy8waW0aAHM1uX7D4TqBq6DrUU8qXVlON1QVaWSlmoC3OBL4Iokyiyy/pub?output=tsv'
NICE = True # Use 'True' for nice list, 'False' for naughty
TWELVE_HOUR = True # If set, show 12-hour vs 24-hour (e.g. 3:00 vs 15:00)
DD_MM = False # If set, show DD/MM instead of MM/DD dates
MAGTAG = MagTag(rotation=0) # Portrait (vertical) display
# SOME UTILITY FUNCTIONS ---------------------------------------------------
def hh_mm(time_struct, twelve_hour=True):
""" Given a time.struct_time, return a string as H:MM or HH:MM, either
12- or 24-hour style depending on twelve_hour flag.
"""
if twelve_hour:
if time_struct.tm_hour > 12:
hour_string = str(time_struct.tm_hour - 12) # 13-23 -> 1-11 (pm)
elif time_struct.tm_hour > 0:
hour_string = str(time_struct.tm_hour) # 1-12
else:
hour_string = '12' # 0 -> 12 (am)
else:
hour_string = '{hh:02d}'.format(hh=time_struct.tm_hour)
return hour_string + ':{mm:02d}'.format(mm=time_struct.tm_min)
# GRAPHICS INITIALIZATION --------------------------------------------------
MAGTAG.graphics.set_background('bitmaps/nice.bmp' if NICE else
'bitmaps/naughty.bmp')
# Add empty name list here in the drawing stack, names are added later
MAGTAG.add_text(
text_font='/fonts/cursive-smart.pcf',
text_position=(8, 40),
line_spacing=1.0,
text_anchor_point=(0, 0), # Top left
is_data=False, # Text will be set manually
)
# Add 14-pixel-tall black bar at bottom of display. It's a distinct layer
# (not just background) to appear on top of name list if it runs long.
MAGTAG.graphics.root_group.append(Rect(0, MAGTAG.graphics.display.height - 14,
MAGTAG.graphics.display.width,
MAGTAG.graphics.display.height, fill=0x0))
# Center white text label over black bar to show last update time
# (Initially a placeholder, string is not assigned to label until later)
MAGTAG.add_text(
text_font='/fonts/helvB12.pcf',
text_position=(MAGTAG.graphics.display.width // 2,
MAGTAG.graphics.display.height - 1),
text_color=0xFFFFFF,
text_anchor_point=(0.5, 1), # Center bottom
is_data=False, # Text will be set manually
)
# MAIN LOOP ----------------------------------------------------------------
# FYI: Not really a "loop" -- deep sleep makes the whole system restart on
# wake, this only needs to run once.
try:
MAGTAG.network.connect() # Do this last, as WiFi uses power
print('Updating time')
MAGTAG.get_local_time()
NOW = rtc.RTC().datetime
print(NOW)
print('Updating names')
RESPONSE = MAGTAG.network.fetch(TSV_URL)
if RESPONSE.status_code == 200:
TSV_DATA = RESPONSE.text
print('OK')
# Set the "Updated" date and time label
if DD_MM:
DATE = '%d/%d' % (NOW.tm_mday, NOW.tm_mon)
else:
DATE = '%d/%d' % (NOW.tm_mon, NOW.tm_mday)
MAGTAG.set_text('Updated %s %s' % (DATE, hh_mm(NOW, TWELVE_HOUR)), 1,
auto_refresh=False)
# Split text response into separate lines
LINES = TSV_DATA.split('\r\n')
# Scan cells in row #1 to find the column number for naughty vs nice.
# This allows the order of columns in the spreadsheet to be changed,
# though they still must have a "Naughty" or "Nice" heading at top.
cells = LINES[0].split("\t") # Tab-separated values
for column, entry in enumerate(cells):
head = entry.lower() # Case-insensitive compare
if ((NICE and head == 'nice') or (not NICE and head == 'naughty')):
NAME_COLUMN = column
# Now that we know which column number contains the names we want,
# a second pass is made through all the cells. Items where row > 1
# and column is equal to NAME_COLUMN are joined in a string.
NAME_LIST = '' # Clear name list
for line in LINES[1:]: # Skip first line -- naughty/nice/notes in sheet
cells = line.split("\t") # Tab-separated
if len(cells) >= NAME_COLUMN and cells[NAME_COLUMN] != "":
NAME_LIST += cells[NAME_COLUMN] + '\n' # Name + newline character
MAGTAG.set_text(NAME_LIST) # Update list on the display
time.sleep(2) # Allow refresh to finish before deep sleep
print('Zzzz time')
MAGTAG.exit_and_deep_sleep(24 * 60 * 60) # 24 hour deep sleep
except RuntimeError as error:
# If there's an error above, no harm, just try again in ~15 minutes.
# Usually it's a common network issue or time server hiccup.
print('Retrying in 15 min - ', error)
MAGTAG.exit_and_deep_sleep(15 * 60) # 15 minute deep sleep
Skateboard Tricks
A couple of things make this program more flexible. First, near the top of the code is this line:
NICE = True # Use 'True' for nice list, 'False' for naughty
This can be set to True or False depending on which list you want to see. The same program then gets installed on both MagTag boards…they don’t each require unique code.
Second…let’s take a look at that final spreadsheet layout again. Notice that each column has a heading (row #1), either “Naughty,” “Nice” or “Notes.”
Santa and Krampus are always butting heads over who’s more important. Sometimes, when the other isn’t looking, they’ll rearrange the columns to put their job first.
By examining those headings, the program’s output will always match the value of the NICE variable’s True or False setting, even if the columns get reordered!
This sort of thing is not required. If you know for certain that Naughty and Nice will always be in a fixed order, the code could be simpler. It’s just to show how things can be made more foolproof with just a few added lines.
Here’s the relevant part of the code, where you can see two passes being made through the LINES list. The first pass looks only at the column headings (row #1 in the spreadsheet, or LINES[0] in CircuitPython) for “nice” or “naughty” (using a case-insensitive compare, again for foolproofishness) that corresponds to the global NICE setting. Second pass collects all the names in that column who’s row number is greater than 1 (so the headings won’t appear in the list).
# Split text response into separate lines
LINES = TSV_DATA.split('\r\n')
# Scan cells in row #1 to find the column number for naughty vs nice.
# This allows the order of columns in the spreadsheet to be changed,
# though they still must have a "Naughty" or "Nice" heading at top.
cells = LINES[0].split("\t") # Tab-separated values
for column, entry in enumerate(cells):
head = entry.lower() # Case-insensitive compare
if ((NICE and head == 'nice') or (not NICE and head == 'naughty')):
NAME_COLUMN = column
# Now that we know which column number contains the names we want,
# a second pass is made through all the cells. Items where row > 1
# and column is equal to NAME_COLUMN are joined in a string.
NAME_LIST = '' # Clear name list
for line in LINES[1:]: # Skip first line -- naughty/nice/notes in sheet
cells = line.split("\t") # Tab-separated
if len(cells) >= NAME_COLUMN and cells[NAME_COLUMN] != "":
NAME_LIST += cells[NAME_COLUMN] + '\n' # Name + newline character
To avoid blank lines in the displayed name list, the code above intentionally checks for empty cells ("") and ignores them.
Page last edited January 21, 2025
Text editor powered by tinymce.
