Comprehensive Guide to Google Apps Script for Google Sheets

Welcome to this in-depth guide on Google Apps Script for Google Sheets. Whether you’re a beginner or looking to enhance your spreadsheet automation skills, this guide is designed to help you master Google Apps Script in the context of Google Sheets. We’ll cover everything from setting up your environment to building complex scripts, complete with practical examples, coding exercises, quizzes, and valuable tips and tricks.

1. Introduction to Google Apps Script and Google Sheets

What is Google Apps Script?

Google Apps Script is a cloud-based scripting language based on JavaScript that allows you to automate tasks and extend the functionality of Google Workspace applications like Google Sheets, Docs, Slides, Gmail, and more.

Why Use Google Apps Script with Google Sheets?

Automation: Automate repetitive tasks like data entry, formatting, and calculations.

Customization: Create custom functions and menus to enhance your spreadsheets.

Integration: Connect Google Sheets with other Google services and external APIs.

Productivity: Save time and reduce errors by automating complex workflows.

Prerequisites

Basic understanding of Google Sheets.

Familiarity with JavaScript is helpful but not required.

2. Getting Started

Setting Up Your Environment

Google Account: Ensure you have a Google account to access Google Sheets and Apps Script.

Access to Google Sheets: Navigate to Google Sheets and create a new spreadsheet.

Accessing the Script Editor

Open your Google Sheet.

Navigate to Extensions > Apps Script.

The Apps Script editor will open in a new tab.

3. Understanding Google Apps Script Basics

What is Google Apps Script?

Cloud-Based: No local installation required; scripts are stored and run on Google’s servers.

Based on JavaScript: If you’re familiar with JavaScript, you’ll find it easy to get started.

Versatile: Interact with various Google services and external APIs.

Key Features

Built-in Libraries: Access to Google Sheets, Docs, Drive, Gmail, and more.

Triggers: Automate tasks based on events like opening a spreadsheet or editing a cell.

Custom Functions: Create your own spreadsheet functions.

Add-ons: Build custom add-ons for Google Sheets.

Use Cases with Google Sheets

Automate data processing and analysis.

Generate reports and dashboards.

Integrate with external APIs to fetch or send data.

Create custom user interfaces within Sheets.

4. Setting Up Your Development Environment

Accessing the Script Editor

Open a Google Sheet.

Go to Extensions > Apps Script.

The Apps Script editor will open, showing a default Code.gs file.

Overview of the Interface

Code Editor: Write your scripts here.

Project Settings: Manage project properties and permissions.

Debugger and Logs: Tools for debugging your scripts.

Menus:

File: Save, rename, or manage versions of your project.

Edit: Undo, redo, find, and replace.

View: Access logs and triggers.

Run: Execute your scripts.

Debug: Step through your code to find issues.

5. JavaScript Fundamentals Refresher

Since Google Apps Script is based on JavaScript, understanding its basics will be beneficial.

Variables and Data Types

Declaration:


var x = 10; // Avoid using ‘var’ in modern code

let y = 20; // Use ‘let’ for variables that may change

const z = 30; // Use ‘const’ for constants

Data Types:

Number

String

Boolean

Array

Object

Null

Undefined

Functions

Function Declaration:


function add(a, b) {

  return a + b;

}

Function Expression:


const multiply = function(a, b) {

  return a * b;

};

Arrow Functions:


const divide = (a, b) => a / b;

Control Structures

Conditional Statements:


if (condition) {

  // Code if condition is true

} else {

  // Code if condition is false

}

Loops:


for (let i = 0; i < 5; i++) {

  // Code to execute

}

while (condition) {

  // Code to execute

}

6. Working with Google Sheets in Apps Script

Google Apps Script provides extensive classes and methods to interact with Google Sheets.

Accessing Spreadsheets and Sheets

Get the Active Spreadsheet:


const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

Get a Specific Sheet:


const sheet = spreadsheet.getSheetByName(‘Sheet1’);

// Or get the active sheet

const activeSheet = spreadsheet.getActiveSheet();

Reading and Writing Data

Get Data from a Cell:


const cellValue = sheet.getRange(‘A1’).getValue();

Set Data in a Cell:


sheet.getRange(‘A1’).setValue(‘Hello, World!’);

Get Data from a Range:


const dataRange = sheet.getRange(‘A1:B10’);

const data = dataRange.getValues();

Set Data in a Range:


const newData = [

  [‘Name’, ‘Age’],

  [‘Alice’, 30],

  [‘Bob’, 25]

];

sheet.getRange(‘A1:B3’).setValues(newData);

Formatting Cells

Set Cell Background Color:


sheet.getRange(‘A1’).setBackground(‘yellow’);

Set Font Style:


sheet.getRange(‘A1’).setFontWeight(‘bold’).setFontSize(12);

Apply Number Format:


sheet.getRange(‘B2’).setNumberFormat(‘0.00’);

Manipulating Rows and Columns

Insert Rows and Columns:


sheet.insertRowBefore(1); // Inserts a row before row 1

sheet.insertColumnAfter(2); // Inserts a column after column 2

Delete Rows and Columns:


