Tag: Educational Technology

Posted on

Stop the Logger – Scratch Game Whitepaper

“Stop the Logger” Whitepaper

Using the ADDIE Model for “Stop the Logger” Scratch Game Development

Avi Megiddo

April 2 – 9, 2023

 

OVERVIEW

This document is a whitepaper/documentation for a game created using the ADDIE model for instructional design. The game involves catching a tree logger or “arresting” the logger using a “catchable” button in Scratch. The game was inspired by a button script from years ago and the birds outside my window. The trees in the game are from PNGTree.com and PNGWing.com, and the code randomly chooses one, “plants” it in a random location on the screen, and grows every second with a bit of accounting for upward and outward growth, but not downward. 

Analysis:

I was inspired initially by the button script from years ago, where it moved away from the mouse pointer as you tried to click it. This inspired me to create a “catchable” button in Scratch, which I used in the development of the game. After thinking about what makes sense to “catch” and still be educational, I came up with catching a tree logger, or “arresting” the logger. 

I found an animated gif of a logger/lumberjack on Dribbble.com,  by searching for synonyms of logger/lumberjack/tree logger and ‘gif’, and used a web-based tool to make all the gif images have a transparent background. Importing them as a new sprite took one click, and that was the basis for the game.

I was also inspired by the birds outside my window while creating the functions, and used bird call sounds from ZapSplat.com to reward the player after 10 trees were alive. Additionally, a student’s project inspired me to use the number 20 for a “winning amount” of loggers to catch.

 

Design:

 

In terms of design, I made the “caught” version of the logger by taking an image from the logger gif, and copy+paste+flip horizontally to make the arms look like they were handcuffed. I also created handcuffs in Google Drawings and finalized the design of the handcuffed tree logger using the Scratch costume editor. When the user successfully clicks on the logger, I decided to show a funny utterance instead of educational content to reward the user. I used my resizing concept to make the logger shrink as he rose to the top of the screen to join his other caught logger friends. 

 

The trees were also made using the ‘clone’ feature in Scratch, and some of the hard challenges included managing the clones, creating actions upon starting as a clone, deleting at the right time, and stopping other scripts in a sprite upon certain actions/states of the game, such as trees equalling zero and 20 loggers being caught.

Development:

 

To develop the game, iteration was key. I revisited the code for 30 minutes to 2-hour sessions, adding features, debugging, iterating over the project, upgrading, and saving to the computer stable backup versions, about 10 times. 

A random tree is chosen every second, with a random location on the screen, size adjusted based on position, and growing every second with a bit of accounting for upward and outward growth, but not downward. I used the size of the tree to make trees smaller or larger, depending on how high their randomly chosen y-values are, using the formula (180 – y-position) times some coefficient.

A recent iteration/upgrade was to how the trees fall when they die. Initially, I just rotated a tree 90 degrees when the logger touches it, but the upgrade involves repeating 10 rotations of 9, and a random choice of clockwise / counterclockwise rotation.  Further, the tree moves down as it rotates, to simulate rotation from the root, not the middle.

Implementation:


During implementation, I continued to test the game and made adjustments as needed. I added scoring functionality, where the player earns one point for each logger caught and loses one point for each tree lost. I also used the stamp tool as a way to delete each caught logger clone. I made the game more challenging by having the trees grow faster and adding more loggers to catch over time.

Evaluation:

In terms of evaluation, I tested the game with several individuals to get feedback on the gameplay and design. Based on the feedback, I made further adjustments to the game to improve the user experience. Overall, the game has been well received and has provided an engaging way to learn about deforestation.

 

MILESTONES

  1. Getting the button to move away from the cursor at a custom speed, based on a custom distance.
  2. Finding and importing the logger .gif 
  3. Dynamic sizing based on height.
  4. Creating the “caught” logger.
  5. Tree growth and death upgrades.
  6. Changing frequency and speed of logger based on # of catches.
  7. Adding educational messages upon loss/win.
  8. Adding a trophy/badge and music upon win.
Posted on

NY Times Connections Game in Google Sheets

August 19, 2024

Introduction

