Google Apps Script is a JavaScript platform built specifically to create different types of apps and automation tasks for Google Workspace products. You can use it to interact with almost all of the Google products. Today, we're going to learn about some of the best Apps Script functions aimed at Google Sheets users. With the help of these functions, you can automate some of the most common and repetitive tasks you do when working with a Google Sheets file. Google Apps Script is a collection of easy-to-use APIs that ease programmatic interaction with Google products. Let's get started!
To fully harness the power of this platform, familiarity with JavaScript is essential. Beginner to intermediate knowledge about the language is good enough to get started with the basic scripts.
Remember, the custom functions presented below are just the tip of the iceberg. With Apps Script functions, the possibilities are endless. All you need is a bit of effort and a creative mind.
Understanding Google Apps Script
If you're new to Google Apps Script, you may have to spend some time getting familiar with the basics and the APIs. Do not try to learn everything.
For example, in our case, the focus should be on learning about the Google Sheets API.
Under the hood, a Google Sheets macro is a chunk of an Apps Script function.
To create custom Apps Script functions for Google Sheets, make sure you know these basics.
- Basic to intermediate knowledge of JavaScript
- Familiarity with Google Sheets.
- Familiarity with the A1 Notation.
- Access to a modern web browser and an internet connection.
Before we dive into actual custom functions, let's first create a simple Apps Script function that reads a cell value from the spreadsheet and then displays its value in a popup.
function readAndDisplayCellValue() {
// Get the value of cell A1 from currently-opened spreadsheet.
var value = SpreadsheetApp.getActiveSpreadsheet().getRange("A1").getValue();
// Display the value of cell A1 in a popup.
SpreadsheetApp.getUi().alert("The value of cell A1 is " + value);
}As you can see, the example code snippet above is simple and self-explanatory. But, how can you run these Apps Script functions? Simply click the Extensions → Apps Script option.
Upon clicking this option, a script editor will open in a new tab. Write or paste the Apps Script code in this editor. To execute the code, click the Run button as shown in the image below.
Here you can see that the Apps Script function has been executed picking the value from the spreadsheet cell and then displaying it in a popup.
But, in the example shown above, we're executing this Apps Script function, manually. The real advantage is when you automate the script execution. And for that, a trigger needs to be created.
To create a trigger, select the Triggers option from the menu and click the Add Trigger button.
The trigger can be customized or configured as per your requirements. You can select the function to run, the events on the spreadsheet, or the date and time when the trigger needs to be fired.
Now that you've got the basics of Apps Script function creation and trigger deployment, let's check out some of the best and most productive functions built specifically for Google Sheets files.
1. Text or Data Manipulation
While working with spreadsheets, we primarily work with numerical or alphabetical data. Sometimes, we need to format or manipulate this data on a repetitive basis. Let's see some of the useful functions for the same.
Remove Duplicates
While working on a large data set in a spreadsheet, we often need to weed out duplicate entries. That's an ideal situation for building a function.
The simplest example is where within a given range of numbers, you remove all the duplicates keeping a distinct number in each cell in that range. The script of such a function is as follows.
function deleteDuplicates() {
// Get the active spreadsheet
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
// Specify the sheet and range where you want to remove duplicates
var sheet = spreadsheet.getActiveSheet();
var range = sheet.getRange('A:A'); // Change this to the desired range, e.g., 'A1:B10'
// Remove duplicates from the specified range
range.removeDuplicates();
}
Remember, you need to provide the range in A1 Notation to the getRange() function. This way, the function can work dynamically on any range of data in a spreadsheet.
Split Text Into Columns
Another common task we often need to perform is to split a multi-word cell entry into multiple columns. Here's an Apps Script function for the same.
function splitTextIntoColumns() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var range = sheet.getActiveRange();
var values = range.getValues();
for (var i = 0; i < values.length; i++) {
var text = values[i][0]; // Assuming the text is in the first column of the active range
if (text) {
// Split the text based on a specific criterion (e.g., comma in this example)
var splitValues = text.split(',');
// Update the values in the same row, starting from the second column
for (var j = 0; j < splitValues.length; j++) {
range.offset(i, j + 1).setValue(splitValues[j].trim()); // Offset by 1 to start from the second column
}
}
}
}The splitting can be done based on different criteria. Here, in this example, we're splitting the text using the , character. Feel free to modify this function as per your needs.
Format Date
Date formatting is another common task spreadsheet users often need to do. In this Apps Script function, whenever a date is entered in a cell, it is converted and formatted in a specific way.
function convertDateFormat() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var cell = sheet.getActiveCell();
var originalDate = cell.getValue();
if (originalDate instanceof Date) {
var formattedDate = Utilities.formatDate(originalDate, Session.getScriptTimeZone(), 'yyyy-MM-dd');
cell.setValue(formattedDate);
} else {
SpreadsheetApp.getUi().alert('Please enter a valid date in the cell.');
}
}Here, formatDate() is the function where you can customize the date format. You can create a trigger for this function that fires every time the sheet is edited.
2. Cell Formatting and Data Analysis
Formatting of cells' appearance and analysis of data in the sheet are two other important tasks every savvy spreadsheet user does—daily. Both of these tasks can be automated through an Apps Script function.
Format Cells' Font and Color
The following Apps Script function customizes the font size, font family, and font color for the given range. The range is provided in A1 Notation. You can customize the call background color too, if required. See the API reference for all the available cell customization functions.
function formatCells() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
// Define the range of cells to format (e.g., A1:B10)
var range = sheet.getRange("A1:B10");
// Set custom font and color properties
var style = {
fontColor: "#0000FF", // Blue color
fontFamily: "Arial",
fontSize: 12
};
// Apply the custom style to the range of cells
range.setFontColor(style.fontColor);
range.setFontFamily(style.fontFamily);
range.setFontSize(style.fontSize);
}Cell formatting functions can be addictive. Make sure you do not overdo it, and write font and cell customization Apps Script functions only when they're mandatorily required.
Create Pivot Table
Pivot tables are the cornerstone of spreadsheet data-crunching activities. Creating custom Apps Script functions to create different types of pivot tables can be a huge time saver.
function createPivotTable() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var range = sheet.getDataRange();
// Define the pivot table data source
var pivotTableSource = sheet.getRange("A1:C" + range.getLastRow());
// Create a new sheet to hold the pivot table
var pivotSheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet('Pivot Table');
// Create the pivot table
var pivotTable = pivotSheet.createPivotTable(
pivotTableSource,
pivotSheet.getRange('A1'),
{
summarizeFunction: SpreadsheetApp.PivotTableSummarizeFunction.AVERAGE
}
);
// Set the headers for the pivot table
pivotTable.addPivotValue(0, SpreadsheetApp.PivotTableSummarizeFunction.AVERAGE)
.setDisplayName('Average Score');
// Set the row grouping
pivotTable.addRowGroup(0);
// Set the column grouping
pivotTable.addColumnGroup(1);
}In our example, we're finding the average score from the entries of three columns in a sheet. In your case, the requirements can be completely different and you must make the changes accordingly.
3. Data Import and Export
While working with spreadsheets, we often import data from different types of external sources. We can automate this import process too with the help of Apps Script functions.
Similarly, we can also export spreadsheet data in different file formats. We can create a function for the export process too.
Import Data
In this first example, we're importing data from a CSV file residing on the internet. For that, we need the absolute URL path of the file. Once we have that, the function does the rest.
function importCSVFromURL(url) {
var response = UrlFetchApp.fetch(url);
var csvData = response.getContentText();
// Parse CSV data
var parsedData = Utilities.CSVToArray(csvData, ",");
// Access the active sheet
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
// Clear existing data in the sheet
sheet.clear();
// Populate the sheet with parsed data
for (var i = 0; i < parsedData.length; i++) {
sheet.appendRow(parsedData[i]);
}
}
// You can use this function as follows
function importCSVFile() {
var csvUrl = 'https://www.example-domain.com/path/to/csvfile.csv';
importCSVFromURL(csvUrl);
}It's not just the CSV file, one can import data from different types of files. You can check the reference API for all the supported file formats and their functions.
Export a Sheet as a PDF File
The most common file format while exporting a Google Sheets file is the PDF version of the same. You can do it easily through the Apps Script function. When paired with a trigger, you can ensure—a custom PDF version—is created automatically, when a specific type of event occurs on the spreadsheet.
function exportSheetAsPDF() {
// Get the active spreadsheet
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
// Get the active sheet
var sheet = spreadsheet.getActiveSheet();
// Get the URL of the spreadsheet
var spreadsheetId = spreadsheet.getId();
// Generate the PDF
var pdf = DriveApp.getFileById(spreadsheetId).getAs('application/pdf');
// Save the PDF to Google Drive
var folder = DriveApp.createFolder('PDFs'); // Change folder name as needed
var pdfFile = folder.createFile(pdf);
Logger.log('PDF file created: ' + pdfFile.getUrl());
}The Apps Script function created above saves the PDF file in a specific folder on the Google Drive platform. You can make the necessary changes in the script to meet your requirements.
4. Data Validation and Error Checking
Needless to say, no spreadsheet work session is free from data validation and error checks. It's the core of a typical spreadsheet editing routine. We can automate this process too.
Validate Numbers
In the example given below, within an active range in the spreadsheet, only numeric entries greater than zero are allowed. This is just one of the infinite validation conditions.
function validateNumber() {
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getActiveRange().setDataValidation(SpreadsheetApp.newDataValidation()
.setAllowInvalid(false)
.requireValueInList(['Option 1', 'Option 2'], true)
.build());
spreadsheet.getActiveRange().setDataValidation(SpreadsheetApp.newDataValidation()
.setAllowInvalid(true)
.requireNumberGreaterThan(0)
.build());
};You can validate cell entries for text, alphanumeric, and numeric values. It entirely depends on what exactly you want to validate.
5. Email Notifications
Whether you're working with Google Sheets or Google Docs, emailing the files you're working on is one of the common tasks. You can automate this too with the help of an Apps Script function.
Email Currently Opened Sheet
The following Apps Script function emails a specific Google Sheets file to a specific email address. The function takes two arguments. The first one is the recipient's email address and the second one is the spreadsheet ID.
function emailGoogleSheetsFile(emailAddress, spreadsheetId) {
// Get the spreadsheet file.
var spreadsheet = SpreadsheetApp.openById(spreadsheetId);
// Get the spreadsheet file as a PDF.
var pdf = spreadsheet.getAs("application/pdf");
// Create an email message.
var email = MailApp.createMessage();
// Set the email recipient.
email.to(emailAddress);
// Set the email subject.
email.subject("Google Sheets file");
// Set the email body.
email.body("Please find the attached Google Sheets file.");
// Attach the PDF file to the email.
email.addAttachment(pdf);
// Send the email.
email.send();
}This function can be used in conjunction with other helper functions to complete a specific task which includes emailing a Google Sheets file.
6. Collaboration and Sharing
Sometimes, multiple users work on the same Google Sheets file. Whether they're viewing it or editing it, this multi-user collaboration is essential for the teams working on the same dataset.
Share Google Sheets file Through a Link
Instead of sending a Google Sheets file's PDF attachment in an email, one may need to share the direct link to the file. Here's an Apps Script function to do the same.
function createShareableLink() {
// Get the currently opened spreadsheet.
const ss = SpreadsheetApp.getActiveSpreadsheet();
// Create a shareable link for the spreadsheet.
const shareableLink = ss.getShareableUrl(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);
// Return the shareable link.
return shareableLink;
};You can use this function within the email function (mentioned in the previous section) to send the link to the spreadsheet instead of sending the PDF attachment.
Conclusion
Google Apps Script is a flexible and extremely powerful tool to work with different types of Google services and Google Sheets is one of them.
Through custom Apps Script functions—combined with triggers—you can automate a lot of important and repetitive tasks to enhance your productivity by many folds. To make the most out of it, you need to learn its API which can be mastered in a couple of months.