What is Google Apps Script? What is it used for?

Excel and Google Sheets have already been the subject of some basic functions such as index, match, vlookup, and conditional formatting.

You can follow the spreadsheet category for further articles on these topics and upcoming related posts.

In this article, I’d like to discuss Google Apps Script, a platform that allows us to perform operations seamlessly across many Google services, including spreadsheets, beyond the basic functions.

Google Apps Script

Google Apps Script (GAS) is a command-line development platform created by Google for lightweight application development within the Google Workspace^{1 2} environment³. Initially developed as a side project by Mike Harm while working as a developer at Google Sheets^{4 5}, Google Apps Script has since evolved into a fully integrated solution capable of working with numerous Google products. Google Apps Script enables you to build applications using HTML, CSS, and JavaScript⁶. With this, you can create add-ons for Google Sheets, Documents, Slides, or Forms, automate workflows, and perform integrated API operations with Google products.

However, there is one issue worth noting on the JavaScript side. JavaScript used within Apps Script does not have a full version match. It primarily includes features from JavaScript versions 1.6 and 1.7, as well as version 1.8.5, such as Object.keys, Object.isExtensible, String.trim, and so on. In addition, support for JSON and E4X⁷ is available⁶. On the other hand, it can also be used with the V8 Runtime enabled^{8 9}.

Developed code is saved with the *.gs extension. This code can be distributed as a web application, as an executable API file, as an add-on, or as a manifest.

Advantages

• Developed on top of JavaScript 1.6, but also includes some features from JavaScript 1.7 and 1.8 versions⁶. The V8 Runtime option is also available¹⁰.
• Uses a cloud-based (cloud) debugger to debug application errors in web browsers.
• It is possible to create tools that can be easily integrated with many Google services and APIs.
• Can be used to perform simple system administration tasks.
• It has a community-based support model.

Things to Keep in Mind

• There are operation limits. As a cloud-based service, Apps Script can limit both the duration during which a user’s script can run and access to Google services.
• New features are continuously being added to Apps Script over time. However, currently, internal (behind-the-firewall) database access, LDAP access, and similar functionalities are not possible. On the other hand, if access to Google servers is granted, such operations can be performed using a JDBC service.
• Because Apps Script is cloud-based, date and time-related functions may produce different results depending on time zones. Therefore, it is necessary to carefully manage and test Date/Time objects and their functions.

Play

References

You can perform a sample operation using Fundamentals of Apps Script with Google Sheets¹¹, and access different examples through Tutorials¹². Additionally, you can progress problem-focused examples within the various scenarios provided under Codelabs¹³. YouTube’s content is a bit disorganized. Since videos are distributed across different channels, it can be difficult to find them all in a single flow. However, you can access beginner-level video tutorials on specific topics via the Google Apps Script Video Library page¹⁴.

Finally, you can explore sample applications related to various Google services through the Apps Script samples (apps-script-samples) shared on GitHub¹⁵, and also access numerous discussions related to the topic on StockOverflow¹⁶.

Let’s clarify the topic with an example.

function formFunction(){
  let formCreate = FormApp.create('FormSample');
  formCreate.setIsQuiz(true);
  
  let forCorrect = FormApp.createFeedback()
  .setText("Correct")
  .build();

  let forIncorrect = FormApp.createFeedback()
  .setText("Incorrect")
  .build();

  let item = formCreate.addMultipleChoiceItem();
  
  item.setTitle("Greenwich time: June 12, 2006, Tuesday at 23:30. What is the date and time at a location on 25 degrees east longitude?")
  .setChoices([
    item.createChoice("June 12, 2006, Tuesday at 21:30", false),
    item.createChoice("June 11, 2006, Tuesday at 20:30", false),
    item.createChoice("June 13, 2006, Wednesday at 01:10", true), // correct answer
    item.createChoice("June 12, 2006, Tuesday at 01:20", false)
  ])
  .showOtherOption(false);

item.setRequired(true);
item.setFeedbackForCorrect(forCorrect);
item.setFeedbackForIncorrect(forIncorrect);
}

