🚀 Boost Your Google Workspace Skills: Exciting Google Apps Script

Exercises! 📊👩‍💻

Exercise 1: Create a Custom Menu in Google Sheets

Objective: To create a custom menu in Google Sheets that triggers a simple script.

Code Sample:

function onOpen() {

  var ui = SpreadsheetApp.getUi();

  ui.createMenu(‘Custom Menu’)

      .addItem(‘Show Alert’, ‘showAlert’)

      .addToUi();

}

function showAlert() {

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

}

Explanation:

This script creates a custom menu named “Custom Menu” in the Google Sheets UI. When “Show Alert” is clicked, it triggers the showAlert function that displays a simple alert box with a message.

Exercise 2: Read Data from a Range in Google Sheets

Objective: Read data from a specified range in a sheet and log it.

Code Sample:

function readRangeData() {

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

  var range = sheet.getRange(“A1:B2”);

  var values = range.getValues();

  for (var i = 0; i < values.length; i++) {

    var row = values[i];

    Logger.log(row[0] + “, ” + row[1]);

  }

}

Explanation:

This script reads data from the range A1:B2 of the active sheet. getValues returns a two-dimensional array of values, which is then iterated over to log each cell’s data.

Exercise 3: Send an Email using GmailApp

Objective: Use Google Apps Script to send an email via Gmail.

Code Sample:

function sendEmail() {

  var recipient = “example@example.com”;

  var subject = “Test Email from Google Apps Script”;

  var body = “This is a test email sent from Google Apps Script.”;

  GmailApp.sendEmail(recipient, subject, body);

}

Explanation:

This script sends an email using the Gmail service. Replace recipient with the desired email address. The GmailApp.sendEmail method is used to send the email.

Exercise 4: Create a Google Doc

Objective: Programmatically create a Google Doc with some content.

Code Sample:

function createDocument() {

  var doc = DocumentApp.create(‘New Document’);

  var body = doc.getBody();

  body.appendParagraph(‘This is a new Google Document created by Google Apps Script.’);

  doc.saveAndClose();

}

Explanation:

This script creates a new Google Document titled “New Document” and adds a paragraph of text to it. The DocumentApp.create method is used to create the document.

Exercise 5: Modify Cell Values in Google Sheets

Objective: Write a script to modify cell values in a Google Sheet.

Code Sample:

function updateCellValues() {

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

  sheet.getRange(“A1”).setValue(“Hello”);

  sheet.getRange(“B1”).setValue(“World”);

}

Explanation:

This script updates the values of cells A1 and B1 of the active sheet to “Hello” and “World”, respectively. The setValue method is used to set the value of a cell.