Student Placement with a Google Sheet and STAR

I’ve been steadily making improvements to my Student Placement system and thought others might find it useful. It is a juiced up Google Sheet that integrates with the STAR RTI screener. We have been using it for years and the time spent on student placement has been cut in half. You will find two sheets linked below - a blank template and a demo file with some fake data added to provide context.

The system calculates student data automatically, both from data recorded with the STAR RTI screener and from teacher reporting. Data can be exported from STAR and easily added to the sheet. Then teachers add their own feedback - filling in services, behavior, attention to task, reading, math, and writing observations. The placement team organizes students into different classes for the upcoming year. Classes can be analyzed using different data points which can be changed dynamically using the controls all the way on the right of the class roster sheet sheet.

The system has several dashboards and can record two sets of placement, just in case a grade level is close to splitting or contracting. The dashboards include a class roster view, a chart view, an in focus page, and a numbers dashboard. We most often use the class roster and chart views during placement. The in-focus page can feed photos automatically, too, if you have a photo DVD from your photographer that uses student ID to name files.

I created an instructions page for administrators and teachers to help make this easier to set up and use. You can take a look at the posts written for the previous versions, too. They work in a similar way and some of the posts went into greater detail. Let me know if you try it. You can ask me questions or provide feedback here or on Twitter. Thanks!

Take a look at the sheet with some demo data (All data is fictional)

Make a copy of the Blank template here

Google Sheets Powered PO Generator

Writing purchase orders is one of the least favorite parts of my job. It’s often a repetitive and monotonous task that requires a deep dive into a file cabinet, a massive catalog, or both. I created this spreadsheet tool to make POs a little less onerous. This system keeps track of your orders, allows you to take notes, monitors your budget in different budget codes, and will actually generate the printed PO sheet! Make a copy if you would like to use the sheet. I hope you find this as useful as I do.

Setting up the sheet is easy. Just add your vendor information to the Vendor Tab. As a general rule, anything in the sheet with a yellow background can be edited. Anything with a grey or black should not be edited. These are controlled with functions. Adding vendor addresses is a one-time outlay of work. The address will auto-fill on the POs. I include my own information here, too, because there are certainly times when I purchase something from a vendor not on the bid list (Amazon, anyone?) and need to submit a PO with me as the vendor. You can add vendors as you need them, secure in the knowledge that the address will be ready the next time a PO is generated for that seller.

Also on the Vendor sheet is a budget code table. I have two budget codes as a technology teacher, one for supplies and another for equipment. In order to keep track of your codes, add the total amount you have to spend and the codes themselves in the appropriate columns (yellow only.) You’ll notice that the sheet automatically keeps track of the amount left in each budget code and the supplies code has a function that helps me keep track of per student expenditure.

The “Master Sheet” is where I fill out my orders (yellow cells only.) It's fairly self explanatory. One this thing I need to point out: the first two columns seem redundant but are not! The first column allows me to specify an order on a specific day, the second column is only the vendor name, linked to your vendor sheet. This second column controls the address information and is independent of the date. Both are necessary!

Once your order is recorded on the “Master Sheet” you can generate your PO. Now you have one place for all your PO records and you don’t have to worry about handwriting a PO form. Better yet, next year I have a clear record and can reorder easily. Open the PO Generator tab. You will notice four yellow cells, three with drop down boxes. Select your Vendor, which will populate all of the address fields. Then, select the date the PO was written, which is fed from the first column of the “Master Sheet.” Finally, select the budget code, write in the potential shipping cost, and add your name in the “Requested by:” field.

Your PO Is ready to print! This has made PO writing so much easier for me. I keep notes on my Master Sheet, such as when an order arrives, or if I was unhappy with the product. My record keeping is better and the process is a lot faster. This was designed using the Scarsdale PO form, so you may need to redesign the PO Generator layout. Let me know if you use this and if you’ve made it better!

Student Placement with a Google Sheet

It’s that time of year when teachers thoughtfully build classes for next September. A while back I built a Google Sheet to help visualize the process with charts and graphs in real time. Each year I try to improve it based on the experiences from the previous year. I’ve tried to make the system easy for other schools to use, too, making most of the variables editable from a sheet. This post will introduce the 2016 articulator. Please note that all of the data used in this example is fake and was created using mockeroo.

How it Works

This system uses teacher feedback and optional data from the STAR RTI screener to create a series of visual dashboards. Each dashboard (numbers, roster, charts, and focus) works in real time to balance student placement when creating next year’s classes. There is some setup required, but I’ve worked hard to make it easier. You can follow the written directions for administrators here.