The New York Times has long been a leader in word games, captivating players with titles like Spelling Bee, Wordle, and most recently, Connections. These games offer not only entertainment but also a valuable mental exercise, encouraging strategic thinking and vocabulary expansion. Inspired by the innovative nature of these NY Times games, I decided to implement a version of NY TImes Connections in Google Sheets using Google Apps Script. This project is an ode to the original game—I hope this Google Sheets version can serve as a gateway for more people to discover and appreciate the original NY Times Connections game as part of their daily routine. This project is designed to be an accessible and educational tool that can help students, particularly those learning English as a second language (ESL), to engage with language in a fun, interactive way. Teachers can customize the word categories and members based on their curriculum.

Project Overview

Objective: To emulate the NY Times Connections game in Google Sheets, allowing users to categorize words based on common themes.

Rules: Players are presented with a 4×4 grid of words and must group them into four categories by identifying which words belong together. The challenge lies in finding the connections between the words, which can be more complex than they initially appear.

How to Play

  1. Start a New Game: Select ‘New Game’ from the custom menu in Google Sheets to generate a new 4×4 grid of words.
  2. Categorize the Words: Hold the appropriate key (Ctrl on Windows, Cmd on Mac, Shift on Chrome OS) and click on four words that you believe belong to the same category.
  3. Check Your Selection: Click the ‘Check’ button to see if your selection is correct. If it is, the words will be highlighted and the category name will appear on the side.
  4. Complete the Game: Continue categorizing words until all four categories have been identified.

Educational Benefits

The game is an excellent educational tool that fosters critical thinking and analytical skills by encouraging players to consider the relationships between different words. As they group words into categories, players experience vocabulary expansion and deepen their understanding of word meanings and connections. The puzzle format is inherently engaging, keeping students focused as they persist in finding the correct groupings. By making learning fun through word categorization, the learning process becomes more enjoyable, with a competitive element that adds excitement as students aim to complete the game as quickly and accurately as possible. This competition not only enhances attention to detail but also fosters a competitive learning environment, driving students to improve their vocabulary and categorization skills through healthy rivalry.

Technical Setup – Google Sheet Structure

There are two main sheets used in the project:

The Category Words Sheet serves as the database for the game’s categories and their corresponding members. Each row in this sheet contains a category in the first column, such as “Fruits,” and a comma-separated list of items in the second column, like “Apple, Banana, Cherry.” The data for this sheet was generated using AI tools like ChatGPT, which allowed for the quick creation of a wide variety of categories and members. This approach not only saved time but also ensured a diverse range of content, making the game more engaging and educational. Additionally, this setup is scalable; if you want to add more categories in the future, you simply need to append them to this sheet, and the game will automatically incorporate the new data.

The Connections Game Sheet is where the gameplay takes place. The primary interface is a 4×4 grid, located in cells C3:F6, where users interact with the game. To play, users select four words by holding down the appropriate key on their keyboard—Ctrl on Windows, Cmd on Mac, or Shift on Chrome OS—while clicking on the words in the grid. After making their selection, they click the ‘Check’ button to see if their selection is correct. When a correct selection is made, the corresponding cells are highlighted with a pastel color, and the text is struck through. The game also provides immediate feedback by displaying the category name and the selected words in a summary area located in cells H2 to the right of the grid.

 

 

AI, specifically ChatGPT, played a significant role in generating the category data for the game. The efficiency of AI allowed for the quick creation of a diverse range of categories and their members, covering common topics like “Fruits” as well as more niche areas like “Magical Creatures” or “Programming Languages.” The data is structured in a format that makes it easy to expand the game’s content without requiring any code changes. This AI-generated content not only makes the game more fun but also introduces students to new words and concepts, adding educational value.

In addition to generating data, AI also assisted in writing the Google Apps Script that powers the game. For instance, AI helped create the newGame function, which populates the grid with words and resets the game state to ensure each game is unique and challenging. The AI also contributed to scripting the logic that validates whether the selected words belong to the same category, ensuring smooth gameplay. Moreover, the feedback system, which highlights correct selections and displays the corresponding category in the summary area, was also co-authored with the help of AI.

