Tag: Classroom Technology

Posted on

Classroom Groups Manager in Google Slides: Seating Charts in Google Slides

I have developed a script-enabled Google Slides application for teachers to create and manage seating charts in Google Slides. Creating dynamic, balanced student groups is an essential part of classroom management. Whether you’re grouping students for projects, discussions, or activities, organizing them efficiently can take up valuable teaching time. The Classroom Groups Manager automates this process, providing you with an easy way to create groups, assign seats, and avoid problematic pairings. With just a few clicks, you can creating seating charts, swap students, change groupings and manage groups based on your needs.

 

Classroom Groups Manager Sidebar  © Avi Megiddo 2024

YouTube demo:

Key Features of the Classroom Groups Manager Tool

This tool leverages Google Apps Script to turn Google Slides into a versatile group creator and random student selector. It’s designed with teachers in mind and offers several features that make classroom management more streamlined and engaging.

New Look:

1. Create Random Student Groups

Teachers can generate random pairs, groups of 3, 4, or 5 students, or even custom groups centered around selected students. Whether forming small teams for a project or organizing students for discussion, this tool provides the flexibility to create groups that fit your classroom structure.

2. Bad Pairings and Wildcards

Certain students may not work well together. The Classroom Groups Manager allows you to save these bad pairings, ensuring they are not grouped together. Additionally, the tool prevents bad pairs from becoming the “odd one out” (wildcard) in groupings where the total number of students does not divide evenly into groups.

If a wildcard needs to be created, the system intelligently chooses someone not involved in a bad pairing and allows them to join a group of their choice. Currently, this feature is not active in circle arrangements or groupings around selected students, but works perfectly for fixed desk or standard group arrangements.

3. Random Student Selector

The random student selector ensures all students have an equal chance to participate. It randomly selects a student, highlights their name in fluorescent green, enlarges their desk/rectangle, and keeps track of the last selected student. This ensures more inclusive and balanced class participation, and that no student is called on repeatedly in succession.

4. Visual Group Layouts

The tool visually organizes groups directly onto the Google Slides canvas, providing an instant view of your classroom seating or group assignments. It places students into rectangles, which can be customized to fit your class’s layout. With drag-and-drop functionality, you can adjust the layout on the fly, enabling real-time flexibility.

5. Grouping Around Selected Students

This feature allows you to assign certain students to specific groups (e.g., group leaders or students needing extra support), while the remaining students are evenly distributed among them. This ensures balanced group dynamics and gives teachers strategic control over group composition.

 

6. Arrange Students in a Circle

For certain classroom activities, like group discussions or icebreakers, you may prefer students to sit in a circle. The tool supports a circle arrangement feature, which automatically spaces students evenly in a circular layout, while
taking bad pairings into account to avoid conflicts within groups.

7. Assign Names to Fixed Desks Without Rearranging

The Assign Names to Desks feature respects the current layout and orientation of desks that a teacher has manually arranged on the slide. Whether desks are rotated, repositioned, or grouped in non-traditional ways (such as bean bags, couches, or different seating zones), this feature simply assigns student names to the desks as they are.

This ensures that teachers can maintain their customized classroom environment, allowing for exact positioning based on various learning areas or furniture setups. Unlike other group creation functions that rearrange desks into grids or circles, this feature keeps your specific arrangement intact and focuses only on assigning names.

How to Use the Classroom Groups Manager Tool

Step 1: Add Your Students

