MakeWords: 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
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
Tracking Performance and Growth
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
Explanation of Functions
Initial Setup Functions
Helper Functions
Core Game Functions
Event Handling Functions
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
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:
Example:
Suppose a player enters the word "cinema". The script:
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:
function binarySearch(arr, target) {
let left = 0;
let right = arr.length - 1;
while (left <= right) {
const mid = Math.floor((left + right) / 2);
const midValue = arr[mid].toLowerCase();
if (midValue === target) {
return true;
} else if (midValue < target) {
left = mid + 1;
} else {
right = mid - 1;
}
}
return false;
}
Implementation Details
Step-by-Step Guide
function drawRandomWord() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Dictionary");
const alphabet = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
const randomLetter = alphabet.charAt(Math.floor(Math.random() * alphabet.length));
const colIndex = randomLetter.charCodeAt(0) - 65 + 1; // Calculate column index based on the random letter
const range = sheet.getRange(1, colIndex, sheet.getMaxRows(), 1);
const words = range.getValues().flat().filter(word => word && word.length >= 8);
if (words.length === 0) {
Logger.log(`No words found in column ${randomLetter} with 8 or more characters.`);
return;
}
const randomWord = words[Math.floor(Math.random() * words.length)];
SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange("C2").setValue(randomWord);
Logger.log(`Random word selected: ${randomWord}`);
}
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:
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!
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:
Full Code Base
let updateQueue = [];
// Function to create a custom menu in the spreadsheet upon opening
function onOpen() {
const ui = SpreadsheetApp.getUi();
ui.createMenu('MakeWords Game')
.addItem('New Game', 'newGame')
.addToUi();
newGame();
}
function newGame() {
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const activeSheet = spreadsheet.getActiveSheet();
// Ensure the current sheet is "MakeWords Game"
if (activeSheet.getName() !== "MakeWords Game") {
throw new Error("MakeWords Game sheet not found. Please ensure it exists.");
}
const ui = SpreadsheetApp.getUi();
const response = ui.alert(
"Save Current Game?",
"Would you like to save the current game before starting a new one?",
ui.ButtonSet.YES_NO
);
if (response === ui.Button.YES) {
// Duplicate the current sheet and rename the original
const timestamp = new Date().toLocaleString().replace(/\//g, "-"); // Replace only slashes with dashes
const savedSheetName = `Game ${timestamp}`;
// Rename the original sheet
activeSheet.setName(savedSheetName);
// Duplicate the renamed sheet and rename the duplicate back to "MakeWords Game"
const newSheet = activeSheet.copyTo(spreadsheet).setName("MakeWords Game");
spreadsheet.setActiveSheet(newSheet);
spreadsheet.moveActiveSheet(0); // Move the active sheet to the left-most position
Logger.log(`Game saved as: ${savedSheetName}`);
}
// Clear the fields of the new "MakeWords Game" sheet for the new game
clearGameFields(spreadsheet.getSheetByName("MakeWords Game"));
Logger.log("Game cleared for reuse.");
// Draw a new random root word for the game
drawRandomWord();
}
// Function to clear the fields for a new game
function clearGameFields(sheet) {
// Clear the root word, words, and points fields
sheet.getRange("C2").clearContent(); // Root word
sheet.getRange("A4:F45").clearContent(); // Words and points
sheet.getRange("A4:A45").setFontLine("none"); // Remove strikethrough
sheet.getRange("C4:C45").setFontLine("none");
sheet.getRange("E4:E45").setFontLine("none");
}
// Helper function to get the count of each letter in a word
function getLetterCount(word) {
const count = {};
for (const letter of word) {
count[letter] = (count[letter] || 0) + 1;
}
return count;
}
// Helper function to perform binary search on a sorted array
function binarySearch(arr, target) {
let left = 0;
let right = arr.length - 1;
while (left <= right) {
const mid = Math.floor((left + right) / 2);
const midValue = arr[mid].toLowerCase();
if (midValue === target) {
return true;
} else if (midValue < target) {
left = mid + 1;
} else {
right = mid - 1;
}
}
return false;
}
// Function to draw a random word from the dictionary sheet
function drawRandomWord() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Dictionary");
const alphabet = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
const randomLetter = alphabet.charAt(Math.floor(Math.random() * alphabet.length));
const colIndex = randomLetter.charCodeAt(0) - 65 + 1; // Calculate column index based on the random letter
const range = sheet.getRange(1, colIndex, sheet.getMaxRows(), 1);
const words = range.getValues().flat().filter(word => word && word.length >= 8);
if (words.length === 0) {
Logger.log(`No words found in column ${randomLetter} with 8 or more characters.`);
return;
}
const randomWord = words[Math.floor(Math.random() * words.length)];
SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange("C2").setValue(randomWord);
Logger.log(`Random word selected: ${randomWord}`);
}
// Function to check if a word is real (on-demand lookup)
function isRealWordLocal(word) {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Dictionary");
word = String(word).toLowerCase();
const firstLetter = word.charAt(0);
const col = firstLetter.charCodeAt(0) - 97 + 1; // Calculate column index based on first letter
const range = sheet.getRange(1, col, sheet.getMaxRows(), 1);
const values = range.getValues().flat().filter(Boolean);
const isReal = binarySearch(values, word);
Logger.log(`isRealWordLocal(${word}) = ${isReal}`);
return isReal;
}
// Function to check if a word is valid by comparing letter counts
function isValidWord(longWord, word) {
if (!longWord || !word) {
Logger.log('Invalid input to isValidWord: ', longWord, word);
return false;
}
const longWordCount = getLetterCount(longWord);
const wordCount = getLetterCount(word);
for (const letter in wordCount) {
if (!longWordCount[letter] || wordCount[letter] > longWordCount[letter]) {
Logger.log(`Invalid word: ${word} (missing or too many of letter '${letter}')`);
return false;
}
}
Logger.log(`Valid word: ${word}`);
return true;
}
// Function to check for duplicate entries in the current game
function isDuplicateEntry(word, currentRange) {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
const wordRanges = ["A4:A45", "C4:C45", "E4:C45"];
const currentA1Notation = currentRange.getA1Notation();
for (const range of wordRanges) {
const values = sheet.getRange(range).getValues();
for (let row = 0; row < values.length; row++) {
const cellValue = values[row][0];
const cell = sheet.getRange(range).offset(row, 0, 1, 1);
if (cellValue && cellValue.toLowerCase() === word.toLowerCase() && cell.getA1Notation() !== currentA1Notation) {
Logger.log(`Duplicate found: ${word} in range ${cell.getA1Notation()}`);
return true;
}
}
}
return false;
}
// Function to update points in the adjacent cell based on word length
function updatePoints(word, range) {
const wordLength = word.length;
const newPoints = Math.pow(2, wordLength - 2);
Logger.log(`updatePoints(${word}) = ${newPoints} points.`);
const pointsCell = range.offset(0, 1);
pointsCell.setValue(newPoints);
}
// Function to show toast notifications within the spreadsheet
function showToast(message) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
ss.toast(message); // Displays a notification within the spreadsheet
}
// Function to handle onEdit event
function onEdit(e) {
if (!e) {
Logger.log("No event object, probably running in test mode.");
return;
}
const sheet = e.source.getActiveSheet();
const range = e.range;
if (!isInWordColumns(range)) {
Logger.log("Edit is outside the target range or not in the correct sheet.");
return;
}
const editedWord = e.value.toLowerCase();
const longWord = sheet.getRange("C2").getValue().toLowerCase();
const adjacentCell = range.offset(0, 1);
// Add to the update queue
updateQueue.push({ editedWord, longWord, range, adjacentCell });
if (updateQueue.length === 1) {
processUpdateQueue();
}
}
// Function to process the update queue
function processUpdateQueue() {
if (updateQueue.length === 0) return;
const { editedWord, longWord, range, adjacentCell } = updateQueue[0];
Logger.log(`Validating word: ${editedWord} against long word: ${longWord}`);
if (editedWord === longWord) {
Logger.log(`The word "${editedWord}" is the same as the root word.`);
range.setFontLine("line-through");
adjacentCell.setValue("root word");
showToast("You cannot enter the root word. Make words using the letters of the root word.");
updateQueue.shift();
Utilities.sleep(100); // Add a short delay to allow the UI to update
processUpdateQueue();
return;
}
const isValid = isValidWord(longWord, editedWord);
const isReal = isRealWordLocal(editedWord);
const isDuplicate = isDuplicateEntry(editedWord, range);
Logger.log(`isValidWord(${longWord}, ${editedWord}) = ${isValid}`);
Logger.log(`isRealWordLocal(${editedWord}) = ${isReal}`);
Logger.log(`isDuplicateEntry(${editedWord}) = ${isDuplicate}`);
if (isValid && isReal && !isDuplicate) {
Logger.log(`The word "${editedWord}" is valid and a real word.`);
range.setFontLine("none");
updatePoints(editedWord, range);
showToast(`The word "${editedWord}" is valid and a real word. Points: ${Math.pow(2, editedWord.length - 2)}`);
} else {
Logger.log(`The word "${editedWord}" is invalid or a duplicate entry.`);
range.setFontLine("line-through");
let message = `The word "${editedWord}" is invalid; `;
if (!isValid) {
message += `it cannot be written with the letters of "${longWord}."`;
adjacentCell.setValue("invalid letters");
} else if (!isReal) {
message += `not a real word.`;
adjacentCell.setValue("not a real word");
} else if (isDuplicate) {
message += `a duplicate entry.`;
adjacentCell.setValue("word already found");
}
showToast(message);
}
updateQueue.shift();
Utilities.sleep(100); // Add a short delay to allow the UI to update
processUpdateQueue();
}
// Helper function to check if the edited cell is in the target range
function isInWordColumns(range) {
const col = range.getColumn();
const row = range.getRow();
return (col === 1 || col === 3 || col === 5) && row >= 4 && row <= 45; // Adjusted to columns A, C, E and rows 4-45
}
// Test function for onEdit
function testOnEdit() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
const range = sheet.getRange("A4"); // Change to the cell you want to test
const editedWord = "germ"; // Change to the word you want to test
const longWord = "emergency"; // Change to the long word for context
// Set up test environment
sheet.getRange("C2").setValue(longWord);
range.setValue(editedWord);
const e = {
source: SpreadsheetApp.getActiveSpreadsheet(),
range: range,
value: editedWord,
};
onEdit(e);
}
function deleteHiddenSheets() {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheets = spreadsheet.getSheets();
sheets.forEach(function (sheet) {
if (!sheet.isSheetHidden()) {
return;
}
spreadsheet.deleteSheet(sheet);
});
}