12 Jan 2024, 11:15

report / GasTips

Abstract

Google Apps Script is one of the strong tools with cloud computing, and it is very useful for various situations. Google Apps Script can be run by various methods. This report introduces how to execute Google Apps Script.

Recently, generative AI has given a lot of new users a chance to use Google Apps Script. If this report helps the users develop applications using Google Apps Script, I’m glad.

Introduction

The recent evolution of AI technology brings people many applications and useful situations. Also, it is useful for developing Google Apps Script. The official document says about Google Apps Script as follows. Google Apps Script is a cloud-based JavaScript platform that lets you integrate with and automate tasks across Google products. Ref Recently, I felt that when I saw Stackoverflow, many new users joined as users of Google Apps Script. This is a welcome. When I saw the questions of the new users, I thought that there were relatively many questions related to the method for running Google Apps Script. In fact, there are various methods for running Google Apps Script. So, it is considered that when such various methods for running Google Apps Script are explained in detail, it will be useful for not only new users but also the users who have already used Google Apps Script. Here, I would like to details explain how to run Google Apps Script.

Here, the following 8 methods are introduced.

Script editor
Triggers
Custom menu
UI
Custom function on Spreadsheet
Button on Spreadsheet
Web Apps
Google Apps Script API
Add-on

1. Script editor

Who does the script run?

When the script is run with the script editor, the script is run by the user who clicks the run button.

Important

At a Google Apps Script project, several script files and HTML files are included. But, for example, when you use 2 script files like Code1.gs and Code2.gs in a Google Apps Script project, all functions are managed as a single project. So, when a function myFunction is created in both Code1.gs and Code2.gs and you try to run myFunction, the script might not be able to be correctly run, because the same function name exists. Please be careful about this.
About the global variables, when a function sample is run, the function sample is run after all global variables are run. So, for example, when a function is run as a custom function, if the methods cannot be run by the custom function are included in the global variables, even when the script of the custom function doesn’t include the methods cannot be run by the custom function, an error occurs. Because the global variables are run before the custom function is run. Please be careful about this.

How to use

When you want to simply run Google Apps Script, you can do it using the script editor of Google Apps Script with your browser.

When you access https://script.new, a new Google Apps Script project is created as a standalone type on your Google Drive.

As a simple script, please copy and paste the following script to the script editor.

function myFunction1() {
  console.log("ok1");
}

function myFunction2() {
  console.log("ok2");
}

When you run myFunction1 with the script editor, you can see the value of ok1 in the log. You can see the demonstration of this in Fig. 1.

Fig. 1. Run a script with the script editor.

In the case of the above functions, no scopes are used. So, you can simply run the script. But, when the methods for using the scopes are included in the script, it is required to authorize the scopes. The sample demonstration is shown in Fig. 2.

Fig. 2. Run a script including a scope with the script editor.

Figure 2 shows the process for authorizing the scopes. In this script, in order to run the script SpreadsheetApp.getActiveSpreadsheet(), it is required to use a scope of https://www.googleapis.com/auth/spreadsheets. By this, when you run the script for the first time, it is required to authorize the scope. In this case, when you run the script, when the scopes for requiring authorization are included in the script, a dialog is opened. When you authorize the scopes as shown in Fig. 2, the script is run. This process is required to run only one time. But, when you add more methods for using other scopes, it is required to authorize them when the script is run. Please be careful about this.

When you want to use the methods of getActiveSpreadsheet of Class SpreadsheetApp, getActiveDocument of Class DocumentApp, getActivePresentation of Clas SlidesApp, and getActiveForm of Class FormApp, it is required to use the methods in the container-bound script of Spreadsheet, Document, Slide, and Form, respectively. When those methods are used in the standalone script, null is returned instead of an error. Please be careful about this.

2. Triggers

The triggers of Google Apps Script can be used for automatically running Google Apps Script by an event. Ref In the current stage, there are 2 kinds of triggers as follows.

Simple triggers
Installable triggers

A. Simple triggers

Who does the script run?

In the case of the simple trigger, when the owner of Google Docs file fires the simple trigger, e.user.email where e is the event object, Session.getActiveUser().getEmail(), Session.getEffectiveUser().getEmail() returns the email of the owner of Google Docs file. On the other hand, when a shared user fires the simple trigger, e.user.email where e is the event object, Session.getActiveUser().getEmail(), Session.getEffectiveUser().getEmail() return no value.

Important

