Alfred J. Pennyworth and Google Apps Script
I know, some of you might be thinking “What’s this ?”, “What does Alfred has to do with Google” and some are like “Who is Alfred ?”.
For folks who have the last question in mind, Alfred is a fictional character who appears in DC comic books with the superhero Batman. He’s Bruce Wayne’s loyal and tireless butler, legal guardian, best friend, aide-de-camp, and surrogate father figure. Wise, Intelligent, Sarcastic and most importantly, the ultimate savior of Batman.
Some of you might have figured it out already that Alfred is the metaphor for Google Apps Script and G-Suite is the Batman. Not known to many, but very useful, helpful and powerful. And this is on “What can be accomplished using Google Apps Script”.
Basically Google Apps Script is a scripting language based on JavaScript that can be used to interact with Google products. The scripts can be written either stand alone as web apps, add-ons or along side google products.
Currently it can be used with,
- Spreadsheets
- Docs
- Forms
- Sites
- Google Drive
- Gmail
- Calendar
- Contacts
- Groups
- Google Maps
- Google Translate
You only need a net connectivity , a browser and you’re good to go.
Like every product, there’re feature limitations in the G-Suite platform. Certain features cannot be implemented to the product, while keeping the easy go user experience. Until the user evolve with the platform, making it too complicate is going to drive the users away from it.
And if such a complex requirement occurs, google apps script is there to provide a solution. These are very easy but important features/requirements that users need, and also very easy to implement.
Some of these are add-ons developed using apps script that can be used in google products, some are features available in apps script it self and some are my personal development experiences.
Real Time Charts Dashboard
Back in the days, google provided a mechanism to create dashboards with tools that can control the data displayed in the charts interactively only using google apps script. The users can still use the old way with google app script to create an interactive dashboard.
Anyhow later, google introduced Google Charts which can be used even in your web site or web application. Since google app script can run HTML and JavaScript as a web app, Google Charts can be used with google apps script as well. And it has many chart types and interactive controls to cater the dashboard.
Move data from Google Docs to Spreadsheet or Vice Versa
This is one of my personal experiences and a very easy one. I was given a google document full of text and organized tables. Since this was kind of a survey document, there were hundreds of copies of the same doc with different data. The goal was to take all those data in the tables to a differently structured spreadsheet for analysis.
The good thing about google app script is that it has classes for each element in the G-Suite. All I had to do was reference both spreadsheet and the google doc, then loop through all the tables in the doc while filling the appropriate cells in the spreadsheet. Very simple but an effective solution, which probably saved many hours of manual copy paste.(Sample Code)
Schedule Email Messages in Gmail
One can either install Boomerang or Sidekick which are specific to browsers or there is an add-on developed using google apps script for google spreadsheet. You can install the add-on known as Email Scheduler, in your google spreadsheet which works across all desktop browsers. You can,
- Send emails once at a specified date and time
- Send repetitive emails at any interval range
- Track Email Opens
User can create a draft email and then schedule the draft to be sent using the scheduler available in the google spreadsheet. The spreadsheet includes all the schedule and sent emails using the scheduler. I hope Gmail will soon add this feature in it’s app it self, since Outlook already got this feature inbuilt.
Send Personalized Emails with Mail Merge
Here again, a feature which Outlook has implemented but not available in Gmail. Have you ever encountered the need to send the same email to multiple people addressing them specifically. Mail Merge is there for the rescue. Mail Merge is an add-on developed using google apps script and you can install it to your google spreadsheet.
In the spreadsheet, you can add columns with variable names to later add in the Email. Then add those column names inside double curly brackets ({{First Name}}) within the email, so that the program can track the variable and replace it with the specific name while executing the script. In this case the variable “{{First Name}}” will be replaced by the given names in the google spreadsheet. You can easily import all your google contacts to the spreadsheet and even specify a date and time for each contact to be sent.
Web Scraping Instagram , Reddit or any site
Not all web applications and sites allow to scrape data, even Reddit has it’s rules when it comes to scraping data. Anyhow if you require a certain set of data to be downloaded to your Google Storage, then you can use google apps script to scrape the data for you. Depending on the nature of the web site/application, you can either connect to the respective APIs (in case of Instagram) or directly scrape from the web site it self. (Reddit web scraper with google apps script)
Develop a REST API using Apps Script
This may sound a bit technical for the average reader, and in layman terms REST API is like a food court. You can request food from the stalls in the court and when paid (or sometimes free) , you can have the specific food item. Likewise google apps script can be used to expose data or other services to external parties. Every google product has it’s own API, but sometimes if any processing of data is required, instead of doing it in the client side application, you can do it using google apps script and get the final result. Mobile apps and Web applications can access RESTful services developed using apps script along with google external API .
Host a simple Angular Application
In the beginning, I’ve mentioned that google apps script can execute as a stand alone web application. Using that feature, you can develop a simple angular application and host it in google apps script. The angular library and other required services should be referenced appropriately and then host the app. It can be complex either, but google apps script doesn’t allow having folders inside it self, which is vital to reduce the complexity and maintainability of an angular app. (Not only angular, any client side web application developed based on web services can be hosted)
And there’s a tool developed using apps script, that helps you publish a website. All you have to do is compress all the files as a zip and upload using the tool and it will publish your site with an address.
See Google Contacts on a Map
Could be useful for businesses to see where the google contacts are situated at once, or one can just know of it for fun. Either way, this can be accomplished using apps script. An app has been developed using apps script called Maps Web App. It’s a script which reads all your google contacts, their addresses and map the longitudes and latitudes in a spreadsheet. Finally it will provide a Google Map embedded HTML file displaying the map location of all the contacts. Not only google contacts, other contact sources can be imported and used along the app.
Send a Self Destructing Message
I’m pretty sure that we all have came across this idea, at least once. And there are many tools available in the internet to accomplish this task. Anyhow, this is how google apps script can be used to implement such a requirement. It’s one of the easiest implementations I came across.
The following was developed to be used with google spreadsheet. You can make a copy of the given spreadsheet. Why ? because it already has an embedded script within it self. Type the message, add the recipient to the spreadsheet. Once the recipient opens the spreadsheet, it will display a message that it’s gonna destroy it self in a given time and then destroys the message. You can add the code on your own and try it.
Save email attachments to Google Drive
This one is kind of a life saver. Suppose you have multiple number of emails containing attachments, and you need all of them at once. All you have to do is create a rule which filters the email (with a specific subject, contains certain words, from specific addresses and etc), specify the folder in the drive and Save Emails and Attachments add-on will take care of the rest. It will download all the attachments in all the emails which gets filtered in to the specific folder. And also, there’s an option where user can even save the email it self as PDFs to the drive.
As per my personal experience, many users in various fields ranging from real estate, fashion, video gaming, tourism, philanthropy have been/are using Google Apps Script to make their life easier. At the end of the day, everything falls in to the category of information and technology. And the category doesn’t matter.
The purpose of this was to show the various kinds of applications possible to develop and requirements that can be accomplished using google apps script. I hope this provided that basic understanding. So next time you get in to trouble with something related to G-Suite, don’t forget to check with Alfred.
You can follow me here : Ahamed Huzaim
