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 

 

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 Smart Rubric

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

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

This tool utilizes Google Sheets + Google Apps Script.

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

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

Please have a look at the demo video:

 

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

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

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

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

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

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

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

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

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

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

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

Cookies Our application does not use cookies.

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

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

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

1. Agreement to Terms

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

2. Changes to Terms

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

3. Use of the Application

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

4. Ownership

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

5. Limitation of Liability

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

6. Contact Information

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

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…

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

 

 

This project is now verified by Google!

Google Sheets is a game-changer for managing tournament brackets.

Author: Avi Megiddo

Welcome to the world of tournament brackets reinvented! In this article, I want to take you on a journey that began with my fascination for brackets during my elementary school days. I vividly remember how our principal would post these elaborate double-elimination tournament brackets, drawn with markers on butcher paper, for various school sports, chess competitions, and other tournaments. I would stare at them, eager to figure out the underlying logic. How could the brackets be structured to accommodate scenarios other than 4, 8, or 16 participants? This curiosity stuck with me, and as technology advanced, I continued to think of innovative solutions to simplify the bracket management process.

As an educator and a tech enthusiast, I have frequently observed the struggles of managing tournament brackets. Whether it’s in a classroom setting or a community sports event, maintaining tournament brackets can be notoriously challenging. Handwritten brackets necessitate rewriting names, erasing and correcting mistakes, and a plethora of papers and posters. The process is not just tedious but is also prone to errors. Additionally, subscription-based digital solutions pose their own set of problems such as subscription, privacy concerns, payment issues, and rigid UI. This led me to a question – Can Google Sheets offer a better solution?

Research, Tinkering, and Discovering

In March 2022, prior to ChatGPT, I started to explore the potential of Google Sheets for creating dynamic tournament brackets. The initial design concept was to use the cell border lines strategically to create the tournament bracket lines on which to write player names and to guide the rounds of elimination. This required meticulous work, but I think it was worth it. 

One strong advantage of using Google Sheets is the automatic advancement of winners to the next round through cell formulas and functions.  However, this would require the user to indicate who won.  An early feature I envisioned was the use of checkboxes to indicate the winners.  I found a snippet of code online that could turn checkboxes into radio buttons,  (credit to Ben Collins): https://www.benlcollins.com/apps-script/radio-buttons-in-google-sheets/.  Being able to click once to update match winners was essential for updating the tournament brackets efficiently. I customized this code to suit the specific needs of the tournament brackets.

The journey continued as I experimented with cell formulas and explored the possibilities of Google Apps Script. Later that year, I found an invaluable ally in ChatGPT, which greatly simplified the creation of cell formulas and scripts.

The Final Product: Interactive Tournament Brackets for All

Demo:

This template is a culmination of innovative thinking, smart use of technology, and the desire to provide an efficient, free, and shareable solution for managing tournament brackets. This Google Sheets tool is designed to be user-friendly, customizable, and efficient. Here’s what it offers:

• Checkboxes to Indicate Winners: No more scribbling names or making erasures. A single click is all it takes to update the brackets, making the process of indicating winners delightfully simple.
• Customizable Bracket Design: You have the freedom to use cell borders to design your tournament brackets. Customize the appearance to fit the theme and aesthetic of your tournament.
• Single and Double Elimination Templates: The sheet provides templates for both single and double-elimination tournaments, catering to different competition formats and preferences.
• Participant Randomization and Easy Resetting: Randomize participant orders for fair play and effortlessly reset the sheet for new tournaments without hassle.
• Streamlined Tournament Management: Once the participants’ names are entered into the sheet, tournament management becomes a breeze. The sheet advances winners based on checkboxes, and manages conditional matches, ensuring a smooth and efficient tournament flow.
• Ensuring Data & Format Integrity with Cell Protection: To prevent accidental changes to crucial data and formulas, cell protection is incorporated into the design. It warns users attempting to edit protected cells and helps maintain the integrity of the sheet. This is particularly useful for safeguarding critical formulas that should not be altered accidentally.
• No Subscription or Privacy Worries: This tool is free to use and fully customizable. You won’t have to worry about subscription costs, and your data stays with you, ensuring privacy.
• Engaging for Educators and Students: With its intuitive design and efficient management capabilities, this tool is hopefully both practical and engaging. Educators and students may find it an accessible and educational addition to their tournament activities.

Taking Tournament Management to the Next Level

While this Google Sheets tool is a highly functional solution, it represents a beta version and is continuously evolving. I am excited to share it with fellow tournament enthusiasts, educators, and tech-savvy individuals who appreciate efficiency, accuracy, and user-friendly experiences. Your feedback and suggestions will be invaluable for refining and enhancing this tool.

Follow the instructions below to get started:

1. Open the template preview of the Single & Double-Elimination Brackets Sheet
2. Click on the ‘USE TEMPLATE‘ button in the top-right corner of your sheet.
3. Choose your tournament type: 3, 4, 6, 7, 8, 10, 12, or 16.
4. Sheets with ‘s’ after the number are single-elimination tournaments.
5. Do not change the sheet names, as the script relies on them to run.
6. Enter the names of the participants in Column A, starting with cell A2.
7. Pick the order, or click the ‘randomize order‘ button for random matchups.
   

   ---

The code:

 

Running this Google Apps Script project is safe. If you do not enter the project number, Google treats your copy of the template as a new project.  Your project is unverified. To verify, enter my project number, which has been verified by Google.

If you do NOT enter my project number, 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 tournament brackets. Play on!