When you use the simple trigger, please confirm the restrictions. Ref For example, in the case of onEdit, GmailApp, MailApp, and UrlFetchApp cannot be used.
If you want to use the installable trigger, please don’t use the reserved function names like onOpen, onEdit, and so on. Because, for example, when you install the OnEdit trigger to the function of onEdit and a cell is edited, the function onEdit is run 2 times by the simple trigger and the installable trigger with the asynchronous process. Ref Please be careful about this.

At the simple triggers, the reserved function names are used as follows.

`onOpen`

When Google Docs files (Spreadsheet, Document, Slide, and Form) are opened, this function is automatically run.

For example, when you open Google Spreadsheet and want to create a custom menu, you can achieve this using this trigger. Ref

`onInstall`

When the Add-on is installed on Google Docs files, this function is automatically run.

About the add-on, you can see the official document. Ref

`onEdit`

When the Spreadsheet is manually edited, this function is automatically run.

For example, when you edit a cell on Google Spreadsheet and want to run a script, you can achieve this using this trigger. The sample script is as follows.

function onEdit(e) {
  const { range, source } = e;
  const sheet = source.getActiveSheet();
  if (
    sheet.getSheetName() != "Sheet1" ||
    range.columnStart != 1 ||
    range.columnEnd - range.columnStart != 0
  )
    return;
  range.offset(0, 1).setValue(new Date());
}

In this case, you copy and paste the above script to the script editor of Google Spreadsheet and save the script. And, when you edit a cell of column “A” of “Sheet1”, a date object is put into column “B” of the edited row as follows.

Fig. 3. Run onEdit function by the simple trigger.

Important

As additional information, when the checkbox and the OnEdit trigger are used, a script can be run by checking the checkbox like a button.

`onSelectionChange`

When the selection on the Spreadsheet is changed, this function is automatically run.

For example, when you want to detect the change of a tab on Google Spreadsheet, you can achieve this using this trigger. Ref

And, when want to highlight row and column at the selected cell, you can achieve this using this trigger. Ref

And, when this trigger is used, a cell can be used as a button. Ref

Important

In the current stage, onSelectionChange trigger cannot be used as the installable trigger. So, for example, when you want to open a sidebar and a dialog when a cell is selected, unfortunately, in the current stage, this cannot be achieved by the current specification of onSelectionChange.

As another important point, when onSelectionChange is not run even when you create a function onSelectionChange and save the script, please try to reopen the Spreadsheet and test it again.

`doGet`

When an HTTP request with the GET method requests Web Apps, this function is automatically run.

The detail of this is introduced in the section Web Apps.

`doPost`

When an HTTP request with the POST method requests Web Apps, this function is automatically run.

The detail of this is introduced in the section Web Apps.

When the event object is e,

