An Introduction to Google Apps Script

An Introduction to Google Apps Script

07/06/2021

Article by Calvin Moser, Software Development Engineering Instructor at mthree Academy

 

There is an amazing tool hidden in Google Workspace called Apps Script. It can be used to help a developer be more productive, create and deploy internal workflow applications, and develop full-fledged customer-facing applications.

You might know Google Workspace (formerly “G Suite,” formerly-formerly “Google Apps”) by its suite of web apps: Gmail, Drive, Chat, Docs, Sheets, Slides, Calendar, Forms, Sites, Meet, etc.

Google Apps Script is a free platform for creating and deploying Javascript scripts that interact with Google’s web apps. For example, it is possible to create a script that automatically updates a slide on a Google Slides presentation as data on a Google Sheets spreadsheet changes, and direct messages a group of users a link to the updated presentation through Google Chat. Apps Script can also be used to change Menus and other parts of the UI for each of the web apps.

Further, Google Apps Script can also be used to create websites that create, read, update and delete data through JDBC database connections. The pages of the website, of course, can also interact with the other Google apps.

Google Apps Script can facilitate any developers workday through prototyping, testing, and monitoring. It is a great way to prototype because its free, functional, data-driven web pages and user interfaces can be quickly created for analysis. It can be used for many kinds of testing, such as testing APIs or applications that read from APIs. A useful monitoring tool can be created that can send daily emails, create weekly slide presentations, and even send a Google Chat as soon as a problem is detected.

The documentation for Google Apps Script is very informative and becomes very easy to navigate as a developer starts exploring. Since it is so thorough, it is easy (at first) to get lost in the pages of information. However, because the look and feel is consistent among all the services, a developer can quickly learn how to swim simply by diving in. For example, using the documentation to help create a script that populates a spreadsheet with NFL data read from a web site would enable a developer to more quickly (using the documentation) create a script that interfaces with any other service.

Though Google Apps Script has been around since 2009, they rolled out a new IDE at the end of last year that is much more user friendly (logs are now a panel on the first page).

One of my earliest uses of Apps Script was on a spreadsheet that I was using to track my time as a contractor. Each tab on the spreadsheet represented a week and contained the dates for that week, the daily start times and stop times, and the amount of time taken for lunch. Since I did not want to copy the tab 52 times a year, I created an app that copied the tabs for me. Likewise, I did not want to have to find the right tab every day, so I had the script automatically open the tab that corresponded to the current week whenever I opened the spreadsheet. Future work might be to add a tab for the current week if it does not already exist to prevent having 52 tabs at the beginning of the year.

 

function onOpen(e) {

// Get reference to this spreadsheet

var ss = SpreadsheetApp.getActiveSpreadsheet();

// Calculate name of this weeks tab (e.g. “Week 15”)

var name = Utilities.formatDate(new Date(), "GMT", "'Week 'w");

// Find tab with name

var sheet = ss.getSheetByName(name);

// Make it active if it exists

// TODO: Have it create it if it does not exist

if (sheet != null)

ss.setActiveSheet(sheet);

}

 

Google Apps Script is not only a great way to continue to learn, explore, and develop Javascript skills, it can also be a great tool in any developers workday. Because of its versatility and customizability, it can be used to improve the workflow of any business.