Advanced Google Apps Script Challenges: Elevate Your Scripting Skills

by

Exercise 6: Create an Automated Reminder in Google Calendar

Objective: Write a script to create a calendar event and set an email reminder.

Code Sample:

function createCalendarEvent() {

  var calendar = CalendarApp.getDefaultCalendar();

  var startTime = new Date(‘March 15, 2024 10:00:00’);

  var endTime = new Date(‘March 15, 2024 11:00:00’);

  var event = calendar.createEvent(‘Meeting with Team’, startTime, endTime);

  event.addEmailReminder(30); // 30 minutes before

}

Explanation:

This script creates an event in the user’s default Google Calendar. It sets up a meeting titled ‘Meeting with Team’ at a specified date and time. An email reminder is added 30 minutes before the event starts.

Exercise 7: Access and Modify Google Drive Files

Objective: List all files in the Google Drive root folder and log their names.

Code Sample:

function listDriveFiles() {

  var files = DriveApp.getRootFolder().getFiles();

  while (files.hasNext()) {

    var file = files.next();

    Logger.log(file.getName());

  }

}

Explanation:

This script accesses the root folder of Google Drive and iterates through all files in it. It logs the name of each file found, which can be viewed in the Google Apps Script’s Logger.

Exercise 8: Fetch Data from an External API

Objective: Write a script to fetch data from an external API and log the response.

Code Sample:

function fetchDataFromAPI() {

  var response = UrlFetchApp.fetch(“https://api.example.com/data”);

  Logger.log(response.getContentText());

}

Explanation:

This script uses the UrlFetchApp service to make a GET request to an external API. It logs the response content, which can be JSON, XML, or plain text, depending on the API.

Exercise 9: Manipulate Spreadsheet Formatting

Objective: Change the background color of the first row in a Google Sheet.

Code Sample:

function formatFirstRow() {

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

  var range = sheet.getRange(“1:1”); // First row

  range.setBackground(“yellow”);

}

Explanation:

This script gets the active sheet and selects the first row. It then changes the background color of this row to yellow, demonstrating basic cell formatting capabilities.

Exercise 10: Sync Spreadsheet Data to Google Calendar

Objective: Create Google Calendar events based on data from a Google Sheets spreadsheet.

Code Sample:

function syncToCalendar() {

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

  var dataRange = sheet.getDataRange();

  var data = dataRange.getValues();

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

    var row = data[i];

    var title = row[0]; // Event title in the first column

    var startTime = new Date(row[1]); // Start time in the second column

    var endTime = new Date(row[2]); // End time in the third column

    CalendarApp.getDefaultCalendar().createEvent(title, startTime, endTime);

  }

}

Explanation:

This script reads data from the active sheet, assuming that each row contains an event title, start time, and end time. It loops through each row and creates corresponding events in the user’s default Google Calendar. This is a basic example of how to sync spreadsheet data with Google Calendar.

Google-Apps-Script-Exercises-10Download