MakeSort: Drag ‘n’ Drop Sorting Games Maker & Player

From Scissors to Scripts:

A Sorting Games Implementation in Google Slides

by Avi Megiddo

Welcome to MakeSort, the ultimate tool for creating and playing sorting games! Build interactive, drag-and-drop sorting activities for education, training, or fun—all within Google Slides.

Features

    • Quickly create custom sorting games with drag-and-drop functionality.
    • Validate solutions with automatic feedback.
    • Seamlessly integrate with Google Slides.
    • Designed for both makers and players—perfect for teachers, trainers, and learners.

How It Works

  1. Sign in with your Google account to enable MakeSort in Google Slides.
  2. Create a new sorting activity or choose from templates.
  3. Share your game with others or challenge yourself to solve it!

Sorting and categorizing activities are a cornerstone of education, from early childhood to advanced computer science. Whether it’s sequencing the steps for making tea, understanding taxonomic hierarchies, or organizing historical events, these tasks engage students in logical thinking and problem-solving.

However, traditional approaches to these activities often present significant challenges:

  • Printing and cutting: Preparing multiple sets of cards or slips is time-intensive and often wasteful.
  • Fragility: Paper slips tear, fold, or go missing.
  • Laminating: While this can extend durability, it adds cost, time, and effort.
  • Managing sets: Keeping multiple sets intact and preventing them from mixing can be frustrating.
  • Sustainability: The environmental impact of repeated printing and discarding materials is hard to ignore.

As a teacher of MYP Design, I’ve frequently encountered these issues, which inspired me to create MakeSort, a digital solution for sorting activities. Built using Google Slides, MakeSort eliminates the need for physical materials while retaining the interactive nature of sorting tasks. The design cycle provided an excellent framework for tackling this problem, allowing me to model practical problem-solving for my students.


The Solution: Gamifying Sorting with Google Slides

MakeSort is a digital, interactive sorting tool designed to address these challenges. Built entirely in Google Slides, it offers:

  • Paperless functionality: No more printing or laminating.
  • Scalability: A single template can be shared with hundreds of students via platforms like Google Classroom.
  • Gamification: Engaging features like drag-and-drop and feedback for correct sorting make learning fun.

Watch the demo video to see MakeSort in action:

Demo Video:

 

Google Slides proved to be an ideal platform. Its intuitive drag-and-drop interface is easy for students to use, and its scripting capabilities enabled the automation of answer-checking and other interactive features.


Setting the Stage: The Need for Shape Creation

One of the first hurdles was enabling teachers to dynamically create shapes for sorting tasks. It was designed to simplify this process, allowing users to input steps (e.g., “Kingdom, Phylum, Class, Order…”) and have the tool automatically generate draggable shapes on the slide. Each shape fulfills specific requirements:

  • Display the Step: Each shape clearly shows a unique step as input by the teacher.
  • Be Uniquely Identifiable: Shapes are assigned unique IDs, ensuring the activity can be validated later.

Key Function: createAndStoreDraggables(steps, slide)

This function handles the entire process of generating shapes dynamically while ensuring usability and flexibility. Here’s a breakdown of its core tasks:

  1. The function reads the teacher’s input from the sidebar and splits it into distinct steps using delimiters like commas or newlines.
  2. The parsed list of steps serves as the blueprint for creating shapes on the slide.
  3. For each step, a shape is created on the slide with a text box containing the step and a transparent overlay to enhance usability (e.g., by expanding the draggable area).
  4. Each shape is positioned randomly within the slide area for a shuffled initial layout.
  5. Every shape group is assigned a unique object ID upon creation, which is stored in the script properties to enable later operations like order validation or reshuffling.

 

Example activity

createAndStoreDraggables

Here’s how createAndStoreDraggables ensures dynamic shape creation:

function createAndStoreDraggables(steps, slide) {
  const groupIds = [];
  const stepCount = Math.min(steps.length, 16); // Limit to 16 steps

  // Dimensions and scaling for shapes
  const shapeWidth = SHAPE_CONFIG.shapeWidth;
  const shapeHeight = SHAPE_CONFIG.shapeHeight;
  const baseFontSize = SHAPE_CONFIG.fontSize;
  const pageWidth = SlidesApp.getActivePresentation().getPageWidth();
  const pageHeight = SlidesApp.getActivePresentation().getPageHeight();

  steps.forEach((step, index) => {
    try {
      // Random position within slide area
      const left = Math.random() * (pageWidth - shapeWidth);
      const top = Math.random() * (pageHeight - shapeHeight);

      // Create the text shape
      const textShape = slide.insertShape(SlidesApp.ShapeType.RECTANGLE, left, top, shapeWidth, shapeHeight);
      textShape.getText().setText(step);
      textShape.getText().getRange(0, step.length).getTextStyle()
        .setFontFamily(SHAPE_CONFIG.fontFamily)
        .setFontSize(baseFontSize);
      // Create a transparent overlay for better interaction
      const overlayShape = slide.insertShape(SlidesApp.ShapeType.RECTANGLE, left, top, shapeWidth, shapeHeight);
      overlayShape.getFill().setTransparent();
      overlayShape.getBorder().setTransparent();

      // Group text and overlay
      const group = slide.group([textShape, overlayShape]);
      groupIds.push(group.getObjectId());
    } catch (error) {
      Logger.log(`Error creating shape for step "${step}": ${error.message}`);
    }
  });

  return groupIds; // Return IDs for tracking
}

Highlights of This Approach

  • Dynamic Input Handling: Teachers have complete flexibility to input steps in natural formats (e.g., comma-separated or newline-separated). The tool intelligently processes these inputs to generate activity shapes.
  • Visual and Interactive Design: By grouping text shapes with transparent overlays, the function ensures that shapes are:
  • Robust Back-End Tracking: Each shape’s ID is stored persistently, allowing future operations like order validation, reshuffling, or clearing to reference the correct objects seamlessly.

Enhancing Usability with Grouped Transparent Covers

