Grading for Equity week 5: Practices that are Mathematically Accurate continued (Chapter 8) #Grade4EqChat

This week the Grading for Equity Twitter chat will be digging deeper into chapter 8: Practices that are Mathematically Accurate (continued). Here are the original questions (the twitter versions will be edited somewhat for space):

1. Do give students a grade “bump” when they have shown improvement or growth over a term? By allowing (and encouraging) students to demonstrate growth over time through improved performance, and recording that most recent performance, do we still need to include a bump, or does the improved score itself recognize and reward growth?
2. How easy should it be for a student to be able to calculate her own grade? Does the traditional “average” method allow for this? Would standards-based-grading allow for this? 
3. In physics & science, we necessarily do a lot of group work (e.g. labs, presentations, etc.) What is your current strategy for group grades? Does it align with the Grading for Equity pillars? If not, how could you make it do so? 
4. Think of an example in the professional workplace in which group work (or more likely, called “collaboration”) is expected. What is the rationale, and how is the effectiveness of that collaboration determined? How could we apply this to grading? 
5. What are your main takeaways from the past two chapters on mathematically accurate grading practices? What changes so far, if any, will you make to your grading system? 

Last week's chat is archived here. You can access all the past chats from the resources section. See you tonight!

Grading for Equity week 4: Practices that are Mathematically Accurate (Chapter 7) #Grade4EqChat

This week in the Grading for Equity Twitter chat, we are finally digging in to the meat of the book. We'll slow down a bit; fewer questions and only one chapter per week.

This week, we are looking at chapter 7: Practices that are Mathematically Accurate.

1. What grading scale do you currently use? What are your thoughts about it now that you’ve read this chapter?
2. If you’ve assigned a zero, was it intended primarily to affect students mathematically or psychologically? Knowing that it is mathematically unsound and demotivating as well as inaccurate, does that change your opinion of it?
3. Did you have any particular light bulb moments while reading this chapter?
4. What other ideas in this chapter would you like to discuss? Do you have any concerns?

Last week's chat is archived here. You can access all the past chats from the resources section. See you tonight!

Grading for Equity week 3: The Case for Change (Chapters 5 & 6) #Grade4EqChat

 

The Grading for Equity Twitter bookclub continues apace. Last week we studied chapters 3 &4, which introduced us to the problems in our current grading schemes. 

That was pretty eye-opening. I always think my 'system' is fair, but if you're juggling 7 different systems, it really doesn't matter. I wish my school would agree to agree on some things, even if it isn't my first choice.#Grade4EqChat

— Jill McLean (@jillmclean27) August 3, 2021

I'm thinking of a line of best fit. We are supposed to look at most recent, most consistent marks (which can be interesting in grade 9/10 sci -- bio/chem/phys/earth&space cafeteria course), but at least it allows us to "ignore" outliers. #Grade4EqChat

— Andrea McPhee (JarvisCI) (@ms_mcphee) August 3, 2021

This week we will be looking at chapters 5 & 6, continuing with The Case for Change and A New Vision for Grading. Feel free to join in, even if you haven't read the book! Here are the questions:

1.  Are students motivated to achieve success or to avoid failure? What specific actions, policies, or words by teachers cause students to experience one type of motivation instead of the other?
2. Do you think of your tasks at work as performance or mastery goals? What affects how you define the goal? How does this affect how you pursue the task?
3. In what ways do schools and classrooms send a message of competition for achievement?
   How does your school's treatment of awards and honors promote or undermine a growth or fixed mindset?
4. Review your classroom's current grading policies through the pillars of our vision: How accurate are they? How bias-resistant? How motivating?
5. How much does this book's vision for equitable grading match against your school's overall vision? How likely is it that your school community could agree on this vision?
6. How much does this book's vision for equitable grading align with your own, personal vision for grading? What concerns do you have about this vision? What are your hopes?

See you then!

Grading for Equity week 2: The Case for Change (Chapters 3 & 4) #Grade4EqChat

Last week's Twitter chat went really well. Please join in, even if you haven't read the book or aren't a physics teacher! I've archived the tweets, and you will always be able to find them on my Resources page.

We had a great discussion about some of our philosophy and current grading practices.