sheet.deleteRow(3); // Deletes row 3

sheet.deleteColumns(1, 2); // Deletes 2 columns starting from column 1

Hide and Unhide Rows and Columns:


sheet.hideRow(sheet.getRange(‘4:4’)); // Hides row 4

sheet.unhideColumn(sheet.getRange(‘B:B’)); // Unhides column B

7. Advanced Concepts

Custom Functions

Create your own functions to use within Google Sheets.

Syntax:


function FUNCTION_NAME(arg1, arg2) {

  // Function logic

  return result;

}

Example:


function ADDNUMBERS(a, b) {

  return a + b;

}

Usage in Google Sheets:

In a cell, type =ADDNUMBERS(5, 10) to get 15.

Triggers in Google Sheets

Automate actions based on events.

Simple Triggers:

onOpen(e): Runs when a spreadsheet is opened.

onEdit(e): Runs when a cell is edited.

onInstall(e): Runs when an add-on is installed.

Installable Triggers:

More powerful and can call services that require authorization.

Set up via Triggers > Add Trigger in the script editor.

Example:


function onEdit(e) {

  const range = e.range;

  if (range.getA1Notation() === ‘A1’) {

    SpreadsheetApp.getActiveSheet().getRange(‘B1’).setValue(‘Cell A1 was edited!’);

  }

}

Data Validation and Protection

Set Data Validation:


const cell = sheet.getRange(‘A2’);

const rule = SpreadsheetApp.newDataValidation()

  .requireNumberBetween(1, 100)

  .setAllowInvalid(false)

  .build();

cell.setDataValidation(rule);

Protect a Range or Sheet:


const protection = sheet.getRange(‘A1:A10’).protect().setDescription(‘Protected Range’);

protection.addEditor(‘user@example.com’); // Grants edit access to a user

8. Practical Examples

Example 1: Automatically Sort Data When a Change Occurs

Objective: Automatically sort data in a sheet whenever new data is added or existing data is changed.

Steps:

Write the Script:


function onEdit(e) {

  const sheet = e.range.getSheet();

  const range = sheet.getDataRange();

  range.sort({ column: 1, ascending: true });

}

Explanation:

The onEdit trigger runs whenever a cell is edited.

The script sorts the entire data range based on the first column.

Example 2: Sending Customized Emails from Sheet Data

Objective: Send personalized emails to a list of recipients in the sheet.

Steps:

Set Up the Sheet:

Column A: Email Addresses

Column B: Names

Column C: Status (e.g., “Not Sent”, “Sent”)

Write the Script:


function sendPersonalizedEmails() {

  const sheet = SpreadsheetApp.getActiveSheet();

  const data = sheet.getDataRange().getValues();

  for (let i = 1; i < data.length; i++) {

    const email = data[i][0];

    const name = data[i][1];

    const status = data[i][2];

    if (status !== ‘Sent’) {

      const subject = ‘Hello ‘ + name;

      const message = ‘Dear ‘ + name + ‘,\n\nThis is a personalized email.\n\nBest regards,\nYour Company’;

      MailApp.sendEmail(email, subject, message);

      sheet.getRange(i + 1, 3).setValue(‘Sent’);

    }

  }

}

Explanation:

Loops through each row and sends an email if the status is not ‘Sent’.

Updates the status to ‘Sent’ after sending the email.

Example 3: Importing Data from an External API

Objective: Fetch live currency exchange rates and display them in Google Sheets.

Steps:

Write the Script:


function getExchangeRates() {

  const url = ‘https://api.exchangerate-api.com/v4/latest/USD’;

  const response = UrlFetchApp.fetch(url);

  const data = JSON.parse(response.getContentText());

  const rates = data.rates;

  const sheet = SpreadsheetApp.getActiveSheet();

  let row = 1;

  for (let currency in rates) {

    sheet.getRange(row, 1).setValue(currency);

    sheet.getRange(row, 2).setValue(rates[currency]);

    row++;

  }

}

Explanation:

Fetches exchange rates from an external API.

Parses the JSON response and writes the data into the sheet.

9. Coding Exercises

Exercise 1: Create a Function to Calculate the Average of a Range

Objective: Write a custom function =AVERAGE_RANGE(range) that calculates the average of numbers in a specified range.

Solution:

function AVERAGE_RANGE(range) {

  let total = 0;

  let count = 0;

  for (let i = 0; i < range.length; i++) {

    for (let j = 0; j < range[i].length; j++) {

      const value = range[i][j];

      if (typeof value === ‘number’) {

        total += value;

        count++;

      }

    }

  }

  return count === 0 ? 0 : total / count;

}

Usage:

In Google Sheets, use =AVERAGE_RANGE(A1:B10).

Exercise 2: Highlight Duplicate Entries in a Column

Objective: Write a script that highlights duplicate values in column A.

Solution:

