8 Surprisingly Useful Things You Can Do With Google Sheets and Google Apps Script

Google Apps Script is a surprisingly powerful scripting language often implemented using Google Sheets. It allows people to develop lightweight web applications, and those scripts run in the cloud on Google’s servers.

This is just scratching the surface of what you can do with Google Apps Script. If there’s something else you want to do, do a Google search for it — it may well be possible.

Schedule a Gmail Message

Gmail doesn’t provide a built-in way to schedule email messages. You could use a third-party application or service for this — or just use Google Apps Script.

The Gmail Sheet Scheduler allows you to create a special spreadsheet that can import draft emails from your Gmail account and assign dates and times to them. Click a menu option afterward and the script will automatically send the messages out at the time you specified. This all happens via a small script running in your Google account, so you don’t need to trust a third-party service or leave a web page open.

Get SMS Messages or Emails When a Website Goes Down

There are commercial website-monitoring services that will constantly check a website you’re responsible for, sending you an SMS message if it ever goes down. This allows you to leap into action and fix it immediately.

The Website Uptime Monitor script does this via Google Docs. Every five minutes, it checks your website. If it’s down, it creates a calendar event with a reminder in your Google Calendar, and that reminder is set to send you an SMS message immediately. It piggy-backs off Google Calendar’s SMS-reminder feature to automatically send you an SMS — the sending-SMS part is free, although it may cost you money to receive an SMS on some cellular carriers. This script can also send you an email message instead.

Receive Files in Google Drive

You may want to use your Google Drive storage as a place to receive files. For example, freelancers could receive files from clients and teachers could receive files from their students. Rather than requiring an email, they could just click an Upload button on a web page and select a file. Google Forms would work well for this, but it doesn’t offer a file-uploading feature.

Google Apps Script allows you to create a file-uploading page that will save the uploaded file directly to your Google Drive storage. Just create a new HTML file with the code, make it publicly accessible, and share the link with people who need to give you files.

Perform a Mail Merge

A “mail merge” sounds like something extremely old fashioned, but it can still be useful. Essentially, a mail merge allows you to send personalized emails to multiple people by building a template. For example, your template could be “Hi $PERSON, how are you?” If you used mail merge, you could have a list of email addresses and person names, and everyone would get a personalized email beginning with their name.

It’s possible to do this with Google Apps Script as well. See the Mail Merge with Gmail script for more information.

Snooze an Email in Gmail

“Snoozing” emails is a trendy new feature found in Gmail’s Inbox and Dropbox’s Mailbox. Essentially, it allows you to hit “Snooze” on an email message and that message will vanish from your inbox, returning after a predetermined period of time. This gets your inbox clear, but it also ensures the email comes back to remind you about something later.

The official Google Apps Developer blog used this feature as an example for what you can do with Google Apps Script, writing a Gmail Snooze script.

Track Product Prices on Amazon

The Amazon Price Tracker script takes a list of URLs (website addresses) to products on Amazon.com or another country-specific Amazon website like Amazon.ca or Amazon.co.uk. It will check these pages once per day, sending you a daily digest email with a list of price movements. If you want to keep an eye on a product and buy it when it’s cheaper, this can do the job for you.

Automatically Delete Certain Old Messages

Microsoft’s Outlook.com has a useful “auto-sweep” feature that can automatically delete emails from specific senders after a period of time. For example, you could get the latest offers from GroupOn every day and set Outlook.com to automatically delete those Groupon newsletters after a month, ensuring years of old newsletters don’t clutter your inbox and take up space forever.

The Gmail Auto-Purge script can monitor a label of your choice and automatically delete emails in it after a certain period of time. Coupled with Gmail’s filters for labelling messages automatically, this could serve a useful function.

Get Read Receipts in Gmail

Outlook has a “read receipts” feature that can inform you when your email is read by the recipient. This lets you know whether the recipient has opened your email or not, although it’s not 100 percent reliable.

Gmail doesn’t include the feature, but the Email Tracker script hooks into Google Analytics, allowing you to track email opens via Google Analytics when you use the script to send an email. It inserts a tiny 1×1 image and Google Analytics reports when this image has been accessed. Now that Gmail will is automatically loading images in emails, this should be even more accurate.


Thanks to Digital Inspiration‘s Amit Agarwal for writing so many of these useful scripts!

Chris Hoffman is a technology writer and all-around computer geek. He's as at home using the Linux terminal as he is digging into the Windows registry. Connect with him on Google+.