Google Apps Script: 100+ Practical Coding Examples 

by

Google Apps Script: 100+ Practical Coding Examples https://www.amazon.ca/dp/B0DDZSQ19Z

is a comprehensive guide for anyone looking to harness the power of Google Apps Script to enhance productivity and simplify workflows. Built on JavaScript, Google Apps Script allows users to create custom functions, automate tasks, and develop web applications within Google Workspace. This book features over 100 carefully crafted code snippets, each designed to demonstrate key functionalities and inspire creative solutions.

Readers will explore the full spectrum of Google Apps Script capabilities, from automating data formatting in Google Sheets to creating advanced web apps and integrating external APIs. Key sections include setting up triggers, building custom user interfaces with HTML Service, and deploying scalable solutions. Each example is accompanied by detailed explanations and insights, making complex concepts approachable for beginners while providing depth for seasoned developers.

This book is more than a collection of examples—it’s a practical resource for real-world application. It encourages readers to experiment, adapt, and innovate as they learn to leverage Google Apps Script’s full potential. Perfect for developers, IT professionals, and anyone in need of efficient Google Workspace automation, this guide empowers readers to master scripting, build impactful solutions, and revolutionize their workflows.

Introduction to Google Apps Script

What is Google Apps Script?

Google Apps Script is a cloud-based scripting language for light-weight application development in the Google Workspace platform. It uses JavaScript syntax and easily integrates with Google services like Sheets, Docs, Gmail, Calendar, and more. With Apps Script, you can automate repetitive tasks, create custom functions, develop add-ons, and build web applications—all within the Google ecosystem.

Key Features:

  • JavaScript-Based: Uses a familiar JavaScript syntax.
  • Cloud-Based: No installation is required; scripts run on Google’s servers.
  • Integration: Seamlessly interacts with Google Workspace apps and external services via APIs.
  • Triggers: Automate scripts to run at specific times or in response to events.
  • Add-ons and Web Apps: Extend the functionality of Google Workspace or create standalone web applications.

Getting Started with Google Apps Script

To begin using Google Apps Script, you need a Google account. Here’s how to access the Apps Script environment:

Accessing Apps Script:

  1. Via Google Sheets, Docs, or Forms:
    • Open a Google Sheets, Docs, or Forms file.
    • Click on Extensions in the top menu.
    • Select Apps Script.
  2. Directly via the Apps Script Dashboard:

The Apps Script Editor:

Once you create a project, you’ll enter the Apps Script Editor, an integrated development environment (IDE) where you can write, edit, and manage your scripts.

Your First Script:

Let’s create a simple “Hello, World!” function.

function sayHello() {

  Logger.log(‘Hello, World!’);

}

Explanation:

  • function sayHello() defines a new function named sayHello.
  • Logger.log(‘Hello, World!’); outputs the text “Hello, World!” to the Logs, which you can view by clicking View > Logs in the editor.

To run the script, click the ▶️ Run button. The first time you run it, you must authorize the script to access your Google account.

Stand-alone vs. Bound Scripts

Google Apps Script projects can be categorized into stand-alone scripts and bound scripts. Understanding the difference is crucial for organizing your projects effectively.

Bound Scripts:

  • Definition: Scripts that are attached to a specific Google Workspace document, such as a Google Sheet, Doc, or Form.
  • Accessing: Created from within the document via Extensions > Apps Script.
  • Use Cases: Automating tasks specific to that document, such as adding custom menus, functions, or triggers.

Example: A script bound to a Google Sheet that automatically formats data or adds custom formulas.

Stand-alone Scripts:

  • Definition: Independent scripts not attached to any particular document.
  • Accessing: Created directly from the Apps Script Dashboard at script.google.com.
  • Use Cases: Projects that interact with multiple Google services or perform actions not tied to a single document, such as sending automated emails, managing calendars, or integrating with external APIs.

Example: A script that sends a daily summary email using data from multiple Google Sheets.

When to Use Which:

  • Use Bound Scripts when your automation or customization is specific to a single document.
  • Use Stand-alone Scripts for broader applications that interact with various Google services or are not tied to one document.

Basic Concepts and Syntax

Google Apps Script uses JavaScript, so if you’re familiar with JavaScript, you’ll find it easy to get started. Here are some basic concepts and syntax elements you’ll use frequently.

Variables:

Variables store data values.

var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

Functions:

Functions perform actions. They can be defined to execute specific tasks.

function myFunction() {

  // Your code here

}

Objects and Methods:

Objects represent entities (like a spreadsheet), and methods are actions you can perform on them.

var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

sheet.getRange(‘A1’).setValue(‘Hello’);

Control Structures:

Use if, for, while, etc., to control the flow of your script.