Privacy Policy for Tournament Brackets – This is a requirement in order to get verified by Google.

What data do we handle? Our application does not actively collect any personal data. However, the application verifies ownership of the Tournament Brackets 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 Tournament Brackets 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 Tournament Brackets Google Sheet Template, and not elsewhere. Users can manually delete the data in the Tournament Brackets 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 Tournament Brackets Google Sheet Template you are using. Your data is not stored anywhere else.

Cookies Our application does not use cookies.

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

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

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

1. Agreement to Terms

By using this Tournament Brackets 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 Tournament Brackets 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.

Google Sheets Word Search Maker

Create custom word search puzzles with ease using this convenient tool. With just a few simple steps, you can generate word search puzzles using your own list of words.

This tool is perfect for teachers, students, or anyone looking to have some fun with words. Follow the instructions below to get started:

1. Open the template preview of the Word Search Generator Google Sheet
2. Click on the ‘USE TEMPLATE‘ button in the top-right corner.
3. Enter or paste a list of words in Column A, starting on cell A2
4. Click the ‘make puzzle + answer key‘ button
   

   ---

This tool will automatically generate a word search puzzle by randomly placing the words in any possible direction within the grid. It will also color-code the cells containing the words to create an answer key. The original sheet’s name with have “-ANSWER KEY” appended to it, it will be duplicated, and the duplicate will have ” – HIDE ANSWERS” appended to its name. This duplicate sheet will serve as the word search puzzle, which can be projected on a whiteboard, shared digitally, or printed.

If you wish to save the puzzle as a PDF, click the ‘Print to PDF’ button. This will create a PDF version of the puzzle in your Google Drive, and open it in a neighboring tab.

Privacy Policy for Word Search Maker

(for the purposes of getting this app verified by Google)

What data do we handle? Our application does not actively collect any personal data. However, the application verifies ownership of the Word Search Maker 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 Word Search Maker 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 Word Search Maker Google Sheet Template, and not elsewhere. Users can manually delete the data in the Word Search Maker 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 Word Search Maker 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

(for the purposes of getting this app verified by Google)

1. Agreement to Terms

By using this Word Search Maker 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 Word Search Maker 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.

 

---

The code:

Word Search Maker - Privacy Policy

 

Running the unverified Google Apps Script I’m sharing is a safe and straightforward process. Here’s what you will see on the first run:

1. Authorization Request: When running the script, you will encounter an authorization request, which is a standard procedure 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 to review and understand the script’s permissions before proceeding.
   
3. Allowing Script to Run: After clicking ‘Advanced’, you might see a warning about the script being unverified. Rest assured, this does not indicate any malicious activity. To proceed, simply select the option to allow the script to run..

Please note that by allowing the script to run, you are granting permission for it to read from and modify the contents of cells in the sheet. This is necessary for the script to perform the requested actions and only needs to be done once.

Additionally, I want to clarify that the code for this script was co-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. I hope this information alleviates any concerns you may have.

I hope you enjoy using the Word Search Generator tool to engage and challenge your students or to simply have fun solving word search puzzles. If you have any questions or feedback, please feel free to reach out.

Happy puzzling!

~ Avi

---

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:

Version 3.0 Make Random Orders, Partners, Groups TEMPLATE
Latest Version July 16, 2023: This version handles remainders well.  If there is an odd number and you are making partners, it adds the remaining person to pair, randomly.  If similarly, if there is a remainder for groups of three, it makes 1 random group of 4. if there are 2 remaining, they are a pair.  Similar handling for group of 4, ensuring no group has less than 3 or more than 5 students each.

Version 3.0 also allows adding/editing bad pairings, to ensure certain pairs do not end up in the same group.  Use at your discretion!

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.

Version 2 code:

Privacy Policy

What data do we handle? Our application does not actively collect any personal data. However, the application verifies ownership of the Make Random Orders Groups 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 Make Random Orders Groups 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 Make Random Orders Groups Google Sheet Template, and not elsewhere. Users can manually delete the data in the Make Random Orders Groups 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 Make Random Orders Groups 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 Make Random Orders Groups 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 Make Random Orders Groups 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.

---

Version 1.0 Make Random Orders, Partners, Groups TEMPLATE
This is an earlier version that does not handle remainders well; it just lists them on a new row, but this doesn’t really work, for example, to have groups and then 1 person working alone.

I am in the process of verifying this app.  In the meantime, running the unverified Google Apps Script I’m sharing is a safe and straightforward process. Here’s what you can expect:

Version 1 code:

---

 

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.  

Lastly, check out my Scratch version of Make Random Partners & Groups

Happy teaching!

Avi

Inspired by the NY Times Spelling Bee, I challenged myself to create a Scratch version of this game.
Rules: Words must be at least 4 letters long, and must contain the yellow center letter. Letters do not have to be adjacent to each other, and duplicating letter is allowed. I created the background hexagons with Google Drawings and Inkscape. In Scratch, I created a “dictionary” based on English word list from: http://www.mediafire.com/file/5cy3vcsag29ic43/Word_List.txt/file
I also created letter frequencies based the following sources:
https://www3.nd.edu/~busiforc/handouts/cryptography/letterfrequencies.html
https://en.wikipedia.org/wiki/Scrabble_letter_distributions
https://en.wikipedia.org/wiki/Letter_frequency

I got a great suggestion from Nirmala Sankaran, Co-Founder of HeyMath, to include the total possible words for a given board. This required iterating over the dictionary and checking if each word is valid for each board, given the game rules.

You can find the original code here: https://scratch.mit.edu/projects/603333011

---