Explore Coding with 120 Interactive Exercises for All Skill Levels

“Coding with Google Apps Script: A Step-by-Step Journey”, authored by Laurence Lars Svekis, serves as a comprehensive guide for anyone keen on mastering Google Apps Script (GAS) through interactive and practical learning. Designed to cater to all skill levels, this book guides readers through a series of 120 interactive exercises that gradually build from basic to advanced applications of GAS.

The book begins with a thorough introduction to GAS, focusing primarily on its integration with Google Sheets. Early chapters aim to familiarize readers with the Google Apps Script environment, teaching how to create simple scripts that can automate tasks within Google Sheets, such as logging data, modifying cell appearances, and inserting dynamic data like the current date. Each exercise is detailed step-by-step, offering readers the chance to understand the fundamentals of scripting, including how to interact with various spreadsheet elements and execute basic functions.

Progressing through the book, Svekis introduces more complex scripting scenarios that involve handling external data. Readers learn to fetch and display real-time data from public APIs, enhancing their Google Sheets with up-to-date information. This section is particularly valuable for intermediate users interested in API integration and JSON data processing, which are crucial for developing interactive spreadsheets that react to external data sources.

The book also dives into advanced data manipulation strategies where readers explore dynamic data management. This includes creating automated processes that respond to data changes and integrate Google Sheets with other Google services like Google Drive and Gmail for comprehensive data management solutions. These exercises prepare readers to handle complex tasks, automate workflows, and manage data across platforms efficiently.

One of the standout features of this book is its commitment to practical application. Each chapter includes tasks that encourage readers to apply learned concepts to real-world scenarios. This approach not only solidifies understanding but also enhances the reader’s ability to solve practical problems using GAS.

In the later chapters, Svekis explores sophisticated automation and reporting techniques that help streamline communication and reporting processes within organizations. The book outlines how to generate detailed reports, manage emails, and create interactive dashboards directly within Google Sheets. This section is especially beneficial for users looking to enhance productivity and implement effective data communication strategies.

Throughout, “Coding with Google Apps Script: A Step-by-Step Journey” maintains a clear, informative tone, making complex concepts accessible. The book is richly supplemented with code snippets, illustrative examples, and insights that offer a deep dive into the capabilities of Google Apps Script.

In summary, Laurence Lars Svekis’ book is an essential resource for anyone looking to leverage the power of Google Apps Script to enhance their productivity and data management skills. From basic scripting to advanced automation, this book provides a robust foundation in GAS, making it an invaluable addition to both novices and experienced developers alike.

For more content and to learn more, visit

https://basescripts.com

Book Source Code on GitHub https://github.com/lsvekis/Apps-Script-Exercises-Book

Chapter 1: Getting Started with Google Apps Script

Introduction to Basic Scripting in Google Sheets

This chapter serves as an introduction to Google Apps Script, focusing on essential scripting tasks within Google Sheets. Readers will learn how to create and execute simple scripts that interact with spreadsheet elements, enhancing their ability to automate tasks and customize their experience. The chapter is designed for beginners and will guide them through practical exercises to gain confidence in scripting.

Concepts Covered:

Hello World

Objective: Familiarize with the Google Apps Script environment and logging outputs.
Code: Using Logger.log to print “Hello, world!”.
Key Learning: Basic function creation and execution workflow in Apps Script.

Create a Custom Menu in Google Sheets

Objective: Enhance user interaction by adding custom menu items to run scripts.
Code: Implementing onOpen and ui.createMenu to create interactive menus.
Key Learning: Interface interaction and event-driven scripting.

Log the Active Cell’s Value

Objective: Read and log data from the spreadsheet to debug or analyze content.
Code: Accessing the active cell and logging its value.
Key Learning: Working with cell references and data retrieval.

Change Cell Background Color

Objective: Modify cell appearance based on script conditions.
Code: Changing the background color of the selected cell.
Key Learning: Direct manipulation of cell styles.