You can test the above code snippet by pasting it into your browser’s address bar: script.google.com¹⁷.

formFunction is the name of the function that will be executed. Inside this function, we first establish access to a Google Forms using FormApp.create() and create a form¹⁸.

If we wanted to use an existing form, we could proceed using FormApp.openById('<form-id>'). In summary, every time FormApp.create() is called, a new form will be created. We specify the form content using setIsQuiz().

The forCorrect and forIncorrect parameters provide data reports based on the given answers, enabling us to retrieve data. We specify the question type using addMultipleChoiceItem. After the question specified as item is created, its title is set using setTitle, and its choice options are created using createChoice (the correct answer receives a true value). Once these operations are completed, our process is finalized.

setRequired ensures that the answer is mandatory. setFeedbackForCorrect and setFeedbackForIncorrect then call the previously created forCorrect and forIncorrect content, respectively.

When the function is called, a form named FormSample will be created within Google Forms¹⁹.

Add-ons

In March 2014, Google made its add-ons (extensions) available for Docs and Sheets. Shortly thereafter, Forms were added to the list of supported services. For add-ons, we can define customizable applications built on Apps Script²⁰.

With the addition of an add-on feature, users can integrate extra functionalities such as email merging, workflows, and diagram builders into Google editors. This enables automation of frequently recurring tasks and/or makes third-party services or data available within Google Workspace. For example, a Google Docs add-on connects to the MailChimp platform to send emails⁴.

We can summarize the operations that can be performed through add-ons as follows:

• We can create customized user interfaces (UI) directly integrated with Google Workspace applications. These interfaces can present users with various pieces of information and/or allow users to control these pieces of information.
• We can enhance workflow efficiency by automating tasks or structuring them into defined processes.
• Using Apps Script, we can easily transfer and manage data between Google services.
• We can provide users with the ability to perform any required actions directly within Google Workspace.
• Google Workspace applications can connect to external services and transfer data to or from Google Workspace, either directly or via the REST API, thereby including external applications in the add-on process²¹.

Google Apps Script provides us with two types of add-on development options: Google Workspace Add-ons and Editor Add-ons. The fundamental difference between these two types can be summarized as follows: while Google Workspace Add-ons allow for development of features integrated with many Google services, Editor Add-ons enable operations only based on the associated Google service²².

You can follow new articles about Google Apps Script in the apps-script category. In the coming period, I will try to share examples based on integration operations, particularly with Google Sheets and external Google applications.

*[HTML]: Hypertext Markup Language
*[CSS]: Cascading Style Sheets
*[E4X]: ECMAScript for XML

Footnotes

1. Formerly known as G-Suite ↩
2. Workspace. Google ↩
3. Google Apps Script ↩
4. Google Apps Script. Wikipedia ↩ ↩²
5. Eric Koleda. (2019). Celebrating 10 years of Apps Script: looking back on how it started. ↩
6. Built-in Google Services. Google Developers ↩ ↩² ↩³
7. ECMAScript for XML. Wikipedia ↩
8. Ben Collins. (2020). Apps Script V8 Runtime Is Here! What Does That Mean? ↩
9. Alexandrina Garcia-Verdin. (2020). Data processing just got easier with Apps Script’s new V8 runtime ↩
10. Stay updated via the Google Cloud Blog. ↩
11. Codelabs: Apps Script Fundamentals. Google Developers ↩
12. Tutorials. Google Developers ↩
13. Hands-on with Google Apps Script: accessing Google Sheets, Maps & Gmail in 4 lines of code! ↩
14. Google Apps Script Video Library. Google Developers ↩
15. Apps Script samples for Google Workspace products. GitHub ↩
16. Questions tagged [google-apps-script]. Stack Overflow ↩
17. script.google.com - Google Script or script.new ↩
18. Forms Service. Apps Script ↩
19. docs.google.com/forms - Google Forms or forms.google.com ↩
20. Extending Google Workspace with Add-ons ↩
21. Google Apps Script API ↩
22. Add-on types. Google Workspace Developer ↩