My Google Apps Script Breakthrough

Google Apps Script has been around since the early 2010s, so using it to automate Google Workspace tasks is not exactly new.

But for me, it might as well have been.

I vaguely remember stumbling upon it when trying to solve a problem and seeing someone on a forum suggest it as the solution. But I didn’t know how to write the actual scripts, and it felt like it was simply not built for people like me.

Until now.

I learned last week that with AI, I can describe what I want automated, and ChatGPT can build the code for me. It writes the script, explains how to implement it, and helps me troubleshoot it.

And to be clear: it did need a few iterations.

At least for me, the code was never perfect on the first try. I would run it, get an error or check the output against what I expected, and then bring the issue back to ChatGPT. It became a back-and-forth process: describe the goal, test the script, find the problem, refine the script, and test again.

I just had to know how to recognize when something wasn’t working. Being a good QA tester and being detail-oriented is key.

To hopefully inspire you, I wanted to share two Google Apps Script automations I recently built, what I learned from each one, and why I think this kind of lightweight automation is suddenly much more accessible.

First, a quick overview

The general process looks like this:

1. Go to Google Apps Script at script.google.com and create a new project.

2. Paste in the code that AI gives you. Clear out the default function myfunction() {} code first.

3. Save the script with Command + S.

4. Toggle to the main function and click “Run”. If you don’t know which one is the main one, AI can tell you. While there may be several functions in the code, you generally only need to manually run the setup or “run” function once.

5. Approve the permissions. You may have to click through some discouraging pop-ups.

6. If you also want your script to run on a schedule, you’ll also need to set up a trigger either by running an install function or clicking on the Triggers icon in the left-hand menu bar and setting it up manually.

Once you finish all these steps, you should be all set. Don’t be surprised when you see your Apps Script show up in your Google Drive.

Automation #1: Auto-deleting files from Google Drive

Sometimes I create a document, sheet, or file by accident and immediately know I do not need it.

When you are inside a Google Doc or Sheet, there is not a simple one-click “delete this file” button. So for years, my workaround has been to rename the file something like “Delete” or “Delete me” and delete it later when I see it sitting in my Drive.

I also use IMPORTRANGE in Google Sheets a lot. When you use this function, it automatically saves the raw file to your Google Drive. It’s unavoidable. So over time, my Drive accumulates a bunch of clutter.

Thus, I wanted a script that would automatically look for files with names like “Delete,” “Delete me,” or common names of files that I typically import, and move them to trash.

The first version seemed like it was working. Then, all of a sudden, it failed.

I pasted the error back into ChatGPT, and it suggested that the script might be trying to search through too many files and timing out.

That made sense. Most of the files I wanted to delete were not buried in a folder; they were sitting in the root of my Google Drive. So I told ChatGPT to update the script to only look there. That solved the problem, and I’m happy to retire my periodic Google Drive “spring cleaning” routine.

Automation #2: Scheduling a time to walk my dog each day

The second automation had to do with my calendar.

I wanted a script that would schedule time to walk my dog, Fred, on days when I would otherwise forget to protect the time.

Here were my initial requirements:

• Run automatically every Thursday around 9 a.m. using a time-based trigger.

• Schedule a 30-minute “🐾 Walk Fred” event on my Temp calendar for the upcoming Tuesday, Wednesday, and Friday.

• Randomly pick event times between 10 a.m. and 5 p.m. ET.

• Avoid conflicts with events on both my work calendar and personal calendar.

• Skip the day entirely if I appear to be out of office or on a company holiday, based on keywords like “OOO,” “vacation,” “PTO,” or “holiday” in event titles or descriptions.

ChatGPT also suggested one additional requirement:

• If no free slot exists between 10 a.m. and 5 p.m., fall back to 8 a.m. ET.

That was a good suggestion, so I added it.

Then, after testing the script a few times, I’d find another preference I had not thought to mention.

For example, I did not want the event to start at some random time like 10:07. I also did not really want the walk dropped randomly into the middle of an open slot. I wanted it to abut existing meetings, so it would create a cleaner block on my calendar instead of fragmenting my day. And I realized my calendar had declined events that were still showing up and blocking the script from scheduling time.

So I added:

• Start times should always snap to :00, :15, :30, or :45.

• Prefer slots adjacent to existing events, with no gap between meetings.

• If no adjacent slot is available, fall back to a random available slot.

• Declined events should be ignored.

• Tentative or unresponded events should still be treated as conflicts.

Without all the extra logic, I’m not sure this automation would have been any better than what I had already been doing manually each week. But now I feel confident that I can take this off my plate.

Final thought

This was seriously so easy. I can’t believe I went 10+ years without using it before. At this point, the real limitation is not the tooling. It’s my ability to think of more good use cases.