Insert Current Date into a Cell

Objective: Use scripts to dynamically insert data into cells.
Code: Inserting the current date into the active cell using new Date().
Key Learning: Manipulating cell content with JavaScript date functions.

What Readers Can Expect:

By the end of this chapter, readers will have a solid understanding of the basics of Google Apps Script, including how to write simple functions, manipulate spreadsheet elements, and create user-friendly scripts. The exercises are designed to build foundational skills that can be expanded upon in subsequent chapters, providing a practical approach to learning Google Apps Script.

Hello World

Objective: Create a script that logs “Hello, world!” to the Google Apps Script log.

Steps:

Open Google Sheets.

Click on Extensions > Apps Script.

Delete any code in the script editor and paste the following:

function helloWorld() {

Logger.log(“Hello, world!”);

}

Save and name your project.

Click on the play/run button next to the helloWorld function.

View the log by clicking on Executions, then click on the latest execution and view the logs.

Explanation: This script defines a function helloWorld that uses Logger.log to print a message to the Google Apps Script log. This is the simplest way to display output in Google Apps Script.

Create a Custom Menu in Google Sheets

Objective: Write a script that adds a custom menu to Google Sheets that runs a script function.

Steps:

Open a new Google Sheet and go to Extensions > Apps Script.

Replace any existing code with:

function onOpen() {

const ui = SpreadsheetApp.getUi();

ui.createMenu(‘My Custom Menu’)

.addItem(‘Show Message’, ‘showMessage’)

.addToUi();

}

function showMessage() {

SpreadsheetApp.getUi().alert(‘Hello from Google Sheets!’);

}

Save and name your project.

Reload your Google Sheets page to see the custom menu appear as “My Custom Menu”.

Click on “Show Message” to see an alert.

Explanation: onOpen is a special function that runs automatically when the spreadsheet is opened. It creates a new menu item in the Sheets UI which, when clicked, triggers the showMessage function. This function uses SpreadsheetApp.getUi().alert() to show a pop-up alert with a message.

Log the Active Cell’s Value

Objective: Create a script that logs the current active cell’s value in Google Sheets.

Steps:

Open a Google Sheet and go to Extensions > Apps Script.

Replace any existing code with:

function logActiveCellValue() {

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

const cell = sheet.getActiveCell();

Logger.log(cell.getValue());

}

Save and name your project.

Click the run/play button next to logActiveCellValue.

View the log in Executions.

Explanation: This script gets the currently active spreadsheet and sheet, identifies the active cell, and logs its value. It’s useful for debugging scripts that depend on cell values.

Change Cell Background Color

Objective: Write a script to change the background color of the currently selected cell in Google Sheets.

Steps:

Open a Google Sheet and access Extensions > Apps Script.

Replace the existing code with:

function changeBackgroundColor() {

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

const cell = sheet.getActiveCell();

cell.setBackground(‘yellow’);

}

Save and name your project.

Select a cell in your sheet and run changeBackgroundColor.

Explanation: This script accesses the active cell and changes its background color to yellow using the setBackground method. This can be adapted to apply different styles to cells based on conditions.

Insert Current Date into a Cell

Objective: Develop a script that inserts the current date into a selected cell in Google Sheets.

Steps:

Open a Google Sheet and go to Extensions > Apps Script.

Replace any code with:

function insertCurrentDate() {

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

const cell = sheet.getActiveCell();

cell.setValue(new Date());

}

Save and name your project.

Select a cell and run insertCurrentDate.

Explanation: This script sets the value of the currently selected cell to the current date and time. The new Date() JavaScript method is used to get the current date and time.

Sum of Selected Cells

Objective: Write a script that calculates the sum of selected cells in Google Sheets and displays the result in a popup.

Steps:

Open a Google Sheet and navigate to Extensions > Apps Script.

Replace any existing code with:

function sumSelectedCells() {

const range = SpreadsheetApp.getActiveSpreadsheet().getActiveRange();

const values = range.getValues();

let sum = 0;

values.forEach(function(row) {

row.forEach(function(cell) {

sum += cell;

});

SpreadsheetApp.getUi().alert(‘The sum is: ‘ + sum);

}

Save and name your project.

Select a range of cells with numbers, then run sumSelectedCells.

Explanation: This script retrieves the range of selected cells, extracts their values, and calculates the sum. It then displays the sum using an alert. This exercise introduces handling arrays and iterating through them.

Insert Multiple Rows

Objective: Develop a script that inserts a specified number of rows below the currently active row in Google Sheets.

Steps:

Open a Google Sheet and go to Extensions > Apps Script.

Replace any code with:

function insertRows() {

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

const activeRow = sheet.getActiveCell().getRow();

const numRowsToInsert = 3; // Change this number as needed

sheet.insertRowsAfter(activeRow, numRowsToInsert);

}

Save and name your project.

Select any cell in a row and run insertRows.

Explanation: This script gets the active row and inserts a specified number of rows directly after it. This demonstrates how to manipulate sheet structure programmatically.

Convert Range to Uppercase

Objective: Write a script that converts all text in the selected range in Google Sheets to uppercase.

Steps:

Open a Google Sheet and access Extensions > Apps Script.

Replace the existing code with:

function convertToUppercase() {

const range = SpreadsheetApp.getActiveSpreadsheet().getActiveRange();

const values = range.getValues();

const uppercasedValues = values.map(row => row.map(cell => typeof cell === ‘string’ ? cell.toUpperCase() : cell));

range.setValues(uppercasedValues);

}

Save and name your project.

Select a range containing text and run convertToUppercase.

Explanation: This script maps over the selected range, checks if each cell is a string, and if so, converts it to uppercase. It then sets the modified array back to the range. This introduces array manipulation and conditional logic.

Delete Empty Rows

Objective: Create a script that deletes all empty rows in the active sheet.

Steps:

Open a Google Sheet and navigate to Extensions > Apps Script.

Replace any code with:

function deleteEmptyRows() {

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

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

for (let i = rows.length – 1; i >= 0; i–) {

if (rows[i].every(cell => cell === “”)) {

sheet.deleteRow(i + 1);

}

Save and name your project.

Run deleteEmptyRows.

Explanation: This script iterates over every row from the bottom up (to avoid indexing issues upon deletion) and checks if all cells in a row are empty. If so, it deletes the row. This exercise is useful for cleaning up data.

Chapter 2 : Integrating External Data into Google Sheets

Fetching and Displaying Weather Data in Google Sheets

In this chapter, we will explore how to leverage Google Apps Script to integrate external data into Google Sheets. Specifically, the focus will be on fetching real-time weather data from a public API and displaying it within a spreadsheet. This chapter is designed to introduce intermediate scripting skills, particularly working with APIs and handling JSON data, while continuing to develop the reader’s ability to automate and customize their Google Sheets environment.

Concepts Covered:

Fetch and Display Weather Data
- Objective: Fetch current weather data for a specific location using a public API and display it in Google Sheets.
- Code: Utilizing UrlFetchApp.fetch to make HTTP requests and JSON.parse to handle the JSON response.
- Key Learning: Understanding API integration, JSON parsing, and dynamic data display in spreadsheets.
Duplicate a Sheet
- Objective: Create a duplicate of the active sheet in Google Sheets and allow the user to name the new sheet.
- Code: Using getActiveSheet, copyTo, and UI prompts to interact with the user.
- Key Learning: Sheet manipulation and user input handling through dialogs.
Format Column as Currency
- Objective: Automatically format a specified column in Google Sheets to display values as currency.
- Code: Applying number formats to spreadsheet columns with setNumberFormat.
- Key Learning: Enhancing data presentation through formatting settings.
Send Emails from a List
- Objective: Send custom emails to a list of addresses stored in a Google Sheet.
- Code: Iterating over spreadsheet data and using MailApp.sendEmail for email automation.
- Key Learning: Integration with Gmail and practical use of loops for data processing.
Create Google Calendar Events
- Objective: Read event details from a spreadsheet and add them to Google Calendar.
- Code: Using CalendarApp to interact with Google Calendar and create events.
- Key Learning: Synchronization between Google Sheets and Google Calendar.
Import Data from External API
- Objective: Fetch and display external data from another API into Google Sheets.
- Code: Similar to the first exercise but adapted to different API data.
- Key Learning: Flexibility in using APIs for diverse data integration needs.

What Readers Can Expect:

By the end of this chapter, readers will have a practical understanding of how to integrate external data sources into Google Sheets using Google Apps Script. They will learn how to interact with APIs, process JSON data, and utilize this data to enhance the functionality of their spreadsheets. Additionally, readers will gain experience in automating tasks across Google’s platform, including sending emails and managing calendar events, all through script interactions within Google Sheets.

Fetch and Display Weather Data

Objective: Use a script to fetch weather data from a public API and display it in Google Sheets.

Steps:

Open a Google Sheet and go to Extensions > Apps Script.

Replace any code with:

function displayWeather() {

const response = UrlFetchApp.fetch(“https://api.open-meteo.com/v1/forecast?latitude=35.6895&longitude=139.6917&current_weather=true”);

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

const weather = weatherData.current_weather;

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

sheet.getRange(“A1”).setValue(“Temperature: ” + weather.temperature + “°C”);

sheet.getRange(“A2”).setValue(“Windspeed: ” + weather.windspeed + ” km/h”);

}

Save and name your project.

Run displayWeather.

Explanation: This script makes an HTTP request to a weather API for current weather data for Tokyo, Japan. It parses the JSON response, extracts temperature and windspeed, and writes these values into the active spreadsheet. This introduces working with external APIs and JSON data.

Duplicate a Sheet

Objective: Write a script that duplicates the active sheet in Google Sheets and renames it based on user input.

Steps:

Open a Google Sheet and access Extensions > Apps Script.

Replace the existing code with:

function duplicateSheet() {

const ss = SpreadsheetApp.getActiveSpreadsheet();

const activeSheet = ss.getActiveSheet();

const newName = SpreadsheetApp.getUi().prompt(‘Enter a name for the new sheet’).getResponseText();

activeSheet.copyTo(ss).setName(newName);

}

Save and name your project.

Run duplicateSheet and follow the prompt to name the new sheet.

Explanation: This script copies the current active sheet and prompts the user to provide a name for the new sheet, which is then set using setName().

Format Column as Currency

Objective: Develop a script that formats a specified column in Google Sheets as currency.

Steps:

Open a Google Sheet and go to Extensions > Apps Script.

Replace any code with:

function formatAsCurrency() {

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

const column = sheet.getRange(‘B:B’); // Assumes column B, change as needed

column.setNumberFormat(‘$#,##0.00’);

}

Save and name your project.

Run formatAsCurrency.

Explanation: This script gets all cells in column B and applies a currency format to them. You can adjust the column by changing the range in getRange().

Send Emails from a List

Objective: Write a script that sends an email to each address listed in a specific column of a Google Sheet.

Steps:

Ensure your Google Sheet contains emails in column A.

Open Extensions > Apps Script and replace the existing code with:

function sendEmails() {

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

const range = sheet.getRange(‘A2:A’); // Starts from A2 to skip headers

const emails = range.getValues();

emails.forEach(function(row) {

if (row[0]) {

MailApp.sendEmail(row[0], ‘Subject Line’, ‘Hello, this is a test email.’);

}

});

}

Save and name your project.

Run sendEmails.

Explanation: This script iterates through all email addresses in column A, sending a simple email to each. Adjust the subject and message body as needed.

Create Google Calendar Events

Objective: Create a script that reads event details from a Google Sheet and adds them to Google Calendar.

A (Event Title)	B (Start Date)	C (End Date)
Team Meeting	2024-05-10 09:00:00	2024-05-10 10:00:00
Project Review	2024-05-11 14:00:00	2024-05-11 15:30:00
Client Call	2024-05-12 13:00:00	2024-05-12 14:00:00
Workshop	2024-05-15 10:00:00	2024-05-15 12:00:00
Conference	2024-05-20 08:00:00	2024-05-20 17:00:00

Steps:

Format your sheet with event details in columns: Title (A), Start Date (B), End Date (C).

Open Extensions > Apps Script and replace the existing code with:

function createCalendarEvents() {

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

const range = sheet.getRange(‘A2:C’);

const events = range.getValues();

const calendar = CalendarApp.getDefaultCalendar();

events.forEach(function(event) {

// Check if the first element (event title) is not empty

if (event[0]) {

calendar.createEvent(event[0], new Date(event[1]), new Date(event[2]));

}

});

}

Save and name your project.

Run createCalendarEvents.

Explanation: This script reads event details from the sheet and creates an event in the user’s default calendar for each row of data.

Import Data from External API

Objective: Write a script to fetch data from an external API and write it to Google Sheets.

Steps:

Open Extensions > Apps Script.

Replace the existing code with:

function importData() {

const response = UrlFetchApp.fetch(“https://randomuser.me/api/?results=5”);

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

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

data.results.forEach(function(item, index) {

Logger.log(`${item.name.first} ${item.name.last}`);

sheet.getRange(index + 1, 1).setValue(`${item.name.first} ${item.name.last}`); // Assume data is an array of objects with ‘name’ property

});

}

Save and name your project.

Run importData.

Explanation: This script makes an HTTP request to an external API, parses the JSON response, and writes each item to the active sheet. Adjust the API endpoint and data handling as per your requirements.

Generate and Email a Spreadsheet Report

Objective: Create a script that generates a report from data in a Google Sheet and emails it as a PDF attachment.

Steps:

Open a Google Sheet that contains some data you want to report on.

Navigate to Extensions > Apps Script.

Replace any existing code with:

function emailSpreadsheetAsPDF() {

const ss = SpreadsheetApp.getActiveSpreadsheet();

const sheet = ss.getActiveSheet();

const url = ss.getUrl().replace(/edit$/, ”) + ‘export?exportFormat=pdf&format=pdf’ +

‘&size=letter&portrait=true&fitw=true&sheetnames=false&printtitle=false’ +

‘&pagenumbers=false&gridlines=false&fzr=false’;

const blob = UrlFetchApp.fetch(url, {

headers: { ‘Authorization’: ‘Bearer ‘ + ScriptApp.getOAuthToken() }

}).getBlob().setName(sheet.getName() + ‘.pdf’);

MailApp.sendEmail({

to: “email@example.com”,

subject: “Monthly Report”,

body: “Here is the monthly report.”,

attachments: [blob]

});

}

Save and name your project.

Modify the email address and run emailSpreadsheetAsPDF.

Explanation: This script generates a PDF from the current active spreadsheet and emails it as an attachment. It adjusts PDF export settings for formatting and fetches the file with OAuth authorization before emailing it.

Log Changes to a Spreadsheet

Objective: Write a script that logs changes to a specific range in Google Sheets to a separate “Log” sheet.

Steps:

Ensure your Google Sheet has two sheets: “Main” (with data) and “Log”.

Open Extensions > Apps Script.

Replace the existing code with:

function logChanges(e) {

const logSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Log’);

const range = e.range;

const value = range.getValue();

const time = new Date();

logSheet.appendRow([time, range.getA1Notation(), value]);

}

function createTrigger() {

// This function creates a trigger for the logChanges function on edit event

const ss = SpreadsheetApp.getActiveSpreadsheet();

ScriptApp.newTrigger(‘logChanges’)

.forSpreadsheet(ss)

.onEdit()

.create();

}

Save and name your project.

Set up an Installable Trigger for this function on edit or run the function createTrigger().

Explanation: This script logs the time, cell address, and new value of any changed cell within a specified range to a “Log” sheet each time an edit occurs. It uses an event object e to access details about the change.

Validate Data Entry

The onEdit function you have defined is a simple trigger and does not require manual setup through the createTrigger function. Simple triggers like onEdit are automatically triggered by editing actions within the spreadsheet.

Objective: Create a script that checks data entered into a specific column and ensures it meets certain criteria (e.g., must be a number greater than zero).

Steps:

Open a Google Sheet and go to Extensions > Apps Script.

Replace any code with:

function onEdit(e) {

const range = e.range;

const value = range.getValue();

if (range.getColumn() === 2 && (isNaN(value) || value <= 0)) { // Checks column B

SpreadsheetApp.getUi().alert(‘Please enter a valid number greater than zero.’);

range.setValue(”); // Clears the invalid entry

}

Save and name your project.

Test by entering data in column B.

Explanation: This script automatically triggers when data is edited in column B. It checks if the data is a positive number and alerts the user if the criteria are not met.

Sync Google Calendar with a Spreadsheet

Event Title	Start Time	End Time
Team Meeting	2024-05-08 09:00 AM	2024-05-08 10:00 AM
Project Deadline	2024-05-10 12:00 PM	2024-05-10 01:00 PM
Client Call	2024-05-11 03:00 PM	2024-05-11 04:00 PM

Objective: Develop a script that syncs events from Google Calendar to a Google Sheet for easy viewing and management.

Steps:

Open a Google Sheet and navigate to Extensions > Apps Script.

Replace the existing code with:

function syncCalendarEvents() {

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Events’);

const calendar = CalendarApp.getCalendarById(‘your-calendar-id@example.com’);

const events = calendar.getEvents(new Date(), new Date(Date.now() + 7 * 24 * 60 * 60 * 1000)); // Next 7 days

events.forEach(function(event) {

sheet.appendRow([event.getTitle(), event.getStartTime(), event.getEndTime()]);

});

}

Save and name your project.

Modify ‘your-calendar-id@example.com’ and run syncCalendarEvents.

Explanation: This script retrieves events for the next week from a specified Google Calendar and writes the event details into a spreadsheet. This is useful for managing events in a tabular format.

New Book Apps Script Code Google Apps Script Playground Free First 2 Chapters

Chapter 1: Getting Started with Google Apps Script

Hello World

Create a Custom Menu in Google Sheets

Log the Active Cell’s Value

Change Cell Background Color

Insert Current Date into a Cell

Sum of Selected Cells

Insert Multiple Rows

Convert Range to Uppercase

Delete Empty Rows

Chapter 2 : Integrating External Data into Google Sheets

Fetch and Display Weather Data

Duplicate a Sheet

Format Column as Currency

Send Emails from a List

Create Google Calendar Events

Import Data from External API

Generate and Email a Spreadsheet Report

Log Changes to a Spreadsheet

Validate Data Entry

Sync Google Calendar with a Spreadsheet

Related

Chapter 1: Getting Started with Google Apps Script

Hello World

Create a Custom Menu in Google Sheets

Log the Active Cell’s Value

Change Cell Background Color

Insert Current Date into a Cell

Sum of Selected Cells

Insert Multiple Rows

Convert Range to Uppercase

Delete Empty Rows

Chapter 2 : Integrating External Data into Google Sheets

Fetch and Display Weather Data

Duplicate a Sheet

Format Column as Currency

Send Emails from a List

Create Google Calendar Events

Import Data from External API

Generate and Email a Spreadsheet Report

Log Changes to a Spreadsheet

Validate Data Entry

Sync Google Calendar with a Spreadsheet

Share this:

Related