One of the subtle yet significant challenges in creating MakeSort was managing the user’s interaction with shapes. By default, Google Slides changes the cursor depending on the user’s hover position relative to a shape. Hovering near the edges or corners of a shape changes the cursor to indicate resizing or moving actions, while hovering in the center suggests text editing is possible. This behavior reflects Google’s thoughtful UX design, anticipating the user’s most likely desired action based on their pointer position.

text edit cursor and drag cursor

For sorting activities, however, where the primary goal is to move shapes rather than edit text, this default behavior can sometimes lead to confusion. Users may unintentionally enter text-editing mode instead of dragging the shape, disrupting the flow of the activity.

To address this, each shape in the sorting tool is grouped with a transparent rectangle slightly larger than the text box itself. This approach extends the draggable area while leaving the text untouched, allowing users to interact confidently with the shapes.

A transparent overlay that is placed over each draggable and grouped with it.

From the createAndStoreDraggables function above:

 // Create a transparent overlay for better interaction
      const overlayShape = slide.insertShape(SlidesApp.ShapeType.RECTANGLE, left, top, shapeWidth, shapeHeight);
      overlayShape.getFill().setTransparent();
      overlayShape.getBorder().setTransparent();

      // Group text and overlay
      const group = slide.group([textShape, overlayShape]);
      groupIds.push(group.getObjectId());

This simple yet effective design adjustment improves usability in several ways:

  • Increased Accuracy: A larger clickable area reduces the likelihood of missing the intended interaction, especially on devices with touchscreens.
  • Streamlined Interactions: Grouping the transparent cover with the text shape makes the action of moving shapes more intuitive and consistent.
  • Preserved Text Integrity: Users can focus on dragging and dropping without accidentally activating text-editing mode, maintaining the activity’s flow.

Adding Interactivity: Shuffling Shapes

A key aspect of MakeSort is engaging students by presenting them with a challenge. To do this, the shapes are deliberately shuffled, creating a “mess” on the slide. This chaotic arrangement encourages students to take charge, fostering a sense of control as they actively work to restore order.


The Value of Random Placement

Random placement serves several purposes:

  1. Engagement Through Challenge: When faced with a disordered slide, students feel empowered to sort it out. The task taps into a natural desire to organize and problem-solve, creating a more rewarding experience.
  2. Minimized Physical Effort: Random placement often means students don’t need to move blocks out of the way to place others in their correct position. This reduces unnecessary interactions and keeps the activity flowing smoothly.
  3. A Deliberate “Mess”: The scattered arrangement of shapes contrasts sharply with the final sorted order, making the activity visually impactful and reinforcing the importance of organization.

Key Function: shuffleGroups()

The shuffleGroups function randomizes the positions of the grouped shapes on the slide. Here’s how it works:

  1. Retrieve All Groups: The function identifies the draggable shapes on the slide using their unique group IDs.
  2. Randomize Placement: Each shape is assigned a new random position within the slide area. The placement logic ensures shapes don’t overlap excessively while spreading them out.
  3. Iterative Swapping: To ensure randomness, the function performs repeated swaps of two shapes’ positions, creating a dynamic and unpredictable arrangement.

Implementation of shuffleGroups

Here’s the function in action:

function shuffleGroups() {
  const slide = SlidesApp.getActivePresentation().getSelection().getCurrentPage();
  if (!slide) return "Error: Please select a slide to shuffle groups.";

  const slideId = slide.getObjectId();
  const props = PropertiesService.getScriptProperties();
  const groupIds = JSON.parse(props.getProperty(`SHAPE_IDS_${slideId}`) || "[]");

  if (!Array.isArray(groupIds) || groupIds.length === 0) {
    return "Error: No valid groups found to shuffle. Please recreate the activity.";
  }

  const groups = slide.getGroups();
  const groupMap = groups.reduce((map, group) => {
    map[group.getObjectId()] = group;
    return map;
  }, {});

  const groupsToShuffle = groupIds.map((id) => groupMap[id]).filter(Boolean);
  if (groupsToShuffle.length === 0) return "Error: No matching groups found to shuffle.";

  // Shuffle logic: Assign random positions
  const slideWidth = SlidesApp.getActivePresentation().getPageWidth();
  const slideHeight = SlidesApp.getActivePresentation().getPageHeight();

  groupsToShuffle.forEach((group) => {
    const left = Math.random() * (slideWidth - group.getWidth());
    const top = Math.random() * (slideHeight - group.getHeight());
    group.setLeft(left).setTop(top);
  });

  return "Success: Groups have been shuffled.";
}

Impact on the Learning Experience

  1. Active Participation: The randomness keeps the activity unpredictable, requiring students to engage fully with each task.
  2. Power Dynamics: Starting with a “mess” gives students a sense of authority as they bring order to chaos. This sense of accomplishment boosts confidence and satisfaction.
  3. Efficiency and Flow: Unlike a sequential layout, where students must move blocks out of the way to rearrange them, random placement minimizes unnecessary interactions, allowing them to focus on solving the puzzle.

Why This Matters

Random shuffling transforms the activity from a static exercise into a dynamic challenge. By creating a deliberate “mess” and encouraging students to take control, MakesSort fosters problem-solving skills and active participation. The psychological effect of resolving disarray into order is powerful, making the sorting activity both memorable and impactful.


3. Validating the Solution: Checking the Order

At the heart of the sorting tool lies its ability to validate whether students have arranged the shapes correctly. This validation is achieved through a straightforward yet elegant approach: comparing the vertical positions of shapes on the slide to the predefined correct order. The simplicity of this technique ensures that the tool is fast, reliable, and intuitive.


The Magic of Y-Values

Google Slides assigns every shape a set of coordinates (top, left) to define its position on the slide. The top coordinate, often referred to as the Y-value, determines the vertical position of a shape:

  • Smaller Y-values indicate shapes higher on the slide.
  • Larger Y-values correspond to shapes lower on the slide.