The game’s mechanics involve several key processes. First, the script randomly selects four categories from the “Category Words” sheet and then randomly picks four members from each category to populate the grid. The cells are formatted to enhance readability and user experience, ensuring that all the information is clearly displayed. As players make correct selections, the game tracks how many categories have been correctly identified and provides immediate visual feedback by highlighting and striking through the selected words. This immediate feedback not only enhances the user experience but also makes the game more engaging and educational.

Explanation of Functions

newGame Function

The newGame function is responsible for setting up a fresh game in the Google Sheets version of NY Times Connections. It starts by accessing the spreadsheet and the specific sheets where the game and category data are stored. It then adjusts the size of the rows and columns to create a 4×4 grid, making sure that the grid is clear and ready for a new game by removing any existing content. The function also ensures that the text in the grid cells is properly formatted and wrapped, so all words are easily readable.

Next, the function retrieves all the available categories and their associated words from the “Category Words” sheet. It randomly selects four different categories and then randomly picks four words from each selected category to fill the grid. The words are shuffled to ensure they are placed randomly within the grid. 

checkSelection Function

The checkSelection function is activated when a player selects four words and clicks the ‘Check’ button. This function first checks if exactly four words have been selected. If not, it prompts the player to select the correct number of words. Once four words are selected, the function compares these words with the categories listed in the “Category Words” sheet.

For each selected word, the function identifies all possible categories that the word might belong to. It then checks for a common category that all four selected words share. If a single common category is found, the selected cells are highlighted with a random pastel color, and the text is struck through, indicating that the selection is correct. The category name and selected words are also displayed in a summary area to the right of the grid. 

If all four categories in the grid are correctly identified, the game asks if the player wants to start a new game. If the selection is incorrect, the player is alerted that the words do not belong to the same category.

showInstructions Function

The showInstructions function provides players with the necessary instructions on how to play the game. When this function is called, it displays an alert box with step-by-step instructions. The instructions explain how to select four words that belong to the same category and which key to hold down on the keyboard while clicking on the words, depending on the operating system (Windows, Mac, or Chrome OS). It also reminds players to click the ‘Check’ button after making their selection to see if they are correct. This function ensures that all players, regardless of their familiarity with the game or the platform, can easily understand how to play.

Feedback and User Experience

Providing Clear Feedback: When a player makes a selection, the script provides immediate feedback on whether the selection is correct. This helps players learn from their mistakes and understand the connections between words more clearly.

Smooth Gameplay: The use of toast notifications and non-intrusive feedback ensures that the game remains engaging and that players can focus on categorizing words without unnecessary interruptions.

Conclusion

The Google Sheets implementation of NY Times Connections offers a unique blend of entertainment and education, making it a valuable tool for both teachers and students. Whether you’re looking to enhance vocabulary, improve categorization skills, or simply enjoy a challenging word game, this project provides a fun and accessible way to engage with language. 

Here again is the template preview.

Full Code Base

The full Code.gs file is below and can also be found here. 

 

 

 

 

 

 

 

 

 

 

 

 

 

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.

 

Posted on

MYP Rubric + Comment Bank: score, comment, share, email


MYP Smart Rubric

I’ve developed an interactive scoring rubric + comment bank for MYP.
I made it to streamline MYP assessment; rubric-based grading and commenting, with task-specific clarifications.

Assessing MYP units requires juggling a number of documents; the rubric, the student’s work, a comment bank perhaps, and the software used to record/communicate the grade.

This tool utilizes Google Sheets + Google Apps Script.

With it, you can:
+ Add rubric comments and custom comments
+ Check boxes to aggregate comments
+ Share, email comments, score PDF

This improves my workflow mainly by aggregating the comments based on checkboxes made in the rubric.

Please have a look at the demo video:

 

Here is what you can expect when trying to run functions or after clicking on buttons:

1. Authorization Request: When you run the script, you will encounter an authorization request. This is a standard procedure for the script to access specific data within your Google account.