You can input student names into the tool’s sidebar in multiple formats:

  • Comma-separated: “Ashley, Catherine, Derek, Liz, Milo, Raysen, Alexandra”
  • Space-separated: “Ashley Catherine Derek Liz Milo Oliver Raysen Alexandra”
  • A combination of both: “Ashley, Catherine Derek, Liz Milo Oliver Raysen, Alexandra”


    Step 2: Create Groups

  • After entering your students’ names, select the group size (e.g., groups of 4) or assign specific students as group leaders. The tool will automatically balance the groups, taking into account any bad pairings you have saved. You can also group students around selected individuals, perfect for strategic group formations.

    Step 3: Make a Duplicate Slide for Each Class

    For teachers managing multiple classes or needing to create new group configurations over time, duplicating a slide is essential. This ensures that previous groupings are preserved, allowing you to experiment with new arrangements without losing the original layout.

    Here’s how to duplicate a slide in Google Slides:

    1. Select the Slide: In the left sidebar of Google Slides (where all the slides are listed), find the slide that contains the groups or seating arrangement you want to duplicate.
    2. Right-Click the Slide: Right-click on the slide you wish to copy. A drop-down menu will appear.
    3. Click “Duplicate Slide”: From the drop-down menu, select Duplicate Slide. This will create an exact copy of the slide, which you can now modify as needed.
    4. Rename the Slide: Double-click the slide name (usually visible in the notes area or in the sidebar) and rename it according to the class or activity you’re working on (e.g., “Block 2: Science Project Groups”).

    By duplicating slides, you can create different group configurations for different classes or activities, while keeping a history of past groupings. This is particularly helpful if you want to revisit a previous arrangement or quickly switch between different layouts for various lessons. It also ensures that you don’t lose the initial setup when experimenting with new configurations.

    Practical Applications for Teachers

    The tool isn’t just for group formation. Here are a few other ways it can be useful in your classrooms:

    • Differentiating Instruction: Assign stronger students as group leaders, ensuring every group has a capable peer for guidance.
    • Avoiding Conflicts: Use the bad pairings feature to avoid putting certain students together, preventing disruptions or conflicts during group work.
    • Creating Varied Groupings: Over time, you can create different configurations by duplicating slides, saving past groupings, and re-using them to keep things fresh.

    Using the “Add Class Name/Label” Feature

    Labeling your slides is important for keeping track of different groupings or activities over time. You can easily add class names or labels to your slides to, for example, “Block 2: Science Project Groups”

By duplicating slides and applying unique labels, you can save past groupings for future reference. This means you can easily switch between different configurations and ensure students experience varied group dynamics over time.

The Classroom Groups Manager is designed to offer flexibility in how teachers manage student groups. Here are just a few ways the tool can be used:

  • Differentiate Instruction: Assign strong students as group leaders, ensuring each group has guidance from a capable peer.
  • Avoid Problematic Pairings: Use the bad pairings feature to avoid placing certain students together, allowing you to prevent known conflicts in group work.
  • Create Varied Groupings: Over time, you can create different group configurations and save past groupings by duplicating slides. This ensures good variation and prevents the same students from working together repeatedly.

Manually Adjusting Text for Long Names

Although the tool resizes text automatically based on name length, some longer names may require manual text fitting. To make sure the text fits properly within shapes, you can set the text padding to zero:

  • Select the shape containing the student’s name.
  • Open the Format Options panel.
  • Scroll to Text Fitting and set the left, right, top, and bottom padding to zero:

 

This ensures that even long names fit within the allotted space. Note that as of this writing, the Google Apps Script API does not allow programmatically changing the text fitting/text padding.

New Look!

If you read this far, this is what the sidebar now looks like (color coded), and with toast notifications (less user clicks requried).

Conclusion

The Classroom Groups Manager simplifies the complex task of managing classroom groups. Whether you’re creating groups for discussions, seating arrangements, or projects, this tool provides the flexibility and control needed to organize your class efficiently. With features like bad pairings, random student selection, and grouping around key students, it saves valuable time while hopefully fostering more dynamic and effective student collaboration.

Try it in your classroom and see how it transforms the way you manage student groups!

 

Posted on

A Wordle for Google Sheets Adventure

 

Introduction:

Creating a Wordle game in Google Sheets, with ChatGPT was a real back-and-forth. ChatGPT, with its vast pool of knowledge and ideas, was a huge help. But in the end, it was the practical skills—knowing what makes a good user experience and applying design thinking—that shaped the project. Every iteration brought new challenges and things to learn, highlighting the value of patience, persistence, and paying attention to the small stuff in making digital tools that people might enjoy using.

Inspiration and Research:

This project was part of my ongoing exploration of how to leverage AI for programming, education, and fun. My project was inspired by the widespread appeal of Josh Wardle’s original Wordle game. His creation showed how a straightforward concept could engage a wide audience, sparking my interest in developing something similar.