Part of my 1st day speech is to tell them they will do work I will not grade but I promise not to make it busy work, that I will have purpose behind what we are doing and they can always ask me why. I should be able to tell them why (if they can't tell) or I shouldn't assign it.

— Bree BarnettDreyfuss (@BarnettDreyfuss) July 27, 2021

It does require a change in the process, but I've found that the culture of the class is so much more positive. This is worth our time. #Grade4EqChat

— Jill McLean (@jillmclean27) July 27, 2021

One big takeaway: No more grading homework!

A6: So many! One thing for sure: Not grading homework next year.

#Grade4EqChat

— Kristin Newton (@KNewtonPhysics) July 27, 2021

This week we will be looking at Chapters 3 & 4, which focus on why we should be changing our grading practices. Here are the questions we'll be answering:

1. Thinking about how others have responded to your mistakes: How have helpful responses impacted you and your effectiveness? How have unhelpful responses impacted you and your effectiveness?
2. Recall something you learned to do outside of the school context. What motivated you to learn and to continue learning when you struggled? 
3. How effective are the use of points for students who are the least motivated and engaged? How might the use of points—the addition and subtraction throughout a student's day—affect those students' relationships with adults and their self-concept about whether school is “for” them?
4. What confidence or uncertainty do you have that two teachers in your school would assign the same grade to a student?
5. Are there teachers with reputations as “hard” or “easy” graders? What, specifically, defines them as that? How does this categorization make you feel? How does it make that teacher feel? How do students react?

See you tonight! The rest of the schedule is posted at this tweet.

Grading for Equity TwitterChat Mondays 8-9pm EST #Grade4EqChat

Several of the physics PD sessions I've attended this summer have mentioned Grading for Equity by Joe Feldman, and how much we'd like to have a discussion about it. So, in the interest of pushing myself well outside my comfort zone, I'm hosting a weekly Twitter chat. It will no doubt have a physics education slant to it, but all educators are welcome to participate, even if you haven't read the book.

The chat will be on Mondays from 8-9 pm EST until October 4. The current plan is to look at 2 chapters per week to begin with, moving to 1 chapter per week for part III. We'll use the questions at the end of each chapter as a starting point for discussion. 

I'll post, from my Twitter profile, a new question about every 10-15 minutes or so. We use the Q# / A# format. Remember to include #Grade4EqChat somewhere in your tweet so others will find it.

TDSB teachers can access the book and in print through the Virtual Library. For Ontario teachers, it is available online through the OCT library. For teachers elsewhere, check and see if it is available through your board or district or professional organization.

The schedule will be

New urls, who dis?

Just a very quick note to say that I've moved this blog url from equalsmcsquared.blogspot.com to andreamcphee.blogspot.com, and also my course webpage became http://mcpheesics.ca last January. I think all my links here are updated, but let me know if I there's anything I didn't catch.

I'm also for real planning to blog more regularly for the next few months; I have no excuse since I'm on leave and a whole pile of posts still in draft. I've got a post queued up for Monday already.

ETA: Make that Tuesday. Oops! On the other hand, it allows me to post this gif I've been using a lot these past few months...

I will be presenting two micro sessions at Unleashing Learning today: Getting Started with Math Techbook from 1:25- 1:45 and Formative Assessment with Plickers from 2:05 - 2:25, both at Table 1. 

Come join me!

A script for turning Sheets data into Calendar entries (part 5)

Here is the final step in turning homework tweets into Calendar entries. Catch up here...

• Part 1: Automating homework tweets to calendar entries using IFTTT and scripts (the overview)
• Part 2: Sorting course tweets in Sheets using query() 
• Part 3: Publishing a Sheet to the web
• Part 4: Importing data to a new Sheet

The gist of this step is that adding a new line to the top of a course's "hw to calendar" Sheet triggers a script that turns the info into a Calendar entry. (This is why the tweets are listed in order of date+time tweeted and not the due date.)

There is probably a way to do this from within the master "Tweets by @Ms_McPhee" Sheet, but when I was setting this up last summer, I couldn't find anything straightforward apart from this script I grabbed from a blog post (which I've lost the link to, so thank you anonymous blogger for your help). I know just enough about coding to add to someone else's code, so it may be clunky, but it works.

