Mastering Google Apps Script: 10 Essential Coding Exercises

Are you looking to enhance your Google Apps Script skills? Whether you’re a beginner or looking to refine your abilities, these 10 essential coding exercises will help you get a solid grasp of Google Apps Script. Each exercise comes with complete code and explanations, providing a comprehensive learning experience.

Exercise 1: Creating a Custom Function in Google Sheets

Objective: Create a custom function that calculates the area of a rectangle.

Code:

function calculateArea(length, width) {
  return length * width;
}

Explanation: This function takes two parameters, length and width, and returns the product, which is the area of a rectangle.

Exercise 2: Sending an Email

Objective: Write a script that sends an email with a custom message.

Code:

function sendCustomEmail() {
  var recipient = "example@example.com";
  var subject = "Hello from Google Apps Script";
  var body = "This is a test email sent using Google Apps Script.";
  MailApp.sendEmail(recipient, subject, body);
}

Explanation: The MailApp.sendEmail function sends an email to the specified recipient with the provided subject and body.

Exercise 3: Adding a Menu to Google Sheets

Objective: Add a custom menu to Google Sheets.

Code:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom Menu')
    .addItem('Show Alert', 'showAlert')
    .addToUi();
}

function showAlert() {
  SpreadsheetApp.getUi().alert('Hello, world!');
}

Explanation: The onOpen function runs automatically when the spreadsheet is opened, adding a custom menu that triggers the showAlert function.

Exercise 4: Creating a Google Doc

Objective: Create a new Google Doc with a custom title and content.

Code:

function createGoogleDoc() {
  var doc = DocumentApp.create('New Document');
  var body = doc.getBody();
  body.appendParagraph('Hello, this is a new document created with Google Apps Script.');
}

Explanation: The DocumentApp.create function creates a new Google Doc, and the appendParagraph method adds a paragraph to the document body.

Exercise 5: Creating a Trigger

Objective: Set up a time-based trigger to run a function daily.

Code:

function createDailyTrigger() {
  ScriptApp.newTrigger('myDailyFunction')
    .timeBased()
    .everyDays(1)
    .atHour(8)
    .create();
}

function myDailyFunction() {
  Logger.log('This function runs daily at 8 AM.');
}

Explanation: The createDailyTrigger function creates a time-based trigger to run myDailyFunction every day at 8 AM.

Exercise 6: Reading Data from Google Sheets

Objective: Read and log data from a specific range in a Google Sheet.

Code:

function readSheetData() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getRange('A1:B2');
  var values = range.getValues();
  Logger.log(values);
}

Explanation: This function reads data from the range A1of the active sheet and logs the values.

Exercise 7: Writing Data to Google Sheets

Objective: Write data to a specific range in a Google Sheet.

Code:

function writeSheetData() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getRange('A1');
  range.setValue('Hello, World!');
}

Explanation: This function writes “Hello, World!” to cell A1 of the active sheet.

Exercise 8: Creating a Calendar Event

Objective: Create a calendar event for a specific date and time.

Code:

function createCalendarEvent() {
  var calendar = CalendarApp.getDefaultCalendar();
  var event = calendar.createEvent('Meeting', new Date('2024-08-01T10:00:00Z'), new Date('2024-08-01T11:00:00Z'));
  Logger.log('Event ID: ' + event.getId());
}

Explanation: This function creates a calendar event titled “Meeting” on August 1, 2024, from 10 AM to 11 AM UTC.

Exercise 9: Creating a Form

Objective: Create a Google Form with a text question.

Code:

function createGoogleForm() {
  var form = FormApp.create('New Form');
  form.addTextItem().setTitle('What is your name?');
}

Explanation: This function creates a new Google Form with a single text question asking for the user’s name.

Exercise 10: Fetching Data from an API

Objective: Fetch data from an external API and log the response.

Code:

function fetchDataFromAPI() {
  var url = 'https://api.exchangerate-api.com/v4/latest/USD';
  var response = UrlFetchApp.fetch(url);
  var data = JSON.parse(response.getContentText());
  Logger.log(data);
}

Explanation: This function fetches exchange rate data from the specified API, parses the JSON response, and logs the data.