function checkValue() {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  var value = sheet.getRange(‘A1’).getValue();

  if (value > 10) {

    Logger.log(‘Value is greater than 10’);

  } else {

    Logger.log(‘Value is 10 or less’);

  }

}

Logging:

Use Logger.log() to print messages for debugging purposes.

Logger.log(‘This is a debug message’);

Triggers:

Triggers allow your scripts to run automatically based on events, such as time-based triggers or when a document is opened.

function createTimeTrigger() {

  ScriptApp.newTrigger(‘myFunction’)

    .timeBased()

    .everyDay()

    .atHour(9)

    .create();

}

Common Simple Examples

Let’s explore some common examples to illustrate how Google Apps Script can be used.

Example 1: Automating Google Sheets

Task: Automatically format the header row in a Google Sheet.

Code:

function formatHeader() {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  var headerRange = sheet.getRange(‘A1:D1’);

  headerRange.setBackground(‘#4CAF50’);

  headerRange.setFontColor(‘#FFFFFF’);

  headerRange.setFontWeight(‘bold’);

}

Explanation:

  • SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();: Accesses the currently active sheet.
  • getRange(‘A1:D1’): Selects the range A1 to D1 (the header row).
  • setBackground(‘#4CAF50’): Sets the background color to green.
  • setFontColor(‘#FFFFFF’): Sets the font color to white.
  • setFontWeight(‘bold’): Makes the text bold.

How to Use:

  • Open your Google Sheet.
  • Go to Extensions > Apps Script.
  • Paste the above code.
  • Save and run the formatHeader function.

Example 2: Sending Emails with Gmail

Task: Send a customized email to a user.

Code:

function sendWelcomeEmail() {

  var recipient = ‘user@example.com’;

  var subject = ‘Welcome to Our Service’;

  var body = ‘Hello,\n\nThank you for signing up for our service!\n\nBest regards,\nTeam’;

  GmailApp.sendEmail(recipient, subject, body);

}

Explanation:

  • GmailApp.sendEmail(recipient, subject, body);: Sends an email using Gmail.
  • recipient: The email address of the recipient.
  • subject: The subject line of the email.
  • body: The main content of the email.

How to Use:

  • Open the Apps Script Editor.
  • Create a new stand-alone script or bound script.
  • Paste the code and modify the recipient as needed.
  • Save and run the sendWelcomeEmail function.

Example 3: Creating Custom Menus in Google Sheets

Task: Add a custom menu to Google Sheets to trigger scripts easily.

Code:

function onOpen() {

  var ui = SpreadsheetApp.getUi();

  ui.createMenu(‘Custom Menu’)

    .addItem(‘Format Header’, ‘formatHeader’)

    .addSeparator()

    .addItem(‘Send Welcome Email’, ‘sendWelcomeEmail’)

    .addToUi();

}

function formatHeader() {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  var headerRange = sheet.getRange(‘A1:D1’);

  headerRange.setBackground(‘#4CAF50’);

  headerRange.setFontColor(‘#FFFFFF’);

  headerRange.setFontWeight(‘bold’);

}

function sendWelcomeEmail() {

  var recipient = ‘user@example.com’;

  var subject = ‘Welcome to Our Service’;

  var body = ‘Hello,\n\nThank you for signing up for our service!\n\nBest regards,\nTeam’;

  GmailApp.sendEmail(recipient, subject, body);

}

Explanation:

  • onOpen(): A special function that runs automatically when the Google Sheet is opened.
  • createMenu(‘Custom Menu’): Creates a new menu named “Custom Menu”.
  • addItem(‘Format Header’, ‘formatHeader’): Adds a menu item that runs the formatHeader function.
  • addItem(‘Send Welcome Email’, ‘sendWelcomeEmail’): Adds another menu item for sending emails.
  • addToUi(): Adds the custom menu to the Google Sheets UI.

How to Use:

  • Open your Google Sheet.
  • Go to Extensions > Apps Script.
  • Paste the entire code.
  • Save and refresh your Google Sheet.
  • You will see a new “Custom Menu” in the menu bar with options to format the header and send a welcome email.

Understanding the Code

Understanding how the code works is essential to effectively using Google Apps Script. Let’s break down one of the examples to understand its components.

Example Breakdown: Sending Emails with Gmail

function sendWelcomeEmail() {

  var recipient = ‘user@example.com’;

  var subject = ‘Welcome to Our Service’;

  var body = ‘Hello,\n\nThank you for signing up for our service!\n\nBest regards,\nTeam’;

  GmailApp.sendEmail(recipient, subject, body);

}

1. Function Definition:

function sendWelcomeEmail() {

  // Code inside the function

}

  • function sendWelcomeEmail(): Defines a function named sendWelcomeEmail that can be called to execute the code within its braces {}.

2. Variables:

var recipient = ‘user@example.com’;

var subject = ‘Welcome to Our Service’;

var body = ‘Hello,\n\nThank you for signing up for our service!\n\nBest regards,\nTeam’;

  • var recipient: Stores the recipient’s email address.
  • var subject: Stores the email’s subject line.
  • var body: Stores the email’s body content. The \n\n creates new lines for better formatting.

3. Sending the Email:

GmailApp.sendEmail(recipient, subject, body);

  • GmailApp: A service provided by Apps Script to interact with Gmail.
  • .sendEmail(): A method that sends an email.
  • recipient, subject, body: Parameters passed to the sendEmail method to specify the recipient, subject, and body of the email.

Execution Flow:

  1. When sendWelcomeEmail is called, it initializes three variables: recipient, subject, and body.
  2. It then uses the GmailApp.sendEmail method to send an email with the specified recipient, subject, and body.

Authorization:

  • The first time you run this script, Google will prompt you to authorize the script to access your Gmail account to send emails on your behalf.

Answering Common Questions

1. Do I need to know JavaScript to use Google Apps Script?

While Google Apps Script is based on JavaScript, you don’t need to be an expert to get started. Basic knowledge of programming concepts like variables, functions, and control structures will help. However, even beginners can learn and use Apps Script effectively with the resources available.

2. Is Google Apps Script free to use?

Yes, Google Apps Script is free to use with your Google account. However, there are quotas and limitations on usage to prevent abuse, such as daily execution limits and runtime restrictions. For most personal and small-scale projects, these limits are sufficient.

3. Can I use Apps Script outside of Google Workspace?

Google Apps Script is primarily designed to work within Google Workspace (formerly G Suite) applications like Sheets, Docs, and Gmail. However, you can create stand-alone scripts and web applications that interact with external services via APIs.

4. How do I debug my scripts?

Apps Script provides built-in debugging tools:

  • Logger: Use Logger.log() to print messages to the Logs, accessible via View > Logs.
  • Debugger: Click on the bug icon in the Apps Script Editor to set breakpoints and step through your code.
  • Error Messages: Read the error messages and stack traces provided when a script fails to execute.

5. Can I share my Apps Script projects with others?

Yes, you can share Apps Script projects similar to how you share Google Docs or Sheets. Additionally, you can publish scripts as add-ons for others to install or deploy web applications that others can access via a URL.

6. Are there templates available to help me get started?

Yes, the Apps Script Editor offers templates and sample projects. Additionally, the Google Apps Script documentation provides numerous tutorials and sample codes to help you get started.

7. How do triggers work in Apps Script?

Triggers allow your scripts to run automatically based on specific events:

  • Simple Triggers: Such as onOpen(), onEdit(), which run automatically without explicit setup.
  • Installable Triggers: Created programmatically or via the Apps Script interface to run functions based on time-based schedules, form submissions, or other events.

Example: Time-Based Trigger

function createDailyTrigger() {

  ScriptApp.newTrigger(‘sendDailyReport’)

    .timeBased()

    .everyDays(1)

    .atHour(9)

    .create();

}

function sendDailyReport() {

  // Code to send a daily report

}

Explanation:

  • createDailyTrigger(): Sets up a trigger to run sendDailyReport every day at 9 AM.
  • sendDailyReport(): The function that contains the code to execute daily.

Next Steps and Resources

Now that you have a foundational understanding of Google Apps Script, here are some next steps and resources to further your learning:

Official Documentation:

  • Google Apps Script Overview: Apps Script Overview
  • Apps Script Reference: Apps Script Reference
  • Tutorials and Codelabs: Apps Script Tutorials

Learning Platforms:

  • Coursera: Offers courses on Google Apps Script and Google Workspace automation.
  • Udemy: Various courses ranging from beginner to advanced levels.
  • YouTube: Numerous tutorials and walkthroughs are available for free.

Community and Support:

  • Google Apps Script Community: Google Apps Script Community
  • Stack Overflow: Use the google-apps-script tag to ask questions and find answers.
  • Reddit: Subreddits like r/GoogleAppsScript can be helpful for discussions and tips.

Practice Projects:

  • Automate Reports: Create scripts to generate and email reports from Google Sheets data.
  • Custom Add-ons: Develop add-ons to extend the functionality of Google Docs or Sheets.
  • Integrate with APIs: Connect your scripts to external APIs to fetch or send data.

Tips for Success:

  • Start Small: Begin with simple scripts to automate basic tasks.
  • Read and Experiment: Use the documentation and experiment with code snippets.
  • Learn Debugging: Understanding how to debug your scripts will save you time and frustration.
  • Stay Updated: Google periodically updates Apps Script with new features and services. Stay informed through official channels.