Using Google Apps Script to automate tasks

In this blog post, I will be introducing Google Apps Script (GAS) and how it can be used to automate business tasks.

What are business applications?

Business applications are software programs that help companies perform tasks and manage their operations. They are implemented to increase efficiency and effectiveness and can be used internally, externally or integratively. Furthermore, they can vary greatly in scale and complexity.

Common examples of business applications include accounting software, human resources management systems and spreadsheet tools.

What is task automation?

Task automation is the use of programs to minimize or eliminate the need for manual human involvement in order to complete a task, from one or all of its steps. Many business applications implement task automation to increase efficiency and overall productivity.

Common everyday examples include ATMS, Checkouts in physical stores and signing up for services. Business examples include automated email responses, data scraping (automated data-fetching and organizing) and automated reception at physical offices.

What is Google Apps Script?

Google Apps Script (GAS) is a development platform provided by Google for creating business applications that integrate with Google Workspace. Google Apps Script is written in JavaScript and there are many libraries available for each google workplace application (think applications such as Gmail, Calendar, Drive and so on).

No installation or setup is required and code can be edited and run directly from the browser using Google’s servers. In addition, no initial payment is required to begin using GAS, all that is required is a Google account. This makes it very accessible as well as beginner-friendly!

What can Google Apps Script be used for?

Google Apps script can be used for the following purposes:
・Add custom menus, dialogs, and sidebars to Google Docs, Sheets, and Forms.
・Write custom functions and macros for Google Sheets.
・Publish web apps — either standalone or embedded in Google Sites.
・Interact with other Google services, including AdSense, Analytics, Calendar, Drive, Gmail, and Maps.
・Build add-ons and publish them to the Google Workspace Marketplace.

In addition, Google Apps Script can also be integrated with externally existing applications and managed, run and so on, programmatically via the Apps Script API.

Some use cases include:
・Validate Google Sheets data
・Create a mail merge using Gmail and Google Sheets
・Fetch and organize email data into a document
・Update your calendar, create google meets from Google sheets or Gmail data

Limitations of Google Apps Script

Although useful for automating tasks and building simple programs and applications, there some limitations of the platform to take into consideration:

・no frontend frameworks, limited UI
・slow loading times
・hosting and development limited to Google’s ecosystem
・low scalability

How to begin using Google Apps Script

To begin using Google Apps Script, you will need a google account. Applications can be created from the Apps Script Dashboard, by clicking on “new project”. This will open a new project and a new file in which you can begin writing code. If you are creating a smaller scale program to function with google docs, sheets etc, then you should open the document you want to use and select “Apps Script” via “Extensions” in the menu bar. This will open a new file in which you can begin writing code. In this case, the document will be referred to as the active sheet or document. The Dashboard has options for running, debugging, deploying code and linking code with other services.

Example of task automation with Google Apps Script: company end-of-year greeting emails

In Japan it is customary for companies to send end of year greetings to business partners, commonly done via email. When done manually, sending emails to hundreds if not thousands of business partners each year can be quite the time-consuming process. This process (partially or fully) can actually be automated using Google Apps Script, Google Sheets, Google Docs and Gmail.

In this example emails are sent from a Google spreadsheet that lists all of the business partners to be contacted. However you could also automate this process by having all of the emails you contacted throughout the year fetched into the spreadsheet and from there send the emails.

First we fill a google sheet with the information of our business partners- one per row. The information in this example includes (from left to right) the company name, department, role, name and email address. Elements such as the cc, attachments and so on can also be included here.

Next we will format the body of the email in Google Docs. Create a new document and add the body text. (In the case that different body text is necessary for different contacts, use different templates and specify which contacts will use which templates in the GAS javascript). Make sure to include where the values input via the spreadsheet will go in the template. In this case {会社名} etc.

Next we move to the Apps Script Dashboard (via the Apps Script link under “Extensions” in the menu bar of the spreadsheet). From here we add the code as follows. Make sure to specify the Google Doc template via its document id. On the other hand the spreadsheet will be referred to as the ActiveSheet. In the code we specify which spreadsheet columns hold which data, and where this data is to be placed in the Google Doc template which will become the body of the email. Specifications that are common to all of the emails such as the from address and subject are also included here.

Caution: Before sending emails to business partners run tests using your own email address to ensure correct functionality.

Finally, run the code via the “run” button in the menu bar and enjoy automated emails! This is just one example of how Google Apps Script can be used to automate tasks.

Thank you for reading until the end.

References
Business Applications:
https://www.ibm.com/docs/en/taddm/7.3.0?topic=using-business-applications https://online.uc.edu/blog/what-are-business-applications/
Task Automation:
https://www.smartsheet.com/task-automation
Google Apps Script:
https://developers.google.com/apps-script/overview
Official Documentation:
https://developers.google.com/apps-script
Google Apps Script Youtube:
https://www.youtube.com/channel/UCUcg6az6etU_gRtZVAhBXaw
Google Apps Script API:
https://developers.google.com/apps-script/api/concepts
Google Apps Script for Google Workspace:
https://workspace.google.com/intl/ja/products/apps-script/
Google Apps Script Limitations:
https://www.quora.com/What-are-the-limitations-of-using-Google-App-Scripts-Javascript-to-develop-a-website