MakeWords: Implementing a Classic Pen & Paper Game in Google Sheets

Make Words Game:

Implementing a Classic Pen & Paper Game in Google Sheets

Introduction

Word games have always been a popular form of entertainment and education, with classics like Boggle, Scrabble, and the New York Times’ Spelling Bee captivating word enthusiasts worldwide. Growing up, I played these games with my family using paper and pencil, which created wonderful memories and a love for word puzzles. Inspired by these timeless games, I set out to create MakeWords, a Google Sheets-based game that brings the excitement of word puzzles into a digital and easily accessible format. This game isn’t just a fun way to pass time; it’s also a valuable educational tool, particularly for ESL and ELL students.

Project Overview

Objective: Form as many smaller words as possible from a given longer word.

Rules: Players are given a long word and must form as many valid smaller words as possible using the letters from the long word.

Video Preview

Educational Benefits

  • Vocabulary Expansion: Players are encouraged to think of and form longer, more complex words, helping them expand their vocabulary.
  • Strategic Thinking: The exponential scoring encourages strategic thinking, as players need to weigh the benefits of finding longer words against the time it takes to think of them.
  • Engagement: The increasing points for longer words make the game more exciting and competitive, which can help maintain students’ interest and engagement.
  • Reinforcement of Spelling: Regularly forming and validating words helps reinforce correct spelling and word usage.

Gamifying Learning

MakeWords is a powerful tool for engaging students through gamification. Teachers assigning this game are leveraging the principles of gamification to make learning fun and interactive. Here’s how:

Engaging Through Gamification

  1. Making Learning Fun: By turning word practice and spelling into a game, students are more likely to enjoy the learning process. The competitive element, where students aim to find as many words as possible from a given word, and the longer the word, the more points are awarded.
  2. Attention to Detail: The game requires students to pay close attention to the letters in the given long word. They need to ensure they are using the letters correctly, checking for duplicates, and verifying the validity of the words. This meticulous approach enhances their attention to detail and spelling accuracy.
  3. Fostering a Competitive Learning Environment: By choosing the same starting long word,and playing simultaneously, users can compete to see who can get the most points in the given time. This healthy competition can drive players to improve their word recall and spelling skills as they strive to outperform their peers.

Tracking Performance and Growth

  1. Timestamps for Tracking Progress: Each game session is automatically named and saved with a timestamp. This feature allows teachers and parents to track the progress of students over time. They can see how students are improving in their ability to recall and form words.
  2. Performance Review: By reviewing past game sessions, shared with teachers or parents, it’s easy to identify patterns in a student’s performance. Teachers can see which types of words students struggle with and provide targeted support to help them improve.
  3. Growth in Word Recall and Retrieval: As students play more games, their ability to recall and retrieve words is likely to improve. Teachers can use the timestamps and saved game sessions to monitor this growth, providing evidence of learning progress over time.
  4. Data-Driven Insights: The structured data from each game session offers valuable insights into a student’s learning journey. Teachers can analyze this data to understand individual strengths and weaknesses, adapting their teaching strategies to better support each student.

Practical Implementation

Assigning the Game: Teachers can easily assign MakeWords to students in Google Classroom. The game is accessible and straightforward, making it easy for students to start playing and learning immediately. Teachers can keep track of usage; this shared access ensures that those involved in the student’s education can see their progress and provide encouragement or additional support as needed.

Encouraging Continuous Improvement: With each game saved and accessible, students can see their own improvement over time. This visibility can be a powerful motivator, encouraging them to keep practicing and improving their word skills.

Technical Setup – Google Sheet Structure

Dictionary Sheet: Contains a list of long words organized alphabetically into columns (A-Z). 

This structure significantly improves efficiency by limiting the search space. Instead of searching through one long column containing the entire dictionary, the search is confined to a single column corresponding to the first letter of the word, making lookups faster and less resource-intensive.

Game Sheet: The gameplay takes place here, where players enter their words and see their scores.

Google Apps Script

  • Random Word Selection: The script draws a random long word from the “Dictionary” sheet.
  • Word Validation: Players enter their words in designated cells, and the script checks their validity, ensuring they can be formed from the letters of the long word.
  • Scoring System: The script calculates points based on the length of the words and handles duplicate entries and invalid words.