When I was testing this last summer, I had no difficulties. Naturally when school started and I was tweeting in earnest, a couple of strange little bugs appeared. Namely, each time the Sheet changed the script ran twice and I got two identical Calendar entries. Also, any time I tweeted anything, homework or not, or was retweeted, or was even mentioned in a tweet, it "changed" the Sheet (even if there was no actual change to the content), triggering the script. Twice. Which created more identical calendar entries. Sometimes I would have upward of 12 identical homework (or even worse, test!) entries on one day. Some of the students were a little startled, and I was spending far too much time in Calendar deleting superfluous entries.

To solve the second problem, I threw in a validation process. You'll need to add the following formula to cell M1:

=COUNTUNIQUE(C:C)

N1 also has a number in it, added by the script; you should leave it blank to begin with. The script now checks the number of essentially non-blank (or unique) rows and compares it to the number in N1. If N1 is less than the number of rows (meaning we've just added new homework information to the Sheet), it will run the calendar function and set the number in N1 to the number of rows. Otherwise, it's a fake "update" and the script can ignore it. It works well; now I can participate in Twitter chats without stressing out my students in the Calendar. The only thing I have watch is when I tweet out similar homework for different sections of a course to make them slightly different; usually all I need to do is change a punctuation mark.

I haven't worked out what's going on with the double posting, though. It now seems to be happening more or less randomly.

Here's how to  get the script running. From the Tools menu, select "Script editor."

A new window will open. Delete the older code, and copy the code below in its place . it's a wee bit long, because my rule is to never delete anything but instead comment it out in case I want to use it later. I grabbed most of this from the internet; the main thing I added is the row counter business.

I'll explain what a few of the lines do. In general, a // in front turns the rest of the line into a comment and a semi-colon finishes an instruction.

• Row 4 tells the script which calendar to use. To get the calendar id, click on the triangle next to the desired calendar and choose Calendar settings.

Scroll down to where it says Calendar Address. It's the code next to "Calendar ID," oddly enough. Copy that into the space between the quotes.

• Rows 8-22 say which columns contain which pieces of information is in. It's a letter-to-number cipher, so A=1, B=2, etc.The start date is in column 1(A), the title in 2(B), the description in 3(C), the row counting cells are in 13 & 14 (M & N).

Rows 24-55 are the main script function, getLatestAndSubmitToCalendar, which is pretty much what it says on the tin.

• Rows 26-32 get information about the sheet, including the number of  rows (which I don't think I need, but they were in the original script and so I left them. I'm not sure why). The original script took the information from the last row of the sheet, but importrange() turns that into a bottomless pit, which is why I reverse sort them.
• Rows 34-47 get the actual date, title, description, and row-counting information from the Sheet.
• Rows 50-55 run the createEvent script only if a new row has been added to the Sheet, and updates N1 to match the number of rows so a new calendar event won't be created if some other "change" is made to the Sheet.

Rows 57-70 are the createEvent function, which actually creates the Calendar entry. This is pretty straightforward. I set it up to create all day events because I think it makes the Calendar look less cluttered.

Before you can release it into the wild, you need to give your script permission to muck about with your Calendar. Save the script with a useful filename, then click Run -> getLatest...Calendar. During this run, a box will pop up asking to allow permission for the script to change your Calendar, etc. Let it. Then run it again to make sure it's working smoothly. I usually check it by manually changing N1 back to 0 (or one less than the number of unique rows), running the script again, checking that N1 has been changed, then checking the Calendar to see if the entry was created. You should get email notifications if the script fails to run.

The last step is to set the trigger. We want this script to run every time a new row is added (that is, when the Sheet is changed). Click on Resources -> Current project's triggers, or the stopwatch icon. If no triggers have been set, click on "No triggers set up. Click here to add one now.".  Make sure "getLatest...Calendar" is selected under Run. Under Event, change "Time-driven" to "From spreadsheet," then choose "On change." Save the script, test it out by tweeting something, and you're now automatically turning your tweets into calendar entries.

So there you have the somewhat involved process of turning tweets into Calendar entries. Please let me know if you have any questions or  suggestions -- especially about that nefarious double-trigger issue!

Importing data to a new Sheet (part 4)

Update 17-03-17: Since originally writing these posts, I've learned more about what query() really does, so I'm updating some references.

Part 4 of an going series dealing with taking tweets about homework, sorting them, putting them on the course website, and creating Calendar entries. The story so far...

• Part 1: Automating homework tweets to calendar entries using IFTTT and scripts (the overview)
• Part 2: Sorting course tweets in Sheets using query() 
• Part 3: Publishing a Sheet to the web

This (mercifully short) post will talk about how to link data in a parent Sheet to a completely separate Sheet. I need to have a separate Sheet for each course to run my calendar script, but there are any number of reasons you might want to link Sheets instead of just making a copy. If you want to embed only certain columns or tabs in the new Sites or in Classroom, it's easier if you import the data into a new Sheet first. The other reasons all pretty much have to do with the fact that if you make a change to the data in the parent Sheet, the new Sheet will update as well; a copy is not linked and won't update.  

It's really easy to do. Create a new sheet (I call mine "CourseCode hw to calendar") and copy the following code into cell A1:

=query(IMPORTRANGE("1hgVADAoR-TlKYPGawmMCmxaQKZ77lkKpbSdNozjzcCQ","Tweets for MCF3M!U:W"))

Importrange() will link to the data in a separate Sheet; it will update when you open the spreadsheet. (Query() lets the spreadsheet update without you having to open it, which is what automating is all about. sort/filter the data and is not strictly necessary here.) You will need to change both the red and the blue text for your Sheet.

• The red text is the spreadsheet key of the main sheet, which you get from its url: https://docs.google.com/spreadsheets/d/1hgVADAoR-TlKYPGawmMCmxaQKZ77lkKpbSdNozjzcCQ/edit#gid=652985193
• The blue text is the name of the tab you are getting the data from, followed by a ! and the columns you want. In this case, it brings in the columns with the sorted and reverse-dated tweets.

Note: When you're setting up the spreadsheet for the first time, you need to give access to IMPORTRANGE() first.^*

Give permission, and wait for your new sheet to populate...

Once permission has been granted, you'll have a nice Sheet you can embed (or run a script on) with no worries. Any changes to the linked cells in the parent Sheet will automatically update in this one.

The final installment of this series will talk about the script to create calendar entries from this data.

^*At the beginning of the year, for each sheet I set up importing from the main one, I needed to copy the importrange part into another cell of the spreadsheet, click on the red error triangle, allow access, then delete that cell. When I was trying to grab screenshots for this post, new sheets already seemed to have access to the main one, which was a nice surprise. It looks like my setup time for new courses will be slightly reduced!

Publishing a Sheet to the web (part 3)

Previously, on equals mc²...

• Part 1: Automating homework tweets to calendar entries using IFTTT and scripts (the overview)
• Part 2: Sorting course tweets in Sheets using query() (part 2) 

In this post I'll show you how to create the code to create a link to a webpage of (or embed) Sheets^* data and talk about some things you have to watch out for. The 'Publish to the Web' feature is the other thing^** that makes not just Sheets, but all of the GAFE tools supremely useful. I use it for lots of things, including self-grading quizzes and drill sheets, but I'll talk about it in the context of embedding my sorted homework tweets on the course webpage.

Update 17-03-17: You could probably do a lot of this with something like Awesome Tables, but if you're a noodler like me, you might do it this way.

Something to note: publishing to the web lets anyone who can visit your webpage (or who has the link) see your data; it makes a copy of the sheet and that's what is published. Any permissions you had on the original Sheet won't be copied over, and people might be able to see the underlying data for charts.

First, decide whether you want to create a web page, PDF, or a spreadsheet format, or if you want to embed the Sheet/tab^*** in an existing webpage. Embedding a sheet looks like the screenshot above. Publishing it as a separate webpage looks like this:

Here's how to do it. I'll talk about creating a link first, since embedding means a bit more work if you have columns to hide.

If you want to link to only part of a tab or more than one of the tabs (but fewer than all) in a sheet, you have to hide everything you don't want seen. Highlight any columns you want to hide (in the case of the homework tweets, that would be columns A-T), click on the triangle in the column header, and select "hide column."  If you want to share multiple (but not all) tabs, click on the triangle on the tabs you want hidden and select "hide sheet." Bear in mind that if you unhide anything, it will be visible on the web until you hide it again. I also take the time to get rid of the gridlines to keep it looking uncluttered.

Once you've taken care of that, you're ready to publish. Click on File->Publish to web.

The default choice is to create a link to a web page, but you can also create a PDF.

The "automatically republish when changes are made" should be checked -- unchecking it creates a static page that won't update.

If you only want to publish one tab at a time, select it from the list. If you want to publish more than one tab in the same page, select "Entire document."

I've been playing around with this a bit, and if you select several tabs from the lower menu, it will only publish one tab at a time. It looks like if you want to publish each tab separately, you have to close the 'publishing' pop-up and reopen it, choosing a separate tab to publish (getting an entirely new link). You would need to check off each tab you want to publish from the lower menu. However, I might be wrong and it might stop publishing the previous tabs. (I should say that I import my columns U-W into an entirely new Sheet for each course because of the calendar script, so I don't have to worry about hiding columns and I have only ever published one tab at a time from any given sheet.)

