Automation with Google Apps Script

I want to share with you how Google Apps Script was incredibly useful in creating an automation to help my friend manage his service calls for his new startup.

He is a good dude who started a tire replacement business as a service at customers’ doorsteps. It was pretty straightforward: you call him, tell him where you live or where you are, what car you have (year and model), and what tires you want (brand and sizes). He’d come in his pickup truck with the tires and the portable device that detaches and attaches the tire to the rim and do the job.

He was running well from the start. A hardworking, enthusiastic, and responsible guy, but when the number of calls started to significantly increase, he could no longer stay on top of things. The paperwork began to become overwhelming. He was getting calls and then manually filing them in Google Sheets, which is a tedious process on its own. You can imagine how fun it is doing it from his phone. Working on Google Sheets from your phone can be a challenge; at least, I experience it that way, and so does he.

One night, the dude dropped by. We were chilling in the garage and having a cold one, trying to figure out how the process of getting the call, filing it, and then analyzing it could be more automatic and modern.

There were a bunch of ideas, ranging from an open-source ticketing system to subscribing to a secretary service. None of those felt like the right solution.

After two more cold ones, when the freestyling got to another level, I came up with the following idea: we build a web form that will be run by a small web server. When the web form is filled out, an email will be sent to his mailbox with the completed form. In addition, the data in the email will be inserted elegantly into Google Sheets, where a row represents a call, and the columns contain the values of the form fields.

The dude thought about it for a minute or two silently and then replied that he liked the idea for two reasons:

First, it’s practically free—I would, of course, charge him a symbolic six-pack for my effort.

Second, it’s very minimalistic. He called it “surgical,” meaning it meets the bare minimum needed.

I started working on it the following weekend.

I used WordPress as a platform for the form—it’s an AWS Lightsail WordPress instance. I love those!

After all the relevant configuration was prepared on the WordPress side (including SMTP configuration and form building), we started to receive the forms as emails. So far, so good. But now, we needed to insert the relevant data from the emails into Google Sheets. This was done with ‘Apps Script,’ where a JavaScript would be executed to do the magic.

The JavaScript I got from GPT; it didn’t take long to get the desired functionality—two or three prompts, and I had a good base to work with.

To the JavaScript I was using, I added a function that prevents duplicate entries and adds a timestamp. Then, on ‘Apps Script,’ I set an interval for execution, and voila! We got ourselves a modest ticket system.

The setup was working well for some time. In fact, my friend told me that he really likes it. Then, while I was at work, I got a phone call. It was the dude: “Hello, Mr. Robot, your damn automatico stopped working!” he said anxiously. He sent me a screenshot from the Apps Script dashboard showing that something went wrong on the 25th of May.

I told him that I had a meeting to attend but would take a look at “my damn automatico” once I finished. When I started debugging, I noticed that the Apps Script couldn’t find the range defined by the validation function. Then I realized that, despite my instructions not to, the dude had changed the name of a Google Sheets tab that the Apps Script was configured to write to but didn’t update the script.

I updated the script with the new tab name and ran a test. Everything was back in place. The dude was satisfied again and now owes me another six-pack.