Master Google Apps Script with These Three Hands-On Exercises

Google Apps Script is a powerful tool that allows you to automate tasks across Google Workspace products like Google Sheets, Docs, and Gmail. In this blog post, we’ll walk you through three coding exercises that will help you get started with Google Apps Script. These exercises range from creating custom functions in Google Sheets to developing a simple web app. Let’s dive in!

Exercise 1: Create a Custom Function to Calculate the Square Root

Objective:
Create a custom function in Google Sheets using Google Apps Script to calculate the square root of a given number.

Step-by-Step Instructions:

1. Open a new Google Sheet.
2. Go to Extensions > Apps Script.
3. Delete any code in the script editor and paste the following code:

function SQRT(number) {
  if (number < 0) {
    return "Error: Negative number";
  }
  return Math.sqrt(number);
}

4. Save the script with a meaningful name, e.g., CustomFunctions.
5. Go back to your Google Sheet.
6. In any cell, use the custom function =SQRT(number), where number is the cell reference or number for which you want to calculate the square root.

Explanation:
This custom function SQRT takes a number as input. It first checks if the number is negative, in which case it returns an error message. Otherwise, it calculates the square root using JavaScript’s built-in Math.sqrt function and returns the result.

Example Usage in Google Sheets:

A	B
Number	Square Root
25	=SQRT(A2)
49	=SQRT(A3)
-4	=SQRT(A4) (Error)

Exercise 2: Send an Email with Google Apps Script

Objective:
Write a script to send an email from your Google account using Google Apps Script.

Step-by-Step Instructions:

1. Open a new Google Sheet.
2. Go to Extensions > Apps Script.
3. Delete any code in the script editor and paste the following code:

function sendEmail() {
  var recipient = "example@example.com"; // Replace with the recipient's email address
  var subject = "Test Email from Google Apps Script";
  var body = "Hello, this is a test email sent using Google Apps Script.";

  MailApp.sendEmail(recipient, subject, body);
}

4. Save the script with a meaningful name, e.g., SendEmailScript.
5. Run the function sendEmail by clicking the play button in the script editor.

Explanation:
This script uses the MailApp service provided by Google Apps Script to send an email. The sendEmail function specifies the recipient’s email address, the subject, and the body of the email. When the function is run, it sends an email to the specified recipient.

Exercise 3: Create a Simple Web App to Display Data

Objective:
Create a simple web application that displays data from a Google Sheet.

Step-by-Step Instructions:

1. Open a new Google Sheet and enter some sample data:

A	B
Name	Age
Alice	30
Bob	25
Charlie	35

2. Go to Extensions > Apps Script.
3. Delete any code in the script editor and paste the following code:

function doGet() {
  var html = HtmlService.createHtmlOutput();
  html.append("<h1>Data from Google Sheet</h1>");
  html.append("<ul>");
  
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data = sheet.getDataRange().getValues();
  
  for (var i = 0; i < data.length; i++) {
    html.append("<li>" + data[i].join(", ") + "</li>");
  }
  
  html.append("</ul>");
  return html;
}

4. Save the script with a meaningful name, e.g., WebAppScript.
5. Deploy the web app by going to Deploy > New deployment.
6. Select Web app and follow the instructions to deploy.
7. Once deployed, open the URL provided to view your web app.

Explanation:
This script defines a doGet function, which is the entry point for GET requests in a web app. It retrieves data from the active sheet, formats it into an HTML list, and displays it. The HtmlService.createHtmlOutput method is used to create the HTML output. The getDataRange().getValues() method retrieves all the data from the sheet, which is then iterated over to create list items for each row of data.

By following these exercises, you will get a hands-on understanding of Google Apps Script, how to create custom functions, send emails, and build simple web applications. Happy coding!