On this page, we’ll show some fragments of CircuitPython code, but these are not yet complete programs. We’ll bring all the pieces together on the example pages!

On the previous page we created a spreadsheet, published it to the web in .tsv format, and created a link so others (or our MagTag) can access the data.

TSV stands for Tab-Separated Values, a simple and fairly standard way for programs to exchange spreadsheet data using plain text. If you’ve done much programming, you may have encountered .CSV or Comma-Separated Value files. Nearly identical, just using a different separator…commas are easier for human-edited files, while tab separators make it easier for code when spreadsheet cells themselves may contain comma or quote characters.

You’ll only need to go through this process once, when designing your CircuitPython code, not every time you edit the spreadsheet data. The exception is if you restructure the spreadsheet, changing the fundamental data organization, in which case you’ll need to revisit these steps and think about your code.

For brevity’s sake, the code fragments that follow assume…

  • Your MagTag board is already running CircuitPython (explained on the “Install CircuitPython” page)
  • The secrets.py file is configured with your WiFi network credentials (explained on the “CircuitPython Internet Test” page)
  • Any additional libraries required by the code are installed and imported (below we show just the minimum, but the examples contain complete programs)

CircuitPython code to access the Google .TSV data requires:

  • A link to the published spreadsheet (TSV_URL in the code fragment below, but change the string to your published Sheet link URL)
  • Instantiating a MagTag object (MAGTAG below)
  • Connecting to the wireless network
  • Calling MAGTAG.network.fetch(TSV_URL) and verifying the response. If successful (status code 200), TSV_DATA is then a Python string containing the whole spreadsheet structure:
from adafruit_magtag.magtag import MagTag

TSV_URL = 'https://YOUR_PUBLISHED_SHEET_LINK'

MAGTAG = MagTag()
MAGTAG.network.connect()

RESPONSE = MAGTAG.network.fetch(TSV_URL)
if RESPONSE.status_code == 200:
    TSV_DATA = RESPONSE.text

This is a bit different from some other MagTag (or PyPortal or MatrixPortal) projects, where the URL is passed to the MagTag() constructor and fetch() simply returns one or more values — a stock price, a temperature, a date and time. That works when the source data is in known fixed format…but here we may need to sift through an indeterminate number of cells in our spreadsheet. We need it all.

The spreadsheet data arrives as one huge string, but this is easily split into multiple separate lines, one per row of the original spreadsheet, with Python’s built-in split() function, passing '\r\n' as a separator (carriage return + line feed).

# Split text response into separate lines
LINES = TSV_DATA.split('\r\n')

LINES[0] is now the first row of data, LINES[1] the second, and so forth. You can iterate through these like any Python list.

Each line is a string. These in turn can be broken out into individual cells, one per column, using split() again, with a tab character ('\t') this time:

for line in LINES:
    cells = line.split('\t')

It is up to you then to pick through rows and columns to extract the data you need and display it as you want. The examples on the following pages show different things that might be done with this information.

Also helpful to know:

  • When splitting into rows or columns, the resulting lists are indexed from 0 in CircuitPython…different from the original spreadsheet, where rows start with 1 and columns with A.
  • Empty cells will return an empty string (""). Your code may need to watch for this and act accordingly to avoid errors.
  • The examples use try/except to catch errors. That’s a good and neighborly thing to have in a finished program. During development though, you might want to just let the code throw errors, in case you try accessing items out of range, etc.

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

This page (Spreadsheet Parsing in CircuitPython) was last updated on Jun 29, 2022.

Text editor powered by tinymce.