Let’s consider a more practical example: a planner that shows your recurring to-dos for each day of the week.

The spreadsheet might look like the following, with one column for each day of the week, and a varying number of tasks down the rows for each day. The first row contains day names:

(European calendars usually show Monday as the first day of the week. That’s entirely possible here, with a corresponding change in the code…this is covered later.)

This has some things in common with the “Naughty or Nice” example:

  • We’ll display only the information from one column
  • The headers (row #1) are ignored

And some differences:

  • The columns are always going to be in this order, so there’s no need for a first pass to read the headings…just use the column number (1–7) corresponding to the current day of the week (Sunday–Saturday)
  • The column selection is based on the system clock (polled from an internet time server), not a global variable in the code

Make and Publish the Spreadsheet

With a Google Sheet resembling the above, 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 save the resulting combined link. You only need to do this part once, any document changes will use the same link.

CircuitPython Code

Here’s the CircuitPython code for the weekly planner (weekly_planner.py). Once again, the spreadsheet layout and our CircuitPython code are designed in concert. The vital bits were already explained on the “Parsing” page, and much of it is recycled from the prior example.

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: Weekly Planner.
Gets JSON spreadsheet from Google, displays task list from today's column.
This example does NOT deep sleep, a USB power connection is recommended.
Fonts from Xorg project.
"""

# 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/1vk6jE1-6CMV-hjDgBk-PuFLgG64YemyDoREhGrA6uGI/1/public/full?alt=json'
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
DAYS = ['Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday',
        'Saturday']

MAGTAG = MagTag(rotation=0) # Portrait (vertical) display
MAGTAG.network.connect()


# 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 --------------------------------------------------

# First text label (index 0) is day of week -- empty for now, is set later
MAGTAG.add_text(
    text_font='/fonts/helvB24.pcf',
    text_position=(MAGTAG.graphics.display.width // 2, 4),
    line_spacing=1.0,
    text_anchor_point=(0.5, 0), # Center top
    is_data=False,              # Text will be set manually
)

# Second (index 1) is task list -- again, empty on start, is set later
MAGTAG.add_text(
    text_font='/fonts/ncenR14.pcf',
    text_position=(3, 36),
    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 task 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 (index 2) over black bar to show last update time
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 ----------------------------------------------------------------

PRIOR_LIST = '' # Initialize these to nonsense values
PRIOR_DAY = -1  # so the list or day change always triggers on first pass

while True:
    try:
        print('Updating time')
        MAGTAG.get_local_time()
        NOW = rtc.RTC().datetime

        print('Updating tasks')
        RESPONSE = MAGTAG.network.fetch(JSON_URL)
        if RESPONSE.status_code == 200:
            JSON_DATA = RESPONSE.json()
            print('OK')

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

        # tm_wday uses 0-6 for Mon-Sun, we want 1-7 for Sun-Sat
        COLUMN = (NOW.tm_wday + 1) % 7 + 1

        TASK_LIST = '' # Clear task list string
        for entry in ENTRIES:
            cell = entry['gs$cell']
            if int(cell['row']) > 1 and int(cell['col']) is COLUMN:
                TASK_LIST += cell['$t'] + '\n' # Task + newline character

        # Refreshing the display is jarring, so only do it if the task list
        # or day has changed. This requires preserving state between passes,
        # and is why this code doesn't deep sleep (which is like a reset).
        if TASK_LIST != PRIOR_LIST or PRIOR_DAY != NOW.tm_wday:

            # Set the day-of-week label at top
            MAGTAG.set_text(DAYS[COLUMN - 1], auto_refresh=False)

            # 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)),
                            2, auto_refresh=False)

            MAGTAG.set_text(TASK_LIST, 1) # Update list, refresh display
            PRIOR_LIST = TASK_LIST        # Save list state for next pass
            PRIOR_DAY = NOW.tm_wday       # Save day-of-week for next pass

    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)

    time.sleep(15 * 60) # Whether OK or error, wait 15 mins for next pass

Skateboard Tricks

Unlike the previous example which uses deep sleep (waking once a day), this one requires continuous USB power because it’s frequently checking the spreadsheet for changes, about four times an hour…a battery wouldn’t last a day with that much WiFi access going on. Because e-ink screen updates can be rather jarring, the code only refreshes the display if it detects a change in the list or the day of the week. The “list” is really just a long string, maybe a couple hundred bytes tops, so it’s not unreasonable to keep that in memory, and is easy to compare.

Earlier it was mentioned how European calendars start their week on Monday. Time functions in Python also start the week on Monday, but because this code is US-centric, there’s a weird line of code to map from Python day-of-week (starting at 0 for Monday) to a spreadsheet column number (starting at 1 for Sunday):

DAYS = ['Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday']

# tm_wday uses 0-6 for Mon-Sun, we want 1-7 for Sun-Sat
COLUMN = (NOW.tm_wday + 1) % 7 + 1

# Set the day-of-week label at top
MAGTAG.set_text(DAYS[COLUMN - 1], auto_refresh=False)

It looks odd, adding 1 to COLUMN only to subtract 1 on the next line, but that’s because this operation is only used once, whereas the subsequent scan for cells matching COLUMN makes this comparison many times over, so we optimize for that case.

For a Euro calendar layout (with matching spreadsheet), this could be:

DAYS = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday']

COLUMN = NOW.tm_wday + 1

MAGTAG.set_text(DAYS[NOW.tm_wday], auto_refresh=False)

Strictly speaking, it would be “more efficient” to just always use the Euro format. But from an overall design standpoint, I’d say “better” to use a layout that the end user is more familiar with, to avoid entering things in the wrong column. A more complete version of this code might use a variable to select between the two formats, but then this starts to turn into a rant about user-centered design when the goal here is just a lightweight introduction Google Sheets and CircuitPython.

The example code uses a fixed column number for each day rather than scanning the headers for a match (as in the prior example). Partly for simplicity, but also because “Wednesday” is often misspelled and might not be found in a search through the headers.

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

This page (Example: Weekly Planner) was last updated on Apr 17, 2021.

Text editor powered by tinymce.