Educators, we have the power to automate!
7 ways to use automation with Google AppsScripts + ChatGPT in your school!
AppsScripts and ChatGPT empower educators to automate tasks and save time
For School Leaders and Teachers, Automation is now very much within reach, even without coding experience.
Use-cases include sending automated messages, updating data dynamically, and creating/managing files efficiently.
My personal transformation Continues
Last month I wrote about my game-changing experience with ChatGPT that empowered me to automate tons of tasks.
Now, 6 weeks later, I can confidently say that this newly acquired automation skill has not only transformed my capabilities but will continue to elevate my work in ways I never imagined!
So far, as a novice, I have already created 20 different automations. In my estimation this has saved me and our admin executives over 100 hours. This has made me feel like superwoman (lol). And so I have gotten even more obsessed and can happily spend hours playing around with new ideas on the weekend.
An effort to catalyze our school community
While I continue to do this, a secondary goal has emerged - As I continued automating tasks, I realized that this skill could benefit more than just myself. Not just that, the Appscript + ChatGPT combo suddenly makes automation accessible to a much wider range of people - no coding expertise needed.
I am confident that many of the leaders and teachers on our team can be empowered to learn the basics and join my ‘innovation through automation’ movement at school. Already, two of our leaders have successfully created their first automations with AppScript + ChatGPT. All it took was a recording of a short 10 minute ‘how to’ demo. I hope to do the same for this blog, but since I don’t want to share any school data publicly, I’ll create a separate video.
Helping Educators get started with some Use-Cases
For many educators, what can sometimes be hard when you are getting started (especially without any coding experience), is imagining the possibilities in your context. And that is what has prompted this post. I thought that listing all my use-cases could help interested educators with this.
Here are some automations that have been useful to me in the last 6 weeks.
1. Triggering Automated Actions:
What it is:
We can have tasks (called ‘functions) run automatically when certain things (called ‘events’) happen, like when someone fills out a form or when data is changed in a spreadsheet. They respond instantly to these actions.
Why it is useful to me:
No more hourly/ daily/ weekly monitoring of status. No more manual updates.
Examples:
Form Completion trigger:
When someone completes the summative assessment (google form) on our self-paced online PD site - we can automatically send them an email acknowledging completion (and a certificate, but more on that in point 8)
Google sheet edit trigger:
When our team addresses feedback items from our feedback forms, they mark it as done on a google sheet. As soon as the status is updated on the google sheet, an automated email is sent to the person who submitted the feedback, letting them know if it has been incorporated now, later or will not be used.
2. Sending Automated Messages:
What it is:
We can create tasks (functions) to automatically send emails, alerts, or notifications when certain conditions are met.
Why it is useful to me:
No more manual emails (or Mail Merge). Especially when you have large teams.
Examples:
Targeted Reminder emails:
We can send reminder emails to specific people that haven’t completed a task by a deadline. We can even cc relevant people that need to provide support or stay informed.
Personalized emails:
These emails can also be personalized to 1) address people by name 2) Give a specific message to specific people, for example - thank the people who finished the work on time but send a reminder message to those who haven’t.
3. Comparing and Matching Lists:
What it is:
We can compare two or more lists of items, looking for differences, matches, or missing information. It’s basically ‘VLOOKUP’ but much easier and smarter.
Why it is useful to me:
No more manual search and less room for human error.
Examples:
Missing items / participants:
Checking which emails are missing from a list, or comparing completed forms to see who hasn’t submitted yet.
You can even have the automation review the data from 20 different forms just by providing the form urls!
Sub-Team Data:
Pulling out data for just the Math team teachers, from the entire list of teachers.
4. Dynamically Updating Information:
What it is:
These functions update content in forms, documents, or spreadsheets based on changes made elsewhere. They ensure that everything stays up to date without manual effort.
Why it use useful to me:
Avoids lags in updation and Eliminates discrepancies across data sheets due to lag or human error
Examples:
Updating Google Sheets
Keeping a spreadsheet updated with new entries or changes in multiple other sheets.
Populating form drop downs
Automatically updating a dropdown menu in a form from a list of teachers in a google sheet. When the sheet is updated, the drop-down is automatically edited. We can now do this for a range of forms we use - teacher observation data, surveys and feedback etc.
5. Data organization and processing:
What it is:
These automations can pull together information from multiple places and perform specialized data processing tasks based on custom logic or specific rules.
Why it use useful:
Extremely quick with fairly large amounts of data, minimal human error, can talk to many different data sources easily for processing
Examples:
Summarizing/ sorting/ filtering
Organizing feedback from surveys based on specific needs. When you have 300+ teachers and four campuses, this is useful - I can quickly pick relevant info - which campus, which section etc.
Program Level data from various different sheets, forms, docs
Calculating Program level data like team averages/ percentile values
Reorganizing back-end data for visualizing on looker studio (this is another game changer, but this needs to be a separate post)
6. On-demand repetition of complex tasks
What it is:
I can create a button that does an automated task (function) on demand. It allows me to teach the computer how to do it once, and then make the computer do it again anytime I need to.
Why it use useful to me:
Avoids repetition of the same task (even if it is fairly complex, as long as it is the same process every time)
Examples
All of the examples given above are usually repeated tasks for me - I need to update form dropdowns, I need to send the automated emails once every quarter, I need to update lists when new teachers join mid-year. Now all I need to do is hit the button and the tasks are repeated with the latest data :)
7. Managing and Creating Files:
What it is:
We can even automate the creation or managing of files like documents, slides or spreadsheets. It can also generate personalized files for different people.
Why it is useful:
Saves a huge amount of manual work
Examples:
Certificates
Creating individual certificates, personalized reports in PDF or Google Docs format for a large number of participants
Google Form Quizzes
I was also able to create a Google form quiz, using questions in a Google doc. I still don’t have confidence that this automation will do it error-free every time, but with a little more testing I am sure I can get it there!
Gathering information from Google drive
I was able to consolidate information from over 100 folders onto a single file.
3 Quick tips to get you started
Just ask ChatGPT ‘how can I …’ and explain as well as you can. ChatGPT will respond by (1) repeating the task back to you as it understood and (2) providing you with code that you can copy. Continue the conversation to build understanding until you feel ChatGPT has repeated the task correctly to you.
Follow the instructions and report the errors back to ChatGPT. Use the corrected code. Keep doing this. Sometimes I take 5 iterations, sometimes I take close to 40 :) .
Persevere. ChatGPT does all the heavy-lifting. All you need to do is be resilient, keep trying and continue learning.
Bonus tip: If you are serious about making this happen, keep a track of all the App script automations you build on a google sheet. You will find yourself revisiting and reusing a ton of stuff. Here is a photo of how I organize mine!
Loved this Radhika. You need to do a course on A.I. in Education for educators. I think it would be a really popular course for teachers and educators in India and the world.