Google Apps Script for Google Workspace: Comprehensive Guide

Google Apps Script for Google Workspace: Comprehensive Guide

Google Apps Script enables you to automate and extend Google Workspace apps such as Gmail, Sheets, Docs, Slides, Forms, Calendar, and Drive. This guide covers the fundamentals, practical examples, exercises, and quiz questions to master Google Workspace automation.

What is Google Apps Script for Google Workspace?

Google Apps Script is a JavaScript-based cloud platform for automating workflows and integrating Google Workspace apps. It allows users to:

• Automate repetitive tasks.
• Create custom integrations between apps.
• Build add-ons and web apps.

How to Access Google Apps Script

1. Open any Google Workspace app (e.g., Sheets).
2. Click Extensions > Apps Script.
3. Write your script in the Apps Script editor.

Common Services in Google Workspace

1. SpreadsheetApp: Automates Google Sheets.
2. DocumentApp: Manipulates Google Docs.
3. GmailApp: Manages Gmail messages.
4. CalendarApp: Automates Google Calendar.
5. DriveApp: Accesses Google Drive.
6. SlidesApp: Automates Google Slides.
7. FormApp: Manages Google Forms.

Basic Examples

Example 1: Automate Google Sheets

function writeToSheet() {

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

  sheet.getRange(“A1”).setValue(“Hello, Apps Script!”);

}

Explanation:

• SpreadsheetApp.getActiveSpreadsheet(): Gets the active spreadsheet.
• getRange(“A1”).setValue(“…”): Writes a value to cell A1.

Example 2: Send an Email via Gmail

function sendEmail() {

  GmailApp.sendEmail(“recipient@example.com”, “Subject Line”, “This is the email body.”);

}

Explanation:

• GmailApp.sendEmail(…): Sends an email with a recipient, subject, and body.

Example 3: Create a Calendar Event

function createEvent() {

  const calendar = CalendarApp.getDefaultCalendar();

  calendar.createEvent(“Meeting”, new Date(), new Date(new Date().getTime() + 60 * 60 * 1000));

}

Explanation:

• CalendarApp.getDefaultCalendar(): Accesses the default calendar.
• createEvent(name, start, end): Creates an event with a specified name, start, and end time.

Advanced Examples

Example 4: Integrate Gmail and Google Sheets

Automatically log email subjects into a Google Sheet.

function logEmailSubjects() {

  const threads = GmailApp.getInboxThreads(0, 10); // Get the first 10 threads

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

  threads.forEach((thread, index) => {

    const subject = thread.getFirstMessageSubject();

    sheet.getRange(index + 1, 1).setValue(subject);

  });

}

Explanation:

• GmailApp.getInboxThreads(start, max): Gets email threads from the inbox.
• thread.getFirstMessageSubject(): Retrieves the subject of the first message in a thread.

Example 5: Backup Google Drive Files to a Spreadsheet

function backupDriveFiles() {

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

  const files = DriveApp.getFiles();

  let row = 1;

  while (files.hasNext()) {

    const file = files.next();

    sheet.getRange(row, 1).setValue(file.getName());

    sheet.getRange(row, 2).setValue(file.getUrl());

    row++;

  }

}

Explanation:

• DriveApp.getFiles(): Retrieves all files in Google Drive.
• file.getName() and file.getUrl(): Get file names and URLs.

Example 6: Generate a Google Docs Report from a Template

function generateReport() {

  const template = DriveApp.getFileById(“TEMPLATE_FILE_ID”);

  const folder = DriveApp.getFolderById(“DESTINATION_FOLDER_ID”);

  const copy = template.makeCopy(`Report – ${new Date().toDateString()}`, folder);

  const doc = DocumentApp.openById(copy.getId());

  const body = doc.getBody();

  body.replaceText(“{{Date}}”, new Date().toDateString());

  body.replaceText(“{{Name}}”, “John Doe”);

  doc.saveAndClose();

}

Explanation:

• DriveApp.getFileById(id): Accesses a specific file by ID.
• makeCopy(name, folder): Creates a copy of a file in the specified folder.
• replaceText(search, replace): Replaces placeholders in the document.

Exercises

Exercise 1: Automate Meeting Invitations

Write a script to:

1. Create a new calendar event.
2. Send email invitations to participants.

Solution:

function automateMeeting() {

  const calendar = CalendarApp.getDefaultCalendar();

  const event = calendar.createEvent(“Team Meeting”, new Date(), new Date(new Date().getTime() + 60 * 60 * 1000));

  event.addGuest(“participant1@example.com”);

  event.addGuest(“participant2@example.com”);

}

Exercise 2: List All Drive Folders

Write a script to list all folders in Google Drive into a Google Sheet.

Solution:

function listDriveFolders() {

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

  const folders = DriveApp.getFolders();

  let row = 1;

  while (folders.hasNext()) {

    const folder = folders.next();

    sheet.getRange(row, 1).setValue(folder.getName());

    sheet.getRange(row, 2).setValue(folder.getUrl());

    row++;

  }

}

Exercise 3: Automate Form Submission Notifications

Write a script to send an email notification for every new Google Form submission.

Solution:

function onFormSubmit(e) {

  const responses = e.values;

  const email = “your-email@example.com”;

  GmailApp.sendEmail(email, “New Form Submission”, `Responses:\n${responses.join(“\n”)}`);

}

Multiple-Choice Questions

Question 1:

Which method is used to get the active spreadsheet?

1. SpreadsheetApp.open()
2. SpreadsheetApp.getActiveSpreadsheet()
3. SpreadsheetApp.getSpreadsheet()
4. SpreadsheetApp.activeSheet()

Answer: 2. SpreadsheetApp.getActiveSpreadsheet()

Question 2:

What does GmailApp.sendEmail(“recipient@example.com”, “Subject”, “Body”) do?

1. Sends an email with attachments.
2. Sends an email without a subject.
3. Sends an email with the specified recipient, subject, and body.
4. None of the above.

Answer: 3. Sends an email with the specified recipient, subject, and body.

Question 3:

How can you retrieve all files in Google Drive?

1. DriveApp.getAllFiles()
2. DriveApp.getFiles()
3. DriveApp.listFiles()
4. DriveApp.getFileList()

Answer: 2. DriveApp.getFiles()

Best Practices

1. Use Triggers: Automate tasks with triggers like onEdit, onOpen, or onFormSubmit.
2. Optimize API Calls: Minimize unnecessary API calls to improve script performance.
3. Secure Permissions: Restrict script access to authorized users.