Creating custom functions and macros using Google Apps Script for beginners

Google Apps Script is a powerful tool for automating and extending the capabilities of Google Sheets. By writing custom functions and macros, users can tailor Google Sheets to their specific needs, making it an invaluable resource for personalized data processing and analysis.

Example: Custom function to calculate averages

Suppose you are managing a Google Sheet named freshers_in_College, storing the exam scores of freshmen in college. Let’s create a custom function that calculates the average score for a given range of cells.

Step 1: Open Script Editor

Open the freshers_in_College Google Sheet.

Go to “Extensions” > “Apps Script” to open the Script Editor.

Step 2: Write the Custom Function

In the Script Editor, write a custom function named CALCULATE_AVERAGE.

* Calculates the average of a range of numbers in Google Sheets.
* @param {range} inputRange - The range of cells to calculate the average of.
* @return The average of the input range.
* @customfunction
function CALCULATE_AVERAGE(inputRange) {
  var sum = 0;
  var count = 0;
  // Loop through each row in the range
  inputRange.forEach(function(row) {
    // Loop through each cell in the row
    row.forEach(function(cell) {
      if (!isNaN(cell)) { // Check if the cell contains a number
        sum += cell;
  // Return the average
  return count === 0 ? 0 : sum / count;

Step 3: Save the Script

After writing the custom function, save the script by clicking on the floppy disk icon or pressing Ctrl + S (Windows) or Command + S (Mac).

Step 4: Use the custom function in the sheet

Go back to the freshers_in_College Google Sheet and type =CALCULATE_AVERAGE(A2:A10) into a cell, replacing A2:A10 with the range of cells containing the scores. The cell will now display the average of the scores in the specified range.

Creating a macro to highlight cells

Let’s say, in the freshers_in_College sheet, you wish to highlight cells containing scores above 75. You can automate this task by creating a macro.

Step 1: Record Macro

Open the freshers_in_College Google Sheet.

Go to “Extensions” > “Macros” > “Record Macro”.

Format a cell (e.g., change the background color) to your liking, this will be applied to all cells above 75.

Click “Save” in the macro recording window and give your macro a name, e.g., HighlightHighScores.

Step 2: Edit the Macro

After recording, you can edit the macro in the Script Editor.

Go to “Extensions” > “Apps Script”.
Locate the HighlightHighScores function and modify it to loop through the cells and apply formatting to cells with values above 75.
function HighlightHighScores() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getRange('A2:A10'); // Adjust the range as needed
  var values = range.getValues();
  // Loop through the values and format cells with values above 75
  for (var i = 0; i < values.length; i++) {
    if (values[i][0] > 75) {
      range.getCell(i + 1, 1).setBackground('yellow'); // Adjust the color as needed

Step 3: Run the macro

Return to the freshers_in_College Google Sheet and run your macro by going to “Extensions” > “Macros” > “HighlightHighScores”. The cells with scores above 75 will be highlighted.

Author: user

Leave a Reply