Feedback

Providing clear reasons why a word is invalid is vital feedback that enhances the learning experience in “Make Words.” When players receive specific feedback, such as a word being invalid due to incorrect letters or being a duplicate entry, they can quickly adjust their strategies and avoid making the same mistakes. This targeted feedback not only aids in faster learning but also keeps the game challenging and educational. Knowing why a word is invalid or seeing their scores improve encourages players to think more critically and enhances their problem-solving skills, making MakeWords both a fun and valuable educational tool.

A key feature of MakeWords is the use of toast notifications. These are small, non-intrusive pop-up messages that appear briefly at the bottom of the screen. The name “toast” comes from the way these notifications pop up like a slice of toasted bread. Toast notifications are particularly effective because they provide feedback without interrupting gameplay. Unlike traditional dialogue boxes or alerts that require user interaction to close, toast notifications allow players to continue playing seamlessly. Players are informed about the validity of their words and their points without having to click anything to dismiss the notification. This ensures a smooth and engaging gaming experience.

Toast notifications provide instant feedback on the validity of their words and the points they earn. This real-time response keeps students engaged and helps them learn from their mistakes without interrupting the flow of the game. Moreover, this immediate feedback is crucial for maintaining high levels of motivation and engagement, as it helps players understand the rules better and improve their word-forming skills dynamically.

Smart Scoring Method

One of the unique aspects of this game is its smart scoring method, designed to encourage players to find longer words. 

This exponential scoring system motivates players to find longer words, as each additional letter significantly increases the points earned. It turns the game into a more strategic and engaging activity.

Performance Optimization with Binary Search

One significant challenge was ensuring the game ran smoothly and updated scores correctly during fast play. This required an efficient way to validate words against a large dictionary.

Efficient Google Sheet Structure

  • Dictionary Sheet: Contains a list of long words organized alphabetically into columns (A-Z). This structure improves efficiency by limiting the search space. Instead of searching through one long column containing the entire dictionary, the search is confined to a single column corresponding to the first letter of the word, making lookups faster and less resource-intensive.

Why Binary Search?

Binary search is a powerful algorithm for finding an item in a sorted list in logarithmic time. Imagine you’re playing a number guessing game, where you have to guess a number between 1 and 100. Each time you make a guess, you’re told whether the number is higher or lower than your guess. You might start by guessing 50. If the number is higher, you then guess 75, and if it’s lower, you guess 25. By continually dividing the range in half, you quickly narrow down the possibilities.

Applying Binary Search to Word Validation

By organizing the dictionary sheet alphabetically into columns based on the first letter of each word, I leveraged binary search to quickly validate words. Here’s how:

  • Alphabetical Columns: The dictionary is divided into 26 columns (A-Z), each containing words that start with the corresponding letter.
  • Sorted Words: Within each column, the words are sorted alphabetically.

Example:

Suppose a player enters the word “cinema”. The script:

  • Locates the Column: Identifies the relevant column ‘C’ for words starting with ‘C’.
  • Performs Binary Search:
    1. Finds the middle word in the ‘C’ column. With 24,797 words, the middle word would be around the 12,399th word.
    2. Let’s assume the middle word in the 0 to 12,398 range is “cobra”.
    3. Compare “cinema” with “cobra”:
      • Since “cinema” comes before “cobra” alphabetically, search the first half of the column.
    4. Narrow the search range to 0 to 6,199.
    5. Find the new middle word in this range, let’s say it’s “cherry”.
    6. Compare “cinema” with “cherry”:
      • Since “cinema” comes after “cherry”, search the second half of this range.
    7. Narrow the search range to 3,100 to 6,199.
    8. Find the new middle word in this range, let’s say it’s “clementine”.
    9. Compare “cinema” with “clementine”:
      • Since “cinema” comes before “clementine”, search the first half of this range.
    10. Narrow the search range to 3,100 to 4,649.
    11. Find the new middle word in this range, let’s say it’s “cite”.
    12. Compare “cite” with “cinema”:
      • Since “cinema” comes before “cite”, search the first half of this range.
    13. Repeat this process until “cinema” is found or the range is exhausted.