TypeError: Cannot destructure property '###' of 'e' as it is undefined.: It is considered that you might directly run the function without giving the event object to the script editor. In the case of triggers, when the event object e is used in the script, please run the function by firing the trigger. By this, the function is automatically run with the event object e.
Exception: The script does not have permission to perform that action. Required permissions: (### || ###,,,): It is considered that the methods that cannot run with the trigger are included in the script. Please confirm your script and confirm the restrictions, again. [Ref](https://developers.google.com/apps-script/guides/triggers#restrictionsagain.
In the case of triggers, most cases run the script using the event object. So, for example, when the following sample script is created,

function onEdit(e) {
  const { range, source } = e;
  console.log(range.getA1Notation());
  console.log(source.getActiveSheet().getSheetName());
}

the event object is e. When this function is run with the simple trigger, the function onEdit is run by giving the event object e. So, when you directly run the function onEdit, an error like TypeError: Cannot destructure property 'range' of 'e' as it is undefined. occurs. Please be careful about this. The questions related to this issue are often seen on Stackoverflow. If you want to run the function executing with the trigger with both the action for firing the trigger and the script editor, please create the script without using the event object.

B. Installable triggers

Who does the script run?

In the case of the installable trigger, when the owner of Google Docs file fires the simple trigger, e.user.email where e is the event object, Session.getActiveUser().getEmail(), Session.getEffectiveUser().getEmail() returns the email of the owner of Google Docs file. On the other hand, when a shared user fires the installable trigger installed by the owner, e.user.email where e is the event object and Session.getActiveUser().getEmail() returns no value. And, Session.getEffectiveUser().getEmail() returns the email of the owner of the Google Docs file.

Important

The basic important points are the same as the simple trigger.
When you use the installable trigger, please confirm the restrictions. Ref
As another piece of information, the installable trigger can be also installed from outside of the script. Ref

At the installable triggers, there is no reserved function name.

Time-driven trigger

A time-driven trigger (also called a clock trigger) is similar to a cron job in Unix. Time-driven triggers let scripts execute at a particular time or on a recurring interval, as frequently as every minute or as infrequently as once per month. (Note that an add-on can use a time-driven trigger once per hour at most.) The time might be slightly randomized—for example, if you create a recurring 9 AM trigger, Apps Script chooses a time between 9 AM and 10 AM, then keeps that timing consistent from day to day so that 24 hours elapse before the trigger fires again. Ref

When you use the time-driven trigger, please confirm the current limitations. Ref

In the case of the time-driven trigger, when the function is run, the event object is given.

I have published a Google Apps Script library for simply using the time-driven trigger. Ref

OnOpen trigger

An installable open trigger runs when a user opens a spreadsheet, document, or form that they have permission to edit.

The basic process of the installable OnOpen trigger is the same as the OnOpen simple trigger while the restrictions are different.

Important

In the current stage, the installable OnOpen trigger cannot be used to Google Slides.

OnEdit trigger

An installable edit trigger runs when a user modifies a value in a spreadsheet.

The basic process of the installable OnEdit trigger is the same as the OnEdit simple trigger while the restrictions are different.

Important

The installable trigger can be installed from outside of the script. So, for example, when the installable OnEdit trigger is used, detecting the change of IMPORTRANGE can be achieved. Ref
Also, automatically installing the OnEdit trigger to the copied Google Spreadsheet using Google Apps Script can be achieved. Ref
In the current stage, unfortunately, the OnEdit trigger can be used to only Google Spreadsheet. If you want to use this with Google Document, this report might be useful. Ref

OnChange trigger

An installable change trigger runs when a user modifies the structure of a spreadsheet itself—for example, by adding a new sheet or removing a column.

For example, when you want to run a script by changing the background of the cell, this trigger can be used.

And, when the cell value is updated by Sheets API, this trigger is fired. So, when you want to run Google Apps Script by updating the cell values by Sheets API with another language (node.js, python, and so on), this trigger can be also used.

OnSubmit trigger

An installable form submit trigger runs when a user responds to a form. There are two versions of the form-submit trigger, one for Google Forms itself and one for Sheets if the form submits to a spreadsheet.

2 event sources can be selected for the OnSubmit trigger.

Google Form: When the OnSubmit trigger is installed as the event source of Google Form, the event object can be seen at this official document. When you use the container-bound script of Google Form and open the installable trigger dialog, this is selected.
Google Spreadsheet: When the OnSubmit trigger is installed as the event source of Google Spreadsheet, the event object can be seen at this official document. When you use the container-bound script of Google Spreadsheet and open the installable trigger dialog, this is selected.

CalendarEvent trigger

An installable calendar event trigger runs when a user’s calendar events are updated—created, edited, or deleted.

For example, when you want to run a script by creating and updating an event on Google Calendar, you can use this trigger. When you install this trigger, it is required to set the calendar ID.

Who does the script run?

When the script is run with the custom menu, the script is run by the user who clicks the menu.

How to use

The details of the custom menu can be seen in the official document.

In the case of Google Documents, Google Spreadsheets, Google Slides, and Google Forms, the users can create a custom menu and can run Google Apps Script from the custom menu. The sample script is as follows.

In this case, when Google Spreadsheet is opened, the custom menu is automatically created by the simple trigger of OnOpen.

// ref: https://developers.google.com/apps-script/guides/menus#custom_menus_inor
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp, SlidesApp or FormApp.
  ui.createMenu("Custom Menu")
    .addItem("First item", "menuItem1")
    .addSeparator()
    .addSubMenu(ui.createMenu("Sub-menu").addItem("Second item", "menuItem2"))
    .addToUi();
}

function menuItem1() {
  SpreadsheetApp.getUi() // Or DocumentApp, SlidesApp or FormApp.
    .alert("You clicked the first menu item!");
}

function menuItem2() {
  SpreadsheetApp.getUi() // Or DocumentApp, SlidesApp or FormApp.
    .alert("You clicked the second menu item!");
}

If you want to dynamically create the custom menu, you can see the sample script at here.

4. UI

Who does the script run?

In this case, the user for executing the script depends on the method for executing the script.

Important

About UI of dialog, sidebar, and so on, the official document says as follows. Ref

Scripts that are bound to Google Docs, Sheets, or Forms can display several types of user-interface elements — pre-built alerts and prompts, plus dialogs and sidebars that contain custom HTML service pages. Typically, these elements are opened from menu items. (Note that in Google Forms, user-interface elements are visible only to an editor who opens the form to modify it, not to a user who opens the form to respond.)

The users can use the following 4 patterns.

Alert
Prompt
Dialog
Sidebar

The official document of Class UI is here. In the following sample scripts, a Google Spreadsheet is used.

