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;
count++;
}
});
});
// 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.
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.
