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, then used a link to access a “wall of text.”

It’s really all the spreadsheet data in a format called JSON (JavaScript Object Notation) — a standardized way for programs to exchange data using plain text. It’s not meant to be human-readable, but good ol’ text moves fairly reliably among different systems.

JSON data is often structured into hierarchy and lists, using matched pairs of braces {} and brackets []. As it’s designed for computers, not people, all the text is smooshed together and the structure isn’t apparent. Thankfully there exist JSON viewers that reinterpret the data into a structure we can see.

A web search for “JSON viewer” turns up many options, with both standalone programs and web forms as options.

Here’s a nice one on the Code Beautify web site (opens in new window).

Use the “Load URL” button and paste the link to your wall-of-text…or just copy-and-paste the text itself into the left pane. Then click the “Tree Viewer” button. The data hierarchy is now shown in the right pane, in collapsible sections. Click the triangle next to “feed” and “entry” and you’ll start working your way down into the individual spreadsheet cells.

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 JSON data requires:

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

JSON_URL = 'https://YOUR_PUBLISHED_SHEET_LINK'

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

RESPONSE = MAGTAG.network.fetch(JSON_URL)
if RESPONSE.status_code == 200:
    JSON_DATA = RESPONSE.json()

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 JSON 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.

Using the JSON Viewer and unfolding the relevant sections, you’ll find a list of spreadsheet cells in

object→feed→entry

“object” there is the same as our JSON_DATA dictionary, so getting to the list of cells in CircuitPython is:

ENTRIES = JSON_DATA['feed']['entry']

You can then iterate through this list…there’s a lot of data to pick through, Google’s JSON is awfully verbose…the most useful information for each entry is usually in a sub-item called 'gs$cell'. This itself is a Python dictionary, and the keys 'row', 'col' and '$t' usually accompany data you’ll be interested in.

One might iterate through the ENTRIES list like so:

for entry in ENTRIES:
    cell = entry['gs$cell']
    row = int(cell['row'])
    col = int(cell['col'])
    value = cell['$t']

The examples on following pages show different things that might be done with this information.

Also helpful to know:

  • Row and column start from 1 (not 0) at the top left.
  • Empty cells are not present in the JSON! This is why row and column are useful — the list may be disjoint, you can’t assume anything’s in a particular order.
  • 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 dictionary keys by the wrong names, things like that.

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 Oct 23, 2021.

Text editor powered by tinymce.