Benefits of This Structure:

Notice how, in just 4-5 comparisons, we got down to less than 1,000 options from the over 24,000 words starting with C. Compare this method to going through the entire dictionary of over 150,000 words every time we want to validate a word! Binary search can reduce the search space significantly, making it ideal for handling large datasets and ensuring quick word validation even during fast-paced play.

Code Snippet for Binary Search:

  • arr: The array (list) of sorted words being searched.

  • target: The word you’re looking for.

  • left and right: The starting and ending indices of the current search range.

  • mid: The middle index of the current search range.

  • midValue: The word at the middle index, compared to the target word.

Implementation Details

Step-by-Step Guide

  1. Set up the Google Sheets: Create a “Dictionary” sheet and a “Make Words Game” sheet.
  2. Google Apps Script: Implement the script to handle word selection, validation, and scoring. Here’s a key snippet from the script:
  3. Start Playing: Draw a random word and begin forming smaller words. The script will handle validation and scoring in real-time.

    Breakdown of the Scoring Formula

    The formula used in cell F2 to calculate the total score is:

    =SUMPRODUCT(IF(ISNUMBER(B4:B45), B4:B45, 0)) + SUMPRODUCT(IF(ISNUMBER(D4:D45), D4:D45, 0)) + SUMPRODUCT(IF(ISNUMBER(F4:F45), F4:F45, 0))

    How It Works:

    • SUMPRODUCT Function: This function multiplies corresponding components in the given arrays and then sums the results. It’s used here to handle both numeric and text entries.
    • IF Function: IF(ISNUMBER(B4:B45), B4:B45, 0) checks each cell in the range B4. If the cell contains a number, it returns that number; otherwise, it returns 0. This ensures that only valid points (numbers) are included in the sum.
    • ISNUMBER Function: This function checks whether each cell contains a number. It’s essential for filtering out non-numeric values like text or empty cells.
    • Sum Calculation: The formula does this for three ranges: B4, D4, and F4. It sums the valid numeric values from these ranges separately and then adds the results together to get the total score.

    This formula efficiently calculates the total points from multiple columns, ensuring that only numeric values are considered, which is particularly useful during fast-paced play.

    Conclusion

    The MakeWords game is a fantastic way to bring the joy of classic word games into the digital age, using tools like Google Sheets and Google Apps Script. Whether you’re an educator looking for a fun classroom activity or a word enthusiast, this game offers endless possibilities for learning and enjoyment. I invite you to try creating your own version and share your experiences!

    Explanation of Functions

    1. Initial Setup Functions
      • onOpen: This function adds a custom menu item to the Google Sheets UI when the spreadsheet is opened.
      • newGame: This function sets up a new game session, renaming the current game sheet with a timestamp, deleting any existing game sheet, duplicating a past game sheet if available, and initializing a new game sheet.
    2. Helper Functions
      • getLetterCount: This function counts the occurrences of each letter in a given word.
      • binarySearch: This function performs a binary search on a sorted array to efficiently find the target word.
    3. Core Game Functions
      • drawRandomWord: This function selects a random word from the dictionary sheet.
      • isRealWordLocal: This function checks if a word is real by performing a binary search in the dictionary sheet.
      • isValidWord: This function validates a word by comparing its letter counts with those of the given long word.
      • isDuplicateEntry: This function checks for duplicate word entries in the current game session.
      • updatePoints: This function updates the points in the adjacent cell based on the length of the word.
      • showToast: This function displays toast notifications within the spreadsheet.
    4. Event Handling Functions
      • onEdit: This function handles the onEdit event, validating the entered word and updating the game state accordingly.
      • processUpdateQueue: This function processes the update queue, ensuring smooth handling of word entries and validations.

    How to Play

    Click the MakeWords game to make your own template. A new menu item called ‘Make Words’ will be created. Select ‘New Game’ from that menu. If you encounter a problem, click on the ‘Extensions’ menu and choose ‘Apps Script’. Then, run the onOpen function from the script editor.

    Running the Google Apps Script shared is safe. Here’s what you can expect during the process:

    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. 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 tournament brackets. Play on!

    Full Code Base:

    Make Words Code.gs