Master Google Apps Script with These Three Additional Hands-On Exercises

Expanding on our previous exercises, here are three more coding challenges to further enhance your skills with Google Apps Script. These exercises will guide you through creating a custom function, automating calendar events, and generating PDF files from Google Sheets data.

Exercise 7: Create a Custom Function to Calculate the BMI

Objective:
Create a custom function in Google Sheets using Google Apps Script to calculate the Body Mass Index (BMI) based on weight and height.

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 BMI(weight, height) {
  if (weight <= 0 || height <= 0) {
    return "Error: Invalid input";
  }
  var bmi = weight / (height * height);
  return bmi.toFixed(2);
}

4. Save the script with a meaningful name, e.g., HealthFunctions.
5. Go back to your Google Sheet.
6. In any cell, use the custom function =BMI(weight, height), where weight is in kilograms and height is in meters.

Explanation:
This custom function BMI takes weight and height as inputs, checks if they are valid, and calculates the BMI using the formula weight / (height * height). The result is rounded to two decimal places.

Example Usage in Google Sheets:

A	B	C
Weight	Height	BMI
70	1.75	=BMI(A2, B2)
60	1.60	=BMI(A3, B3)
85	1.80	=BMI(A4, B4)

Exercise 8: Automate Calendar Event Creation

Objective:
Write a script to automate the creation of calendar events in Google Calendar from data in Google Sheets.

Step-by-Step Instructions:

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

A	B	C	D
Event	Date	Start Time	End Time
Meeting	2024-06-15	10:00	11:00
Workshop	2024-06-16	14:00	16:00

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

function createCalendarEvents() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data = sheet.getRange(2, 1, sheet.getLastRow() - 1, 4).getValues();
  var calendar = CalendarApp.getDefaultCalendar();

  data.forEach(function(row) {
    var eventTitle = row[0];
    var eventDate = new Date(row[1]);
    var startTime = new Date(eventDate);
    var endTime = new Date(eventDate);
    
    startTime.setHours(row[2].split(":")[0], row[2].split(":")[1]);
    endTime.setHours(row[3].split(":")[0], row[3].split(":")[1]);
    
    calendar.createEvent(eventTitle, startTime, endTime);
  });
}

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

Explanation:
This script reads event details from a Google Sheet and creates corresponding events in the default Google Calendar. It uses the CalendarApp service to create events based on the data from the sheet, including the event title, date, start time, and end time.

Generate PDF from Google Sheets Data

Objective:
Write a script to generate a PDF file from data in a Google Sheet and email it to a specified recipient.

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 exportSheetToPDF() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getActiveSheet();
  var pdfBlob = createPDF(spreadsheet);
  
  var email = "example@example.com"; // Replace with recipient's email
  var subject = "PDF Export of Google Sheet";
  var body = "Please find the attached PDF file.";
  
  MailApp.sendEmail(email, subject, body, {
    attachments: [pdfBlob]
  });
}

function createPDF(spreadsheet) {
  var spreadsheetId = spreadsheet.getId();
  var sheetId = spreadsheet.getActiveSheet().getSheetId();
  var url = "https://docs.google.com/spreadsheets/d/" + spreadsheetId + "/export?";

  var params = {
    exportFormat: 'pdf',
    format: 'pdf',
    size: 'A4',
    portrait: true,
    fitw: true,
    sheetnames: false,
    printtitle: false,
    pagenumbers: false,
    gridlines: false,
    fzr: false,
    gid: sheetId
  };

  var options = [];
  for (var key in params) {
    options.push(key + "=" + params[key]);
  }
  var fullUrl = url + options.join("&");
  
  var token = ScriptApp.getOAuthToken();
  var response = UrlFetchApp.fetch(fullUrl, {
    headers: {
      'Authorization': 'Bearer ' + token
    }
  });
  return response.getBlob().setName(spreadsheet.getName() + ".pdf");
}

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

Explanation:
This script converts the active sheet into a PDF file by constructing the URL for exporting the sheet as a PDF. It uses the UrlFetchApp.fetch method to fetch the PDF and then emails it as an attachment using the MailApp.sendEmail method.

Example Usage in Google Sheets:

A	B
Name	Age
Alice	30
Bob	25
Charlie	35

These additional exercises provide further practice with Google Apps Script, covering health calculations, calendar event automation, and PDF generation. Happy coding and feel free to share your progress or ask any questions in the comments below!