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

Continuing from our previous blog post, here are three more coding exercises for Google Apps Script. These exercises will help you deepen your understanding of Apps Script by creating custom functions, manipulating Google Sheets data, and integrating with external APIs.

Exercise 4: Create a Custom Function to Convert Celsius to Fahrenheit

Objective:
Create a custom function in Google Sheets using Google Apps Script to convert Celsius to Fahrenheit.

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 CELSIUS_TO_FAHRENHEIT(celsius) {
  return (celsius * 9/5) + 32;
}

4. Save the script with a meaningful name, e.g., TemperatureConversion.
5. Go back to your Google Sheet.
6. In any cell, use the custom function =CELSIUS_TO_FAHRENHEIT(celsius), where celsius is the cell reference or number for which you want to convert to Fahrenheit.

Explanation:
This custom function CELSIUS_TO_FAHRENHEIT takes a Celsius temperature as input and converts it to Fahrenheit using the formula (celsius * 9/5) + 32.

Example Usage in Google Sheets:

A	B
Celsius	Fahrenheit
0	=CELSIUS_TO_FAHRENHEIT(A2)
25	=CELSIUS_TO_FAHRENHEIT(A3)
-10	=CELSIUS_TO_FAHRENHEIT(A4)

Exercise 5: Sort Data in Google Sheets

Objective:
Write a script to sort data in a Google Sheet by a specified column.

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 sortData() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getRange("A1:B4");
  range.sort({column: 2, ascending: true});
}

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

Explanation:
This script sorts the data in the range A1:B4 by the second column (Age) in ascending order. The sort method is used with an object specifying the column to sort by and the sort order.

Exercise 6: Fetch Data from an External API

Objective:
Write a script to fetch data from an external API and display it in Google Sheets.

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 fetchData() {
  var url = 'https://api.exchangerate-api.com/v4/latest/USD'; // Example API URL
  var response = UrlFetchApp.fetch(url);
  var data = JSON.parse(response.getContentText());
  var rates = data.rates;

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.clear();
  
  var headers = [["Currency", "Rate"]];
  var rows = Object.keys(rates).map(function(currency) {
    return [currency, rates[currency]];
  });

  sheet.getRange(1, 1, headers.length, headers[0].length).setValues(headers);
  sheet.getRange(2, 1, rows.length, rows[0].length).setValues(rows);
}

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

Explanation:
This script fetches data from an external API (Exchange Rate API in this case) using the UrlFetchApp.fetch method. It parses the JSON response to extract exchange rates, clears the current sheet, and then populates it with the fetched data. The script sets headers and fills the sheet with currency rates.