This article will teach you how to use Google Apps Script in Google Sheets. We will share script examples and how they affect spreadsheets. We will also explain some key concepts like Custom Functions and Data Types.
Let’s get right into it!
Key Takeaways:
- In Google Sheets, Apps Script can be used to automate data entry, generate reports, and clean and format data, among other things.
- To use Google Apps Script in Google Sheets, go to Extension > Apps Script and type or paste your code.
- Key concepts in Apps Script include Custom functions, Triggers, Properties and Methods, Apps Script Services, Error Handling, and Libraries.
How to use Google Apps Script in Google Sheets?
What is Google Apps Script?
Google Apps Script is a cloud-based platform that lets you create your own programs to work with your Google apps (like Gmail, Docs, and Sheets). These programs perform tasks automatically. They can send emails or organize data. They can also add new features to your apps, like custom menus or buttons.
Google Apps Script uses a simple programming language called JavaScript. You don’t need to be an expert coder to write it, though.
In simple terms, this is how Google Apps Scripts works:
- You write simple instructions (code) in the Apps Script editor.
- Google’s servers read your instructions and perform the tasks you want.
- The results appear in your Google apps. These results could be new menus, automated tasks, or custom apps.
Now, how to use Apps Script in Google Sheets?
How to use Google Apps Script in Google Sheets?
- Open your Google Sheet.
- Click on “Extensions“ in the menu bar and select “Apps Script”. This will open a new tab with the Apps Script editor.
3. You will see Code.gs. This is the main file where you’ll write your JavaScript code. It’s located in the “Editor” menu.
4. Type in or paste your code. You might need to learn Javascript to know how to write code, or you can generate code with Gemini or ChatGPT. But here is the script we’ll use for this tutorial:
function sendEmails() {
const sheet = SpreadsheetApp.getActiveSheet();
const data = sheet.getDataRange().getValues(); // Get all data
const headers = data.shift(); // Remove and store headers
for (let i = 1; i < data.length; i++) {
const row = data[i];
const emailAddress = row[headers.indexOf(“Email”)]; // Get email from column
const subject = “Automated Email”; // Customize subject
const message = `Hello ${row[headers.indexOf(“Name”)]}, this is an automated email from Google Sheets.`; // Customize message
if (emailAddress) { // Check if email exists
MailApp.sendEmail({
to: emailAddress,
subject: subject,
body: message
});
}
}
}
What’s the scenario behind this script? Let’s assume we have a contact list in a Google Sheet and want to use it for a basic email campaign. The script will fetch all the necessary data, such as email and name, to compose and send emails.
5. Click the “Save” icon to save the script.
6. In the top left corner, rename your project and click “Run.” The script will send emails to all addresses in your sheet.
7. Apps Script scripts require authorization to access your Google Sheets data. You’ll be prompted to grant these permissions when you run a script.
8. You can also set up a trigger to run the script automatically on a schedule (e.g., daily or weekly). To do that, hover over your mouse on the left icons and click “Triggers.”
9. Click the “+ Add Trigger” button.
10. Go to event source and choose “Time-driven.”
11. Select a time-based type.
- Specific date and time: For a one-time trigger.
- Minutes timer: To run at a specified interval in minutes (e.g., every 15 minutes).
- Hour timer: To run at a specified interval in hours (e.g., every 3 hours).
- Day timer: To run daily at a specific time.
- Week timer: To run on specific days of the week at a specific time.
- Month timer: To run on specific days of the month at a specific time.
12. Click “Save.” The trigger is now active.
Here is the email I received after running the script.
There you go! You now know how to use Google Apps Script in Google Sheets. But let’s discuss more use cases and see how Apps Script enhances spreadsheets.
Practical examples of Apps Scripts in action
1. Automate data entry
You can use Google Apps Scripts in Google Sheets to automate data entry.
Manual data entry is prone to typos. With automation, you reduce the chances of human mistakes. Apps Script can pull data from other sources (like websites, forms, or emails) and automatically plug it into your spreadsheet.
Let’s say you get emailed receipts for business expenses. Apps Script can scan those emails, extract the relevant numbers (date, amount, category), and neatly organize them in your expense tracker spreadsheet.
Let’s write and explain a script:
function extractExpensesFromEmails() {
// 1. Get Unread Emails with Receipts
var label = GmailApp.getUserLabelByName(“Receipts”); // Change “Receipts” if your label is different
var threads = label.getThreads(0, 5); // Get the first 5 unread threads
// 2. Process Each Email
for (var i = 0; i < threads.length; i++) {
var messages = threads[i].getMessages();
for (var j = 0; j < messages.length; j++) {
var message = messages[j];
// 3. Extract Expense Details (This is a simplified example)
var body = message.getBody();
var amountMatch = body.match(/\$\d+\.\d{2}/); // Look for $ amounts
var dateMatch = body.match(/\d{2}\/\d{2}\/\d{4}/); // Look for MM/DD/YYYY dates
// 4. If found, add to Spreadsheet
if (amountMatch && dateMatch) {
var amount = amountMatch[0];
var date = dateMatch[0];
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
sheet.appendRow([date, amount, message.getSubject()]); // Adjust columns as needed
}
}
// 5. Mark Email as Read
threads[i].markRead();
}
}
What does this script mean?
We assume you have a Gmail label called “Receipts” where you store expense emails. The script fetches the first 5 unread threads (conversations) from that label. It loops through each thread and its individual messages (emails).
It then extracts expense details. Keep in mind that we’ve highly simplified this part. The script assumes a specific format for expenses in the email body (e.g., “$15.99” and “07/25/2024”).
Then, if the script finds both the amount and date, it gets the active spreadsheet and adds a new row with the extracted data (and the email subject for reference).
Finally, after processing, the script marks the email thread as read to avoid duplicates.
Note: Our script is simplified. The accuracy of the data depends entirely on the email format and the script’s ability to parse it correctly. Moreover, you might have additional columns in your actual expense tracker (e.g., category, payment method).
2. Generate Reports
You can also use Google Apps Script in Google Sheets to generate reports.
Think about those tedious reports you have to make—maybe sales summaries, project updates, or student grades. Apps Script can take the pain out of this process by pulling that data from a Google Sheet or other places.
Let’s say you track your weekly sales in a Google Sheet. Apps Script can:
- Grab the latest sales numbers from your sheet.
- Calculate total sales, sales by product, and maybe even growth compared to last week.
- Make a pie chart or line graph report and a summary.
- Email the report to your manager every Monday morning.
You set it up once, and then Google Apps Script does the rest – week after week!
Here is a script example, and we will explain it below.
function generateSalesReport() {
// Get the sales data
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“WeeklySales”);
var data = sheet.getDataRange().getValues();
// Calculate total sales and sales by product
var totalSales = 0;
var salesByProduct = {};
for (var i = 1; i < data.length; i++) { // Skip header row
var product = data[i][1];
var sales = data[i][2];
totalSales += sales;
salesByProduct[product] = (salesByProduct[product] || 0) + sales;
}
// Create the report
var report = “<h2>Weekly Sales Report</h2>\n\n”;
report += “<p>Total Sales: $” + totalSales.toFixed(2) + “</p>\n\n”;
report += “<h3>Sales by Product:</h3>\n\n”;
for (var product in salesByProduct) {
report += “<p>” + product + “: $” + salesByProduct[product].toFixed(2) + “</p>\n\n”;
}
// Send the email
var emailSubject = “Weekly Sales Report”;
var emailBody = report;
MailApp.sendEmail(“YOUR_EMAIL_ADDRESS”, emailSubject, “”, {htmlBody: emailBody}); // Include your email address
}
Recommended reading: Top 5 Google Sheets sales templates
What does this script mean?
The script fetches all the values from the “WeeklySales” sheet. It loops through the data and calculates total sales and sales per product. It then creates an HTML report with the calculated data. Finally, the script uses MailApp.sendEmail to send the report as an email.
MailApp.sendEmail
Here is a Google Sheet grouping all sales:
Here is what the total weekly sales report looks like in the email:
Note: This is a simplified script. Change “WeeklySales” if your sheet has a different name. Replace “[email protected]” with the actual recipient’s email address.
3. Clean and format data
Cleaning and formatting data is another way to use Apps Scripts in Google Sheets.
Imagine you have a messy spreadsheet filled with customer information. Maybe some names have extra spaces, dates are in different formats, or some cells have typos. Cleaning this up manually would be a real headache. This is where Apps Scripts come in to save the day.
You can use Google Apps Scripts to scan your data and find and fix specific issues, such as:
- Extra spaces: Automatically remove extra spaces before and after names.
- Incorrect formats: Change dates from “29/07/2024” to “July 29, 2024” so everything matches.
- Typos: If a product name is misspelled, the script can fix it.
You can also use Google Apps Scripts to organize information and combine or split data.
Let’s create a Javascript for this example.
Imagine you have a Google Sheet with customer orders. Each row has information like Order ID, Customer Name, Product, Quantity, and Order Date. You want to organize the orders by date, with the most recent orders at the top.
Our script could be something like this:
function sortSheetByDate() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var range = sheet.getRange(“A2:E26”); // Adjust the range to cover your data
// Get the data
var data = range.getValues();
// Check if dates are in Date object format and convert them to strings
data.forEach(function(row) {
if (Object.prototype.toString.call(row[4]) === ‘[object Date]’) {
row[4] = Utilities.formatDate(row[4], SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone(), ‘dd/MM/yyyy’);
}
});
// Parse the dates correctly and sort
data.sort(function(a, b) {
var dateA = new Date(a[4].split(‘/’).reverse().join(‘-‘));
var dateB = new Date(b[4].split(‘/’).reverse().join(‘-‘));
return dateB – dateA; // Sort from newest to oldest
});
// Set the sorted data back to the sheet
range.setValues(data);
}
What does this script mean?
The script finds the order of information you want to sort. It then carefully takes all the information from each order form – customer name, order date, etc. – and puts it into separate cells. This is where the magic happens! The script looks at each order date and finds which orders are newer and which are older.
Data with dates not in chronological order:
Data with dates in chronological order after using the script:
Essential Apps Script concepts to know and use in Google Sheets
1. Custom functions
Custom functions allow you to create your own functions that you can use directly in Google Sheets, just like the built-in functions (SUM, AVERAGE, etc.). These functions are written in JavaScript within Apps Script and can perform various tasks, from simple calculations to complex data manipulations.
Here is an example of a custom function:
function DOUBLE(input) {
return input * 2;
}
This custom function doubles numbers in a cell. After adding the custom function to the Script Editor, go back to your spreadsheet.
Let’s say cell A1 contains the number 580.
- Type =DOUBLE(A1) in cell B1.
=DOUBLE(A1)
- Google Sheets runs your function, giving it the value 1160 in cell B1.
- The function calculates 580 * 2 = 1160.
- Cell B1 displays the result: 1160.
Difference between a custom function and a general code you write in the Google Apps Script
The main difference between a custom function and any other code lies in how you use and call them within Google Sheets.
A custom function code must return a value, like:
return input * 2;.
A function cannot modify any part of the spreadsheet or trigger any other actions. It can only compute and return a result based on its inputs.
Moreover, to use a custom function, you must enter the =functionName() code into a cell on the spreadsheet, like:
=DOUBLE(A1)
2. Triggers
You also need to learn about triggers if you want to use Google Apps Script in Google Sheets.
Imagine you have a task in Google Sheets that should happen automatically, without you having to click any buttons. That’s where triggers come in! A trigger is like a little alarm clock you set up within your Apps Script code. It tells your script to wake up and run a specific part of your code when a particular event happens in your spreadsheet.
You can customize triggers to run your script when a sheet is opened, a cell is edited, a form is submitted, or even on a specific date and time.
You can also use triggers to keep your data fresh. For example, a trigger could automatically pull in the latest stock prices or weather information.
3. Properties and Methods
Properties and methods let you change how Google Sheets works. You can write scripts that automatically format cells or move data around. For example, instead of manually changing the color of hundreds of cells, your script can do it instantly.
Think of properties like the traits or attributes of an object. In Google Sheets, this could be:
- The background color of a cell:
cell.getBackground()
- The value inside a cell:
cell.getValue()
- The font size of the text:
cell.getFontSize()
You can find out a property’s current value or set it to change its appearance or behavior.
Methods, however, are like the verbs or actions that an object can perform. For example, a cell in Google Sheets can:
- Change its contents:
cell.setValue(“Hello!”)
- Clear itself completely:
cell.clearContent()
- Merge with neighboring cells:
cell.mergeAcross()
You “call” a method to make it happen. It’s like giving an instruction.
Here is a simple script example:
function formatImportantCells() {
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange(“A1:B10”); // Get a group of cells
var cells = range.getCells(); // Get individual cells
for (var i = 0; i < cells.length; i++) {
if (cells[i].getValue() > 100) { // Check the value
cells[i].setBackground(“yellow”); // Set a property (color)
}
}
}
This script checks the values in cells A1 through B10. If a value is greater than 100, the script turns the cell’s background yellow.
In short:
- Properties describe: They tell you about the current state of things.
- Methods do: They make things happen.
- Dot notation: You use a dot to access properties and methods (e.g., cell.getValue()).
cell.getValue()
4. Data types
Data types are another concept you need to learn to use Google Apps Script in Google Sheets.
Data types are labels that tell Apps Script what kind of information it’s dealing with. Just like you’d distinguish between apples and oranges, Apps Script needs to know if it’s working with numbers, text, dates, or other types.
But why do data types matter in Google Sheets?
There are at least three reasons:
- Correct Calculations: Imagine you’re adding up expenses. If Apps Script thinks your numbers are actually text, you’ll get weird results. Data types guarantee calculations happen accurately.
- Proper Formatting: You want your dates to look like dates and your phone numbers to behave like phone numbers. Data types help you control how things are displayed and used in your spreadsheet.
- Error Prevention: If you try to use a date as if it were a number, Apps Script might flag an error. Knowing the right data types helps you avoid these hiccups and write code that runs smoothly.
Let’s say you’re tracking sales and have a column in Google Sheets with the number of items sold. When you use Apps Script to calculate total sales, it needs to understand this column contains numbers, not just random characters.
For instance, the script below mentions the data type:
if (valueType === “number”) {
Here’s the breakdown:
- valueType: This variable stores the result of typeof value. The typeof operator is a JavaScript feature that directly tells you the data type of a variable.
- === “number”: This comparison checks if the valueType is strictly equal to the string “number”. If it is, the script knows it’s dealing with a numerical value and proceeds to add it to the totalSales.
We’ve just covered a few Apps Scripts concepts or terms. There are more, though, such as Apps Script Services, Error Handling, and Libraries.
Tips and best practices for Apps Script
1. Learn Javascript
Learning JavaScript gives you a solid foundation for understanding how Google Apps Script works. It becomes way easier to figure out code you find online, troubleshoot your own projects, and write more advanced scripts.
Once you’ve grasped the basics of JavaScript, you’ll manipulate data in really clever ways and interact with other services on the web.
Moreover, JavaScript is a widely used language, so your new skills won’t go to waste. You can apply them to other projects beyond Google Apps Script. For instance, you can build simple websites or even work on complex web development projects.
2. Write efficient and maintainable code
Your code is like a recipe, and a good recipe is:
- Efficient: It doesn’t waste ingredients or time. Efficient code runs faster and uses less of your Apps Script quota (the limit on how much your scripts can do).
- Maintainable: It’s easy to understand and update later. Maintainable code saves you headaches when you need to make changes down the road.
Here are a few additional tips for Google Apps Script:
- Don’t name your variables “x” or “temp.” Choose names that describe what they store. For example, instead of “data,” use “customerNames” or “orderDates.”
- Break your code into smaller, reusable functions. Each function should ideally perform one specific task. This will make your code more organized and easier to test.
- Things don’t always go as planned. Use “try…catch” blocks to gracefully catch errors, which prevents your script from crashing unexpectedly.
- If you find yourself writing the same code multiple times, create a function for it. This principle is called “Don’t Repeat Yourself” (DRY).
3. Debug smartly
In Google Apps Script, bugs are errors or unexpected behavior in your code. Debugging is the process of finding and correcting these issues.
When Apps Script encounters a problem, it usually displays an error message. This message is your first clue. It often tells you what went wrong. You’ll know if you used a variable that doesn’t exist, divided by zero, or something else. You’ll also know where it went wrong, like the line of code causing trouble.
Apps Script has a built-in debugger. It’s like a more powerful version of “console.log().” You can pause your code, step through it line by line, and inspect variables in real time.
Let’s say you have this code:
function calculateArea(length, width) {
var area = length * width;
console.log(“The area is: ” + area);
}
calculateArea(10,); // Notice the missing argument for width
You run it, and it crashes with an error: “TypeError: Cannot read property ‘*’ of undefined.” What’s happening?
- Read the error: It says you’re trying to use the ‘*’ (multiplication) operator on something undefined.
- Check the code: You realize you forgot to pass a value for the “width” argument.
Fix the error: Change the last line to:
calculateArea(10, 5);
Use Google Apps Scripts to enhance your Google Sheets
Congratulations! You’ve just unlocked the power of Apps Script within Google Sheets. In this article, we saw some practical use cases and shared script examples. You are now fully equipped to use Google Apps Script in Google Sheets.
By automating tasks, customizing functions, and even building interactive web applications, you’re well on your way to becoming a spreadsheet ninja. Keep learning, keep scripting, and keep pushing the boundaries of what’s possible in Google Sheets.
FAQ
How to add Script Editor to Google Sheets?
The Script Editor is built into Google Sheets. You don’t need to add it separately.
How to open the Script Editor in Google Sheets?
Go to Extensions > Apps Script. This will open the Script Editor in a new tab.
Where is the Script Editor in Google Sheets?
The Script Editor is not physically located within your Google Sheet. It opens in a separate tab or window when accessed through the Extensions menu. Go to Extensions > Apps Script.