Here's what it looks like if you publish more than one tab at the same time using "Entire document." The user can click the links at the top to visit the different courses.

In the above example, I tried choosing to publish only the first four tabs, but it published the last one anyway. I hid Sheet1 and another tab, and as you can see they didn't get published. Hide what you don't want others to see, because the selection defaults to only one tab or the entire (viewable) document.

Any time you change the original Sheet, the webpage (or PDF, etc.) will also update. If at any time you want to stop publishing the Sheet, go to the publishing tab and click "Stop publishing" at the bottom. That way even if someone still has a link, they'll get a "We're sorry; that page is not published" error message.

Embedding a Sheet works the same way for the most part, except that you get html <iframe> code that you add to your site or blogpost. The main difference is, because you're embedding a copy of the Sheet, it will publish every column, including any you have hidden. To fix this, you need to either a) bring only the desired information into a new tab or Sheet and embed that (this is what I do) or b) manually add code to limit the range you embed. This post from hellotumo.com explains how to do that.

You will need to manually add width and height codes since the default is pretty small. I add width="90%" but keep the height the same since I only want the most recent homework tweets to be visible; users can scroll down to see older tweets. The code will look something like this:

<iframe src="https://docs.google.com/spreadsheets/d/1hgVADAoR-TlKYPGawmMCmxaQKZ77lkKpbSdNozjzcCQ/pubhtml?gid=769621547&amp;single=true&amp;widget=true&amp;headers=false" width=90%></iframe>

