We’re grateful for Clark Jason Ngo’s tutorial at freeCodeCamp, on which these next instructions are based…

Start at 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 “Publish to the web.”

A dialog box will pop up with many fiddly options, but you can just leave all these at their defaults: “Link,” “Entire Document” and “Web page.” Just click the “Publish” button.

The dialog box will now expand with more stuff, including a URL. Ignore all that, just click the “X” close button at the top-right of this box.

Do not publish sheets containing any sensitive information. Anyone with the link is able to read (but not modify) what’s there.
Your email address WILL be tucked away in the published data. There is no opt-out. If you consider it sensitive, your options are to use a throwaway second account, or skip this project and try one of the others.

URL Shenanigans

Here’s where things get funky…

Now, copy the following link, open a new browser window or tab, and paste it into the address bar…but don’t actually “go” there yet, just do the copy and paste steps:

https://spreadsheets.google.com/feeds/cells/GOOGLESHEETCODE/1/public/full?alt=json

We need to edit this address to point to your newly-published spreadsheet. Go to the browser window where your Google Sheet is open for editing, and copy just the gobbledygook part in the middle of the address (not the link shown on the Publish dialog):

Yours will look a little different — this is a unique Google Sheet code or ID…each document has its own distinct code. Make sure you copy that whole middle section, including any hyphenated bits, but not the stuff around it.

Now return to the other browser window, where a “fake” link was previously pasted. Select just the GOOGLESHEETCODE part, and paste the gobbledygook we just copied in its place, like so:

circuitpython_combine-urls.png
Google Sheets page URL + fake URL = combined URL

Press return, and if it all goes as planned you’ll be greeted by an imposing wall of text. Good news! That actually means it’s working!

Keep that browser window open for now. You’ll need to copy and paste the combined URL into your CircuitPython code later.

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.

If you don’t see the wall of text:

  • Check the “Publish to Web” steps above. When published, the pop-up that appears should say “This document is published to the web.”
  • Confirm that you got the whole sheet ID string, including any hyphenated part, but not the slashes or other characters around it. This should be taken from the browser’s address bar where you’re editing the spreadsheet, not the link shown on the Publish dialog. Confirm that you pasted it over just the GOOGLESHEETCODE part, and not over slashes or other characters around it.

Next Steps

Now we’ll look at what that wall of text is and write some CircuitPython code to convert it into something readable on the MagTag’s display…

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

This page (Spreadsheet Setup) was last updated on Apr 04, 2021.

Text editor powered by tinymce.