Teacher Input

Once a grade level is prepared, teachers add scores for student behavior and academics. Then teachers fill in any special services that are being delivered and add any other pertinent notes. As per last year’s feedback, behavior has been split into a social and an “attention to task” component. Manual categories for ELA and Math have also made a return, being left out in lieu of STAR data last year. Teachers felt that the STAR scores were not adequate alone. Fortunately, this makes the system more adaptable to schools who don’t use STAR. I’ve included directions for classroom teachers here.

Student Placement

My teachers often complete an initial placement before our first official meeting by changing the class number in the first column of the Data Beta sheet. Column B can be used If it is believed that a class might split or contract. The number of classes available in each set can be adjusted from the variable sheet. Each of the Dashboard sheets can switch from class set A or B by using a drop down menu in the top left. This configurability allows you to see how the proposed classes look in each configuration. You adjust the classes using the A and B columns on the Data Beta sheet during your placement meeting and the dashboards update in real time. Because the sheet is shared with everyone involved in placement, many eyes can watch for issues. Typically we appoint one person to be the official "updater" to keep confusion down.

Student Photos

This year I added student photos to the “In Focus” sheet. This optional feature requires the photos to be named using your student ID numbers and uploaded to a web directory (For the example I use the attachments feature of a Google Site.) Then paste the URL of the web directory into cell J15 of the Variables sheet. Since different systems export student photos using different formats, make sure you select the appropriate file type in cell J14.

Hope it Works for You, too!

This system has been extremely helpful during our student placement meetings. I hope it can be useful to others, as well. I’ve tried to make the system customizable and extensible and I’ll happily answer any setup questions in the comments. Please contact me directly on Plus or Twitter if you need help making any significant modifications.

Kickin' Library Databases with a Google Sheet

We have a number of database subscriptions and our students use them regularly for research and reference. They each have strengths, some are well suited for biographies for instace, while others excel at zoology. Age appropriateness also varies. While our librarian discusses these attributes with our students, they sometimes need a reminder. Why not create a sortable database for the databases?

Creating something like this is a perfect task for Romain Vialard’s Awesome Table - a very cool Google Gadget that I’ve written about before. Our meta database allows students to filter results by grade levels and goals.

This post is a “how to.” It’s long, but it works well. Our system has a few features that I do not describe in detail below because it would just be too long. We use a second Awesome Table as a dashboard to allow Kate Byrnes, our librarian, to edit submissions and turn entries on and off. Form Mule gives us the capability to edit submissions and email the submitter. I promise to  blog about these enhancements in the future.

What You Need Before You Start

• A google form connected to a Google Spreadsheet. The form should ask for the information needed in your site. This is grouped into two categories: information that will be visible in the awesome table and information that will filter the awesome table. My form uses the following:

Question	Question Type
Subscription Title	Text
Subject Area	Checkbox
Grade Level	Checkbox
Useful For	Checkbox
Icon URL	Text
Description	Paragraph Text
Image URL	Text
Web Link	Text
Password	Multiple Choice (Y,N)
Available Platforms	Checkbox

• A protected Google Doc with your database passwords. We share this document only within our school domain to control access to the passwords.
  
  

Design Your HTML Template

Set Up Your Awesome Table Template

Awesome tables can use an HTML template to render variables from form. This is an incredibly powerful feature that Romain Vialard kind of snuck into his tool. I learned about it from talking to James Peterson, he’s a smart and helpful guy - worthy of following!

AwesomeTables uses HTML to draw the cards for the interface. You can be creative with the HTML. If you aren’t comfortable with HTML, you can use a page in a Google Site to design your card with sample data. Once your composition looks right in sites, click on the “HTML” button in the menu bar. Copy the HTML from the window that appears. Then, return to your Google Sheet and add a page named “Template.” Paste your HTML into cell B1 of the template sheet. I have included my HTML further down the post.

The key to using the AwesomeTable template feature is identifying the variables in your HTML. These are the values that will change from entry to entry, such as the subscription title. Each place in the HTML where these variables are used should be changed to reflect the name of the corresponding column in the Google sheet (shown in red below.) Each variable needs to start with ${“ and end with “} (shown in green below.) The HTML used in my template is:

<table style="width:100%;background-color:#ffffff;border-radius:25px;padding:15px">
<tbody>
<tr>
<td>
<div><img border="0" src="${"Icon URL"}" style="display:inline;margin:5px 50px 10px 10px;background-color:transparent;width:30%">