2. Clicking ‘Advanced’: During authorization, you will need to click on ‘Advanced’ to review and understand the script’s permissions.

3. Allowing the Script to Run: You might see a warning about the script being unverified. Rest assured, this is not indicative of any malicious activity. Simply select the option to allow the script to run. It’s important to note that this grants the script permission to read and modify the contents of cells in the sheet, which is necessary for it to perform the requested actions.

I hope you find this tool to be a useful game-changer. Here’s to taking tournament management to the next level! Please enjoy these interactive MYP Smart Rubric. Play on!

Privacy Policy for MYP Smart Rubric – This is a requirement in order to get verified by Google.

What data do we handle? Our application does not actively collect any personal data. However, the application verifies ownership of the MYP Smart Rubric Google Sheet Template for certain permissions using the user’s Google account.

Google Data Usage Compliance This application’s use and transfer to any other app of information received from Google APIs will adhere to Google API Services User Data Policy, including the Limited Use requirements. This ensures that your data received via Google APIs is used and handled according to strict standards.

How do we use your data? The user’s Google account is solely used to check the ownership of the MYP Smart Rubric Google Sheet Template and to grant permissions accordingly within the context of the application.

How is your data stored? All data inputted by the user is stored within the MYP Smart Rubric Google Sheet Template, and not elsewhere. Users can manually delete the data in the MYP Smart Rubric Google Sheet Template.

Limited Use Compliance Disclosure We comply with the Google API Services User Data Policy, including the Limited Use requirements. For more information, please review the Google API Services User Data Policy by clicking here What are your data protection rights? You have the right to access your own personal data, which is stored directly in the MYP Smart Rubric Google Sheet Template you are using. Your data is not stored anywhere else.

Cookies Our application does not use cookies.

Changes to this privacy policy We keep this privacy policy under regular review and place any updates on our website and other platforms where we have posted our application. This privacy policy was last updated on July 26, 2023.

How to contact us If you have any questions about this privacy policy, please do not hesitate to contact us. Email us at megiddo at gmail.com

Terms of Service – This is a requirement in order to get verified by Google.

1. Agreement to Terms

By using this MYP Smart Rubric Google Sheet Template application, you agree to abide by these Terms of Service. If you disagree with any part of the terms, then you may not access the application.

2. Changes to Terms

I reserve the right, at my sole discretion, to modify or replace these Terms at any time.

3. Use of the Application

You are responsible for any activity that occurs through your use of the application. You agree not to distribute any part of the application without my explicit permission.

4. Ownership

The application, including the associated MYP Smart Rubric Google Sheet Template and Apps Script code, is owned by me. You are granted a limited, non-exclusive, non-transferable license to use the application for its intended purpose.

5. Limitation of Liability

In no event shall I, nor my partners, agents, suppliers, or affiliates, be accountable for any indirect, incidental, special, consequential or punitive damages, including without limitation, loss of profits, data, use, goodwill, or other intangible losses, resulting from your use of the application.

6. Contact Information

If you have any questions about these Terms, please contact me at
megiddo at gmail.com.

Posted on

Mood Meter Google Sheets App

Enhancing Emotional Intelligence in the Classroom

Have you ever wondered how the emotional state of your students impacts their learning experiences? Or have you found it challenging to track and understand the emotional climate of your classroom? Today, I want to introduce a digital tool that could help us perceive and respond to our students’ emotions: the Mood Meter Google Sheets App. This project was inspired by Marc Brackett’s pioneering work with the Mood Meter, as outlined in his book “Permission to Feel“, and my passion for Google Sheets and Apps Script.

The Mood Meter was designed to elevate emotional awareness and enhance our overall well-being. Marc Brackett is the Director of the Yale Center for Emotional Intelligence. He and his uncle developed the RULER method—an evidence-based approach to social-emotional learning. It focuses on improving 5 skills of emotional intelligence: Recognizing emotions in self and others, Understanding the causes and consequences of emotions, Labeling emotions accurately, Expressing emotions appropriately, and Regulating emotions effectively. Managing emotions effectively allows us to navigate challenges and setbacks with resilience and adaptability, fostering a fulfilling and harmonious life.