Two particular resources significantly shaped the direction of this project: Tyler Robertson’s Zapier article revealed the potential of Google Sheets to host a Wordle-like game without Google Apps Script (incredible!).  He creatively uses formulas, data validation, and conditional formatting, reinforcing my appreciation for Google Sheets as a versatile platform for creating interactive experiences.

Equally inspiring was Sajad Deyargaroo’s GitHub project, which uses Google Apps Script. I emulated his cell layout for the color-coded feedback keyboard. 


Software Development:

The script function selectRandomWord randomly selects a word from the ‘Words’ sheet to serve as the challenge for the game session. In terms of implementation, here’s a simplified pseudo-code representation of the selectRandomWord function:

In detail:

  • Accessing the Spreadsheet: The function begins by getting a handle on the active spreadsheet using SpreadApp.getActiveSpreadsheet(), which allows the script to interact with the spreadsheet content. 
  • Locating the Words Sheet: It then identifies the specific sheet named ‘Words’ where the list of words is stored. This is done through getSheetByName(“Words”). 
  • Random Word Selection: To select a random word, the function first determines the number of words available by finding the last row in the ‘Words’ sheet with getLastRow(). This is essential to ensure that the random selection is within the bounds of the word list. 
  • Generating a Random Number: A random row number is generated within the range of available words. This is where Math.random() comes in, which generates a number between 0 and 1, and then it’s multiplied by the lastRow to scale it to the size of the word list. The Math.floor() function is used to round down to the nearest whole number, ensuring you get a valid row index. 
  • Retrieving the Word: With the random row number in hand, the script retrieves the word from the “Words” sheet using getRange() and getValue(). The range is dynamically constructed using template literals to incorporate the row number. 
  • Formatting the Word: Finally, the selected word is converted to uppercase with toUpperCase() to maintain consistency throughout the game, as user input will also be converted to uppercase for comparison purposes.

Version 1 of selectRandomWord:

At the start of each game or round, this function is called to set the challenge word that players will attempt to guess—more on how this function changed later when I discuss word categories.

User Experience:


I initially considered using a checkbox that, when checked, would trigger the onEdit function which calls the checkGuess function. However, this required the user to perform multiple actions: typing the guess, navigating to the checkbox, and then clicking it. This method, while functional, did not offer the most fluid experience.

The original onEdit trigger:

I didn’t want users to have to type a word and then click the mouse — I wanted to allow players to just type a word and press ‘Enter.’

To refine this, I made pressing ‘Enter’ trigger the onEdit function. This single-step interaction mimics the ease of the original Wordle game, where a guess is submitted with just a keystroke, thereby maintaining the user’s focus and flow (note that clicking outside of the edited cell would also trigger the function).

The enhanced onEdit trigger:

Incorporating this functionality reduces the cognitive load on the user, allowing them to remain engrossed in the game rather than the mechanics of the game’s interface. It’s an example of user experience (UX) design principles; by recognizing that minimizing the number of user actions leads to a more enjoyable and engaging experience, you’ve made the game more accessible and enjoyable, particularly for users who may not be as comfortable switching between keyboard and mouse actions.

Dealing with Duplicates

In creating Wordle in Google Sheets, a key challenge was managing duplicate letters—both in players’ guesses and the puzzle words. This required refining the updateUI function. Initially, it color-coded cells based on the correctness of guesses. The twist came with handling duplicates, where the game logic had to discern between excess and correctly placed letters.

The solution involved a dual-pass approach:

  • First pass: We mark the correctly placed letters and decrement their counts from the correctLetterCounts map.

Second pass: We deal with misplaced letters and excess letters. A letter is colored yellow if it’s in the correct word and we haven’t used up all its occurrences yet (as per the correctLetterCounts map). If it’s not in the correct word or we’ve used up all its occurrences, we color it gray.

However, this can cause the letters and their color coding not to be revealed from left to right. Adjusting the logic to reveal each letter from left to right, regardless of whether they’re correct, misplaced, or incorrect, required rethinking the two-pass approach. To achieve the sequential reveal effect similar to the real game, I had to integrate the logic for determining the color into a single pass. This way, each cell is updated one by one in order, maintaining the suspense of the game.

Modified updateUI function:

