Category: Google

Volunteer Form Fixed

September 11, 2024 / Troy / 0 Comments

Well, after a good bit of thinking, I’ve fixed the Volunteer Form. This was the form where I created a way to track Volunteers. Since there is someone who needs to print the form, it needs to be visual and a way to select a day. You can read the write up on the Volunteer Form Update to get a bit of background.

I started by wondering if I could add a SORT command into the validation formula. That led me to thinking about this a little differently. What if instead of trying to sort all the rows and columns, I created a column that pulls from that range? So, I added a column and added the following formula into it. Then, I needed to make sure that was one column. Thus, I added FLATTEN as a command. This basically puts all of the dates into one column.

=SORT(UNIQUE(FLATTEN($M$2:W)),1,FALSE)

What this formula does is to grab all of the dates in the range (cells M2 through the end of column W) and sorts them in reverse chronological order. The UNIQUE part makes sure that I don’t have duplicate dates (because there are lots of duplicated dates). Now I can use this column for my data validation.

I still need to pull data from each of the other rows and columns, so I use the following formula for that.

=FILTER(‘Form Responses 24/25’!C2:H,(‘Form Responses 24/25’!M2:M=A2)+(‘Form Responses 24/25’!N2:N=A2)+(‘Form Responses 24/25’!O2:O=A2)+(‘Form Responses 24/25’!P2:P=A2)+(‘Form Responses 24/25’!Q2:Q=A2))

This is a basic formula that uses multiple filter criteria (basically one for each column needed).

Together, this has cleaned up the Volunteer Form to be much more user friendly.

This is also a transferable strategy. This same process can allow me to create forms and then present that information by date. I’ve used this to create an Announcement process to collect announcements. These can then be sorted and presented by date.

Volunteer Form Update

September 9, 2024 / Troy / 0 Comments

I’ve created a Volunteer Form for one of our schools (and written up the process). This allows a volunteer sheet to be printed every day with the appropriate information. Basically, a Google form is filled out that populates a Google Spreadsheet. The Spreadsheet has a tab where the Administrative Assistant can pick the date (populated from a range on the spreadsheet). Picking that date populates the names of volunteers who have volunteered for that day.

Screenshot of a spreadsheet. There is a date (5/22/2024) in Column A, cell 2. The header row includes the following: Time In, Time Out, Teacher/Location, First Name, Last Name, Trained, Registration, CBC, Volunteer Signature, Minutes Spent Volunteering. Fake information is included in line 2: Library, Joanne, Lett.

The one caveat with this is that the date isn’t in order. I would love to be able to sort the date.

The date is populated by using Data Validation Rules. The Criteria is “Dropdown (from a range)”. The actual criteria is:

=’Form Responses 3′!$M$2:T

This works well, except the dates don’t get filtered or sorted. The dates are in whatever order they appear on the spreadsheet.

Screenshot of a spreadsheet. The dates are in the following order: 5/22/2024, 5/29/2024, 6/5/2024, 5/10/2024, 5/20/2024, 6/4/2024, 6/11/2024, 1/8/2024

If anyone has any ideas on how to sort the dates, or another way to accomplish the same end result, I would be greatly appreciative.

A public version (with Fake Names and data) with Commenting Rights is available.

Cartoon graphic of 3 people sitting at desks. The word "Assessment" is written on a screen in front of them.

Assessment Reporting

June 11, 2024 / Troy / 0 Comments

I was asked to provide a data sheet for the NWEA assessment.

Interestingly, NWEA provides two spreadsheets with the necessary information. The first sheet (NWEAWinter20) is quite a lengthy sheet with all the testing information. This sheet includes testing dates, time on task, school, and the breakdown of all the testing. The second sheet contains the student information. So the first task is to copy the Student information onto a new tab in the Assessment sheet. Now we have all of the information on one spreadsheet (although two tabs).

Next, combine the student information with the assessment data. Good news is that since StateIDNumber is on both, this means a “simple” VLOOKUP.

I added five blank columns in order to merge the five data columns that I needed (StudentLastName,StudentFirstName, StudentMI, Grade, and StudentDateOfBirth). I left the first two columns (StateStudentID, and StudentID) in place. This allows me to use the first column for the LOOKUP. In cell C2, I added the following formula:

=VLOOKUP(A2,Students!$A$2:$G, 3,0)