This inherent property of Google Slides allows us to efficiently validate the order of shapes by sorting them based on their Y-values.


Key Function: checkShapeOrder()

The checkShapeOrder function validates the arrangement of shapes with the following steps:

  1. Retrieve Shapes: The function identifies all draggable groups on the slide using their unique IDs stored in the script properties.
  2. Sort by Y-Values: The groups are sorted in ascending order of their Y-values (getTop()), representing their vertical order from top to bottom on the slide.
  3. Compare with Correct Order: The sorted order of shapes is compared against the pre-defined correct order stored in the MASTER_LIST during activity creation.
  4. Provide Feedback: If the two orders match, a success message is returned. If they don’t match, the function provides an error message, encouraging students to try again.

Implementation of checkShapeOrder

Here’s how the function works under the hood:

function checkShapeOrder() {
  const slide = SlidesApp.getActivePresentation().getSelection().getCurrentPage();
  if (!slide) {
    return "Error: Please select a slide to check the order.";
  }

  const slideId = slide.getObjectId();
  const props = PropertiesService.getScriptProperties();
  const masterList = JSON.parse(props.getProperty(`MASTER_LIST_${slideId}`) || "[]");
  const groupIds = JSON.parse(props.getProperty(`SHAPE_IDS_${slideId}`) || "[]");

  if (!Array.isArray(masterList) || !Array.isArray(groupIds)) {
    return "Error: Activity data is corrupted. Please recreate the activity.";
  }

  const groups = slide.getGroups();
  const groupMap = new Map(groups.map((group) => [group.getObjectId(), group]));

  // Validate all group IDs exist in the map
  const validGroups = groupIds.map((id) => groupMap.get(id)).filter(Boolean);

  if (validGroups.length !== groupIds.length) {
    const missingIds = groupIds.filter((id) => !groupMap.has(id));
    Logger.log(`Missing group IDs: ${JSON.stringify(missingIds)}`);
    return `Error: Some groups are missing or have been deleted. Missing IDs: ${missingIds.join(", ")}`;
  }

  // Sort by vertical position
  const sortedGroups = validGroups.sort((a, b) => a.getTop() - b.getTop());
  const currentOrder = sortedGroups.map((group) => group.getObjectId());

  if (JSON.stringify(currentOrder) === JSON.stringify(groupIds)) {
    Logger.log("Groups are in the correct order. Triggering winner dialog.");
    triggerWinnerDialog();
    return "Success: The groups are in the correct order!";
  } else {
    Logger.log(`Expected order: ${JSON.stringify(groupIds)}`);
    Logger.log(`Current order: ${JSON.stringify(currentOrder)}`);
    return "Error: Groups are not in the correct order. Try again!";
  }
}

Why Y-Values Work So Well

The Y-value method for validating shape order is not only intuitive but also highly efficient and robust. MakeSort simplifies the process by leveraging the inherent properties of Google Slides to streamline what would otherwise be a tedious manual task.

Key Advantages

  • Intuitive Logic: Since the Y-value corresponds directly to a shape’s vertical position, sorting by it mirrors the natural top-to-bottom order of items as they appear visually on the slide. This makes the process both logical and predictable.
  • Simplicity and Efficiency: By using the getTop() property, the function avoids the need for complex tracking, iterative comparisons, or external references. Sorting the Y-values and comparing them with the correct order is a single-step operation, making it highly efficient.
  • Seamless Feedback: Students receive instant feedback on whether their arrangement matches the correct order. This reinforces their understanding of the task and encourages them to refine their approach as needed.

Key Benefits

  • Accuracy in Validation: Sorting by Y-values ensures that the tool reliably identifies the correct top-to-bottom order, regardless of small positional variations.
  • Interactive Learning: Instant feedback keeps students engaged and allows them to learn through trial and error. They can visually see their progress and adjust their sorting accordingly.
  • Robust and Resilient: Even if shapes are slightly misaligned or overlap, the Y-value sorting remains accurate as long as the general vertical hierarchy is preserved.

Here’s why this method is more efficient:

  • The Y-value approach provides unmatched precision and speed compared to manual validation.
  • Manual validation requires a human verifier to repeatedly check each item’s position on the slide against the correct order, a process that becomes slower and more error-prone as the number of shapes increases.
  • The tool automates the entire process by retrieving all Y-values, sorting shapes in a single step, and instantly comparing the order to the correct answer.
  • This automation makes validation nearly instantaneous and eliminates the risk of human error or bias.

The Takeaway

By leveraging Y-values, MakeSort transforms what could be a labor-intensive validation process into an automated, efficient, and error-free operation. It aligns perfectly with the tool’s goal of providing an intuitive, interactive experience for students while ensuring teachers can trust the accuracy of the results.


4. Gamifying Success: Adding Celebration

Considering a Listener: Why Not Automate Validation

During development, I explored adding a listener to MakeSort. The concept was simple: a background function, called after shuffling is complete, that would automatically check the order every 2 seconds, eliminating the need to click the “Check Answer” button. To implement, I repeatedly call the checkShapeOrder function at set intervals to verify whether the shapes were arranged in the correct order. At first glance, this seemed like a user-friendly enhancement, streamlining the process and providing immediate feedback.

/**
 * Listener function to repeatedly check the order of shapes.
 * This function can be set to run at regular intervals to validate the current arrangement.
 */
function startValidationListener() {
  const interval = 2000; // Interval in milliseconds (2 seconds)

  // Function to repeatedly check the order
  function validateOrderPeriodically() {
    const isValid = checkOrder(); // Assuming checkOrder is implemented to validate current order

    if (isValid) {
      clearInterval(listener); // Stop validation once correct
      triggerWinnerDialog();  // Trigger success dialog or action
    }
  }

  // Set an interval for validation
  const listener = setInterval(validateOrderPeriodically, interval);
}

