Google Apps Script Fundamentals & Examples

by

I. Introduction to Google Apps Script

GAS is a powerful cloud-based scripting language that extends the functionality of Google Workspace applications like Sheets, Docs, Gmail, and Drive. It allows users to automate tasks, manipulate data, and build custom solutions within the Google Workspace ecosystem.

Key Features:

  • Cloud-based: No installation is required, and it is accessible via a web browser.
  • JavaScript Based: Leverages JavaScript syntax and concepts.
  • Integrated with Google Services: Access and manipulate data within Google Workspace apps.
  • Customizable: Create custom functions, menus, and web applications.

II. Bound vs. Standalone Scripts

GAS offers two script environments: bound and standalone.

Bound Scripts:

  • Attached to a Specific Google Workspace Document: Accessed and managed within the specific document.
  • Limited Scope: Primarily interact with data within the bound document.
  • Shared with Document: Automatically shared when the document is shared.

Example: A script bound to a Google Sheet that automatically clears specific ranges or inserts templated content.

function onOpen() {

var ui = SpreadsheetApp.getUi();

ui.createMenu(‘Custom Menu’)

.addItem(‘Clear Range’, ‘clearRange’)

.addToUi();

}

function clearRange() {

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

sheet.getRange(‘A1:D10’).clear();

}

Standalone Scripts:

  • Independent of Any Google Workspace Document: Created and managed through the Apps Script dashboard.
  • Broader Scope: Can interact with multiple Google services and perform tasks unrelated to a specific document.
  • Shared Independently: Can be shared with others regardless of document access.

Example: A script that checks for unread emails in your Gmail account and logs the count.

function checkUnreadEmails() {

var unread = GmailApp.getInboxUnreadCount();

Logger.log(‘You have ‘ + unread + ‘ unread emails.’);

}

III. Core Concepts & Programming Fundamentals

GAS utilizes JavaScript syntax and includes essential programming concepts like:

  • Variables & Data Types: Store and manipulate data using different data types (string, number, boolean, etc.).
  • Operators: Perform actions on variables and values using arithmetic, assignment, comparison, and logical operators.
  • Conditional Statements: Execute code blocks based on specific conditions using if, else if, and else statements.
  • Loops: Repeat code blocks multiple times using for, while, and do-while loops.
  • Functions: Encapsulate reusable blocks of code with parameters and return values.
  • Arrays: Store and manage collections of data in ordered lists.

IV. Working with Google Sheets

GAS provides robust functionality for interacting with Google Sheets.

  • Accessing Sheets & Ranges: Identify and select specific sheets and cell ranges within a spreadsheet.
  • Reading & Writing Data: Extract and manipulate data within cells, rows, and columns.
  • Creating & Formatting Sheets: Generate new sheets and apply formatting to cells and ranges.

Example: Splitting a string in a cell into multiple cells.

function splitStringIntoCells() {

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

var range = sheet.getRange(“A1”);

var stringValue = range.getValue();

var splitValues = stringValue.split(“,”);

for (var i = 0; i < splitValues.length; i++) {

sheet.getRange(1, i + 2).setValue(splitValues[i]);

}

}

V. Google Drive Integration

GAS can interact with Google Drive for file management tasks.

  • Creating & Managing Files: Create, delete, rename, and move files within Google Drive.
  • Managing File Permissions: Grant or revoke access to specific users or groups for files and folders.

Example: Organizing files into folders based on their creation date.

function organizeFiles() {

var folder = DriveApp.getFolderById(“folderId”);

var files = folder.getFiles();

while (files.hasNext()) {

var file = files.next();

var dateCreated = file.getDateCreated();

var year = dateCreated.getFullYear();

var month = dateCreated.getMonth() + 1;

var subfolderName = year + ‘-‘ + month;

var subfolder = getOrCreateFolder(folder, subfolderName);

file.moveTo(subfolder);

}

}

function getOrCreateFolder(parentFolder, folderName) {

var folders = parentFolder.getFoldersByName(folderName);

if (folders.hasNext()) {

return folders.next();

} else {

return parentFolder.createFolder(folderName);

}

}

VI. User Interface Customization

GAS allows for user interface customization within Google Workspace apps.

  • Custom Menus: Create menus with functions that execute when selected.
  • Custom Dialogs & Sidebars: Design custom UI elements using HTML, CSS, and JavaScript.

Example: Creating a custom menu in Google Sheets.

function onOpen() {

var ui = SpreadsheetApp.getUi();

ui.createMenu(‘Custom Menu’)

.addItem(‘Say Hello’, ‘sayHello’)

.addToUi();

}

function sayHello() {

SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(‘B1’).setValue(‘Hello!’);

}

VII. Best Practices & Optimization

  • Code Readability: Use consistent formatting, comments, and meaningful variable names.
  • Error Handling: Implement try-catch blocks to handle exceptions and gracefully recover from errors.
  • Optimization: Optimize loops and iterations for efficiency and minimize unnecessary API calls.
  • Modularization & Reusability: Break down code into reusable functions and modules for better organization and maintainability.

VIII. Key Takeaways

Google Apps Script empowers users to automate tasks, manipulate data, and build custom solutions within Google Workspace. By understanding core concepts and best practices and leveraging the vast range of GAS functionalities, users can significantly enhance their productivity and unlock new possibilities within the Google Workspace ecosystem.