The data that we want to copy over lives in columns C, D, E, F, and G. The data that we are matching (StateStudentNumber) is in column A. VLOOKUP’s must run off the first column. (* You can do similar things with different commands if you can’t use the first column.)

Let’s take a quick look the formula. We always start with an “=” to indicate a formula. “VLOOKUP” is our magic formula. This is a vertical lookup; meaning that the spreadsheet will vertically (thus the V).

Next, the A2 says to look at cell A2 to find a match. Thus, the StateIDNumber in A2 will be used to find the student on Student tab. Next, we have “Students!$A$2:$G”. Let’s break this one down. Since we are pulling the data from a different tab, we need to say which tab that is. Thus the Students!. Next, we add the range on that tab that we pulling from: A2:G. But, what about the $? We want to use absolute rows, thus the $. This means that the spreadsheet will always look through the whole column (note that we don’t close the G column so that we don’t risk “cutting data off”).

The next number maps to the column that we need. In this case, we are looking for the information in Column C (which is the third column, thus 3). Basically, wherever the information in cell A2 on our current sheet, matches the information in Column A on Student tab, the information in Column C (last name) will be inserted in this cell.

Finally, the last argument is a “0” meaning that we don’t have a header row. * We do have a header row, but we have skipped this in both spots for ease of use.

As we copy this formula down the column, the next row will change from:

=VLOOKUP(A2,Students!$A$2:$G, 3,0)
to
=VLOOKUP(A3,Students!$A$2:$G, 3,0)

The only thing that is changing is the first argument (A2 becomes A3) which is referencing the StateStudentNumber for the next row.

For our First Name column (Column D), we use the same process, but change the Column imported to 4. Thus, the formula for cell D2 becomes:

=VLOOKUP(A2,Students!$A$2:$G, 4,0)

Next, comes StudentMI (Column E). The formula for cell E2 becomes:

=VLOOKUP(A2,Students!$A$2:$G, 5,0)

Grade is our next column (Column F). You guessed it:

=VLOOKUP(A2,Students!$A$2:$G, 6,0)

Finally, Birthdate (Column G).

=VLOOKUP(A2,Students!$A$2:$G, 7,0)

Now we have all the students combined with all the assessment data. Cool! However, there is tons and tons of data here. There is a mix of Mathematics and Language Arts.

The request was to create a tab with selected information:

State ID
Student ID
LastName
FirstName
MI
Grade
DOB
RIT
Percentile
Quartile
Lexile
Goal 1
Goal 1- RIT
Goal 2
Goal 2 – RIT
Goal 3
Goal 3 – RIT
Goal 4
Goal 4 – RIT
Goal 5
Goal 5 – RIT

Still lots of information, but much more focused.

So, we create a new tab. We name the tab “Math”. We now have three tabs: NWEAWinter20, Students, and Math.

Now we need a formula to pull just the Math information onto the “Math” tab.

Here’s our formula:

=QUERY(FILTER(NWEAWinter20!A2:ET,NWEAWinter20!M2:M=”Mathematics”),”SELECT Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col29,Col31,Col32,Col77,Col83,Col84,Col88,Col89,Col93,Col94,Col98,Col99,Col103,Col104 WHERE Col6>3 and Col6<9″)

Again, let’s break this down. We need a QUERY to pull the information from another sheet. We need a FILTER to, well, filter the information. The FILTER allows us to pick the tab (NWEAWinter20) and use the Column M (which is Subject: either “Mathematics” or “Language Arts”) to limit the results to that subject. This will only pull the test results that are Mathematics.

The SELECT says to just bring over the columns that we need. The first seven columns are demographic information, so we pull all of those. Next, we have to convert Columns to Numbers. Better Solutions had a terrific conversion chart. This tells me that Column AC is 29. The list of Columns pulls those columns listed above.

The last part is WHERE. The request was limited to grades 5-8. Column F(Col6) listed the grade of the student. Since I wanted grades 4-8, I used “L>3 AND L<9”.

Now that we have this set, we can do the same thing for Language Arts. Since we have the hard work done, we simply duplicate the “Math” tab, rename that tab “Language Arts”. Then we change the formula in cell A2 from:

=QUERY(FILTER(NWEA_Spring_2024!A2:ET,NWEA_Spring_2024!M2:M=”Mathematics”),”SELECT Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col29,Col31,Col32,Col77,Col83,Col84,Col88,Col89,Col93,Col94,Col98,Col99,Col103,Col104 WHERE Col6>3 and Col6<9″) to =QUERY(FILTER(NWEA_Spring_2024!A2:ET,NWEA_Spring_2024!M2:M=”Language Arts”),”SELECT Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col29,Col31,Col32,Col77,Col83,Col84,Col88,Col89,Col93,Col94,Col98,Col99,Col103,Col104 WHERE Col6>3 and Col6<9″)

Simply changing the Filter criteria after the M2:M from “Mathematics” to “Language Arts” means that we are done.

Now we are ready to slice and dice the information even more. We can now utilize Pivot Tables, Filtering and more.

image of a blank spreadsheet, Columns A-D, and rows 1-3, with a variety of numbers rising above the spreadsheet.

Google Sheets Fun

March 7, 2024 / Troy / 0 Comments

I’ve previously posted about a volunteer form that I created. Apparently, there was an issue with the form, so I had to take a look.

Basically, there is a form that collects volunteers and the days that they will be volunteering. This all goes into a spreadsheet.

The spreadsheet is set up to allow for a printable sheet by each day. The day is picked in a cell that uses Data Validation. Previously, I had the data validation pulling from a range of dates. However, this meant that the dates weren’t in any real order. So, I thought about it and added a column to pull all of the dates from the range into a single column. This was done via the UNIQUE and FLATTEN commands. Thus,

=UNIQUE(FLATTEN(VDates))

was entered. This is great as it identifies UNIQUE dates, thus no duplicates, and FLATTENS the various cells into one column. (The range of cells has been named “VDates” via the Data | Named ranges menu selection. Thus M2:W is named “VDates”).

This is great, except that the dates are not SORTed. So, in the next column, I simply created a formula to sort them (

=SORT(Y2:Y,1,FALSE)).

The formula reaches into the previous column (Y), is only going to create 1 column, and FALSE is for is_ascending – i.e. this will be in reverse chronological order).

Now, the Date cell uses Data Validation that pulls from that one column. Everything is fixed and running fine.

Click to add tags…

Field Trips Spreadsheet

February 13, 2024 / Troy / 0 Comments

In chatting with an Administrative Assistant, I noted the frustration with them not knowing about Field Trips.

Hey, I thought. I’ve done a Bus Field Trip request form. So, what if I took the data from that and pushed it to a new spreadsheet with just the particular school’s request?

I created a new spreadsheet, wrote a quick QUERY(IMPORTRANGE) formula, and now they are all set. (I used Google Sheets for this.)

The formula goes in cell A2. (I copy and paste the header information, row 1, from the originating sheet).

