TM028 Pyret and Google Sheets (Part 1)
This page serves as a tutorial for creating tables in Pyret from data contained in Sheets documents in your Google Drive. If you are unfamiliar with Sheets, Sheets is Google's free spreadsheet software. After this tutorial, you will be able to import tabular data from Sheets documents and manipulate it with Pyret.
Warning: This tutorial is still under development, so the content is subject to change.
| Terminology | Meaning |
|---|---|
| Sheets | Google's spreadsheet software |
| Sheets document | A file to be opened in the Sheets software |
| sheet | A single page containing tabular data within a Sheets document |
First, visit code.pyret.org and sign in with your Google account. Once you've signed in, the code.pyret.org homepage should look like this:
Click on My Programs. This will take you to a screen that looks like this:
NOTE: The name of this folder is code.pyret.org. When referring to this folder, we use bold text: code.pyret.org.
When referring to the development environment, we use a hyperlink: code.pyret.org.
This folder contains all of the programs you will write using code.pyret.org. First, we need to create a Sheets document that will contain our tabular data. Right click in some empty space in the folder, then select the Google Sheets icon like so:
This will take us to a completely blank Sheets document.
Name and fill out this Sheet as shown below.
Now if you open the code.pyret.org folder in your Google Drive, you should see a new Sheets document titled music. This will be our first example of loading tabular data from a Google Spreadsheet.
Now go back to code.pyret.org and hit Start Coding.
Now that we've made our Sheets document, it's time to use it. For the rest of this tutorial, you will be adding provided code to your own program. Code will look like this:
print("Ahoy world!")
For each snippet of code, type it into the definitions window (the editor on the left) of your program on code.pyret.org, then run your program.
First, we need to use Pyret's library for interacting with files in the Google drive sheets:
import gdrive-sheets as GDS
Next, we'll use this library to access the Sheets document we created.
msheet = GDS.my-spreadsheet("music")
However, we're not ready to access the data just yet. Navigate back to the Sheets document we've created. In the bottom left corner you should see the following:
Each Sheets document can have one or multiple sheets in it. In the case of our music data, there is only one sheet called "Sheet1". Related tabular data will often be placed in separate sheets within a single Sheets document. This next snippet of code will use what we've written so far to load a table from data on a particular sheet within our Sheets document:
music-sheet = load-table: title, artist, album, plays
source: msheet.sheet-by-name("Sheet1", true)
end
This code loads our table, and assigning the names title, artist, album, plays to the columns of the data. These names could be anything we want (EX. {a, b, c, d}, {sail, the, high, seas}, etc.) but we recommend naming columns to inform users about the data contained in these columns.
The source parameter will load the sheet identified by the first argument (for our music data, it's "Sheet1". The second argument is a boolean representing whether or not to skip the first row during loading. For our "Sheet1" data, the first row contains column titles, and the rest of the rows contain the actual data. Since we define the table's column names explicitly, the first row should be skipped. Real world data sets may not have header rows, so it's important to tell Pyret how the data in the sheet is organized.
Congratulations, you've loaded your first table! Now, if you type music-sheet into the interactions window (the editor on the right), you should see the table like so:
You can now manipulate this tabular data using the commands outlined here.
You're done with Part 1! Click here to move on to Part 2.