In the case of custom dialog and sidebar using HTML, the Google Apps Script is run with the server side of Google. But, HTML and Javascript are run with your browser.

As additional information, when you open the dialog on the user’s browser by a trigger, please use the installable triggers instead of the simple trigger.

1. Alert

function sample() {
  const ui = SpreadsheetApp.getUi();
  const res = ui.alert("sample", "Yes or No", ui.ButtonSet.YES_NO);
  ui.alert((res == ui.Button.YES ? "Yes" : "No") + " was clicked.");
}

When this function is run, a dialog is opened. When you click “Yes”, “Yes was clicked” is shown in a new dialog.

2. Prompt

function sample() {
  const ui = SpreadsheetApp.getUi();
  const res = ui.prompt("sample", "Yes or No", ui.ButtonSet.YES_NO);
  ui.alert(
    `Inputted text is ${res.getResponseText()}. ${
      res.getSelectedButton == ui.Button.YES ? "Yes" : "No"
    } was clicked.`
  );
}

When this function is run, a dialog including a text input is opened. When you input “sample” in the text input and click “Yes”, “Inputted text is a sample. No was clicked.” is shown in a new dialog.

3. Dialog

Please copy and paste the following HTML to the HTML file of index.html in the container-bound script of the Spreadsheet.

<!DOCTYPE html>
<html>
  <head>
    <base target="_top" />
  </head>
  <body>
    <form>
      <input type="text" name="text" />
      <input type="submit" value="submit" onclick="main(this); return false;" />
    </form>
    <script>
      function main(e) {
        google.script.run
          .withSuccessHandler(google.script.host.close)
          .sample(e.parentNode);
      }
    </script>
  </body>
</html>

Please copy and paste the following script to the script file of Code.gs in the container-bound script of Spreadsheet.

function sample(e) {
  Browser.msgBox(e.text);
}

function openDialog() {
  SpreadsheetApp.getUi().showModalDialog(
    HtmlService.createHtmlOutputFromFile("index"),
    "sample"
  );
}

When the function openDialog is run, a dialog including a text input is opened. When you input “sample” in the text input and click “submit” button, “sample” is shown in a new dialog.

Important

The values on the HTML side can be sent to the Google Apps Script side with google.script.run. This is run with the asynchronous process. Please be careful about this.
When you want to run google.script.run with the synchronous process, this repository might be useful. Ref
When you want to put the values from Google Apps Script to HTML when the HTML is opened, you can also use the HTML template. Ref

Please copy and paste the following HTML to the HTML file of index.html in the container-bound script of the Spreadsheet.

<!DOCTYPE html>
<html>
  <head>
    <base target="_top" />
  </head>
  <body>
    <form>
      <input type="text" name="text" />
      <input type="submit" value="submit" onclick="main(this); return false;" />
    </form>
    <script>
      function main(e) {
        google.script.run
          .withSuccessHandler(google.script.host.close)
          .sample(e.parentNode);
      }
    </script>
  </body>
</html>

Please copy and paste the following script to the script file of Code.gs in the container-bound script of Spreadsheet.

function sample(e) {
  Browser.msgBox(e.text);
}

function openSidebar() {
  SpreadsheetApp.getUi().showSidebar(
    HtmlService.createHtmlOutputFromFile("index")
  );
}

When the function openSidebar is run, a sidebar including a text input is opened. When you input “sample” in the text input and click “submit” button, “sample” is shown in a new dialog.

Important

The values on the HTML side can be sent to the Google Apps Script side with google.script.run. This is run with the asynchronous process. Please be careful about this.
When you want to run google.script.run with the synchronous process, this repository might be useful. Ref
When you want to put the values from Google Apps Script to HTML when the HTML is opened, you can also use the HTML template. Ref

5. Custom function on Spreadsheet

Who does the script run?

When the script is run with the custom function, the script is run by the owner of Spreadsheet.

Important

Before you use the custom function, I would like to recommend to read the official document. Ref
When I see questions on Stackoverflow, I think that the questions related to permission are sometimes seen. In the current stage, the custom function cannot use all methods of Google Apps Script. For example, the custom function can use UrlFetchApp. But, it cannot use DriveApp, GmailApp, MailApp, SpreadsheetApp.openById(), and so on. When you see an error like Exception: You do not have permission to call ###. Required permissions: (###), please confirm your script again.
The custom function cannot export a formula. Please be careful about this.

How to use

About the custom function, the official document says as follows. Ref

Google Sheets offers hundreds of built-in functions like AVERAGE, SUM, and VLOOKUP. When these aren’t enough for your needs, you can use Google Apps Script to write custom functions — say, to convert meters to miles or fetch live content from the Internet — then use them in Google Sheets just like a built-in function.

