Dive Deeper into Google Workspace: Try These Advanced Google Apps Script Exercises!

by

Exercise 16: Automated Email Responses Using Gmail

Objective: Write a script to automatically send a response to every email received with a specific subject.

Code Sample:

function autoRespondToEmails() {

  var query = ‘subject:”specific subject” is:unread’;

  var threads = GmailApp.search(query);

  var response = “Thank you for your email. We will get back to you shortly.”;

  threads.forEach(function(thread) {

    thread.getMessages().forEach(function(message) {

      if (message.isUnread()) {

        GmailApp.sendEmail(message.getFrom(), “Re: ” + message.getSubject(), response);

        message.markRead();

      }

    });

  });

}

Explanation:

This script searches for all unread emails with a specific subject line. For each email found, it sends a predefined response to the sender and marks the email as read. This is useful for handling common queries or out-of-office responses.

Exercise 17: Updating Google Calendar Event Details

Objective: Modify the details of upcoming events in Google Calendar.

Code Sample:

function updateCalendarEvents() {

  var calendar = CalendarApp.getDefaultCalendar();

  var now = new Date();

  var events = calendar.getEventsForDay(now);

  events.forEach(function(event) {

    var newTitle = “Updated: ” + event.getTitle();

    event.setTitle(newTitle);

  });

}

Explanation:

This script retrieves all events for the current day from the user’s default calendar and updates their titles with a prefix “Updated:”. This can be used to dynamically change event details.

Exercise 18: Creating a Custom Google Sheets Function

Objective: Develop a custom function in Google Sheets to calculate the sum of two numbers.

Code Sample:

function SUM_TWO_NUMBERS(number1, number2) {

  return number1 + number2;

}

Explanation:

This script creates a custom function called SUM_TWO_NUMBERS that can be used directly in a Google Sheets cell, similar to built-in functions. It takes two arguments and returns their sum. In Google Sheets, you would use it as =SUM_TWO_NUMBERS(A1, B1).

Exercise 19: Fetching Weather Data and Displaying in Google Sheets

Objective: Retrieve weather data from an external API and display it in a Google Sheet.

Code Sample:

function fetchWeatherData() {

  var response = UrlFetchApp.fetch(“https://api.openweathermap.org/data/2.5/weather?q=London&appid=yourAPIKey”);

  var json = JSON.parse(response.getContentText());

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

  sheet.getRange(“A1”).setValue(“Weather in London: ” + json.weather[0].main);

}

Explanation:

This script fetches current weather data for London from the OpenWeather API and writes the main weather description to cell A1 of the active sheet. Replace yourAPIKey with a valid API key from OpenWeather.

Exercise 20: Extracting Text from Images Using Google Drive

Objective: Use Google Drive’s OCR capabilities to extract text from an image.

Code Sample:

function extractTextFromImage() {

  var imageId = ‘yourImageFileId’; // Replace with your image file ID in Google Drive

  var blob = DriveApp.getFileById(imageId).getBlob();

  var text = DocumentApp.create(‘Extracted Text’)

                .getBody()

                .setText(DriveApp.createFile(blob).setContentTypeFromExtension().getAs(‘text/plain’).getDataAsString());

}

Explanation:

This script takes an image file from Google Drive, identified by its file ID, and uses Google Drive’s built-in OCR (Optical Character Recognition) to extract text from the image. The extracted text is then written to a newly created Google Document.

Google-Apps-Script-Exercises-20Download