In this revised approach:

First Pass: Initial Coloring and Occurrence Tracking

In the first loop over the guessed word, the function performs several tasks for each letter:

  • Visual Update: It updates the corresponding cell in the Google Sheet with the guessed letter, setting its font size and weight for clarity.
  • Occurrence Tracking: It increments the count of each guessed letter in guessedLetterCounts.
  • Initial Coloring: It assigns an initial background color to each cell based on a simple check:
    • Green (#6aaa64) if the guessed letter is in the correct position.
    • Yellow (#c9b458) if the letter is present in the correct word but not necessarily in the right place or the right quantity.
    • Gray (#787c7e) if the letter is not present in the correct word at all.

After setting the initial color, it decrements the count in correctLetterCounts for green letters, acknowledging that one correct occurrence of the letter has been accounted for.

Second Pass: Correcting Colors for Misplaced vs. Excess Letters

The function’s second loop over the guessed word adjusts the initial color assumptions to handle the nuances of duplicate letters and their placements:

  • It reevaluates cells initially marked as yellow to determine if they represent misplaced letters or excess letters. This step is necessary because the first pass might incorrectly mark an excess letter (one that appears more times in the guess than in the correct word) as yellow.
  • For each guessed letter not in the correct position, the function checks if the number of guesses for that letter exceeds the number of occurrences in the correct word. If so, it changes the cell’s color to gray (#787c7e), indicating an excess letter, and decrements guessedLetterCounts to reflect that one of the excess occurrences has been accounted for.

Key Mechanisms

  • Two-Pass Coloring Logic: This approach allows the function to initially mark potential matches as yellow and then refine those marks by distinguishing between correctly placed, misplaced, and excess letters. This is crucial for handling words with duplicate letters accurately.
  • Occurrence Tracking: By tracking the occurrences of each letter in both the guessed word and the correct word, the function can accurately determine when to mark a letter as excess (gray) rather than misplaced (yellow).
  • Delayed Visual Feedback: The SpreadsheetApp.flush() and Utilities.sleep(50. This was a design choice inspired by the original Wordle;  including a delay between the reveals of each letter as a UX consideration. By revealing each letter one by one, we allow for brain processing time, preventing cognitive overload, and mirror the pace at which the human brain processes information, creating suspense and drawing the player into the game experience, hopefully enhancing anticipation/drama/engagement/fun.  More on why this was removed later in the article.

In summary, the updateUI function adeptly handles the complexities of Wordle’s coloring rules, especially in the context of duplicate letters, through a methodical two-pass system that first assigns preliminary colors based on simple matches and then refines those colors to accurately reflect the actual state of the game.

First Pass: All letters are placed and initially colored. Correctly placed letters are immediately marked green. Letters that exist in the word are temporarily marked as yellow, and incorrect letters are marked gray. This creates the left-to-right reveal effect.

Second Pass: Adjusts the color of letters marked as yellow if they are found to be excess. This pass refines the initial guesswork by turning excess letters gray.

Accessibility

Introduction of Categories:

To diversify the Wordle game, I decided to introduce categories, allowing players to choose words from specific themes like Animals and Cities. By importing different word lists by category, I could potentially broaden the game’s educational scope, using it as a tool for language learning. This necessitated modifications in how words were selected based on the user’s chosen category. Here’s a summary of the process.

Word List Organization:

The ‘Words‘ sheet was organized with different categories in separate columns: Column A for all words, Column B for Animals, and Column C for Cities. Each column had a header indicating the category, facilitating easy identification and selection of words from the desired category.

Conceptualization:

  • Category Selection: The first step was selecting broad categories with ample five-letter words. Categories such as Animals, Cities, Foods, Colors, Tools, Sports, Music, and Brands were chosen for their rich vocabulary and accessibility to a diverse audience. 
  • Word Sources: For each category, legitimate sources were identified to compile the words. These included online dictionaries, encyclopedias (e.g. Wikipedia), GitHub repositories, educational resources, APIs (like Datamuse for programmatically fetching words), and community contributions.

Implementation:

  • Removing Duplicates: An optimized script function removeDuplicatesOptimized was developed to process large datasets efficiently, ensuring that unique words remained. This script employs batch processing techniques, significantly reducing execution time compared to row-by-row operations. 
  • Filtering Five-Letter Words: A script filterFiveLetterWords was created to cleanse the imported lists, removing any words not exactly five letters long or that were compound words. This script streamlined the process of preparing category-specific word lists for the game. 
  • Loading Category Words: The loadCategoryWords function was introduced to dynamically load words from the specified category into the game, enhancing the game’s flexibility. This function used the spreadsheet headers to identify the correct column for each category, loading the words into an in-memory cache for quick access. 
  • Category-Specific Gameplay: Functions like playWordleAnimals, playWordleCities, etc., were implemented to allow players to start games with words from their chosen categories, enriching the gameplay experience with variety and customization. 
  • Changes to selectRandomWord: The key improvement here is the dynamic determination of the word list based on the given category, allowing the game to draw from different sets of words depending on the user’s choice. This not only diversified the gameplay but also showcased the flexibility of Google Apps Script in handling data dynamically in Google Sheets. 
  • Using a helper function, columnToLetter, I converted column indices to letters, enabling the script to work with the A1 notation that Google Sheets requires. This utility function was essential in mapping the 1-indexed category position to the corresponding column letter in the sheet.

Current version of selectRandomWord:

 

Efficiency and Optimization:

  • Batch Processing: Emphasized throughout the implementation was the use of batch processing for reading and writing data to Google Sheets, significantly enhancing performance, especially for large word lists. 
  • Cache Utilization: The strategic use of a cache (categoryWordsCache) minimized repetitive reads from the spreadsheet, further optimizing the game’s responsiveness and efficiency.

Function Modifications for Category Selection:

Given the need for functions that would select a random word from the user’s desired category, I modified the existing selectRandomWord function to accept a category parameter. This adjustment allowed for dynamic word selection based on the category chosen by the user.

Incorporating User Selection through UI:

To enable users to choose a category directly from the Google Sheets UI, I added a custom menu with options for each category, using Google Apps Script:

Dynamic Range Calculation:

To accurately select a random word from the desired category, it was essential to calculate the last row for each specific column, addressing the varying lengths of each category. ChatGPT suggested an updated selectRandomWord function that dynamically calculates the range for the specified category column:

Handling Function Overloading:

In Google Apps Script, which adheres to JavaScript’s limitations, the concept of function overloading—where multiple functions with the same name differ only by their parameters—is not supported. Initially, this project included two distinct functions for the selectRandomWord task: one without parameters for selecting a default word, and another accepting a category parameter for category-specific selections. Due to the scripting environment’s constraints, attempting to use both functions led to a TypeError when the no-parameter version was called, highlighting the limitations of function overloading in this context.

To overcome this, the solution was to merge these functions into a single, versatile selectRandomWord(category) function. This unified function adeptly checks for the presence of a category argument. Based on whether a category is provided, it either executes category-specific logic for word selection or falls back to a default operation, typically selecting words from a predefined column (e.g., column A) when no category is specified:

This approach ensures a single selectRandomWord function can handle both scenarios: selecting words from a specific category or a default list, enhancing the script’s manageability and user experience.

Moreover, the playWordle function was refined to accommodate an optional category parameter. This modification enables dynamic category selection for the game. If a category is provided, playWordle invokes selectRandomWord with the given category; otherwise, it defaults to the general word list. This flexibility is crucial for supporting a variety of word categories without the need for separate functions for each category type:

This code structure not only simplifies adding new categories by merely adding a new column to the ‘Words’ sheet but also enhances the game’s versatility. Players can now enjoy a tailored game experience with categories ranging from “Animals” to “Cities,” facilitated by the dynamically adjusted playWordle and selectRandomWord functions. To reflect these changes and new category options in the Google Sheets UI, users must refresh the UI after script updates, ensuring the custom menu accurately presents the new categories available for gameplay. This design philosophy promotes ease of expansion and user engagement by seamlessly integrating new word categories into the game.

Why I removed Utilities.sleep

Earlier, I discussed delayed visual feedback as a design choice, including a delay between the reveals of each letter to allow for brain processing time, suspense, and engagement. In practice, I noticed that it was not necessary given the latency already occurring. Google Apps Script runs on Google’s servers, meaning that every operation, especially those that modify a Google Sheet, requires communication between the user’s device and the server. This communication incurs a natural latency that can vary based on several factors, including network speed, server load, and the complexity of the operations being performed. Human perception of delays in user interfaces is an important factor to consider. Research in human-computer interaction suggests that users can perceive delays as short as 100 milliseconds and that delays of around 200-300 milliseconds are generally perceived as instantaneous. Given the natural latency involved in the script’s execution and the UI’s update process, these delays might be sufficient to create a staggered update effect without the need for additional artificial delays introduced by Utilities.sleep().

 

SpreadsheetApp.flush() is a method that forces the execution of all pending changes in the script to be applied to the spreadsheet immediately. Normally, Google Apps Script batches changes and applies them at the end of the script execution to optimize performance and reduce the number of calls to the server. By calling flush(), you are explicitly telling the script to apply all queued changes up to that point, which can be used to create a real-time update effect in the spreadsheet.

If you were to remove SpreadsheetApp.flush() from the script, Google Apps Script would revert to its default behavior of applying all changes at once, at the end of the script’s execution. This means the entire word’s color coding would appear simultaneously, removing the sequential reveal of each letter’s status. The removal of flush() would effectively batch all updates together, which might be less engaging for users who expect a more dynamic interaction, akin to the original Wordle game experience. Removing Utilities.sleep while keeping SpreadsheetApp.flush() creates a balance between responsiveness and the dynamic reveal of updates, leveraging Google Apps Script’s server-side execution model. This setup offers a practical compromise between performance and user experience, demonstrating the nuanced interplay between script execution speed, network latency, and UI update mechanisms within Google Sheets.

 

Extensibility

This code structure allows users to add categories by simply adding a new column to the ‘Words‘ sheet, updating the onOpen function, and creating a new playWordleCategory function, mirroring the structure of the existing category functions:

Challenge: Try to add your own category of words to your copy of Wordle for Google Sheets!

  • Adding a New Category Column: In Wordle for Google Sheets: Add a new column to your ‘Words’ sheet. Let’s say you’re passionate about ‘Plants’. Rename the ‘Your Category’ header as ‘Plants’ and fill the column with relevant five-letter words like ‘tulip’ or ‘ferns’, one word per cell.


  • In the script: You’ll need to update the onOpen function to include your new category in the custom menu.  The first quoted phrase is what appears in the Wordle menu in the sheet.  The second quoted phrase is the function name.

Also in the script: you’ll need to add a function with the exact function name you chose to add to the onOpen function.

  • Refresh the Sheet: After saving your script, refresh the spreadsheet for the new menu item to appear in the Wordle menu.

By carefully following these steps and ensuring that function names and sheet headers match, you can add endless categories to your game, making it a customized, robust educational tool. The addition of each new category not only expands the vocabulary but also the breadth of knowledge that players can gain from engaging with your Wordle game.

Conclusion:

The development of Wordle in Google Sheets illustrates the practical application of computer science principles and design thinking in an educational setting. This project underscores the iterative nature of design and the effective use of Google Apps Script to elevate teaching tools within a platform that’s familiar to both students and educators.

Educational Value: This project not only provides a fun and engaging game but also serves as a practical example of applying computer science concepts such as data structures, algorithms, and optimization techniques.

Design Thinking: Students and educators are encouraged to think critically about user experience and game design, considering aspects such as category selection, user interface, and gameplay dynamics.

Customizability: Allowing users to edit/add new words/categories fosters a collaborative environment, providing a platform for students and educators to contribute creatively to the game’s development.

Collaborating with AI, particularly through exchanges with ChatGPT, is akin to having a skilled partner. I directed the project, while the AI provided support with code suggestions and troubleshooting. To conclude, this Wordle project is a testament to the potential of conversational coding and instructional design. It demonstrates how Google Apps Script can be an excellent resource for educators introducing interactive coding projects. Innovating within Google Sheets offers a myriad of opportunities for those aiming to blend coding and app development into their curriculum. This endeavor goes beyond coding; it fosters a design-centric mindset for problem-solving that I hope my students will use in their future projects.