The Mood Meter Google Sheets app, which is currently in development, focuses on tracking and analyzing the emotions and moods of students on a daily basis. This can be particularly useful in understanding students’ well-being, identifying patterns or trends, and addressing potential concerns. It generates a heat map of the aggregated moods in each class over time, showing which emotions were more often felt over time. The student mood table tracks how each student felt each day and can provide valuable insights for teachers and administrators.

This Apps Script project is now verified by Google!

 

The tool I’m developing is an interactive version of Marc Brackett’s Mood Meter, enhanced with Google Apps Script. It enables concurrent editing by an entire class, providing a dynamic visual snapshot of the collective emotional state. This can be implemented in a Homeroom, Advisory, or other class setting.

The sheet records user data, generates daily and cumulative heat maps, and can manage multiple sub-sheets for educators overseeing various classes. Though we are on summer break and I’ve yet to test it with students, I’ve strived to make the code robust, anticipating a large group of students using it simultaneously.

Foremost in the design is the privacy and security of students’ emotional data. The tool ensures that such sensitive information is accessible only to authorized individuals.

Demo:

Detailed Walkthrough:

Understanding Emotional Intelligence: Emotional intelligence (EQ) refers to the ability to recognize, understand, and manage our own emotions, as well as understand and empathize with the emotions of others. It encompasses skills such as recognizing emotions, understanding their causes and consequences, accurately labeling emotions, expressing emotions appropriately, and regulating emotions effectively. Developing EQ empowers individuals to navigate emotions, build healthier relationships, and make informed decisions.

The Role of Emotional Awareness and Metacognition: Emotional awareness is the foundation of emotional intelligence. It involves consciously recognizing and understanding our emotions, as well as the impact they have on our thoughts, behaviors, and overall well-being. By cultivating emotional awareness, individuals can develop metacognition skills, which involve the ability to reflect on and regulate one’s own thinking and learning processes. The Mood Meter serves as a powerful tool to enhance emotional awareness and metacognition, enabling individuals to explore and express their emotions more effectively.

The Power of the Mood Meter in the Classroom: The Mood Meter provides teachers with a valuable resource to gauge the emotional well-being of their students in an engaging and efficient manner. Unlike traditional methods of checking in on emotions, such as asking “How are you today?” or using simple thumbs-up, sideways, or thumbs-down indicators, the Mood Meter offers a technologically-driven solution that encourages active participation. Teachers can easily create a class-specific Mood Meter using the app, enabling students to select their current mood by clicking on corresponding checkboxes.

Data Aggregation for Valuable Insights: One of the key advantages of using the Mood Meter is its data aggregation capability. The sheet automatically aggregates and analyzes the data, providing valuable insights to teachers. By monitoring trends and patterns, educators can identify students who consistently experience negative emotions, detect classes that exhibit low energy levels, and gain a comprehensive understanding of the emotional well-being of their students. This information empowers teachers to offer timely support and interventions when necessary, leading to a more nurturing and inclusive learning environment.

Exploring Color Psychology: Colors play a significant role in the Mood Meter, representing different emotional states and energy levels. The hex color codes assigned to each quadrant (red, yellow, blue, and green) were chosen to reflect the emotional experiences associated with different energy levels and pleasantness. The heat map creates a visually stimulating and educational experience. Exploring the impact of colors on emotions enhances students’ understanding of color psychology and contributes to their overall emotional awareness.

Empowering Homeroom Teachers: Homeroom teachers, in particular, can greatly benefit from using the Mood Meter as a tool to monitor the emotional well-being of their class. By using the Mood Meter, teachers can efficiently gather data about their students’ emotions and energy levels without relying solely on verbal or non-verbal cues. This technology-driven approach provides a comprehensive view of the emotional climate of the classroom, fostering empathy, open communication, and targeted support for students.

Conclusion: 

The Mood Meter offers a powerful platform for enhancing emotional intelligence, fostering emotional awareness, and promoting metacognition skills. Its gamified and interactive nature engages students, enabling them to explore and express their emotions effectively. Homeroom teachers and educators worldwide can harness the power of the Mood Meter to create inclusive learning environments, gain insights into students’ emotional well-being, and provide targeted support.

