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, copy the document’s URL, and then paste this link into the code. 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 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/weekly_planner/ 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: Weekly Planner. Gets tab-separated-value (TSV) 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 ------------------------ TSV_URL = 'https://docs.google.com/spreadsheets/d/e/2PACX-1vR1WjUKz35-ek6SiR5droDfvPp51MTds4wUs57vEZNh2uDfihSTPhTaiiRovLbNe1mkeRgurppRJ_Zy/pub?output=tsv' 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(TSV_URL) if RESPONSE.status_code == 200: TSV_DATA = RESPONSE.text print('OK') # Split text response into separate lines LINES = TSV_DATA.split('\r\n') # 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 line in LINES[1:]: # Skip first line -- days of week in sheet cells = line.split("\t") # Tab-separated! if len(cells) >= COLUMN: TASK_LIST += cells[COLUMN - 1] + '\n' # 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.
Page last edited January 22, 2025
Text editor powered by tinymce.