I'm still trying to put together my blogs for the fall PD I've been doing, but for now I wanted to share a few things I'm really excited that I figured out how to do.
Inspired by Robert Prior many years ago, I started coding a lot of Excel spreadsheets as randomized drill sheets on many topics. My goal for this year is to get most of them up as Google Sheets so I can publish them to the web and students can go straight to my website to print out an infinite number of sheets.
Anyway, I've also been working on adding new, richer sheets to my catalogue. In particular, I was very jazzed last year when I figured out how to get a spreadsheet to draw spectrographs (hint: error bars). And last night I spent far too long working on how to get a Hertzsprung-Russel diagram (yay for the Bubble chart, boo for the fact that the labels are arbitrarily hidden when you go to a larger font).
Behold, I give you my Spectrograph and HR diagram drills for the grade 9 space unit (and Earth and Space science, too).
I'm still working on the HR diagram answers; I have to figure out how best to get it to choose whether it's on the main sequence or not. Also, I think I need more white dwarf stars, because that area is looking pretty sparse.
However:
If you'd like to see my other drill sheets, head on over to the drill sheet section of my course webpage. It's very much a work in progress. I have a lot more sheets than I've listed there.
I have completely fallen down on my plan to blog at least once biweekly. I'm going to catch up on blogging all the PD I've been doing though, and I'm going to try to get them done before the break.Consider it a pre-holiday present. For TDSB teachers, make sure to sign up for the Technology-Enabled Learning sessions (aka the after school workshops) on K2L. For LN24, there are a number of sessions you can still attend this and next month. Note the change to the Virtual Library session. Sign up now!
Wednesday night I attended York U's open-house night for high school physics teachers. hosted by the Physics and Astronomy Department. It's a great evening of PD, not just because they serve dinner with wine, but also it's a chance to learn about some of the ground-breaking research taking place right now. The topic this year was "How large is the proton? ̶ the proton size puzzle".
During dinner, there were three 15-minute talks, which is an excellent length. The first was from Dr. Randy Lewis. He talked about how my previous 3-quark understanding of the proton (seen below in the basic Wikipedia image)...
...is incomplete; the masses of the three quarks make up less than 1% of the mass of the proton. In reality, at least as far as we currently understand, what we have is much more complicated: at any given time, uncountable pairs of quarks and anti-quarks are appearing and disappearing (along with the associated gluons), and the whole thing somehow makes sure that three valence quarks are always unpaired, as in the picture below, grabbed from phys.org
The blue circle isn't really there. As with everything, protons are mostly empty space. The green circles represent quarks, the orange antiquarks, and the springs are gluons.
Because of this hurricane of energy, current theoretical attempts to calculate the size of the proton aren't there yet, so we need to turn to experiment (the subject of the next two talks).
Dr. Eric Hessels firstly blew our minds by telling us that because of this – We can determine the size using atoms – but atoms with electrons and atoms with muons give different answers.
Dr. Marko Horbatsch – Maybe scattering electrons off of protons can determine the size – but maybe it can’t.
There are some fantastic PD opportunities coming up in the next few weeks.
Google Camp 5.0 will be on November 4; sadly it has already sold out, but you can put your name on the waiting list and some of the sessions will be simulcast on Twitter. I'm thrilled to be joining some amazing speakers; I'll be presenting a follow-up session to my Flipping Your Classroom session from March -- I'm going to try modelling a flipped lesson! There will be pre-session homework (if you so choose)! I'm very excited to be talking about EquatIO and Desmos, which are two really amazing ways to take math and STEM digital. As usual 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.
The TDSB's Renewing Math Summit on the Friday, December 1 PD day is offered to secondary teachers. The focus is on Teaching/Leading in an Equitable Classroom and 21st Century Global Competencies. I'll be giving a session called Rope-a-Slope: Inquiry in Grade 9 Math where a simple piece of rope and a measuring tape and guided inquiry can lead to learning about relationships between variables, slope, direct and indirect variation, and more. The other sessions look amazing as well -- I'd love to be able to attend them all. There might also be a drop-in room to learn more about digital math tools like Knowledgehook, Desmos, Geogebra, etc.
Registration for TDSB secondary teachers is on K2L until November 23, or they reach capacity.
Note: Google Camp 5.0 will be Saturday, Nov. 4. Registration is open for TDSB teachers on K2L. This event always sells out, so register soon! If you're interested in presenting, submissions are open until Monday, Oct. 2. The TDSB's Renewing Math Summit will be Friday, Dec. 1; you can still submit a proposal until Sept. 30. Yes, that's today. Hurry!
When I did my physics honours specialist with John Caranci way back when, he told us that one of the easiest ways to become a great teacher is to try or adopt one new technique per month. Well, I'm still working on that (I probably average 3-4 a year), but this year I'm going to really make the effort to try them several times per month.
I've already made the first change by getting the students used to grouped tables -- a bit challenging in one of my classrooms which has fixed benches, but I'm trying to make it work -- and I started my October technique a bit early because I couldn't wait.
I was inspired by this blog post by Sara Van Der Werf to try a Stand and Talk with my grade 11 mixed math students last week. To summarize, the old-style "share with your neighbour/elbow-partner/TPS" doesn't really work most of the time. Sara has found that getting the students to stand up and walk across the room to talk to another person and giving each pair a paper with something to look at with the instruction "notice 10/20/50 things about this" really increases student engagement. Her post is excellent, with specific instructions on how to make it successful and a lot of math examples to use.
(By the way, the link to the "rumors" group learning routine at the end of Sara's blog post would be great for the prediction part of POE or for review.)
I thought mapping diagrams would be a good place to try this. We'd looked at domain and range and function/NAF. I prepared this picture for them to look at and notice at least 10 things (yes, it's supposed to be a big number).
This is my revised version
Did it work? Mostly. I wound up grabbing the wrong folder and left the students' copies of the diagrams in my office, but I did put them up on the screen. Not ideal, because on my original version the arrow heads were not as obvious and I used too small a font for the sets of points and the labels, so they were a bit hard to read from the back of the room. There was a bit of "I don't know what she wants, do you know what she wants?" at the beginning, but after I encouraged them to go for the obvious first and used Sara's prompts ("I should see you pointing," "What do you wonder?" "Everything on the screen is there for a reason. What else to you notice?"), I heard some good discussions. And once we were talking as a class, I had volunteered suggestions right away instead of the usual silence.
Some of the suggestions:
there are circles on the page
there are numbers in the circles
the numbers go from negative to positive in both of the left circles
there are no negative numbers in the right circles
the numbers go in order
there are 4 numbers in one left circle and 3 in the other
both right circles have 3 numbers
I was a bit surprised that nobody mentioned the arrows, but that could be because the arrow heads wee small and didn't really register, but when I pointed out that there were arrows, more suggestions came in thick and fast:
an arrow goes from the -3 to the 3
another arrow goes from the -2 to the 1 (etc)
two arrows go to the 3 in both right circles
there are two arrows going from the -1 in one circle, but all the rest have only one arrow
Nobody noticed the connection to the coordinate pairs above the diagrams, but I think that is because the font was too small and they didn't really notice it. Once I asked "do you see a -3 anywhere else on the page?" the penny dropped.
Oh! The arrow goes from -3 to 3, and there's a -3 and 3 together above.
Same with the -2 and 1.
That first circle is all the first numbers and the second is all the second numbers
At this point I switched to Socratic questioning, and we established that the left circles were the x's, or domain, and the right circles were the y's, or range; none of the numbers were repeated and were in order from most negative to most positive; that one was a function and the other wasn't; and that you could tell whether it was a function or not by the number of arrows coming from each of the points in the domain. I then told them these were called mapping diagrams and had them create some from sets of points.
We stood the whole time we did this, and nobody complained. This was very surprising to me because there are a few students in that class who complain as a matter of principle, but who were actually mostly engaged in the activity and even offered a suggestion or two.
So will I be using stand and talks again? You bet. I'm already scheming my next picture. I love the way I could work concept attainment* into the notice and wonder. I need to make I also focus on the "what do you wonder" questions. The diagrams do require a bit of thought first, so I'm aiming to do two per month in my math classes to begin with and work up to once a week in all classes. I'm already planning on trying this as a way to introduce B-R diagrams, chemical formulas, and circuit diagrams later on in grade 9 science; and more immediately, rational vs irrational numbers, polynomials, like vs unlike terms in grade 9 math; standing waves in physics; and different forms of the quadratic function in the mixed math. That will do to start with, I think!
*I did my math honours specialist final project on concept attainment, and I keep meaning to work it into lessons whenever I can. Perhaps I'll do a blog post about it so I will remember to use it.
Last year while on leave I had the opportunity to watch a live webinar with Eric Mazur on assessment as a silent killer of learning, and I got really excited by one of the ideas he presented. Here's a video of that same lecture; the pertinent section starts at around 41 m 44 s and it's only about 6 minutes long. I recommend watching the whole video some time.
I love this idea. It's like test corrections, but without my having to grade the test first. Because of the nature of the test, the question level should be such that it should be difficult for any one student to get 80% by themselves. Lots of higher-order thinking skills, not so much of the recall.
I was hoping to try this method out with my pre-AP physics class several times this year, but I only got a chance to do it once right at the end in the electromagnetism unit. I opted to go the scratch card route, since coding a trouble-free non-mc group test would take more time and energy than I usually have in May and I also already have a nice bunch of conceptual mc questions (plus some shamelessly pulled from previous OAPT physics contests for extra oomph).
My test was 15 questions long. The students sat around trapezoidal tables in groups of 3-4 more or less based on their (self-chosen) lab groups -- the class is pretty homogeneous so that worked out fairly well grade-wise. I gave them 25 minutes to solve the questions on their own, then put the scratch cards on the tables. I also gave them individual white boards and let them use the blackboards if they wished. [One of my students is mute, and since I didn't let them use their phones, having a personal whiteboard for communicating was crucial.] They had the rest of the period (45 minutes) to redo the test as a group. Difficulty-wise, I tried to err on the side of the test being too easy since it was our first try (and I always tend to think questions are too easy when in reality, not so much).
I have to say, it was a lot of fun to watch. There was cheering. There were groans of agony. Most importantly, there was immediate feedback and learning... and I didn't have to mark it myself. Marks-wise, we went from high 50s to mid-90s, with most marks in the 70s. The marks are a bit lower than this class is used to, but I'm putting that down to it being the last test of the year and having rushed through teaching some of the material. I wound up just adding their individual marks to the group marks and making the whole thing out of 70 (one of the questions was a bit too confusing, so I made it a bonus).
Weirdly, not many of them used the whiteboards. I need to get the students using the whiteboards early and often in class so they are used to thinking things through visually.
I wish I had done this for the post-friction lab quiz. I am thinking that I will adopt this for the multiple-choice sections of future tests; since I'm considering moving to standards-based grading for the calculations/written explanations, I might get the best of both worlds.
On to the slightly more crafty section of the post.
I used 4x6" matte photo cards because I have a huge number of them at home, but you could probably use construction or even regular paper. There is also the online IF-AT test maker, but that is geared towards (very) large groups (minimum 125 cards). To send the cards through the "no, I really only want to print on letter-sized paper and maybe legal if you really insist" school laser printer, I used loops of masking tape to tape the wrong side of the photo card to a scrap piece of letter-sized paper and send it through. Using masking tape is important because it doesn't form an immediate permanent bond like clear tape does; you're less likely to tear the card when you remove it. Painter's tape would be even better for this. I had to experiment to see which side tore less.
Once you've printed your cards and answered them (I used a red checkmark), you make them into scratch cards. How to DIY: some quick Googling brought me to this site. Essentially, you need some clear tape, acrylic paint, dish soap, and a brush.
You tape over the bubbles, then mix 2 parts paint to 1 part dish soap, and apply. Ideally, you'd apply thin coats so you don't get a lumpy paint job, but frankly the bubbles are so small I don't think it matters. I started by using gold paint but it was taking too long to become opaque -- I got up to 5 coats on my tester cards and you could still see through the paint (on both sides if you held it up to the light), although it's possible I originally had too high a ratio of soap to paint. I added a large dollop of grey paint and presto! I only needed 2 coats to cover my bubbles.
You could make a stencil if you wanted to get really finicky and avoid overpainting; I just scraped off the worst of the excess paint where I could.
I also made scratching tools by cutting up an old plastic membership card. The flat edge was pretty much the size of a bubble, so they wouldn't "accidentally" scratch off part of the wrong bubble. The kids loved scratching off the answers; this would be fun to do as a vocabulary lottery card-type thing or a fun take on a homework pass. And it's reusable!
I'm also going to explore doing this as a computer exercise because multiple choice is great for conceptual questions, but a bit of a pain for calculation exercises. I like that in Mazur's version, the group members' answers come up and that's what they discuss. I'm sure Mazur got someone to code specialty software, but I think it could be done with GAFE tools using a combination of Forms, Sheets, my self-grading quiz tutorial, and the FormRanger add-on. The one difficulty I see is getting the students to write exactly what I put in as an answer, and how to let them know that they need to fix a small issue (say, sig figs or direction) as opposed to having completely the wrong answer.
What other ways could we use scratch cards (physical or computer-based) in class?
Update: I've added a link to the data in Desmos and TI lists below.
Yes, I jumped on board the "if you can't beat 'em, join 'em" boat.
Inspired mostly by Harry O'Malley's site, I brought a fidget spinner* and my phone to grade 11U math class one day and the students modelled the spin. The above graph is the one of my student's results. It's a bit wonky because in the two days since I had bought it, one of the end caps had fallen off, which made the central bearing ring shift off centre. This made it slow down a lot; the above graph shows a few tries at spinning. The student at the top of the post took an average of the cycles while other students just looked at the first.
To make a good video, mark one of the edges of the spinner so you have a reference point to track. The app I used is VidAnalysis Free for Android (for Apple fans, Vernier has an app for LoggerPro that lets you do the same stuff, if not more). Don't spin it too quickly unless you have much better equipment than I do -- I tried to analyse that lovely first video and got goobledeegook because it's spinning too fast for the video to capture properly.
You then pick reference points (known length and origin of coordinate system) and track your mark. You can skip forward and backward in time to get to the section you want to analyse. I goofed because I forgot that I had made a spin without my finger in the way; by the time I remembered, I had already invested too much time getting the other data. Hence the starts and stops.
I didn't want to take up class time getting the analysis ready, so we discussed what equation we were likely to see, and then worked on other problems. Before the next class, I made the analysis and turned the data into graphs in Google Sheets. The next class, I put the x-distance graph up on the screen and got the students to figure out the model. When they had an equation, I graphed it against the data (if you do this, remember that spreadsheets do trig with radians, not degrees).
It was a really good exercise, considering it's the first time I've officially used the VidAnalysis in class. We had some great discussions about the vertical translation (did I deliberately make the coordinate system off-centre? no, but I will next time because that led to interesting math); how to deal with the increasing period; how the amplitude of our function compared with the actual measured distance.
I've since shifted the centre bearings back and made another, better video analysis. The screen shots are below:
What I like about this, mathematically, is a) how it shows the spinner slowing down; b) how it shows that I didn't hold the camera completely still -- notice that the "zero line" of the equation shifts up (nice for composition of functions!); and c) the x- and y-distances are essentially translations of each other (sin vs cos). I could have really expanded on this activity and got them to break the functions into different domains.
The velocities show the same math effects as the distances; this could be used to show that the derivative of sinusoidal functions are still sinusoidal (and how). If only Google Sheets would get their act together and let us connect points in scatter plots.
More screenshots. What I really like is that you can upload the data as a csv file to Drive.
Copy-paste makes it simple to create a spreadsheet.
Note that the Free part of the app means ads. I was still giving it a trial run, but I think I will upgrade to the premium version because it's a great little app.
I started the trig functions section by creating a periodic wave using a salt-shaker pendulum (an idea I cribbed from someone on Twitter -- I can't remember who it was, but I'd love to give her the credit). I now wish we had filmed the pendulum at the same time so we could compare our rough model to the data. Future ideas!
I have a graphing calculator assignment that grabs tuning fork data from a microphone; I get the students to model the function and work backwards to determine the frequency of the fork. We didn't get to it this year, but it would go well with this exercise.
If you'd like to use my data, feel free to make a copy of fidget spinner 2, or download the csv file. I'm going to import this into a Desmos activity and graphing calculator lists at some point; when I do, I'll update this post with links.Update:Here are links to the data in Desmos and as TI lists. *I meant to use one of the students' spinners in my MCF3M class, but ran into difficulties because that student wasn't in class the day we were supposed to do the model. Since I wanted a permanent mark on the spinner, I decided to not buy trouble and get my own. Plus, they're fun.
I did this exercise with the 11M students as well, but we wound up doing it as a class instead of individually.
PD on a Saturday? Yes, please, especially if it's Google Camp. Yeah, yeah, fangirl, whatever. There's always so much I learn, even when I don't quite make it to the sessions I wanted to.
The day started with an excellent keynote by Jesse Brown (yes, thatJesse Brown) about how to take advantage of disruption. His biggest point for me was, of course, fail. Building failure into the model is also a feature of the growth mindset, which I'm still struggling to bring to many of my students.
Speaking of failures, I utterly failed to go to any sessions because I decided at the last minute that my presentations needed gifs (and why can't I stop typing figs every time?), but I've been creeping everyone's Slides ever since. There are terrific resources there; you should check them out.
I'll end by including the slidedecks (and a how-to video) for my presentations, but one of the things I find when I'm preparing for presentations like this is that I always learn new things. For instance, while preparing for my first session -- which was about how to make a self-marking, non-multiple choice quiz that gives immediate feedback -- I discovered that I need to revise my previous blog posts, and specifically my comments about what query() does (because it doesn't do what I thought it did, for the most part) and because I also learned about arrayformula(). Always learning!
My second session was about tools and tips for flipping lessons. The most important take-away from this presentation, I think, is "What is the best use of face-to-face time with students?"
Part of my session shills for EDpuzzle, which if you remember I learned about at STAO in November. Since then I've jumped in with both feet and lately, I've been plaguing them with suggestions for new features.
What did I learn through prepping for this presentation? I reread Flipped Learning by Bergmann and Sams and remembered the post-video reflection forms one of the teachers uses. I had let those go by the wayside since using EDpuzzle in favour of embedded quizzes, but I think I will start reintroducing two/three-question reflection forms as exit cards.
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!
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...
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!
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:
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.
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 andCourseCode 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","")))))