function highlightDuplicates() {

  const sheet = SpreadsheetApp.getActiveSheet();

  const data = sheet.getRange(‘A:A’).getValues();

  const values = data.flat();

  const duplicates = values.filter((item, index) => values.indexOf(item) !== index && item !== ”);

  for (let i = 1; i <= values.length; i++) {

    const cellValue = sheet.getRange(‘A’ + i).getValue();

    if (duplicates.includes(cellValue)) {

      sheet.getRange(‘A’ + i).setBackground(‘red’);

    } else {

      sheet.getRange(‘A’ + i).setBackground(null);

    }

  }

}

Explanation:

The script checks for duplicates and sets the background color of duplicate cells to red.

10. Quiz Questions and Answers

Question 1

How do you get the active sheet in a Google Spreadsheet using Apps Script?

A) SpreadsheetApp.getActiveSheet()
B) SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
C) SpreadsheetApp.getSheet()
D) SpreadsheetApp.getActive()

Answer: B) SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()

Question 2

Which method would you use to set the background color of a cell?

A) setColor()
B) setBackgroundColor()
C) setBackground()
D) setCellColor()

Answer: C) setBackground()

Question 3

What does the getValues() method return when called on a range?

A) A two-dimensional array of cell values
B) A single value
C) The formula in the cells
D) The formatting of the cells

Answer: A) A two-dimensional array of cell values

Question 4

How can you protect a range so only specific users can edit it?

A) By using Range.protect() and adding editors
B) By setting Range.setProtected(true)
C) By changing the cell’s formatting
D) By hiding the range

Answer: A) By using Range.protect() and adding editors

Question 5

Which method allows you to insert a new row at the top of the sheet?

A) sheet.addRow(1)
B) sheet.insertRowBefore(1)
C) sheet.insertRow(1)
D) sheet.createRow(1)

Answer: B) sheet.insertRowBefore(1)

Question 6

What is the correct way to write a custom function that can be used in Google Sheets?

A) function myFunction() {}
B) function MYFUNCTION() {}
C) Both A and B
D) None of the above

Answer: C) Both A and B

Question 7

Which Apps Script class is used to fetch data from external URLs?

A) UrlFetchApp
B) HttpApp
C) ExternalDataApp
D) FetchApp

Answer: A) UrlFetchApp

Question 8

How do you create a data validation rule that requires a number between 1 and 10?

A) Using DataValidationBuilder.requireNumberBetween(1, 10)
B) Using Range.setValidation(1, 10)
C) Using Range.requireNumberBetween(1, 10)
D) Using DataValidation.requireBetween(1, 10)

Answer: A) Using DataValidationBuilder.requireNumberBetween(1, 10)

Question 9

Which trigger would you use to execute a function whenever a cell in the spreadsheet is edited?

A) onOpen
B) onEdit
C) onChange
D) onUpdate

Answer: B) onEdit

Question 10

How can you get the name of the active spreadsheet?

A) SpreadsheetApp.getActiveSpreadsheet().getName()
B) SpreadsheetApp.getName()
C) SpreadsheetApp.getActiveSheet().getName()
D) SpreadsheetApp.getSpreadsheetName()

Answer: A) SpreadsheetApp.getActiveSpreadsheet().getName()

11. Tips and Tricks

Tip 1: Use Named Ranges

Benefit: Makes your code more readable.

Usage:


const range = sheet.getRange(‘NamedRange’);

Tip 2: Optimize Your Scripts

Batch Operations: Minimize calls to the Spreadsheet API by working with arrays.


// Bad: Multiple setValue calls

for (let i = 0; i < data.length; i++) {

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

}

// Good: Set values in bulk

sheet.getRange(1, 1, data.length, 1).setValues(data);

Tip 3: Use Logger Wisely

Debugging: Use Logger.log() to print variables.

View Logs: Go to View > Logs or press Ctrl + Enter.

Tip 4: Error Handling

Try-Catch Blocks:


try {

  // Code that might throw an error

} catch (e) {

  Logger.log(‘Error: ‘ + e.message);

}

Tip 5: Comment Your Code

Maintainability: Comments help you and others understand your code.


// This function calculates the total sales

function calculateTotalSales() {

  // …

}

Tip 6: Leverage Autocomplete

Efficiency: The script editor provides autocomplete suggestions.

Shortcut: Press Ctrl + Space to see available methods.

Tip 7: Learn from Examples

Explore: Look at sample scripts in the Apps Script Gallery.

Adapt: Modify existing scripts to suit your needs.

Tip 8: Stay Organized

File Structure: Split your code into multiple .gs files for better organization.

Naming Conventions: Use meaningful names for variables and functions.

Tip 9: Use Version Control

Backup: Use File > Manage Versions to save versions.

Revert: Easily roll back to a previous version if needed.

Tip 10: Keep Security in Mind

Sensitive Data: Avoid hardcoding sensitive information.

Properties Service: Store API keys and credentials securely.

12. Conclusion

Congratulations on completing this comprehensive guide to Google Apps Script for Google Sheets! You’ve learned how to automate tasks, create custom functions, and enhance your spreadsheets with powerful scripts.

Key Takeaways

Automation: Save time by automating repetitive tasks.

Customization: Tailor Google Sheets to meet your specific needs.

Integration: Connect Google Sheets with other services and APIs.

Productivity: Improve efficiency and reduce errors.