=QUERY(IMPORTRANGE(“GOOGLE SHEET URL WITH THE DATA“,”TABNAME!A:R”), “SELECT * WHERE Col7=’SCHOOLNAME‘”)
Thus (some data truncated for privacy): =QUERY(IMPORTRANGE(“https://docs.google.com/spreadsheets/d/1……./edit#gid=1……2″,”D….w!A:R”), “SELECT * WHERE Col7=’Middle School'”)

Here are the import parts:

= to start a formula
QUERY – I want to limit the information that I’m pulling from another sheet
(IMPORTRANGE – brings the data from another spreadsheet
(URL – of the sheet that you are pulling from,
, – breaks for the next section
“Tab Name” – the name of the tab that you are pulling information from (Note the double quote marks here)
!
A:R – the column range of information that you are pulling from that spreadsheet (*Note the closing double quote marks)
,
“SELECT * – this selects ALL (*Note the double quote marks)
WHERE Col7 – the tells the formula which column to match – (7 = Col E)
=
‘Middle School’ (*Note the single quote marks around the EXACT text that you want to match)
” – Close the double quotes
) – Close the parenthesis from the one be QUERY and IMPORTRANGE

Once you do this you will see a #REF error. Click on that to see the “Allow Access” button.

Then I went into the spreadsheet and shared it with the appropriate people. I have also warned them not to type into any cell that is synced. They can use text effects (like strikethrough), or filters, but they can’t type over anything (that breaks the sync and will remove all existing data).

This at least provides them with the field trips for which a bus is requested (which is most of them).

Moodle vs Classroom Update

April 22, 2015 / Troy / 0 Comments

The last post pointed out some of the differences between Moodle and Google Classroom. Of course, Google being Google, they updated Google Classroom the next day.

The update addressed a couple of major concerns: multiple teachers and the ability to delay posts (create drafts). These are two very welcome upgrades. It demonstrates one of the advantages of Google’s iteration scheme. A weakness was discovered and addressed. Much joy in Mudville.

On the flip side, note that the teacher that is invited to edit the classroom has all of the same rights as the originating teacher – with the exception that the invited teacher can’t delete the course. In Moodle, a teacher has much more fine grain permissions that can be granted. The originating teacher in Moodle can give the co-teacher the right to just grade but not to change the content of the course OR to have the same rights OR just about anything that the teacher wants. Of course, this means planning and training. This is a great feature in the real world. Teachers who work together sometimes have different ways of accomplishing goals. Sometimes they have different understandings. A teacher knowing for sure that their content is safe and can’t be changed can be very reassuring. Also, this helps prevent accidental changes. I know many co-teachers who are working with two or three lead teachers. Keeping things organized is paramount. Accidental mistakes can happen.

Moodle contains many ways to prepare content ahead of time and schedule the delivery of content, activities and resources. Theoretically, one could schedule an entire year ahead of time (bad pedagogy for a classroom that meets physically though).

Also note that students can move/delete files from the Classroom folder. This breaks the connection between those files and Classroom. Hopefully, Google will resolve this issue soon as well.

The recent updates are a nice snapshot of the advantage and disadvantage of Google Classroom. It is still regularly updated. The updates address needs that users have. However, Google is not coming at this from a true educational perspective. They are still not addressing the underlying issues of pedagogy. They are focused on the S in the SAMR model.

Moodle is also frequently updated (every six months an updated version is released). Moodle also addresses teacher concerns. Moodle is also built on the concepts of good educational practice. However, Moodle is also more complex and needs more of a training commitment.

Neither tool is the right tool. Both have their place. Thankfully, teachers have options.

Moodle vs Google Classroom

April 20, 2015 / Troy / 0 Comments

Dr Jak Tangkuampien, over at Jak’s Thoughts, has a terrific write up about Moodle vs Google Classroom. I had been thinking many of the same thoughts, but he has written up before I did. Give his post a good read. But first, I’d like to expand on couple of thoughts about Moodle vs Google Classroom.

Underlying pedagogy

Google Classroom does a really good job of replicating what many teachers are very comfortable doing already. That is, Google Classroom allows teachers to create documents (templates) that are then distributed to the students to complete and turn in. Google Classroom organizes this nicely. This is analogous to creating a worksheet and passing it out to students. Classroom does make this a digital transaction, but it doesn’t fundamentally change the relationship or the process of education.

Moodle was founded with constructivist educational strategies in mind. Founded by Martin Dougiamas, Moodle was the result of his experience with distance learning in the Australian outback. He was also interested in social constructivist teaching strategies.

Developed by Teachers

Whereas Moodle is developed by educators with an educational bent, Classroom is designed by engineers geared toward education. There is absolutely nothing wrong with this. Engineers can come up with wonderful ideas.

Long term viability

Google has a tendency to iterate, iterate, iterate. That means that things change. Most of the time, this is for the best. However, as a long time user of Google Reader, well, as a former user of Google Reader, I can tell you that relying on Google to have a product around forever and lead to disappointment. Google killed Google Reader back in 2013. This was after Google had effectively killed off all the other RSS readers but making Reader free and phenomenal. Similarly, Google has retired many other products. A few come to mind:

Google Wave
Google Health
iGoogle
Knol

Google has no problem discontinuing products that it no longer feels deserve it’s attention. One must be aware that the discontinuation of a product is definitely a possibility. Thus, given this history, the end of Google Classroom is always a possibility.

Moodle is open source. Even if Martin Dougiamas (founder of Moodle) decides to move on, Moodle can continue to be developed. In fact, there are several forks of Moodle already in existence. Thus, Moodle is sure to be around for quite some while.

Flexibility

Google Classroom handles the distribution and collection of materials. Using Google tools, the teacher can also create quizzes that students complete. Moodle does these things as well. However, Moodle has a great more flexibility and functionality built in.

Jak’s Thoughts

Jak’s Thoughts is a nice write up. He includes things like multiple teachers, groups, the ability to create prior to distribution and more. Multiple teachers is a huge issue for my district. We utilize team teaching throughout the district. Moodle allows for that collaboration to happen easily. Teachers can allow other teachers to just view, to help with grading or to fully edit a course. This power is greatly needed.

Overall

Google Classroom is a nice tool. It has a beneficial role for teachers. The learning curve to get started is certainly much lower than Moodle. If you are looking for an investment that can lead to true change, Moodle is hard to beat. If you are looking to move toward using digital tools and taking a small step with low barriers, Google Classroom is a great choice.

Moodle & Google Classroom

September 7, 2014 / Troy / 1 Comment

Google Classroom is now available for Google Apps for Education users. Please note that you must be a Google Apps customer to use Classroom. Google Classroom comes with the tag line More teaching, Less tech-ing. One of the major points of Classroom is the move to paperless. The bullet points include:

Easy to set up
Saves time
Improves organization
Enhances communication
Affordable and secure

In the real world, how does this play out? I’ve only had a limited experience with Classroom so far, but here are my thoughts.

Enrollment

Originally, anyone in the district could sign in as a teacher. There was absolutely no way to control this. Whenever a user went to Classroom, the user was asked if they were a Student or Teacher. If they clicked the Teacher button, they were automatically given teacher privileges. This means that anyone could create a class and enroll students. Furthermore, the actual setting was for anyone verified or pending. Thus, once the district administrator went in, students could be rejected. However, if the administrator never checked, the students would always have access. More importantly, the potential for frustration, miscommunication and misunderstanding is huge. Google did pretty quickly add a setting into the administrative counsel to adjust the setting to verified only. This means that users can request to be a teacher, but an administrator must approve them before they are ready to use Classroom. This is largely because teachers are essentially just members of a special Group. This group can now be pre-populated by uploading a csv file. However, this is extra work. We already have all of our teachers in an OU (organizational unit) within Google. It’s frustrating that we can’t just use that.

Appearance

Google Classroom is simple and appealing in looks. There is a large header graphic and then a two column layout. The left hand column is narrower and holds information (Upcoming assignments, Class code). The right hand column contains the main feed (box to update status and a listing of previous posts). The posts come in two flavors:

Announcements
Assignments

This makes it very easy to, well, make and announcement or add an assignment. The feed looks very similar to Facebook or Google+. It is simple to read.

Use

From a teacher perspective, the assignment feed is very powerful. There is one button to click Assignment to create a new assignment. Then fill in a couple of fields (Title, Description, Due), click on an icon to upload an assignment, to add one from Drive, from YouTube, or a link. If the teacher picks a document that is in Google Drive, they can choose how to distribute it to the students:

Students can view file
Students can edit file
Make a copy for each student

This makes it really easy to create a template document and distribute to students. Essentially, Google Classroom creates a shared folder (called Classroom) in the teacher’s Drive folder. Each class that the teacher creates is a folder within the Classroom folder. Then each assignment becomes a folder within the class folder. Each students’ assignment is a file within this folder. This means that if the teacher is familiar with Google Drive, this will be familiar. However, I can easily see this getting out of hand quickly. We’ll need to monitor this.

Classroom and Moodle

The process of enrolling teachers and students is a pretty much a wash. Moodle is easier for us (district administrators) to get teachers enrolled. It also requires no additional attention by us. New teachers are automatically assigned the proper permissions when they are hired and put into an OU. For students, the process is very similar.

In terms of appearance, Classroom wins. It is very pleasing to eye. There are some discoverability issues, as in “What do I click?, but mostly it is very quick to learn and very not confusing. It looks like a 21st century application. Moodle is making strides, but still lags behind. Moodle does present all of the options that you have though.

In terms of use, well, this will need a few more posts. Suffice it to say that Classroom is very easy to use in terms of recreating the worksheet model. That is, if you consider a teacher’s job to be handing out worksheets, Classroom does this really, really well. It the major concern is to move to a paperless system, Classroom is a great choice. This is not to say that great things can’t be done. They can. However, I’m concerned that this become substitution only. Moodle has more powerful options, more opportunities for changing the way of teaching.

My thoughts

This is not an either/or for us at this point. We’ll offer both to teachers and hope that they take advantage of both. Classroom seems very limited right now. However, teachers also need to get used to using online tools. The learning curve for Classroom is lower than Moodle. It is my hope that Classroom will lead to teachers wanting more power and options and control. Then Moodle will be a great choice.