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 though, 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, piece together a link from the template and your document’s URL, and then copy the resulting combined 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 JSON_URL to point to your published spreadsheet, and rename the file to “code.py” to run automatically on the CIRCUITPY drive.

If you're having difficulty running this example, it could be because your MagTag CircuitPython firmware or library needs to be upgraded! Please be sure to follow https://learn.adafruit.com/adafruit-magtag/circuitpython to install the latest CircuitPython firmware and then also replace/update ALL the MagTag-specific libraries mentioned here https://learn.adafruit.com/adafruit-magtag/circuitpython-libraries-2
"""
Google Sheets to MagTag example: Naughty or Nice?
Gets JSON 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 ------------------------

JSON_URL = 'https://spreadsheets.google.com/feeds/cells/1Tk943egFNDV7TmXGL_VspYyWKELeJO8gguAmNSgLDbk/1/public/full?alt=json'
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.splash.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(JSON_URL)
    if RESPONSE.status_code == 200:
        JSON_DATA = RESPONSE.json()
        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)

    ENTRIES = JSON_DATA['feed']['entry'] # List of cell data

    # 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.
    for entry in ENTRIES:
        cell = entry['gs$cell']
        if int(cell['row']) == 1:     # Only look at top row
            head = cell['$t'].lower() # Case-insensitive compare
            if ((NICE and head == 'nice') or (not NICE and head == 'naughty')):
                NAME_COLUMN = int(cell['col'])

    # 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 entry in ENTRIES:
        cell = entry['gs$cell']
        if int(cell['row']) > 1 and int(cell['col']) is NAME_COLUMN:
            NAME_LIST += cell['$t'] + '\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 ENTRIES list. The first pass looks only at the column headings (row #1) 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).

ENTRIES = JSON_DATA['feed']['entry'] # List of cell data

# 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.
for entry in ENTRIES:
    cell = entry['gs$cell']
    if int(cell['row']) is 1:     # Only look at top row
        head = cell['$t'].lower() # Case-insensitive compare
        if ((NICE and head == 'nice') or (not NICE and head == 'naughty')):
            NAME_COLUMN = int(cell['col'])

# 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 entry in ENTRIES:
    cell = entry['gs$cell']
    if int(cell['row']) > 1 and int(cell['col']) is NAME_COLUMN:
        NAME_LIST += cell['$t'] + '\n' # Name + newline character

This guide was first published on Dec 14, 2020. It was last updated on Dec 14, 2020.

This page (Example: Naughty or Nice?) was last updated on Oct 15, 2021.

Text editor powered by tinymce.