Below is what happens if you embed a sheet (with multiply tabs) and try to hide columns.

I did hide columns A-T in the original sheet, but embed doesn't care. It does care that I hid a few tabs, which is nice of it.

In my next post, I`ll talk about how to import data from one Sheet to another. The final post will deal with the script to create a Calendar entry.

^*If you are using Sites or Classroom, embedding the Sheet is easy; however I recommend importing the relevant information (such as the formula for column U) into a new Sheet since there doesn't seem to be a way to embed only one tab without hiding the rest and it won't let you hide the other columns. Here's hoping that the new Sites gives us an html code widget soon.

^**The query() function being the best thing, of course. Oh, and that collaboration business.

^***Again, by "tabs" I mean the different "sheets" within any one "Sheet," because that's just confusing.

Sorting course tweets in Sheets using query() (part 2)

Update 17-03-17: Since writing these posts, I've learned more about how query() actually works and the joys of arrayformula(), so I've updated some references below and modified the code at the bottom.

PS: TDSB teachers! Don't forget about Google Camp 4.0, March 4. Register on Key To Learn.

In this post, I'll discuss the nitty-gritty of how I take the tweets gathered by IFTTT into a Google Sheet and sort them out, ready to embed them by course on my website (see this post for an overview of the process).  I use them for homework tweets, but this information is useful for any data you've gathered into a spreadsheet (say, using Forms) and want to separate the information and embed the results somewhere. I can see it being used for sports teams, different subjects for one primary class, lesson plans, etc.

I've taken screenshots of my formulas and sheets and will go through in detail what each cell does. At the end of the post, I've written the formulas in a form that (hopefully) you can cut and paste into your own sheet, as well as links to a couple of my sample sheets so you can see what they look like. I'll talk about how to embed them and  the script to turn them into calendar entries in upcoming posts. This one will be long, image-heavy, and detailed. You have been warned.

First, a note about vocabulary. The tabs at the bottom are called 'sheets,' as in Excel, but since the whole thing is also called a sheet and that will get confusing, so I'm just going to call them tabs.

Once you've set up your IFTTT recipe to collect your tweets, open a new tab and rename it to your first course name. I use "Tweets for Coursecode," but the name isn't that important; you can do it by period or subject, or team, depending on how you want to separate your tweets. Recall that I structure my tweets thusly:

CourseCode hw for next day:: what needs to be done. for regular homework tweets and CourseCode quiz date_in_accepted_format:: what's on the test/quiz or review. for tests and quizzes. For example:

SPH3U3 hw for Fri:: prepping for presentation; continue reviewing for test.

MCR3U quiz 2017-02-07::simple vs compound interest

The first line of formulas goes from column A to U.  I'll break this into two parts: from A to L and from M to U.

Here's what each cell does:

• A1 uses query() to pull the date/time, content, and link of each tweet with the given course code (in this case, SPH3U3) into the tab. Column B in Sheet1 is the tweeter's name; since I'm only pulling tweets by me, I don't need it. I don't really need the links, either, but they help me differentiate between real and fake tweets (more on that in a bit). A holds the date and time.
• B1 and C1 are empty, waiting for the content and link data from Sheet1.
• A quick comment about query(): it's one of the coolest things about Sheets and what elevates it above stand-alone spreadsheets. It turns your Sheet into a real-time database, which means that everything I talk about below is automated without your having to open the spreadsheet. Set it and forget it. I'll do a future post about my love for query() and everything I use it for if you don't get a chance to catch my Sheets workshop at Google Camp 4.0. Update 17-03-17: Don't get me wrong, query() is still awesome, but it's Google itself that lets you set it and forget it. The awesomeness of query() comes from the way it lets you filter/sort and do math on data at the same time. Here, we're just using the filter & sort bits.
• D1 is just a spacer.
• Update 17-03-17: It's not in the screenshots, but I've added arrayformula() to each of cells E1 through Q1. What it does is let me do away with that copy-paste thing.
• E1 turns the date and time into decimal values and adds them together. I use this to order the tweets in columns U-W so the most recent is on the top. This is only needed for the calendar script.
• F1 uses the split() function to take cell A1 ("October 13, 2016 at 10:54") and separate it into date and time. F holds the date.
• A few notes about the split() function: 
• It's case-sensitive.
• if you leave off the 'false', it will split the string at every separate instance of  each individual character of your splitter. In the above example, split(A1," at ") would result in "Oc", "ober", "13,", "2016", "10:54" because it will break the string at the first t, each space, and at " at "). Let's just say that can get super-fun when you're splitting using a word containing the letter e.
• Notice that splitting the string leaves out whatever your splitter is.
• You need to leave enough columns after the column with split() to contain all the separated parts, otherwise it won't work and you'll get a #REF! error ("Array result was not expanded because it would overwrite data in O3", say.)
• The split() function can't handle blank cells, so until you have something for it to split, it will return a #VALUE! error. Don't worry about it.
• G1 is blank to hold the time data from F1's split().
• H1 separates the content of the tweet into two parts. I use "::" as a splitter so that I don't accidentally break URLs apart. H holds the course code and whether it's homework, quiz, or a test; if none of these (and there's no '::' in the tweet), it will hold the entire tweet.
• I1 contains the meat of the tweet. This is the part that will go into the description of the calendar entry.
• J1, K1, & L1 are insurance spacers in case I need the extra room for some weird separation thing that I wasn't expecting. I haven't so far, but it's nice to be sure.

Here's what the first set of formulas look like with actual data: 

Oh, so about those fake tweets? Row 28 in the screenshot above is an example; you can tell because there's no link in column C. Sometimes, especially when I'm extra busy and haven't scheduled my tweets, I forget to tweet out a quiz date or homework before the due date, but I still want it recorded. In that case, I just go into Sheet1, copy & paste the date/time format into the bottom row, make any changes I want, then type the 'tweet' content as usual. This is also how I test things out without going public on Twitter. And how it seems that I went back in time and tweeted about a quiz that had already happened.

The formulas for rows M through U look like this:

• M1 labels the tweets that are homework, quiz, or test (as opposed to general info or links). This becomes the title of the calendar entry. If it's none of these, it leaves the cell blank.
• N1 is a spacer left over from when I was first playing around with how to do all this.
• O1 is the cell that pulls the date of quizzes and tests out of the first part of the tweet. This is mostly for the calendar script: calendar entries for homework are created on the day it is tweeted, but tests/quizzes get entries on the day of the test/quiz. If this is a test/quiz tweet, it splits the tweet into two parts; otherwise it just uses the date of the tweet. I used "est" and "uiz" as splitters so I don't have to worry about whether or not to use capital T or Q. Incidentally, this only looks in H1 (before the '::', so I can talk about tests, quizzes, and estimations in the content of the tweet without worrying about it being split up).
• P1 holds the date of the test or quiz, otherwise it's blank. It's important to tweet the date in Sheets-approved date format. Protip: 2016-12-14 is approved, 16-12-14 is not.
• Q1 pulls all the dates into one column to make it easier to use query(). If P1 is blank, then O1 is the date of the tweet; otherwise, it takes the date from P1.
• R1, S1, and T1 are just spacers.
• U1 grabs the information that I'm going to embed in my website as well as turn into calendar entries. I only want the date (Q), title (M), and description (I), but obviously you could choose any columns in any order, For the calendar script, I need them listed by the order tweeted with the most recent on top so I use column E, but you could order it by Q if you wanted them by due date. U holds the date.
• Leave columns V and W blank to hold the title and description.

Here's what they look like with data:

Looking at U26-28, you can see that the dates are out of order because I tweeted out the test date a week before.

In my next installment, I'll talk about how to take that information and publish it to the web, either by creating a link to a webpage or by embedding the Sheet. The last installment of this series will talk about the calendar script.

****

Here are the formulas for the first row in the course tabs in the original tweets sheet (Tweets by @Ms_McPhee). Copy cells B1:T1 down 100 or so rows (or however many you think you'll need; you can always copy more later). Do not copy cells A1 or U1. [Update 17-03-17: You don't need to copy these down because we are now using arrayformula()!] Change the information between the single quotes as needed. Until you have a tweet in the row, columns E, F, H, O, and Q will say "#VALUE!". This is because the split() function can't handle blank cells.

=query(Sheet1!A:D, "Select A, C, D where C contains 'SPH3U3'")

=arrayformula(DATEVALUE(F1:f)+TIMEVALUE(G1:g))

=arrayformula(split(A1:a,"at",false))

=arrayformula(split(B1:b,"::",false))

=arrayformula(if(isnumber(search(" Test ",H1:h)),"Test",if(isnumber(find(" hw ",H1:h)),"Homework",if(isnumber(search(" Quiz ",H1:h)),"Quiz","")))))

=arrayformula(if(M1:m="Test", split(H1:h,"est ",false), if(M1:m="Quiz", split(H1:h,"uiz ",false),F1:f)))

=arrayformula(if(isblank(P1:p),O1:o,P1:p)

=query('Tweets for SPH3U3'!E:S, "Select Q, M, I where M='Homework' or M='Test' or M='Quiz' order by E desc")

Google Camp is coming...

TDSB Google Camp 4.0 will be Saturday, March 4. At the risk of seeming like a Google Groupie, I love Google Camp; I've learned and been inspired by so much at every session I've attended -- not least during the demo slam at the end. I'm excited and more than a little trepidatious this year to be joining some amazing speakers. During the day, there will be a room with helpful helpers available if you've been running into difficulties with GAFE and need someone to walk you through the solution.

More info will be available soon at http://bit.ly/tdsbcamp, but TDSB teachers can register now through Key To Learn. This will sell out, so act now!

The beginning... of a sort

I've been meaning to start blogging about my teaching for a few years, and I'm finally making a start.

My goal is to blog at least once every two weeks, although I will probably be a bit more prolific at the beginning since I have two months of teaching to catch up on. My goals for this year are to

• increase the number of flipped lessons to at least one, possible two (or three, in the case of the pre-AP classes) per unit per course;
• to increase my class use of GAFE (or GSuite, as I suppose I must learn to call it, which has the sole benefit of not wondering how to pronounce it); 
• formally get my students into a growth instead of fixed mindset
• to finally start (and follow up on) a non-robotics Sci/Eng design club; and
• my standard, yearly goal of trying one new/reincorporating a forgotten teaching technique per month.

Here be dragons...