# MagTag Lists From Google Spreadsheets
## Overview
**Cloud-connected** , programmable devices like Adafruit’s _ **MagTag** _ make it easy to pull live data from internet sources: [tides](https://learn.adafruit.com/magtag-tides-viewer), [space launches](https://learn.adafruit.com/spacex-next-launch-display-with-adafruit-magtag), [transit schedules](https://learn.adafruit.com/nextbus-transit-predictions-for-adafruit-magtag) and more.
Getting _your own stuff_ out there isn’t always so easy though. This often involves server hosting, writing web applications…generally a whole extra layer of knowledge, resources and patience that most of us don’t have.
Why reinvent the wheel? We’ve learned a pretty easy way to do this using [_ **Google Sheets** _](https://docs.google.com/spreadsheets) — a free, web-based spreadsheet platform. You might already have an account.
One can create and edit lists and reminders easily using a web browser or mobile app…then, combined with some CircuitPython programming, have this information displayed on MagTag. Google Sheets allows multiple people to _collaborate_ on the same document. Or, share just the data feed while the original document is off-limits to others. We’ll demonstrate a couple simple examples in this guide…but if you really get to know your way around Google Sheets, it’s possible to have it (and thus MagTag) showing dynamic data like stock quotes or days-remaining counters. Some potent magic!
## Parts Required
The MagTag starter kit includes an e-ink development board, LiPoly battery and magnetic feet…bring-your-own USB type A to type C cable. Or the individual pieces can be rounded up separately…
### Adafruit MagTag Starter Kit - ADABOX017 Essentials
[Adafruit MagTag Starter Kit - ADABOX017 Essentials](https://www.adafruit.com/product/4819)
The **Adafruit MagTag** combines the new ESP32-S2 wireless module and a 2.9" grayscale E-Ink display to make a low-power IoT display that can show data on its screen even when power is removed! The ESP32-S2 is great because it builds on the years of code and support for the...
Out of Stock
[Buy Now](https://www.adafruit.com/product/4819)
[Related Guides to the Product](https://learn.adafruit.com/products/4819/guides)
### Adafruit MagTag - 2.9" Grayscale E-Ink WiFi Display
[Adafruit MagTag - 2.9" Grayscale E-Ink WiFi Display](https://www.adafruit.com/product/4800)
The Adafruit MagTag combines the ESP32-S2 wireless module and a 2.9" grayscale E-Ink display to make a low-power IoT display that can show data on its screen even when power is removed! The ESP32-S2 is great because it builds on the years of code and support for the ESP32 and also adds...
In Stock
[Buy Now](https://www.adafruit.com/product/4800)
[Related Guides to the Product](https://learn.adafruit.com/products/4800/guides)
### USB Type A to Type C Cable - approx 1 meter / 3 ft long
[USB Type A to Type C Cable - approx 1 meter / 3 ft long](https://www.adafruit.com/product/4474)
As technology changes and adapts, so does Adafruit. This **USB Type A to Type C** cable will help you with the transition to USB C, even if you're still totin' around a USB Type A hub, computer or laptop.
USB C is the latest industry-standard connector for...
In Stock
[Buy Now](https://www.adafruit.com/product/4474)
[Related Guides to the Product](https://learn.adafruit.com/products/4474/guides)
### Lithium Ion Polymer Battery with Short Cable - 3.7V 420mAh
[Lithium Ion Polymer Battery with Short Cable - 3.7V 420mAh](https://www.adafruit.com/product/4236)
Lithium-ion polymer (also known as 'lipo' or 'lipoly') batteries are thin, light, and powerful. The output ranges from 4.2V when completely charged to 3.7V. This battery has a capacity of 420mAh for a total of about 1.55 Wh. If you need a larger (or smaller!) battery,
In Stock
[Buy Now](https://www.adafruit.com/product/4236)
[Related Guides to the Product](https://learn.adafruit.com/products/4236/guides)
### Also needed:
- **WiFi network** (802.11 b/g/n)
- A desktop or laptop **computer** is required for initial setup: any **text editor** will suffice
- A **Google account** to create and collaborate on cloud-based spreadsheets.
In the next section—specifically the “CircuitPython Internet Test” page—you’ll create a **_secrets_ file** to access your wireless network. This is a necessary step, don’t just skip ahead. If you’ve done some WiFi-connected MagTag projects before, you probably already have this file.
# MagTag Lists From Google Spreadsheets
## Install CircuitPython
Danger: Make sure that you [update the TinyUF2 Bootloader](https://learn.adafruit.com/adafruit-magtag/update-tinyuf2-bootloader-for-circuitpython-10-4mb-boards-only) before installing CircuitPython!
### Adafruit MagTag - Update TinyUF2 Bootloader for CircuitPython 10 and Later
[Adafruit MagTag](https://learn.adafruit.com/adafruit-magtag)
[Update TinyUF2 Bootloader for CircuitPython 10 and Later](https://learn.adafruit.com/adafruit-magtag/update-tinyuf2-bootloader-for-circuitpython-10-4mb-boards-only)
[CircuitPython](https://github.com/adafruit/circuitpython) is a derivative of [MicroPython](https://micropython.org) designed to simplify experimentation and education on low-cost microcontrollers. It makes it easier than ever to get prototyping by requiring no upfront desktop software downloads. Simply copy and edit files on the **CIRCUITPY** drive to iterate.
## Set Up CircuitPython
Follow the steps to get CircuitPython installed on your MagTag.
[CircuitPython Download for MagTag](https://circuitpython.org/board/adafruit_magtag_2.9_grayscale/)
Warning: WARNING: The updated Adafruit MagTag 2025 Edition will not work with CircuitPython 9.2.x or earlier. Make sure you install 10.x.x or later!
**Click the link above and download the latest .BIN and .UF2 file**
You can use a 9.x.x release for a pre-2025 MagTag. You **must** use a 10.x.x release for the updated MagTag 2025 Edition.
(depending on how you program the ESP32S2 board you may need one or the other, might as well get both)
Download and save it to your desktop (or wherever is handy).
Plug your MagTag into your computer using a known-good USB cable.
**A lot of people end up using charge-only USB cables and it is very frustrating! So make sure you have a USB cable you know is good for data sync.**
# Option 1 - Load with UF2 Bootloader
This is by far the easiest way to load CircuitPython. **However it requires your board has the UF2 bootloader installed. Some early boards do not (we hadn't written UF2 yet!) - in which case you can load using the built in ROM bootloader.**
Still, try this first!
Warning: Make sure that you [update the TinyUF2 Bootloader](https://learn.adafruit.com/adafruit-magtag/update-tinyuf2-bootloader-for-circuitpython-10-4mb-boards-only) before following these steps for the UF2 bootloader!
## Try Launching UF2 Bootloader
Loading CircuitPython by drag-n-drop UF2 bootloader is the easier way and we recommend it. If you have a MagTag where the front of the board is black, your MagTag came with UF2 already on it.
Launch UF2 by **double-clicking** the Reset button (the one next to the USB C port). You may have to try a few times to get the timing right.
If the UF2 bootloader is installed, you will see a new disk drive appear called **MAGTAGBOOT**
Copy the **UF2** file you downloaded at the first step of this tutorial onto the **MAGTAGBOOT** drive
If you're using Windows and you get an error at the end of the file copy that says **Error from the file copy, Error 0x800701B1: A device which does not exist was specified.** You can ignore this error, the bootloader sometimes disconnects without telling Windows, the install completed just fine and you can continue.[If its really annoying, you can also upgrade the bootloader (the latest version of the UF2 bootloader fixes this warning)](https://learn.adafruit.com/adafruit-magtag/install-uf2-bootloader)
Your board should auto-reset into CircuitPython, or you may need to press reset. A **CIRCUITPY** drive will appear. You're done! Go to the next pages.
# Option 2 - Use esptool to load BIN file
If you have an original MagTag with while soldermask on the front, we didn't have UF2 written for the ESP32S2 yet so it will not come with the UF2 bootloader.
You can upload with **esptool** to the ROM (hardware) bootloader instead!
Follow the initial steps found in the [Run esptool and check connection section of the ROM Bootloader page](https://learn.adafruit.com/adafruit-magtag/rom-bootloader#run-esptool-and-check-connection-3076823-5) to verify your environment is set up, your board is successfully connected, and which port it's using.
**In the final command to write a binary file to the board, replace the port with your port, and replace "firmware.bin" with the the file you downloaded above.**
The output should look something like the output in the image.
Press reset to exit the bootloader.
Your **CIRCUITPY** drive should appear!
You're all set! Go to the next pages.
# Option 3 - Use Chrome Browser To Upload BIN file
If for some reason you cannot get esptool to run, you can always try using the Chrome-browser version of esptool we have written. This is handy if you don't have Python on your computer, or something is really weird with your setup that makes esptool not run (which happens sometimes and isn't worth debugging!) You can follow along on the [Web Serial ESPTool](https://learn.adafruit.com/adafruit-magtag/web-serial-esptool) page and either load the UF2 bootloader and then come back to Option 1 on this page, or you can download the CircuitPython BIN file directly using the tool in the same manner as the bootloader.
# MagTag Lists From Google Spreadsheets
## CircuitPython Internet Test
One of the great things about most Espressif microcontrollers are their built-in WiFi capabilities. This page covers the basics of getting connected using CircuitPython.
The first thing you need to do is update your **code.py** to the following (it will error until WiFi details are added). Click the **Download Project Bundle** button to download the necessary libraries and the **code.py** file in a zip file. Extract the contents of the zip file, and copy the **entire** **lib** **folder** and the **code.py** file to your **CIRCUITPY** drive.
https://github.com/adafruit/Adafruit_Learning_System_Guides/blob/main/ESP32_S2_WiFi_Tests/CPy_Native_WiFi_Test/code.py
Your **CIRCUITPY** drive should resemble the following.
To get connected, the next thing you need to do is update the **settings.toml** file.
## The settings.toml File
We expect people to share tons of projects as they build CircuitPython WiFi widgets. What we want to avoid is people accidentally sharing their passwords or secret tokens and API keys. So, we designed all our examples to use a **settings.toml** file, that is on your **CIRCUITPY** drive, to hold secret/private/custom data. That way you can share your main project without worrying about accidentally sharing private stuff.
If you have a fresh install of CircuitPython on your board, the initial **settings.toml** file on your **CIRCUITPY** drive is empty.
To get started, you can update the **settings.toml** on your **CIRCUITPY** drive to contain the following code.
https://github.com/adafruit/Adafruit_Learning_System_Guides/blob/main/ESP32_S2_WiFi_Tests/CPy_Native_WiFi_Test/settings.toml
This file should contain a series of Python variables, each assigned to a string. Each variable should describe what it represents (say `wifi_ssid`), followed by an **= ** (equals sign), followed by the data in the form of a Python string (such as `"my-wifi-password"` including the quote marks).
**At a minimum you'll need to add/update your WiFi SSID and WiFi password, so do that now!**
As you make projects you may need more tokens and keys, just add them one line at a time. See for example other tokens such as one for accessing GitHub or the Hackaday API. Other non-secret data like your timezone can also go here.
For the correct time zone string, look at [http://worldtimeapi.org/timezones](http://worldtimeapi.org/timezones) and remember that if your city is not listed, look for a city in the same time zone, for example Boston, New York, Philadelphia, Washington DC, and Miami are all on the same time as New York.
Of course, don't share your **settings.toml** - keep that out of GitHub, Discord or other project-sharing sites.
Warning:
If you connect to the serial console, you should see something like the following:
In order, the example code...
Checks the ESP32's MAC address.
```python
print(f"My MAC address: {[hex(i) for i in wifi.radio.mac_address]}")
```
Performs a scan of all access points and prints out the access point's name (SSID), signal strength (RSSI), and channel.
```python
print("Available WiFi networks:")
for network in wifi.radio.start_scanning_networks():
print("\t%s\t\tRSSI: %d\tChannel: %d" % (str(network.ssid, "utf-8"),
network.rssi, network.channel))
wifi.radio.stop_scanning_networks()
```
Connects to the access point you defined in the **settings.toml** file, and prints out its local IP address.
```python
print(f"Connecting to {os.getenv('WIFI_SSID')}")
wifi.radio.connect(os.getenv("WIFI_SSID"), os.getenv("WIFI_PASSWORD"))
print(f"Connected to {os.getenv('WIFI_SSID')}")
print(f"My IP address: {wifi.radio.ipv4_address}")
```
Attempts to ping a Google DNS server to test connectivity. If a ping fails, it returns `None`. Initial pings can sometimes fail for various reasons. So, if the initial ping is successful (`is not None`), it will print the echo speed in ms. If the initial ping fails, it will try one more time to ping, and then print the returned value. If the second ping fails, it will result in `"Ping google.com: None ms"` being printed to the serial console. Failure to ping does not always indicate a lack of connectivity, so the code will continue to run.
```python
ping_ip = ipaddress.IPv4Address("8.8.8.8")
ping = wifi.radio.ping(ip=ping_ip) * 1000
if ping is not None:
print(f"Ping google.com: {ping} ms")
else:
ping = wifi.radio.ping(ip=ping_ip)
print(f"Ping google.com: {ping} ms")
```
The code creates a socketpool using the wifi radio's available sockets. This is performed so we don't need to re-use sockets. Then, it initializes a a new instance of the [requests](http://docs.python-requests.org/en/master/) interface - which makes getting data from the internet _really really easy._
```python
pool = socketpool.SocketPool(wifi.radio)
requests = adafruit_requests.Session(pool, ssl.create_default_context())
```
To read in plain-text from a web URL, call `requests.get` - you may pass in either a http, or a http **s** url for SSL connectivity.
```python
print(f"Fetching text from {TEXT_URL}")
response = requests.get(TEXT_URL)
print("-" * 40)
print(response.text)
print("-" * 40)
```
Requests can also display a JSON-formatted response from a web URL using a call to `requests.get`.
```python
print(f"Fetching json from {JSON_QUOTES_URL}")
response = requests.get(JSON_QUOTES_URL)
print("-" * 40)
print(response.json())
print("-" * 40)
```
Finally, you can fetch and parse a JSON URL using `requests.get`. This code snippet obtains the `stargazers_count` field from a call to the GitHub API.
```python
print(f"Fetching and parsing json from {JSON_STARS_URL}")
response = requests.get(JSON_STARS_URL)
print("-" * 40)
print(f"CircuitPython GitHub Stars: {response.json()['stargazers_count']}")
print("-" * 40)
```
OK you now have your ESP32 board set up with a proper **settings.toml** file and can connect over the Internet. If not, check that your **settings.toml** file has the right SSID and password and retrace your steps until you get the Internet connectivity working!
## IPv6 Networking
Starting in CircuitPython 9.2, IPv6 networking is available on most Espressif wifi boards. Socket-using libraries like **adafruit\_requests** and **adafruit\_ntp** will need to be updated to use the new APIs and for now can only connect to services on IPv4.
### IPv6 connectivity & privacy
IPv6 addresses are divided into many special kinds, and many of those kinds (like those starting with **FC** , **FD** , **FE** ) are private or local; Addresses starting with other prefixes like **2002:** and **2001:** are globally routable. In 2024, far from all ISPs and home networks support IPv6 internet connectivity. For more info consult resources like [Wikipedia](https://en.wikipedia.org/wiki/IPv6_address#Local_addresses). If you're interested in global IPv6 connectivity you can use services like [Hurricane Electric](https://www.he.net/) to create an "IPv6 tunnel" (free as of 2024, but requires expertise and a compatible router or host computer to set up)
It's also important to be aware that, as currently implemented by Espressif, there are privacy concerns especially when these devices operate on the global IPv6 network: The device's unique identifier (its EUI-64 or MAC address) is used by default as part of its IPv6 address. This means that the device identity can be tracked across multiple networks by any service it connects to.
### Enable IPv6 networking
Due to the privacy consideration, IPv6 networking is not automatically enabled. Instead, it must be explicitly enabled by a call to `start_dhcp_client` with the `ipv6=True` argument specified:
```python
wifi.start_dhcp_client(ipv6=True)
```
### Check IP addresses
The read-only `addresses` property of the `wifi.radio` object holds all addresses, including IPv4 and IPv6 addresses:
```python
>>> wifi.radio.addresses
('FE80::7EDF:A1FF:FE00:518C', 'FD5F:3F5C:FE50:0:7EDF:A1FF:FE00:518C', '10.0.3.96')
```
The `wifi.radio.dns` servers can be IPv4 or IPv6:
```python
>>> wifi.radio.dns
('FD5F:3F5C:FE50::1',)
>>> wifi.radio.dns = ("1.1.1.1",)
>>> wifi.radio.dns
('1.1.1.1',)
```
### Ping v6 networks
`wifi.radio.ping` accepts v6 addresses and names:
```python
>>> wifi.radio.ping("google.com")
0.043
>>> wifi.radio.ping("ipv6.google.com")
0.048
```
### Create & use IPv6 sockets
Use the address family `socket.AF_INET6`. After the socket is created, use methods like `connect`, `send`, `recfrom_into`, etc just like for IPv4 sockets. This code snippet shows communicating with a private-network NTP server; this IPv6 address will not work on your network:
```python
>>> ntp_addr = ("fd5f:3f5c:fe50::20e", 123)
>>> PACKET_SIZE = 48
>>>
>>> buf = bytearray(PACKET_SIZE)
>>> with socket.socket(socket.AF_INET6, socket.SOCK_DGRAM) as s:
... s.settimeout(1)
... buf[0] = 0b0010_0011
... s.sendto(buf, ntp_addr)
... print(s.recvfrom_into(buf))
... print(buf)
...
48
(48, ('fd5f:3f5c:fe50::20e', 123))
bytearray(b'$\x01\x03\xeb\x00\x00\x00\x00\x00\x00\x00GGPS\x00\xeaA0h\x07s;\xc0\x00\x00\x00\x00\x00\x00\x00\x00\xeaA0n\xeb4\x82-\xeaA0n\xebAU\xb1')
```
# MagTag Lists From Google Spreadsheets
## Getting The Date & Time
A very common need for projects is to know the current date and time. Especially when you want to deep sleep until an event, or you want to change your display based on what day, time, date, etc. it is
**Determining the correct local time is really really hard. There are various time zones, Daylight Savings dates, leap seconds, etc.** Trying to get NTP time and then back-calculating what the local time is, is extraordinarily hard on a microcontroller just isn't worth the effort and it will get out of sync as laws change anyways.
For that reason, we have the free adafruit.io time service. **Free for anyone with a free adafruit.io account.** You _do need an account_ because we have to keep accidentally mis-programmed-board from overwhelming adafruit.io and lock them out temporarily. Again, it's free!
Info:
## Step 1) Make an Adafruit account
It's free! Visit [https://accounts.adafruit.com/](https://accounts.adafruit.com/) to register and make an account if you do not already have one
## Step 2) Sign into Adafruit IO
Head over to [io.adafruit.com](https://io.adafruit.com/) and click **Sign In** to log into IO using your Adafruit account. It's free and fast to join.
## Step 3) Get your Adafruit IO Key
Click on **My Key** in the top bar
![](https://cdn-learn.adafruit.com/assets/assets/000/097/449/medium800/adafruit_products_image.png?1607208628 "My Key" has been replaced with a key-shaped icon!)
You will get a popup with your **Username** and **Key** (In this screenshot, we've covered it with red blocks)
Go to the **settings.toml** file on your **CIRCUITPY** drive (or create one with the text editor with your operating system) and add three lines for `AIO_USERNAME`, `ADAFRUIT_AIO_KEY` and `TIMEZONE` so you get something like the following:
```python
# This file is where you keep secret settings, passwords, and tokens!
# If you put them in the code you risk committing that info or sharing it
CIRCUITPY_WIFI_SSID = "your-wifi-ssid"
CIRCUITPY_WIFI_PASSWORD = "your-wifi-password"
ADAFRUIT_AIO_USERNAME = "your-adafruit-io-username"
ADAFRUIT_AIO_KEY = "your-adafruit-io-key"
# Timezone names from http://worldtimeapi.org/timezones
TIMEZONE="America/New_York"
```
The timezone is optional, if you don't have that entry, adafruit.io will guess your timezone based on geographic IP address lookup. You can visit [http://worldtimeapi.org/timezones](http://worldtimeapi.org/timezones) to see all the time zones available (even though we do not use Worldtime for time-keeping, we do use the same time zone table).
## Step 4) Upload Test Python Code
This code is like the Internet Test code from before, but this time it will connect to adafruit.io and get the local time
```python
import ipaddress
import os
import ssl
import wifi
import socketpool
import adafruit_requests
# Get our username, key and desired timezone
ssid = os.getenv("CIRCUITPY_WIFI_SSID")
password = os.getenv("CIRCUITPY_WIFI_PASSWORD")
aio_username = os.getenv("ADAFRUIT_AIO_USERNAME")
aio_key = os.getenv("ADAFRUIT_AIO_KEY")
timezone = os.getenv("TIMEZONE")
TIME_URL = f"https://io.adafruit.com/api/v2/{aio_username}/integrations/time/strftime?x-aio-key={aio_key}&tz={timezone}"
TIME_URL += "&fmt=%25Y-%25m-%25d+%25H%3A%25M%3A%25S.%25L+%25j+%25u+%25z+%25Z"
print("ESP32-S2 Adafruit IO Time test")
print("My MAC addr:", [hex(i) for i in wifi.radio.mac_address])
print("Available WiFi networks:")
for network in wifi.radio.start_scanning_networks():
print("\t%s\t\tRSSI: %d\tChannel: %d" % (str(network.ssid, "utf-8"),
network.rssi, network.channel))
wifi.radio.stop_scanning_networks()
print("Connecting to", ssid)
wifi.radio.connect(ssid, password)
print(f"Connected to {ssid}!")
print("My IP address is", wifi.radio.ipv4_address)
ipv4 = ipaddress.ip_address("8.8.4.4")
print("Ping google.com:", wifi.radio.ping(ipv4), "ms")
pool = socketpool.SocketPool(wifi.radio)
requests = adafruit_requests.Session(pool, ssl.create_default_context())
print("Fetching text from", TIME_URL)
response = requests.get(TIME_URL)
print("-" * 40)
print(response.text)
print("-" * 40)
```
After running this, you will see something like the below text. We have blocked out the part with the secret username and key data!
Note at the end you will get the date, time, and your timezone! If so, you have correctly configured your **settings.toml** and can continue to the next steps!
# MagTag Lists From Google Spreadsheets
## Spreadsheet Setup
Start at **[https://docs.google.com/spreadsheets](https://docs.google.com/spreadsheets)** and **sign in** …or, if you don’t already have a **Google account** , there’s an option to **create** one.
Create a new **blank** sheet. Just the basic plain one.
Be a dearie and double-click “Untitled spreadsheet,” give it a descriptive name.
**Two things to keep in mind:**
1. Keep the spreadsheet design **small and simple**. A few dozen cells, tops. Text, numbers and dates are fine. No graphics or colors or merged cells. We’re just using this spreadsheet to hold some short notes, not doing serious data analysis or structuring. Formulas and functions can be used, as long as the output is text, numbers or dates.
2. Later, on MagTag, there will be some accompanying **CircuitPython** code that _ **you** _ will write, and it needs to know how the table is formatted…the whole spreadsheet doesn’t simply appear there. We’ll walk through some **examples** on pages that follow. Each one’s a little different.
If you’ll be **collaborating** with others, click the **Share** button at top-right. You can then add people by email address (they’ll also need Google accounts). Everyone can then add to or edit the spreadsheet, even at the same time.
If it’s **just you** editing the data, you can **skip** this step.
## Publish to Web
From Google Sheets’ **_File_** menu (not the browser’s _File_ menu), select **“Share,”** which rolls over to include “ **Publish to web**.”
A dialog box pops up with various settings and menus. Most can be left at their defaults, but the second menu selects the published format: change this from the default “Web page” to “**Tab-separated values (.tsv)**” then click the “ **Publish** ” button. Confirm “ **OK** ” when asked.
The dialog box expands a bit now, and includes a long URL for others to access the spreadsheet. **Copy** that URL string to the clipboard, we’ll paste it into some CircuitPython code later. Double check that this ends with “output=tsv”. If not, change the format in the menu above this field.
Danger:
**You only need to set this up once. Any changes to the original spreadsheet are automatically re-published using the same link you just put together.**
## Next Steps
Now we’ll look at what that the .tsv format _is_ and write some **CircuitPython** code to convert it into something **readable** on the MagTag’s display…
# MagTag Lists From Google Spreadsheets
## Spreadsheet Parsing in CircuitPython
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:
```python
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).
```python
# 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:
```python
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.
# MagTag Lists From Google Spreadsheets
## Installing Examples
The button below downloads a ZIP file of **images and fonts** for the examples. The “Download Project Bundle” links on subsequent pages will fetch the necessary _code and libraries,_ but not these files. You’ll need _both_ to piece together a working example.
[Download ZIP file for MagTag Google Sheets examples](https://github.com/adafruit/Adafruit_Learning_System_Guides/tree/main/MagTag/MagTag_Google_Sheets)
Warning:
This is _ **not** _ a complete package. You’ll still need a few things…
- You must **create and publish the spreadsheets** as shown on each example page. Don’t fret, they’re simple ones.
- Prerequisite **CircuitPython libraries** must be installed (see below).
- **secrets.py** file (_not_ a part of the project files) must be configured with your **WiFi network** and **Adafruit IO** credentials (as shown on the “CircuitPython Internet” Test page) and time zone (“Getting the Date & Time” page).
- The CircuitPython examples have **descriptive filenames** , but the active file should be renamed **code.py** when copied to the **CIRCUITPY** drive, otherwise it won’t run.
- Your **spreadsheet links** must be pasted into the example code.
Here’s a map of all this project’s required images, fonts and code on the **CIRCUITPY** drive. Remember that nothing will happen until one of the examples is renamed **code.py** :
**If you run out of space when copying items to CIRCUITPY:** make a **backup** of any files currently on that drive, then delete files that aren’t related to this project to free up space.
**After setting up any of the examples, tap the board’s RESET button.** Occasionally the WiFi connection has some lingering data buffered from prior code, and this can cause the examples to fail with an error the first time they’re run. RESET makes sure everything starts clean and fresh.
## Font Licenses
Copyright (c) 1999, Thomas A. Fine
License to copy and distribute for both commercial and non-commercial use is herby granted, provided this notice is preserved.
fine@head-cfa.harvard.edu http://hea-www.harvard.edu/~fine/Produced with bdfedit, a tcl/tk font editing program written by Thomas A. Fine
Copyright 1984-1989, 1994 Adobe Systems Incorporated.
Copyright 1988, 1994 Digital Equipment Corporation.
Adobe is a trademark of Adobe Systems Incorporated which may be registered in certain jurisdictions.
Permission to use these trademarks is hereby granted only in association with the images described in this file.
Permission to use, copy, modify, distribute and sell this software and its documentation for any purpose and without fee is hereby granted, provided that the above copyright notices appear in all copies and that both those copyright notices and this permission notice appear in supporting documentation, and that the names of Adobe Systems and Digital Equipment Corporation not be used in advertising or publicity pertaining to distribution of the software without specific, written prior permission. Adobe Systems and Digital Equipment Corporation make no representations about the suitability of this software for any purpose. It is provided "as is" without express or implied warranty.
# MagTag Lists From Google Spreadsheets
## Example: Naughty or Nice?
**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, 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, then copy the document’s URL 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 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/naughty\_nice/** 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:
Warning:
https://github.com/adafruit/Adafruit_Learning_System_Guides/blob/main/MagTag/MagTag_Google_Sheets/naughty_nice/code.py
## Skateboard Tricks
A couple of things make this program more flexible. First, near the top of the code is this line:
```python
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 **LINES** list. The first pass looks only at the column headings (row #1 in the spreadsheet, or LINES[0] in CircuitPython) 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).
```python
# Split text response into separate lines
LINES = TSV_DATA.split('\r\n')
# 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.
cells = LINES[0].split("\t") # Tab-separated values
for column, entry in enumerate(cells):
head = entry.lower() # Case-insensitive compare
if ((NICE and head == 'nice') or (not NICE and head == 'naughty')):
NAME_COLUMN = column
# 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 line in LINES[1:]: # Skip first line -- naughty/nice/notes in sheet
cells = line.split("\t") # Tab-separated
if len(cells) >= NAME_COLUMN and cells[NAME_COLUMN] != "":
NAME_LIST += cells[NAME_COLUMN] + '\n' # Name + newline character
```
To avoid blank lines in the displayed name list, the code above intentionally checks for empty cells (`""`) and ignores them.
# MagTag Lists From Google Spreadsheets
## Example: Weekly Planner
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:
Warning:
https://github.com/adafruit/Adafruit_Learning_System_Guides/blob/main/MagTag/MagTag_Google_Sheets/weekly_planner/code.py
## 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):
```python
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:
```python
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.
## Featured Products
### Adafruit MagTag Starter Kit - ADABOX017 Essentials
[Adafruit MagTag Starter Kit - ADABOX017 Essentials](https://www.adafruit.com/product/4819)
The **Adafruit MagTag** combines the new ESP32-S2 wireless module and a 2.9" grayscale E-Ink display to make a low-power IoT display that can show data on its screen even when power is removed! The ESP32-S2 is great because it builds on the years of code and support for the...
Out of Stock
[Buy Now](https://www.adafruit.com/product/4819)
[Related Guides to the Product](https://learn.adafruit.com/products/4819/guides)
### Adafruit MagTag - 2.9" Grayscale E-Ink WiFi Display
[Adafruit MagTag - 2.9" Grayscale E-Ink WiFi Display](https://www.adafruit.com/product/4800)
The Adafruit MagTag combines the ESP32-S2 wireless module and a 2.9" grayscale E-Ink display to make a low-power IoT display that can show data on its screen even when power is removed! The ESP32-S2 is great because it builds on the years of code and support for the ESP32 and also adds...
In Stock
[Buy Now](https://www.adafruit.com/product/4800)
[Related Guides to the Product](https://learn.adafruit.com/products/4800/guides)
### USB Type A to Type C Cable - approx 1 meter / 3 ft long
[USB Type A to Type C Cable - approx 1 meter / 3 ft long](https://www.adafruit.com/product/4474)
As technology changes and adapts, so does Adafruit. This **USB Type A to Type C** cable will help you with the transition to USB C, even if you're still totin' around a USB Type A hub, computer or laptop.
USB C is the latest industry-standard connector for...
In Stock
[Buy Now](https://www.adafruit.com/product/4474)
[Related Guides to the Product](https://learn.adafruit.com/products/4474/guides)
### Lithium Ion Polymer Battery with Short Cable - 3.7V 420mAh
[Lithium Ion Polymer Battery with Short Cable - 3.7V 420mAh](https://www.adafruit.com/product/4236)
Lithium-ion polymer (also known as 'lipo' or 'lipoly') batteries are thin, light, and powerful. The output ranges from 4.2V when completely charged to 3.7V. This battery has a capacity of 420mAh for a total of about 1.55 Wh. If you need a larger (or smaller!) battery,
In Stock
[Buy Now](https://www.adafruit.com/product/4236)
[Related Guides to the Product](https://learn.adafruit.com/products/4236/guides)
## Related Guides
- [Adafruit MagTag](https://learn.adafruit.com/adafruit-magtag.md)
- [CircuitPython Web Workflow Code Editor Quick Start](https://learn.adafruit.com/getting-started-with-web-workflow-using-the-code-editor.md)
- [MagTag Covid Tracking Project IoT Display](https://learn.adafruit.com/magtag-covid-tracking-project-iot-display.md)
- [50 Cent CPI Tracker for MagTag](https://learn.adafruit.com/50-cent-cpi-tracker-for-magtag.md)
- [MagTag Progress Displays](https://learn.adafruit.com/magtag-progress-displays.md)
- [CircuitPython Display_Text Library](https://learn.adafruit.com/circuitpython-display-text-library.md)
- [Adafruit MagTag Project Selector](https://learn.adafruit.com/adafruit-magtag-project-selector.md)
- [MagTag AR Tarot Card Reading](https://learn.adafruit.com/magtag-tarot-cards.md)
- [AdaBox 017](https://learn.adafruit.com/adabox017.md)
- [MagTag Cat Fed Clock](https://learn.adafruit.com/magtag-cat-feeder-clock.md)
- [MagTag Weekly Showtimes Event Notifier](https://learn.adafruit.com/magtag-weekly-event-showtimes-display.md)
- [Language Flashcards on the MagTag](https://learn.adafruit.com/magtag-flashcards.md)
- [OSHWA Project Display With Adafruit MagTag](https://learn.adafruit.com/oshwa-project-display-with-adafruit-magtag.md)
- [CircuitPython Animated Holiday Wreath Lights](https://learn.adafruit.com/circuitpython-animated-holiday-wreath-lights.md)
- [MagTag Sports Schedule Viewer](https://learn.adafruit.com/magtag-sports-schedule-viewer.md)
- [Adafruit MagTag Kitchen Timer](https://learn.adafruit.com/adafruit-magtag-kitchen-timer.md)