Here’s why I realized the drawbacks outweighed the benefits:

  1. Encouraging Thoughtful Interaction: Clicking “Check Answer” encourages students to reflect on their arrangement before submitting, reinforcing deliberate problem-solving. In contrast, a listener could promote brute-force trial and error, bypassing the critical thinking that makes sorting activities valuable.
  2. Maintaining Learning Integrity: Actively submitting an answer mirrors real-world scenarios, like test-taking, where deliberate action is required. Automating validation might reduce the cognitive engagement students bring to the activity.
  3. Avoiding Performance Overhead: A listener constantly checking for correctness introduces background processing, which could impact performance on devices with limited resources or large datasets.

Ultimately, I chose to keep validation as a deliberate, user-initiated action. This decision reflects a balance between user convenience and MakeSort’s primary educational purpose: fostering logical reasoning and intentional interaction. As a result, the “Check Answer” button remains central to the tool, providing clear feedback while preserving the integrity of the learning process.


Feedback and Celebration: Toasts and the Winner Dialog

Clear, engaging feedback is a cornerstone of the sorting activity tool. To achieve this, two separate UI elements were implemented: toast notifications for routine feedback and a Winner dialog for celebrating success. Each plays a distinct role in enhancing the user experience.

Toast Notifications: Streamlining Sidebar Functionality

Every action triggered from the sidebar (e.g., creating shapes, shuffling, checking answers) provides immediate feedback via a toast notification. These toasts:

  • Purpose: Communicate success or failure for routine actions like validating an order or resetting the activity.
  • Design: Styled as small, non-intrusive messages that appear temporarily at the bottom of the screen, they

Example toast notifications

function showToast(message) {
  const toast = document.getElementById("toast");
  toast.textContent = message;
  toast.className = "toast show";
  setTimeout(() => { toast.className = toast.className.replace("show", ""); }, 3000);
}

This function is called after every sidebar-triggered action using the google.scrit.run API, ensuring users receive contextual feedback whether an operation succeeds or fails.

 

winner dialog box

The Winner Dialog: Celebrating Success

The “Winner” dialog takes feedback a step further, offering a dedicated celebration when students correctly arrange the shapes. Unlike toasts, which provide routine updates, the Winner dialog:

  • Purpose: Rewards users for achieving the correct order, reinforcing their accomplishment.

Design: A modal dialog appears with a styled HTML banner featuring celebratory visuals like balloons, a bold “Winner!” message, and congratulatory text. For example:

function triggerWinnerDialog() {
  const html = HtmlService.createHtmlOutput(`
    <div style="text-align: center; padding: 20px;">
      <h1 style="color: green;">🎉 Winner! 🎉</h1>
      <p>Congratulations, you arranged the steps correctly!</p>
    </div>
  `).setWidth(300).setHeight(200);
  SlidesApp.getUi().showModalDialog(html, "Winner!");
}

When Each Element Appears

  1. Toasts: Triggered after every sidebar function. They provide feedback for actions like validating the order with “Check Answer,” indicating the success of “Shuffle Shapes” or “Clear Shapes,” or alerting users about missing input or errors.
  2. Winner Dialog: Triggered only when the checkShapeOrder() function confirms the shapes are in the correct sequence. It appears as a modal to emphasize the accomplishment.

Why Two Feedback Systems?

  1. Toasts: These provide quick, continuous feedback for routine interactions, ensuring users are informed without disrupting their workflow.
  2. Winner Dialog: This offers a dedicated moment of recognition for successfully completing the activity, reinforcing motivation and engagement.

Together, these UI elements balance functionality and celebration, supporting the tool’s focus on usability and ensuring users feel rewarded at every step.

5. Tailoring for Teachers and Students

To provide a streamlined experience for both teachers and students, the tool dynamically customizes the sidebar interface based on the logged-in user’s email. For students, unnecessary features are hidden, ensuring they only access the “Check Order” button. Teachers, on the other hand, have full access to all functionality.

This was achieved by analyzing the structure of student email addresses. In this system, student emails follow a specific format:

Email Format: [firstInitial][lastName][graduationYear]@schoolname.edu For example:

Key Function: checkUserRole

The checkUserRole function identifies whether a user is a teacher or student based on their email. It uses a regular expression (regex) to match the typical student email structure:

function checkUserRole() {
  const email = Session.getActiveUser().getEmail();
  const studentEmailPattern = /^[a-zA-Z]\w*\d{2}@kas$/; // Matches "[email protected]"

  if (studentEmailPattern.test(email)) {
    return "student"; // Graduation year pattern indicates a student
  } else {
    return "teacher"; // Otherwise, assume it's a teacher
  }
}

How the Regex Works

  1. ^[a-zA-Z]: Ensures the email starts with a single letter, which represents the first initial of the first name.
  2. \w*: Matches any combination of letters, numbers, or underscores (to represent the last name).
  3. \d{2}: Matches exactly two digits at the end of the name, representing the graduation year.
  4. @kas$: Matches the domain @schoolname.edu at the end of the email.

For example:

Dynamic Role-Based UI and Sidebar Adjustments

The checkUserRole function seamlessly integrates with the sidebar logic, dynamically adapting the interface based on whether the user is a teacher or a student. This ensures that each role has access to only the necessary features, enhancing both usability and security.

Student-view of Sidebar

 

Teacher-view of Sidebar

Key Sidebar Code

The following code dynamically adjusts the sidebar based on the user’s role detected by the checkUserRole function:

document.addEventListener("DOMContentLoaded", function () {
  const buttons = document.querySelectorAll("button");
  const checkOrderButton = document.getElementById("checkButton");
  const playAgainButton = document.getElementById("playAgainButton");
  const textArea = document.getElementById("stepsInput");
  const studentInstructionsButton = document.getElementById("studentInstructionsBtn");
  const bgImageDiv = document.querySelector("div > h3"); // Get Background Image section
  // Hide all elements by default
  buttons.forEach((button) => (button.style.visibility = "hidden"));
  if (textArea) textArea.style.visibility = "hidden";
  if (bgImageDiv) bgImageDiv.parentElement.style.visibility = "hidden";

  // Adjust visibility based on user role
  google.script.run.withSuccessHandler(function (role) {
    if (role === "teacher") {
      // Show all elements for teachers
      buttons.forEach((button) => (button.style.visibility = "visible"));
      if (textArea) textArea.style.visibility = "visible";
      if (bgImageDiv) bgImageDiv.parentElement.style.visibility = "visible";
    } else if (role === "student") {
      // Show limited elements for students
      if (checkOrderButton) checkOrderButton.style.visibility = "visible";
      if (playAgainButton) playAgainButton.style.visibility = "visible";
      if (studentInstructionsButton) studentInstructionsButton.style.visibility = "visible";
    }
  }).checkUserRole();
});

The Outcome: A Digital Sorting Tool

MakeSort successfully addresses the challenges it set out to solve. By eliminating the need for printed materials, it removes paper waste and the tedious process of cutting, laminating, and managing sorting cards. The digital format ensures that teachers save time and resources while also adopting a more sustainable approach.

Students benefit from an engaging, interactive experience through the tool’s drag-and-drop functionality. This hands-on approach not only makes sorting tasks enjoyable but also reinforces learning by integrating gamified feedback. Each correct solution is met with celebratory prompts, keeping students motivated and eager to engage with the activity.

Moreover, the tool is highly scalable. With a single script-enabled Google Slides file, teachers can create activities that are easily distributed to hundreds of students through platforms like Google Classroom. This simplicity makes the tool a powerful asset for educators managing large groups or diverse subjects.


Reflections: Why This Project Worked

The success of this project lies in its foundations:

It began with a clear, tangible need. As a teacher, I personally experienced the frustrations of managing traditional sorting activities. This firsthand understanding provided the motivation and clarity needed to develop a solution that directly addressed the problem.

The project was shaped by iterative development. Instead of attempting to create a perfect tool from the start, each feature was built step by step. New challenges arose throughout the process, and the tool was refined at every stage to overcome them. This flexible approach ensured continuous improvement.

Collaboration was key, with much of the coding and debugging done through conversations with ChatGPT. This approach allowed for brainstorming ideas, solving problems, and generating code in real time. Each feature reflects the creative synergy of coding alongside an AI partner, streamlining the development process and enhancing the tool’s design.


Next Steps: Expanding the Tool

While MakeSort already offers significant benefits, there’s still room for enhancement. Future versions could introduce features that extend its functionality and deepen its educational impact.

Hints or partial feedback for incorrect answers could provide students with additional guidance while preserving the challenge of sorting tasks. This would make the tool more accessible for learners who may struggle with certain topics.

A database of prebuilt sorting tasks for various subjects could save teachers even more time. Ready-made activities for subjects like history, science, or mathematics could help educators quickly integrate the tool into their lesson plans.

Allowing students to create their own sorting activities would open up a new realm of possibilities. By designing tasks for their peers, students could deepen their understanding of the material and develop their creativity in the process.

Closing Thoughts

Transforming Challenges into Opportunities

This project demonstrates the power of applying innovative thinking and technology to address practical challenges in education. By replacing paper-based sorting activities with an interactive, scalable digital tool, we’ve not only streamlined logistics but also redefined how students engage with learning tasks. Teachers now have a sustainable, efficient solution that prioritizes usability while empowering students through gamified, hands-on experiences.

The success of this project underscores a few key principles:

  • Design Thinking in Action: Starting with a real-world problem, each step of development focused on refining solutions to meet both teacher and student needs.
  • Collaboration and Adaptability: The iterative process ensured continuous improvement, turning obstacles into opportunities for innovation.
  • Sustainability and Scalability: By leveraging existing platforms like Google Slides, the tool eliminates waste and can easily be adapted for diverse educational contexts.

Looking ahead, the possibilities for enhancing MakeSort are boundless. From incorporating subject-specific sorting tasks to enabling students to design their own activities, this foundation sets the stage for broader applications in fostering creativity and critical thinking.

At its core, MakeSort serves as a testament to the transformative potential of digital tools in education. By focusing on solving real problems, we can create meaningful, lasting change in how we teach and learn.

I would love any and all feedback on MakeSort, especially from teachers who choose to use it in their classrooms or assign it through Google Classroom. Your insights and experiences are invaluable in refining and expanding this tool to better meet the needs of educators and students alike. Let’s keep exploring how technology can empower educators and inspire learners together.

 

Contact Us

If you have any questions, feel free to contact us at megiddo at gmail dot com.

Privacy Policy

Your privacy is important to us. MakeSort only uses Google account information to authenticate your access and ensure your activities are saved securely. Learn more in our Privacy Policy.

Terms of Service

 

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.

Go to Privacy Policy

Terms of Service

 

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.


Privacy Policy

Effective Date: January 5, 2025

Classroom Groups Manager (“the Application”) is committed to protecting the privacy of its users. This Privacy Policy outlines how your data is collected, used, and protected when using the Application.

1. Data We Collect

The Application may collect the following data:

  • User-Provided Data: Information such as student names input by users to facilitate seating chart creation and group management. This data is stored only within your Google Sheets and is not transmitted to external servers.
  • Usage Data: Basic analytics, such as error reports or performance metrics, may be collected to improve the Application. This data is anonymized and aggregated.

2. How We Use Your Data

Your data is used solely for:

  • Organizing seating charts and student groups within your Google Slides.
  • Enhancing the functionality and user experience of the Application.

We do not share, sell, or disclose your data to third parties.

3. Data Storage

All user-provided data remains in your Google Drive environment and is never stored or accessed by external servers.

4. Permissions

The Application requests permissions only as necessary to function:

  • Google Slides & Drive Access: To read, edit, and update slides for seating charts and groups.
  • Google Script Execution: To perform automated processes like group creation or chart updates.

5. Your Rights

  • You retain ownership of all data entered into the Application.
  • You may revoke the Application’s permissions at any time through your Google Account settings.

6. Security

We use Google’s secure platform and APIs, ensuring your data remains safe within the Google ecosystem.

