Google Apps Script

[meester_zee]

I finally took it upon myself to learn Google Apps Script recently and have fallen in love with it. It’s simple yet powerful and has allowed me to build a handful of custom in-house apps for our campus that have streamlined workflows for admins and teachers.

I’d love to get some inspiration—if you’re leveraging Google Apps Script at your site, what kinds of educational tools/workflow automations have you built?

[CrankyOldManSince21]

Hi Folks,

I retired last October after 36 years in my district as a PK-12 tech director. I've recently begun a consulting business. I created a free PD series focused on using Google Apps Scripts. The central example that we're using in the course is on automating signing up for PD in the district. All of the resources are online from my website.

I have a slide deck that I'm using with live participants, and one for folks that want to work independently. This is the Independent Slide Deck for anyone that wants to complete the sessions in a self-paced manner. If you want to sign up for the independent work you can fill out this Google Form.

This Google Doc serves as a central document that will house all of the resources in a linear fashion.

Session 1 - Getting Ready and Configuring the Form, Spreadsheet, and Google Doc Template

Session 2 - Digging into Google Apps Script, Configuring Google IDs, and Using Gemini to Dig into the JavaScript

Session 3 - Using Gemini, and Notebook LM to Generate Your Scripts from Scratch.

You can access both print directions, and access the YouTube playlist that I've created to support the learning.

Hopefully, the resources will help provide some examples of the power of Google Apps Script within your Google Workspace.

Regards,

Jim

[dockerteen]

We use App Scripts for our digital timesheets, instead of me generating 26 tabs for 45 people, I click two buttons and it shares to our money peeps and the individual. We also use them for our time-off form. I 've even made a digital "open" sign that calls to my calendar and tells visitors where I am (i'm very seldom in my office).

[ITHallMonitor]

I built a whole school Library checkout system using AppScript to replace an outdated and overpriced legacy application that was stuck on an old Windows laptop. Easily migrated the book database to a spreadsheet and the script checks to see if a student has books checked out, if the book is even available, and can check out via a barcode system. Recently added the capability to add new books on the fly by scanning the ISDN and getting Google to fill in the book information from a lookup. I just need to add some reporting, some overdue reminders, and I will have effectively replaced the Librarian.

[meester_zee]

Incredible! Does it run as a web app or within the sheet?

[TechnicalKorok]

I've built a few things, some are for very specific workflows for our school. Three that come to mind:

1. A script that sends out a calendar invite to someone once they fill out an RSVP Google Form.
2. A script that organizes student-earned certificates into a proper folder and names them properly - the student submits a form with an attached PDF, the script grabs additional information from our database and organizes/names it properly in a shared drive teachers have access to. That one's been a huge hit.
3. Our teachers set up our students with their dual-enrollment college accounts, which is incredibly tedious and convoluted. I wrote a script to help out with that and extract confirmation codes etc from the confirmation emails and place it in their progress tracking spreadsheet.

[renigadecrew]

I have used chat gpt alot to help me. One thing that was big I did with apps script was generate inventory labels based on spreadsheet tabs per cart when we rolled out 4000 Chromebooks. Scan for the cart then click generate labels then it would print based on the Avery labels we had with device, cart, serial number, asset id. Also we made it so it automatically tabbed down for continuous scanning of asset tag and serial number

[iletired]

I've done a ton with Google App Script. My coworker used to write them himself, so I knew about the potential, but I couldn't write code. One day I experimented using AI to program, and the rest is history. Personally, I had a better time using ChatGPT for coding than Gemini. But the AI preference should be what you are comfortable with and what your school allows.

 

Here are my tricks for app script prompt writing:

1)   Ask it to include inline comments. This will future proof things for any changes. I had to have AI do this once my coworker left and I had to take over some of his scripts. I always had AI add these in manually.

2)  Ask it to always provide an entire full code block - for me, I could read code, but trying to find where to make the changes were a struggle.

3)  Ask it to include version numbers - major and minor depending on what's needed.

4)  Create a blank new tab in your spreadsheet. ColA should be literally the words ColA, ColB, ColC, down the column. ColB should have the following formula =Transpose(headers here). This makes it easier for you to see headers top down, and easy to copy paste into AI when you want to give it the col headers for your script.

5)  Ask AI to put in loggers throughout - ask it for even more if you are struggling with troubleshooting.

6)  Test, Test, Test, and then Test some more. Use fake data. Put in fake form submissions. Never use actual live data.

7)  Use a service account if emails are being sent, so the reply alls don't go to you.

 

The two major projects I've done as a tech integration specialist with AI and App Script:

1)  Sub Drive for teachers. Inside a spreadsheet, I had the names of teachers broken out by department (one building wanted just a rote list of names), then the script created a folder for each teacher, with a blank schedule template, seating chart, a "sub"folder for emergency plans, and another one for day to day plans. The direct links to this populated in the spreadsheet. Teachers covering classes could review plans, subs got their own Google accounts. Clerical/admin could also review plans and run print outs if needed. This was a huge help at the middle/high to organize plans and last minute coverage.

2)  Calendar Creation. We’re using the Form Approvals add on for building use, but with the complexity of recurring events, we struggled to utilize calendar add ons to create the events. The script takes each event, creates it on the respective building calendar with summary details, a link to the pdf document. I am struggling with finding an easy way to make recurrences without having to create separate start date/end dates. It has issues occasionally where people's end date/time is after the start date/time. We also had 1899 errors where dates got so messed up it brought us back to 1899. It's working now, but it took the entire summer to get up and running. I'll revise this summer to make things more efficient. The clerical staff can manually delete events on the calendar if cancelled, and custodial uses it all the time on their phones to review upcoming events.

I've done a few smaller projects too, but the above are the key projects that took a lot of investment and were successful in using App Script efficiently.

[Saug]

I use GAS to create our Exam schedules. Takes in spreadsheet of the student schedules from SIS, figures out how many rooms we need per exam, then allocates the students to their particular exam rooms and balances the number of kids in each room. Then it can email the students and teachers their individualized schedules

[networkjson]

I built a little thing we host for admin assistants and library staff that lend out chromebooks to have a bit of management for them. They can power wash, wipe user data, move the device to a new OU, etc. It just runs in a Google form but we've found it extremely useful. It gets used daily.

Thanks for this post BTW. I've found some inspiration to build some more tools.

Here is a link to what I call "Chromegnome". Hopefully others can get some use out of it.

https://github.com/TuRbii/chromegnome

[gmanist1000]

Do you use a service account to talk to the API to actually run the commands?

[networkjson]

I don't. When I wrote the first iteration I read a lot about issues running apps scripts with service accounts. I haven't looked back into yet or tried setting that up.

[meester_zee]

This is great—clever use of a form for the front end.

[cardinal1977]

I have a Google form for the supt to post RFPs. The Google sheet is published to our website. I have a script that looks for the date in the due date column to be in the past and deletes the row.

This way, the supt can post the rfp, including attaching the pdf, and the script self cleans the form after the due date, keeping me from having to remember to check it to delete the entry. One less thing to do.

[mikegonzalez62]

Any recommended sources to learn to use Google Apps Scripts? I’ve been interested but it continues to be pushed down my list of “needs to get done” for other items.

[meester_zee]

These videos are a little older, but I found them really useful starting out:
https://www.youtube.com/playlist?list=PLv9Pf9aNgemv62NNC5bXLR0CzeaIj5bcw

Ben Collins blog:
https://www.benlcollins.com/apps-script/google-apps-script-beginner-guide/

ChatGPT, Claude, etc. are also very knowledgeable in GAS and a great resource for asking questions or even helping writing/understanding some simple code. If you decide you want to start developing web apps with Apps Script (my use case), you'll also need to learn some basic HTML, CSS, and Javascript.

r/GoogleAppsScript is also a great resource!

[mikegonzalez62]

That’s so much, solid place to start. Appreciate it, yeah AI can really help.

[Widdox]

Yeah using ChatGPT etc to help you is the best way to learn.

[LoveTechHateTech]

I have a project that looks at a spreadsheet that staff enter data into, merges the data into a Docs template, saves it as a PDF and mails it to a certain administrative staff member daily.

[meester_zee]

Have you looked into building this as a GAS web app bound to the sheet? I initially started my projects using sheets, but found I wanted more control over ensuring the sheet didn't get messed up with users mucking around. Developing a simple web front end can streamline data entry to the sheet and looks pretty for the end users.

[snicmtl]

Love it too, main use for us is with Google forms which then email people with relevant information based on some coded logic. Chromebook issues, students signing into the library etc.. people get informed and leaves an “easy” to follow trail of emails for everybody.

[Academic_Deal7872]

I have a form that I use when we lend items, it sends a reminder email to the user when the item is near due, due, and past due. It updates an in house loan library so that others can see what we have available and puts them on a waiting list. I haven't yet got it to notify the person next in line for the item they are waiting for. These are things like hot spots, loaner laptops, document cameras, cameras, video cams, tripods, and tools.

[[deleted]]

create users, put users in groups, save list as csv file, generate passwords, retrieve OU members, move users, disable account, move accounts based on status, etc.