As a simple sample script, please copy and paste the following script to the script editor of the container-bound script of Google Spreadsheet, and save the script.

function SAMPLE() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const sheetName = sheet.getSheetName();
  return sheetName;
}

When this script is used as the custom function, the following result is obtained.

Fig. 4. Sample of the custom function.

6. Button on Spreadsheet

Who does the script run?

In this case, the script is run by the user who clicks the button. So, when you want to make the user create a file in a folder on your Google Drive, it is required to share the folder with the user. Please be careful about this.

Important

When I see questions on Stackoverflow, I think that the questions are related to the arguments of the function for executing by the button. In the current stage, the function for executing by a button cannot be executed with the arguments. But, this sample script might be useful. Ref
When you want to create a switching button, this report might be useful. Ref
When you want to disable a button, this report might be useful. Ref

How to use

The simple flow for executing a function of Google Apps Script by clicking the button is as follows.

First, please copy and paste the following script to the script editor of the container-bound script of Google Spreadsheet, and save the script.

function sample() {
  const range = SpreadsheetApp.getActiveRange();
  range.setValue("sample");
}

The demonstration for executing this function using a button is as follows.

Fig. 5. Demonstration for using a button.

7. Web Apps

Who does the script run?

In this case, you can select the user who runs Google Apps Script. In order to confirm this, please do the following flow.

On the script editor, at the top right of the script editor, please click “click Deploy” -> “New deployment”.
Please click “Select type” -> “Web App”.

By this flow, you can see the following dialog.

Fig. 6. Dialog for setting Web Apps.

You can select the user who runs the script here.

When “Execute as: Me” is selected, the script runs as the owner of Web Apps.
When “Execute as: User accessing the web app” is selected, the script runs as each user.

The details of this can be seen in my report.

Important

When you modify the Google Apps Script of Web Apps, please modify the deployment as a new version. By this, the modified script is reflected in Web Apps. You can see the details of this in my report “Redeploying Web Apps without Changing URL of Web Apps for new IDE (Author: me)”.
In the case of setting of “Execute as: Me” and “Who has access to the app: Anyone”, the Web Apps can be accessed without authorization. But, in the case of other settings, in order to access Web Apps, it is required to use the access token. Please be careful about this. About this situation, you can see the detailed information in my report.

How to use

I think that in order to take advantage of Web Apps, my report will be useful.

Also, the sample situations can be seen using Web Apps at my report.

8. Google Apps Script API

Who does the script run?

In this case, you can select the user who runs Google Apps Script by selecting “Only myself” or “Anyone with Google account”. Ref

Important

In the current stage, Google Apps Script API cannot be used for Advanced Google services. So, when you want to use this, it is required to link the Google Apps Script project with the Google Cloud Platform Project. The flow of this can be seen in my report. This report also introduces a sample script for using Google Apps Script API.
About “Method: scripts.run” of Google Apps Script API, in the current stage, this cannot be used with the service account. The official document says Warning: The Apps Script API does not work with service accounts.. Ref Please be careful about this. If you want to run Google Apps Script with the service account, there is a workaround. Ref

How to use

Google Apps Script API can be used for managing and executing Google Apps Script. Ref

In this case, this report will help in understanding the setting of Google Apps Script API and the sample script. Ref

As a sample script, when Google Apps Script API is used, the history of Google Apps Script project can be also managed. Ref

9. Add-on

About the add-on, I think that the official document will be useful for understanding it. Ref

Report: How to Run Google Apps Script

Abstract

Introduction

1. Script editor

Who does the script run?

Important

How to use

Related errors

2. Triggers

A. Simple triggers

Who does the script run?

Important

onOpen

onInstall

onEdit

Important

onSelectionChange

Important

doGet

doPost

Related errors for simple triggers

B. Installable triggers

Who does the script run?

Important

Time-driven trigger

OnOpen trigger

Important

OnEdit trigger

Important

OnChange trigger

OnSubmit trigger

CalendarEvent trigger

3. Custom menu

Who does the script run?

How to use

4. UI

Who does the script run?

Important

1. Alert

2. Prompt

3. Dialog

Important

4. Sidebar

Important

5. Custom function on Spreadsheet

Who does the script run?

Important

How to use

6. Button on Spreadsheet

Who does the script run?

Important

How to use

7. Web Apps

Who does the script run?

Important

How to use

8. Google Apps Script API

Who does the script run?

Important

How to use

9. Add-on

Share!

`onOpen`

`onInstall`

`onEdit`

`onSelectionChange`

`doGet`

`doPost`