7. Updates to This Policy

This Privacy Policy may be updated periodically. Continued use of the Application signifies acceptance of the latest policy.

Contact Us
If you have any questions, please contact me at megiddo at gmail.com


Terms of Service

 

Effective Date: January 5, 2025

These Terms of Service (“Terms”) govern your use of Classroom Groups Manager (“the Application”). By installing or using the Application, you agree to these Terms.

1. Application Overview

Classroom Groups Manager is a Google Slides add-on designed for teachers to create and manage dynamic seating charts and student groups.

2. License

You are granted a non-exclusive, non-transferable license to use the Application for personal or educational purposes, subject to these Terms.

3. Acceptable Use

You agree to:

  • Use the Application for its intended purpose.
  • Not reverse engineer, copy, or redistribute the Application.

4. Limitations of Liability

The Application is provided “as is,” without warranty of any kind. We are not liable for:

  • Any loss or damage resulting from the use or inability to use the Application.
  • Errors or inaccuracies in group or seating chart outputs.

5. Termination

We reserve the right to terminate your access to the Application if you violate these Terms.

6. Modifications

We may update these Terms periodically. Continued use of the Application signifies acceptance of the updated Terms.

7. Governing Law

These Terms are governed by and construed in accordance with the laws of [Your Jurisdiction].

Contact Us
For questions about these Terms, please email megiddo at gmail dot com


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!

 

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

 

 

 

 

 

 

 

 

 

 

 

 

 

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

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.

 

Student Comment Generator

 

By Avi Megiddo and ChatGPT


In the educational landscape, providing individualized and fair feedback to students is a cornerstone of effective teaching. Yet, the task of crafting comments can be a daunting challenge for educators, particularly when faced with the sheer volume of evaluations required. The  Student Comment Generator supports the creation of student feedback based on set performance metrics.

Simplifying Feedback through Automation

The Student Comment Generator is a custom Google Apps Script designed to automate the process of writing student feedback comments in Google Sheets. 

It works by analyzing performance metrics from specified columns, and using predefined phrases to compose “tailored” comments for each student.

The tool is designed with both efficiency and fairness in mind. By automating the initial draft of student comments, it offers a consistent framework that ensures each student is evaluated against the same criteria. This not only saves valuable time for educators but also fosters a more equitable assessment environment.  

Instructions

Setting Up Your Sheet: Begin by entering each student’s first name in column B and selecting the appropriate gender pronoun in column A from a dropdown list. This step primes the tool with the necessary personalization details for each comment.

Defining Performance Metrics: In row 1, cells D1 through K1, define up to eight performance metrics such as “Summative completion” or “Participation.” These metrics will drive the content of the automated comments.


I teach MYP Design, and we use performance metrics such as ‘overall completion’, ‘design cycle knowledge’, and ‘original & creative work’:

 

Crafting Performance Descriptors: For each metric, write corresponding low, middle, and high descriptors in columns N through U. These descriptors will be used to construct comments reflecting each student’s performance (thank you to my colleague YJ Choi for some UI/UX ideas). The descriptors text should include the placeholder [Name], and placeholder pronouns ‘He’, ‘he’, ‘His’, ‘his’, and ‘him’, and ‘himself’ instead of actual student names and specific gender pronouns. The script includes a function to replace the placeholders [Name], ‘He’, ‘he’, ‘His’, ‘his’, and ‘him’, and ‘himself’ in the phrases with actual student names and appropriate gender pronouns.  One of the intricacies of the tool lies in its handling of language, particularly gender pronouns. To accommodate the automation process and ensure clarity in substitutions, template comments are designed with masculine pronouns. This is not a reflection of gender bias but a practical decision based on linguistic clarity — ‘him’ and ‘his’ clearly map to ‘her’, whereas mapping ‘her’ to ‘his’ or ‘him’ is ambiguous. This choice ensures precision and correctness when customizing comments for students of any gender, whereas the placeholder ‘her’ would require more context to determine whether it should be replaced with ‘his’ vs. ‘him’ to match student gender pronouns. 

Generating Comments: With a simple click in cell L1, comments tailored to each student’s performance will populate in column C. You can also add an optional custom final sentence for each student in column L for that personal touch.

Continuity for New Classes: To replicate the setup for new classes, duplicate the sheet and replace names, genders, and metrics accordingly.

Key Components: Understanding the Script’s Mechanics

  • Data Retrieval: The script activates the Google Sheet and determines the range where student data and performance metrics are stored. Columns D to K are designated for performance metrics, with each level—Low, Middle, and High—representing different areas of student performance.
  • Comment construction: For each student, the script dynamically assembles a comment by sequentially appending the appropriate phrases, based on the performance level in each metric; It retrieves pre-defined phrases corresponding to each performance level and appends them into a coherent comment. Phrases corresponding to performance levels are stored separately in Columns N to U. When the ‘generateComments‘ button is clicked, the script matches each student’s performance level from Columns D to K with the appropriate phrases from Columns N to U, crafting a comment that reflects their achievements and areas for growth.

  • Rich Text Styling: As the comment for each student is built, the script also applies rich text styling. The color of each sentence within the comment corresponds to the performance level it represents, enhancing readability and emphasis.

  • Final Output: The fully constructed and styled comments are then written back to the Google Sheet, each aligned with its respective student. The script ensures that the comments are visually coherent and aligned with the data they are based on.


Handling Incomplete Data

  • Flexibility with Performance Metrics: The Student Comment Generator script is designed to accommodate varying amounts of performance metrics. Users are not required to fill all 8 performance metrics for each student; the script intelligently ignores empty columns, ensuring smooth operation even if some metrics are missing.
  • Dealing with Partial Evaluations: In situations where educators might not evaluate certain students on all metrics, or if they fail to set every dropdown menu to “Low”, “Middle”, or “High”, the script is equipped to handle these cases gracefully. If a metric is left unevaluated for a student, the script will simply omit that part of the comment, ensuring that the remaining evaluations are still processed and comments generated accordingly.
  • Note: It is important to ensure that every metric column has a valid value (Low, Middle, High, or empty) to avoid errors during script execution.