<h2><a href="
${"Web Link"}
" target="_blank">
${"Subscription Title"}

</a></h2>
<hr>
<div style="line-height:1.5">${"Description"}
<div><br>
<div style="line-height:1.5">
<div>
${"Password Condition"}
</span></div>
</div>
</div>
</div>
</div>
</td>
</tr>
</tbody>
</table>

Creating the Awesome Table

The Awesome table is fed through the form, but the gadget needs row two of the the Google sheet to contain instructions. Drag the freeze frame line under row two, locking the first and second row. In this second row, add instructions for how Awesome Table will filter and show the data from that column. You can learn more about Awesome Table filters here. My system uses the filters shown below. Notice that they all say “Hidden.” This is because the visible bits are being handled by the template. The items that are being used in the template say “nofilter.” These are not showing as a filter on the top of the table because they are expressly used for rendering the database cards. Ultimately, the sheet looks something like this.

Question	Question Type	Purpose	Filter
Subscription Title	Text	Builds cards	nofilter - Hidden
Subject Area	Checkbox	Filters table	csvFilter - Hidden
Grade Level	Checkbox	Filters table	csvFilter - Hidden
Useful For	Checkbox	Filters table	csvFilter - Hidden
Icon URL	Text	Builds cards	nofilter - Hidden
Description	Paragraph Text	Builds cards	nofilter - Hidden
Image URL	Text	Builds cards	nofilter - Hidden
Web Link	Text	Builds cards	nofilter - Hidden
Password	Multiple Choice (Y,N)	Builds cards	nofilter - Hidden
Available Platforms	Checkbox	Filters table	csvFilter - Hidden

Making the Password Link Conditional

Some of the items in our database are subscription based, like World Book, and need a password to access. Other sites are free, like DK Find Out, but are certainly worthy of representation! We are managing our passwords with a protected Google Doc and wanted a way to show the link to the doc when a password is required but read “No password required” when the site was free. This is done with an “If, then” statement.

Title the column on the left of your Form Response sheet “Password Condition.” In row two, set this column to “nofilter - Hidden.” This “If, Then” statement is going to reference the “Password” Yes or No question. If no password is need, it will say simply that.  If a password is required, HTML for the password link will be used. I add this HTML to the Template page, in cell B11. Again, use that Google Sites trick  if you aren’t big on HTML

In row 3, add the function below. This is a nested “If, Then” statement. If this function could talk, this is what it would say, “Okay, I’m an array, so I will apply to whole column (ARRAYFORMULA.) I’m going to look at the timestamp column (A3:A) to tell me whether or not I should work. If there is something there, I’m going to look at the Password column (J3:J.) If this says "No", then I guess I’ll write "No password needed.” But, it might say "Yes," in which case I’ll use the stuff in cell B11 of the Template page.”

=ARRAYFORMULA( IF( A3:A = "",, IF( J3:J="No", "No password needed", IF( J3:J="Yes", Template!B11,))))

Pulling it All Together

Now the sheet is ready to connect to the Awesome Table gadget.

1. Start by editing a Google Site page and inserting a Gadget. Use the “Featured” gadget library to find Awesome Tables.
2. Once added, open the gadget’s preferences window and log into Awesome Tables with your Google Account. Click the “New” button.
3. Then, paste in your sheet URL, the sheet name, and the complete cell range for your data. Remember to share the sheet to the same audience as your table. If the table is meant to public, the sheet also must be public.
4. Next, click the “View” menu and change the default view from “Table” to “Cards.” The variables in this menu require some trial and error. You can play with how many cards are in a row and wide each card is, but first, click the “General Settings” tab and name your table. I like three cards across.
5. Now, click the “Advanced Parameters” tab to connect the HTML template. In the box labeled “Template Range” add the sheet name and the box cell of the HTML code separated by an exclamation point. In my demo file this is “Template!B7.”
6. Set your gadget display height on the bottom of the preferences window. I usually use 2000.
7. Finally, and this is important, click the green “Save” button. Then click “OK” and save your Google Site page to see how it looks. Edit the page again to adjust the settings.

Congratulations for getting this far! I use Awesome Tables all the time and you will too when you unlock how they work. The great thing about something like this is that you can reuse the bones of the system. My next step with this system is to convert it into a Student Bookmarks tool and I’ll let you know how it goes. If you use this post, or if you have any questions, let me know in comments.