By integrating the Mood Meter into their teaching practices, teachers empower students to navigate emotions, develop essential social-emotional skills, and lead happier and more fulfilled lives.

I would love to hear your thoughts on this tool. How do you think it could be improved? How could it be applied in your own classroom or environment? If you are interested in testing an early-access version of the Mood Meter Google Sheets App, please reach out to me directly. However, please note that the tool is currently in the verification process with Google and might show warnings about being an “unverified app”. If you are comfortable with this, your feedback would be extremely valuable for the tool’s development and refinement.

Please share your thoughts and interests in the comments section below!

Plans for Implementation and App Verification:

As an educator, I am excited about the prospects of the Interactive Mood Meter Google Sheets App. I plan to utilize this tool in my Homeroom class in the upcoming fall semester.

However, I understand the significance of security and credibility when it comes to digital tools, especially those used in educational environments. To ensure the highest level of safety and confidence, the Interactive Mood Meter is currently in the process of being verified by Google.

While this verification process is crucial to ensure the security of our users, it may take 1-2 months to complete.” As a result, I’ve decided to withhold the public release of the Mood Meter Google Sheets app until it has been fully verified by Google. My priority is to provide a tool that is not only beneficial but also secure and reliable.

I am looking forward to seeing the impact this tool will make in the classroom and am eager to share it with you once it’s fully verified. Your understanding and patience are appreciated as we navigate this process together.

Privacy Policy:

What data do we handle? Our application does not actively collect any personal data. However, the application verifies ownership of the Mood Meter Google Sheet Template for certain permissions using the user’s Google account.

Google Data Usage Compliance This application’s use and transfer to any other app of information received from Google APIs will adhere to Google API Services User Data Policy, including the Limited Use requirements. This ensures that your data received via Google APIs is used and handled according to strict standards.

How do we use your data? The user’s Google account is solely used to check the ownership of the Mood Meter Google Sheet Template and to grant permissions accordingly within the context of the application.

How is your data stored? All data inputted by the user is stored within the Mood Meter Google Sheet Template, and not elsewhere. Users can manually delete the data in the Mood Meter Google Sheet Template.

Limited Use Compliance Disclosure We comply with the Google API Services User Data Policy, including the Limited Use requirements. For more information, please review the Google API Services User Data Policy by clicking here What are your data protection rights? You have the right to access your own personal data, which is stored directly in the Mood Meter Google Sheet Template you are using. Your data is not stored anywhere else.

Cookies Our application does not use cookies.

Changes to this privacy policy We keep this privacy policy under regular review and place any updates on our website and other platforms where we have posted our application. This privacy policy was last updated on July 26, 2023.

How to contact us If you have any questions about this privacy policy, please do not hesitate to contact us. Email us at megiddo at gmail.com

Terms of Service

1. Agreement to Terms

By using this Mood Meter Google Sheet Template application, you agree to abide by these Terms of Service. If you disagree with any part of the terms, then you may not access the application.

2. Changes to Terms

I reserve the right, at my sole discretion, to modify or replace these Terms at any time.

3. Use of the Application

You are responsible for any activity that occurs through your use of the application. You agree not to distribute any part of the application without my explicit permission.

4. Ownership

The application, including the associated Mood Meter Google Sheet Template and Apps Script code, is owned by me. You are granted a limited, non-exclusive, non-transferable license to use the application for its intended purpose.

5. Limitation of Liability

In no event shall I, nor my partners, agents, suppliers, or affiliates, be accountable for any indirect, incidental, special, consequential or punitive damages, including without limitation, loss of profits, data, use, goodwill, or other intangible losses, resulting from your use of the application.

6. Governing Law

These Terms shall be governed and construed in accordance with the laws of [Your Country], without regard to its conflict of law provisions.

7. Contact Information

If you have any questions about these Terms, please contact me at
megiddo at gmail.com.

 

 

Code Overview:

Coming soon…

Complete Code:

Coming soon…