Extending Functionality

Adding More Performance Metric Columns: Expanding the tool to accommodate additional performance metrics involves several steps and requires a deeper understanding of the script:

  • Update the Google Sheet: Add new columns for the additional metrics and extend the data validation (dropdown menus) to these columns.
  • Adjust the Phrase Range: Move the corresponding phrases for the new performance levels (Low, Middle, High) to align with the newly added columns.
  • Modify the Script:
  1. Update performanceLevelEndColumn in the script to reflect the new end column index.
  2. If you’ve added the new metrics beyond the original phrase range (“N2:U4”), update the phrase range in the script to include these new phrases.
  3. Ensure that the logic for constructing comments (commentParts array) and applying styles accommodates the additional columns.

Caution: Extending the functionality for more metrics requires careful adjustments to maintain the script’s integrity. It’s recommended for users with a basic understanding of Google Apps Script.

Robustness and Customization: The script is robust enough to handle varying scenarios and can be customized to fit different assessment structures. However, significant changes or additions might require a deeper dive into the script’s logic and structure.

Controversy and the Human Element

The use of automated tools in education can stir debate. Critics might argue that automation could depersonalize the feedback process. However, the Student Comment Generator tool is intended to create a starting point for comments, which educators can then review and personalize. It ensures that comments retain a consistent structure, while the final sentence or any additional feedback remains open for customization. This balance between automation and human oversight ensures that the tool enhances the educator’s workflow without compromising the personal touch so crucial in student feedback.

Navigating App Verification and Maintaining Trust

As with many custom scripts developed for Google Sheets, the Student Comment Generator tool starts as an unverified app. Google’s verification process is a rigorous one, and not all apps undergo this procedure. However, the label “unverified” does not inherently signify a security risk. It simply means that the app has not been through Google’s official verification process.

When educators make their own copy of the tool, it becomes a new project, inheriting the unverified status anew. Despite this, users can trust in the transparency and safety of the script. We encourage educators to examine the code themselves and run the tool within their domain to maintain control over their data and privacy.

Granting Permissions with Understanding

When you start using the Student Comment Generator, Google’s security measures will prompt you with a few steps to ensure that you are authorizing a script that has not been verified by Google. This is a standard part of the process for scripts that automate tasks within Google Sheets:

  • Unverified App Prompt: You’ll see a message stating “Google hasn’t verified this app.” This is normal for custom scripts. Click “Advanced” to see more options.
  • Proceed Safely: After clicking “Advanced,” choose “Go to Student Comment Creator (unsafe).” The label “unsafe” is automatically applied to scripts that haven’t been through the Google verification process, but as long as you trust the source or have verified the code yourself, you can proceed safely.
  • Choose Your Account: Select the Google account you’d like the script to access. This allows the script to integrate with your Google Sheets.
  • Permission to Run: Lastly, grant the necessary permissions by clicking “Allow.” This step is crucial for the script to operate, enabling it to read and modify your spreadsheet data to generate comments.

As this tool is in its beta phase, I invite your feedback and suggestions.

The Promise of Productivity

The Student Comment Generator tool is designed with both efficiency and fairness in mind. By automating the initial draft of student comments, it offers a consistent framework that ensures each student is evaluated against the same criteria. This not only saves valuable time for educators but also fosters a more equitable assessment environment.

Complete Code:

Student Comment Generator by Avi Megiddo code.gs

© Avi Megiddo 2023 CC BY-NC 

 

Teacher tool: make random orders, partners, and groups


Make random orders, partners, and groups

This is a handy tool that will make organizing random orders, partners, and groups a breeze in your classroom. With just a few simple steps, you can randomize student names, create random partners, and form groups of three or four. This tool is designed to save you time and ensure fairness in your classroom activities and assignments.

DEMO:

To get started, follow these instructions:

  1. Open the template preview of the Google Sheet.
  2. Click the ‘USE TEMPLATE‘ button in the top-right corner.
  3. Paste the names of your students in Column B, starting from cell B3.
  4. To randomize the order of the names, click the ‘Randomize‘ button.
  5. To create random partners, click the ‘Make Partners‘ button.
  6. Click the ‘Groups of 3‘ or ‘Groups of 4‘ buttons as you wish.

The code:

I would like to assure you that running the unverified Google Apps Script I’m sharing is a safe and straightforward process. Here’s what you can expect:

  1. Authorization Request: When you run the script, you’ll encounter an authorization request. This is a standard procedure and necessary for the script to access specific data within your Google account.
  2. Clicking “Advanced”: During the authorization process, you will be prompted to click on the “Advanced” option. This step allows you to review and understand the script’s permissions before proceeding.
  3. Allowing Script to Run: After clicking “Advanced,” you might encounter a warning about the script being unverified. Rest assured, this doesn’t mean any malicious activity is occurring. To proceed, simply select the option to allow the script to run.

Please note that by allowing the script to run, you are basically giving it permission to read from and modify the contents of cells in the sheet. This is necessary for the script to perform the requested actions for you.

Additionally, I want to clarify that the code for this script was authored with ChatGPT. However, it is self-contained and does not involve any external APIs or calls to AI when it is run. Your data and device will remain secure. Feel free to use and modify the code as per your requirements.

I hope this tool simplifies your classroom management and fosters a collaborative and inclusive learning environment. If you have any questions or feedback, please don’t hesitate to reach out.

Happy teaching!

Avi

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


MYP Rubric Share

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 Rubric ShareThis is a requirement in order to get verified by Google.

 

Terms of ServiceThis is a requirement in order to get verified by Google.

1. Agreement to Terms

By using this MYP Rubric Share 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 Rubric Share 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.

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…

Aquarium of Appreciation a.k.a. “Box of Thanks”


This project is now verified by Google!

 

 

 

 

 

 

 

