TANAIKE
The Thinker
About Articles Topics Tags Contact Links Web Applications Donate Home

Report: How to Run Google Apps Script

report / GasTips
Google Apps Script

Gists

Report: How to Run Google Apps Script

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.

  1. Script editor
  2. Triggers
  3. Custom menu
  4. UI
  5. Custom function on Spreadsheet
  6. Button on Spreadsheet
  7. Web Apps
  8. Google Apps Script API
  9. 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

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.

Report: How to Run Google Apps Script 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.

Report: How to Run Google Apps Script 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.

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.

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

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.

Report: How to Run Google Apps Script Fig. 3. Run onEdit function by the simple trigger.

Important

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

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,

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

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

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

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

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.

  1. 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.
  2. 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.

3. Custom menu

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!");
}

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.

  1. Alert
  2. Prompt
  3. Dialog
  4. 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

4. Sidebar

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

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

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.

Report: How to Run Google Apps Script 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

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.

Report: How to Run Google Apps Script 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.

  1. On the script editor, at the top right of the script editor, please click “click Deploy” -> “New deployment”.

  2. Please click “Select type” -> “Web App”.

By this flow, you can see the following dialog.

Report: How to Run Google Apps Script Fig. 6. Dialog for setting Web Apps.

You can select the user who runs the script here.

The details of this can be seen in my report.

Important

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

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

 Share!