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")