A.K.A. Box of Thanks

This project was “Box of Thanks” for most of its design cycle, until a deep dive down the rabbit hole of name options with ChatGPT, which yielded, among others, “Basket of Blessings”, “Bin of Beneficence”, “Barrel of Blessings”, “Bottle of Bravos”, “Bowl of Benevolence”, “Binder of Benisons”, “Chest of Cheers”, “Canister of Compliments”, “Cup of Commendations”, “Crate of Credit”, “Capsule of Congratulations”, “Cask of Compliments”, “Coffer of Congratulations”, “Container of Compliments”, “Carafe of Cheers”, “Dish of Devotion”. “Drawer of Dedication”, “Envelope of Esteem”,  “Flask of Felicitations”, “Folder of Favors”, “Goblet of Gratitude”, “Hive of Homage”, “Jar of Jubilation”, “Jug of Joy”, “Keg of Kudos”, “Kit of Kindness”, “Locker of Love”, “Mug of Merit”, “Magazine of Magnanimity”, “Notebook of Nods”, “Niche of Niceties”, “Pouch of Praise”, “Pot of Props”, “Receptacle of Recognition”, “Reservoir of Respect”, “Sack of Salutations”, “Safe of Support”, “Tub of Tribute”, “Trunk of Thanks”, “Urn of Uplift”, “Vase of Validation”

I then requested some names that end with “… of Appreciation”, which yielded “Archive of Appreciation”, “Album of Appreciation”, “Anthology of Appreciation”, “Alcove of Appreciation”, “Atrium of Appreciation” “Attic of Appreciation”, and “Aquarium of Appreciation (if you’re open to a bit of a metaphorical stretch, with each message of appreciation being like a colorful, unique fish in a communal tank)”. Yes, it was ChatGPT’s idea.  It seems more engaging to click ‘Save to Aquarium’ rather than ‘Submit’, and to click on the fish to read the messages others wrote, rather than boring old ‘Read Messages’.  The underlying code is virtually identical.

DEMO:

Having a “Box of Thanks” or “Aquarium of Appreciation” for a class serves as a powerful tool for acknowledgment, recognition, and appreciation. It allows individuals to express their gratitude towards others who have made a positive impact on their lives or contributed to the school community. By encouraging the habit of expressing thanks, the “Box of Thanks” promotes a culture of appreciation and strengthens interpersonal relationships. This practice cultivates emotional intelligence by nurturing empathy, recognizing the efforts of others, and fostering a supportive and uplifting atmosphere.

Having a virtual Box of Thanks or a suggestion box in Google Sheets offers significant practical advantages over a physical box located somewhere at school or in the office. The traditional method of using a physical box requires individuals to locate the box, write their notes on paper using a pen, and physically place their submissions inside. However, this process can be cumbersome and may present several challenges.

Firstly, finding the physical box itself can be a barrier. Individuals must know the specific location where the box is placed, which may not always be easily accessible or well-known to everyone. In contrast, a virtual box in Google Sheets can be easily accessed by all stakeholders with just a click, ensuring that the submission process is convenient and readily available to everyone.

Secondly, the use of paper notes in a physical box can lead to challenges in organization and preservation. Paper scraps can get lost or damaged, making it difficult to keep track of the submissions. On the other hand, a virtual box in Google Sheets allows for efficient data management, ensuring that all entries are stored securely, organized, and easily retrievable.

Furthermore, the process of reading and addressing submissions from a physical box can be time-consuming and limited to a single person or a small group of individuals responsible for handling the box. It may take a considerable amount of time for the box to be opened and for the contents to be reviewed. This can delay acknowledgment or action on the feedback provided. In contrast, a virtual box in Google Sheets enables multiple authorized individuals to access and review the submissions simultaneously, facilitating a more efficient and prompt response to the ideas, suggestions, or expressions of gratitude received.

Welcome to the Aquarium of Appreciation

Under the surface (of the water), it’s just this:

With this tool, students or teachers can share their messages of gratitude by clicking on “Appreciate Someone”. They can also read previous notes of appreciation by clicking on the fish.

The template features an ‘Appreciate Someone’ script-enabled button in the “Main Display” sheet, which allow users to add messages to the aquarium. When clicked, this button opens a modal dialog box with a form, enabling users to enter a name, recipient, and a message of thanks. The form submission triggers the processForm(form) function. This function stores the form values in the “Entries” sheet and protects the newly added range, ensuring that only the owner can edit it.

Each fish is assigned the same Google Apps Script function. Clicking on a fish triggers this getRandomKindness() function. When clicked, this function retrieves a random entry from the “Entries” sheet, displays it in a modal dialog, and keeps track of the displayed rows to avoid displaying the same entry twice.

All the data is stored in a hidden sheet but can be accessed by a user. This is because the script needs to update the entries on the user’s behalf, so it cannot be locked or protected in this implementation.

The data is stored in a hidden sheet but can be accessed by a user. This is because the script needs to update the entries on the user’s behalf, so it cannot be locked or protected in this implementation.


 

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.


Privacy Policy for Aquarium of Appreciation

This is required by Google for verification purposes.

What data do we handle? Our application does not actively collect any personal data. However, the application verifies ownership of the Aquarium of Appreciation 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 Aquarium of Appreciation 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 Aquarium of Appreciation Google Sheet Template, and not elsewhere. Users can manually delete the data in the Aquarium of Appreciation 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 Aquarium of Appreciation 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 (required by Google for verification):

1. Agreement to Terms

By using this Aquarium of Appreciation 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 Aquarium of Appreciation 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.


Complete code and explanations here:

There are 3 scripts you will need to add to the Google Apps Script Editor: Code.gs  UI.html, and messages.html. You will need all three in order to implement the Box of Thanks in Google Sheets.

Code.GS file:


Box of Thanks - Code.GS

UI.html file:


Box of Thanks - UI.HTML

message.html file:


Box of Thanks - message.HTML

Aquarium of Appreciation - Privacy Policy